Friday, September 03, 2010

String.Format Equivalent for VBA

This is an implementation of the String.Format function in C# for use in VBA.  It allows you to pass in upto 5 arguments, but you could extend it to support as many as you need. 

Function StringFormat(str As String, Optional arg0, _
    Optional arg1, Optional arg2, Optional arg3, _
    Optional arg4, Optional arg5) As String
    
    Frmat = str
    Dim replaceStr As String
    Dim I As Integer
    I = 0
    While InStr(str, "{" & I & "}")
        Select Case I
            Case 0
                replaceStr = Nz(arg0, "")
            Case 1
                replaceStr = Nz(arg1, "")
            Case 2
                replaceStr = Nz(arg2, "")
            Case 3
                replaceStr = Nz(arg3, "")
            Case 4
                replaceStr = Nz(arg4, "")
            Case 5
                replaceStr = Nz(arg5, "")
            Case Else
                replaceStr = ""
        End Select
        
        Frmat = Replace(Frmat, "{" & I & "}", replaceStr)
        I = I + 1
    Wend
    StringFormat = Frmat
End Function
Here is an example of how to use this fuction:
MsgBox StringFormat("{0} Big {1}", "Hello", "World")
The resulting output is: "Hello Big World"

4 comments:

Mentalbug said...

Thanks, this was useful :)

On a quick note, this will work in Excel vba with a quick change:

Nz(arg0, "") --> IIf(IsNull(arg0), "", arg0)
(for each argument, obviously)

And at the end of the function, I think you're missing one little line:

StringFormat = Frmat


Cheers ;)

Paradigm said...

thanks for the feedback and I'm glad you found it useful.

Oscar said...
This comment has been removed by the author.
Oscar said...

Hi all.

An improved version with unlimited arguments:

Function StringFormat(source As String, ParamArray Args() As Variant) As String
Dim formatted As String
Dim assigned As String
Dim value As String
Dim index As Integer
' Initialize.
formatted = source
index = 0
' Assign Values.
While InStr(source, "{" & index & "}")
value = Args(index)
assigned = IIf(IsNull(value), "", value)
formatted = Replace(formatted, "{" & index & "}", assigned)
index = index + 1
Wend
StringFormat = formatted
End Function