vba - Using Pre-Made Stringbuilder Class

Question

I am trying to learn how to use stringbuilder functions in VBA, but am having trouble finding resources in VBA on how to use them. I could use some help to use a pre-made stringbuilder class.

I do know that each of these subs play some role in writing a final code. For example, I have seen other examples that use "string.append" but I am not sure if that is how it works in this case. I could use a little bit of insight into how to utilize this code. Please help!

The best way for me to understand is if someone can throw together a few lines of sample code using this example stringbuilder class. Thank you!!

Private m_arrBuffer
Private m_strDelimiter
Private Sub Class_Initialize()
    m_arrBuffer = Array()
    m_strDelimiter = “”
End Sub

Private Sub Class_Terminate()
    m_arrBuffer = Empty
End Sub

Public Property Get Delimiter()
    Delimiter = m_strDelimiter
End Property

Public Property Let Delimiter(strDelimiter)
    m_strDelimiter = strDelimiter
End Property

Public Sub Append(strValue)
    ReDim Preserve m_arrBuffer(UBound(m_arrBuffer) + 1)
    m_arrBuffer(UBound(m_arrBuffer)) = strValue
End Sub

Public Sub AppendLine(strValue)
    Me.Append strValue & vbCrLf
End Sub

Public Sub Compact()
    If Not Me.Delimiter = “” Then
        strOriginalDelimiter = Me.Delimiter
        Me.Delimiter = “”
    End If
    strTemp = Me.ToString
    m_arrBuffer = Array()
    Me.Append strTemp
    Me.Delimiter = strOriginalDelimiter
End Sub

Public Function ToArray()
    ToArray = m_arrBuffer
End Function

Public Function ToString()
    ToString = Join(m_arrBuffer, m_strDelimiter)
End Function

Answer 1

String concatenation (&) in VBA is notoriously slow, so often "stringbuilder" classes like this one are used to speed up the process if you have a large number of strings you need to combine.

The general idea is to use an Array() to store individual string components and then combine all strings once, when you need them, using the Join() function. The array is automatically resized as strings are added. Many use a "GrowBy" feature (although this one doesn't) to grow the array by a static size or factor as the array limit is reached. That can improve performance as well, since calling ReDim Preserve for every string insertion can take its toll.

To answer your question, pretend you needed to build a portion of an HTML file. You could use the shown string class like so:

Dim sb
Set sb = New StringBuilder      ' Guessing here. You haven't shown the class name.
sb.Append "some string"
sb.Append "another string"
sb.Append "a third string"
....
sb.Delimiter = "<br>"
myHtmlFile.Write sb.ToString()

Would print the following:

some string<br>another string<br>a third string

That's the general idea. Avoid the use of & as much as possible by using an array and you should see some significant performance improvements.