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:
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 ;)
thanks for the feedback and I'm glad you found it useful.
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
Post a Comment