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

Tachytelic.net

  • Sco Openserver
    • Sco Unix Support
    • SCO Openserver Installation ISOs
    • SCO Openserver Binaries
    • Add a Hard Drive to an Openserver 5 Virtual Machine
    • Install SCO Vision SQL-Retriever ODBC Driver on Windows 10
    • License Expired on Virtual SCO Openserver Installation
    • How to reset the root password on SCO Openserver 5
  • Scripting
    • PowerShell
      • Add leading zeros
      • Check if a File Exists
      • Grep with Powershell
      • Create Environment Variables
      • Test for open Ports
      • Append to a Text File
    • VBScript
      • Check if a File Exists
      • lpad and rpad functions
      • Windows Update E-Mail Notification
  • Office 365
    • Connect to Office 365 with PowerShell
    • Add or remove an email alias using Powershell
    • Change Primary email address of Active Directory user
    • How to hide an AD user from the Global Address List
    • How to hide mail contacts from the Global Address List
    • Change the primary email address for an account with PowerShell
    • Change Primary email address of an AD User
    • Grant a single user access to access to all calendars
    • Forward email to an external address using Powershell
    • Convert shared mailbox to user mailbox with Powershell
  • Get in Touch
  • About Me
    • Privacy Policy

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: Classic ASP, VBA, VBScript

Reader Interactions

Leave a Reply Cancel reply

Primary Sidebar

Link to my LinkedIn Profile

Subscribe to Blog via Email

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

Join 107 other subscribers.

Go to mobile version