Thursday, September 23, 2010

Tuesday, September 21, 2010

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"