SOLUTION - Objective-C stringWithFormat as a function for VBA

[AD_Taylor]

Firstly this is a solution not a request for assistance, so please treat it accordingly.

One thing I find myself doing a lot in VBA is making a long string out of other shorter strings and integers. This was normally for things like message boxes (MsgBox) or generating a dynamic formula.

I used to do this the long (ish) way by simply concatenating the various parts of the string required, together with the variables I had. So I'd normally end up with something similar to this:

Sub quickMsgBox()
    Dim unitName            As String
    Dim unitsShipped        As Integer
    Dim unitsAvailable      As Integer
    
    unitName = "Oven Gloves"
    unitsShipped = 12
    unitsAvailable = 123
    
    Dim msgBoxText          As String
    msgBoxText = "For the unit " & unitName & " there are " & unitsShipped & " units shipped and " & unitsAvailable & " units available." & vbCrLf & _
                    "Percentage of units shipped = " & Format(unitsShipped / unitsAvailable * 100, "0.00") & "%"
                    
    MsgBox msgBoxText
    
    ' The above concatenation makes this string:
    
    ' For the unit Oven Gloves there are 12 units shipped and 123 units available.
    ' Percentage of units shipped = 9.76%
End Sub

Which as I'm sure you can tell is very messy and difficult to understand. If even one quotation mark or ampersand is missing the whole thing doesn't work and it can be a pain tracking it down.

So when I started learning the Objective-C language I was very happy to see a method called stringWithFormat:. It allows you to specify a string peppered with codes such as "%@", that are sort of like placeholders for values. You place these where you'd like the variable values to go and then specify the required variables, in order, at the end.

Suddenly that example above becomes:

Sub quickMsgBox()
    Dim unitName            As String
    Dim unitsShipped        As Integer
    Dim unitsAvailable      As Integer
    
    unitName = "Oven Gloves"
    unitsShipped = 12
    unitsAvailable = 123
    
    Dim msgBoxText          As String
    msgBoxText = stringWithFormat("For the unit %@ there are %@ units shipped and %@ units available." & vbCrLf & _
                                    "Percentage of units shipped = %@%%", _
                                    unitName, _
                                    unitsShipped, _
                                    unitsAvailable, _
                                    Format(unitsShipped / unitsAvailable * 100, "0.00"))
    MsgBox msgBoxText
    
    ' The above stringWithFormat call makes this string:
    
    ' For the unit Oven Gloves there are 12 units shipped and 123 units available.
    ' Percentage of units shipped = 9.76%
End Sub

This, in my opinion, is much easier to read and understand what is the text and what parts are the variable values.

My implementation of this is below. If you'd like any changes made or anything added please let me know and I'll see what I can do. Please feel free to use this in your own projects and I hope that it helps!

stringWithFormat: Implementation for VBA

Public Function stringWithFormat(formatString As String, ParamArray params() As Variant) As Variant
    ' VBA Implementation of the stringWithFormat: , as used in Objective-C's NSString class
    ' Written by Adam Taylor // May 2013
    
    ' Creates a string from a formatted template string by substituting the remaining argument values
    
    ' ARGUMENTS (1 or more)
    '   formatString (Format String - Required)
    '       A string including the text "%@" at least once.
    '       E.g.  "Hello my name is %@ %@."
    
    '   params (Parameters - Optional)
    '       Specify arguments to subsitiute in place of the "%@" identifiers
    '       E.g. "John", "Smith"
    
    ' REMARKS
    '   To use an actual percent symbol use "%%".
    '   This results in the output in the overall string "%".
    
    '   You can specify more or less parameters than you have "%@" identifiers in your format string.
    '   If you specify more parameters these will not be included in the returned string.
    '   If you specify less, then the returned string will contain the unused "%@" identifiers.
    '   If you specify zero parameters, the returned string will be equal to the format string
    
    Dim sWF                 As String
    Const formatStringId    As String = "%@"
    
    Dim paramType           As String
    Dim currentString       As String
    Dim formatStringIndex   As Integer
    Dim i                   As Integer  'Loop Counter
    
    sWF = formatString
    If InStr(sWF, formatStringId) = 0 Then
        stringWithFormat = CVErr(xlErrNA)
        Exit Function
    End If
    
    For i = LBound(params) To UBound(params)
        If InStr(sWF, formatStringId) = 0 Then
            stringWithFormat = sWF
            Exit Function
        End If
        
        paramType = TypeName(params(i))
        
        Select Case paramType
            Case "String"
                currentString = params(i)
            Case "Boolean", "Integer", "Long", "LongLong", "LongPtr", "Single", "Double", "Date"
                currentString = CStr(params(i))
            Case "Range"
                currentString = CStr(params(i).Cells(1, 1).Value)
            Case "Worksheet", "Workbook"
                currentString = CStr(params(i).Name)
            Case Else
                stringWithFormat = CVErr(xlErrNA)
                Exit Function
        End Select
        
        formatStringIndex = InStr(sWF, formatStringId)
        sWF = replaceCharactersAtIndexWithString(sWF, formatStringIndex, formatStringIndex + Len(formatStringId), currentString)
    Next i
    
    sWF = Replace(sWF, "%%", "%")
    stringWithFormat = sWF
End Function


Public Function replaceCharactersAtIndexWithString(initialString As String, startIndex As Integer, endIndex As Integer, replaceString As String) As Variant
    ' VBA Implementation of the replaceCharactersInRange:withString: , as used in Objective-C's NSMutableString class
    ' Written by Adam Taylor // May 2013
    
    ' Replaces the characters in a string, between start and end indexes, with the string specified
    
    ' ARGUMENTS (4)
    '   initialString (Initial String - Required)
    '       The initial string to search
    
    '   startIndex (Start Index - Required)
    '       The position in the initial string at which to start replacing
    
    '   endIndex (End Index - Required)
    '       The position in the initial string at which to finish replacing
    
    '   replaceString (Replace String - Required)
    '       The string to insert into the range of characters specified by the the start and end indexes
    
    If startIndex < 0 Or endIndex < 0 Or _
        startIndex >= Len(initialString) Or _
        endIndex > Len(initialString) Then
        
        replaceCharactersAtIndexWithString = CVErr(xlErrNA)
    End If
    
    replaceCharactersAtIndexWithString = Left(initialString, startIndex - 1) & replaceString & Right(initialString, Len(initialString) - endIndex + 1)
End Function