• Skip to primary navigation
  • Skip to main content
  • Skip to primary sidebar

Tachytelic.net

  • Get in Touch
  • About Me

String.Format in VBScript or VBA

March 10, 2019 by Paulie Leave a Comment

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:

Image showing output of a string formatting function in VBScript

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:

Image showing output for string.format function for Excel / VBA

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:

Image showing use of a user defined function in Excel to replicate the functionality of the string.format method from C# in VBA

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

Filed Under: How To Tagged With: VBA, VBScript

Reader Interactions

Leave a Reply Cancel reply

Primary Sidebar

Link to my LinkedIn Profile
Buy me a coffee

Subscribe to Blog via Email

Enter your email address to subscribe to this blog and receive notifications of new posts by email.

Join 245 other subscribers.

Go to mobile version