String.Format in VBScript or VBA 5/5 (1)

Because I often switch from writing C# back to VBScript or VBA. The lack of a string.format() method in both languages gets is annoying. So I wrote a small function which performs string substitutions in the similar way. It does not have all the formatting capability of the C# method.

String.Format Function for VBScript

Here is the function and example usage:

Option Explicit
Dim strTest : strTest = "Hello {0}, the weather today is {1} and {2}." & vbCrLf & "Have a {3} Day {0}!"
WScript.Echo StrFormat(strTest, Array("Paulie", "Cloudy", "Very Windy", "Great"))

Function StrFormat(FS, Args())
    Dim i
    StrFormat=FS
    For i = 0 To UBound(Args)
        StrFormat = Replace(StrFormat, "{" & i & "}", args(i))
    Next
End Function

This will produce output like this:

Of course in Classic ASP you would substitute wscript.echo for response.write, but the function would work just the same.

String.Format Function for VBA

You can use almost exactly the same function for VBA. Insert this function into a module:

Function StrFormat(FS As String, ParamArray Args()) As String
    Dim i As Integer
    StrFormat = FS
    For i = 0 To UBound(Args)
        StrFormat = Replace(StrFormat, "{" & i & "}", Args(i))
    Next
End Function

Then use VBA Code to call it like this:

The great thing about adding this as a User Defined function in Excel that you can use it directly in a cell formula like this:

=strFormat("Hello {0}, I hope you have a {1} day. {2} the weather is {3}", "Paulie", "Great", "Shame", "bad")

And you will get this output:

Hope this helps to make your code a bit easier to read. If it did, I would really appreciate it if you left a rating, or if you can see a way to improve the code, Please let me know in the comments.