Excel VBA: Using .NET string.Format in Excel

Microsoft ExcelLeave a Comment on Excel VBA: Using .NET string.Format in Excel

Excel VBA: Using .NET string.Format in Excel

The functions string.Format and StringBuilder.AppendFormat are two very usefull functions for formatting strings and increasing the readability of your .NET code. The Format function in VBA unfortunately works in a quite different way than the string.Format function in .NET. As far as I know there is no built-in function in VBA to acomplish the exactly same result as string.Format. In Excel VBA the functionality can be achieved the following way:

' Format string using the .NET way
Public Function StringFormat(ByVal strValue As String, ParamArray arrParames() As Variant) As String
    Dim i As Integer

    ' Replace parameters  
    For i = LBound(arrParames()) To UBound(arrParames())
        strValue = Replace(strValue, "{" & CStr(i) & "}", CStr(arrParames(i)))
    ' Get the value    
    StringFormat = strValue
End Function


Ulf Emsoy has long working experience in project management, software development and supply chain management.

Leave a Reply

Your email address will not be published. Required fields are marked *

Back To Top