Bringing The System.Text.StringBuilder Up To Lightning Speed In The VBA

Question

After having to swap the feature rich System.Text.StringBuilder for the very fast but very vanilla A lightning-fast StringBuilder, I decided it was time create a StringBuilder class of my own.

The concept is quite simple: As text is appended to the encapsulating class it is added to an Array. Whenever a method is called that needs to reference all the data, the Array of data is joined and appended to an internal System.Text.StringBuilder.

Speed

The main reason the I choose to use System.Text.StringBuilder and not try and replicate its functionality was the speed of its Replace method. Without a doubt, the StringBuilder.Replace() performance is amazing (+10x faster than VBA.Replace() and 5 MBs of data). The other reason is that no one in their right mind...would do all this just to format strings.

I cho0se not to add any speed timings between the technique the I am using and that of A lightning-fast StringBuilder to my post because they would be highly subjective. At worse my implementation is less than 10% slower; At best it is over 30% faster. Note: The performance of this class can be optimized by changing the size of the String Array Buffer.

Memory Usage and Capacity

All the StringBuilders mentioned here are memory hogs. The main problem is that when the data expands beyond the StringBuilders capacity, the StringBuilders doubles its buffer size. Unchecked, this limits the actual capacity to theoretically just over %50 percent of its potential. With some coercing both the System.Text.StringBuilder capacity can be increased. The trick is that you must ensure that the capacity is always greater than maximum data length.

Without modification my StringBuilder class capacity is roughly 500 MB of data on a 10 year old system using with 6 GB of memory running Office 360 32 bit. Having successfully appended a +1 GB string to the System.Text.StringBuilder, I know that this can be increased. Still this is a far cry from the 2 GB string size limit of 32 bit VBA.

Error Handling

What? What "Error Handling"? Exactly!!

I unsuccessfully tried to implement a System, by which, the length of both the Buffer strings and the actual StringBuilder string length would be tracked and EnsureCapacity() would be used to maximize the potential storage space. This System included Error Handlers which would trigger the data to be compacted when the size of the data in the Buffer throw an error. In any case, it didn't work so I gutted it.

References

This code is based off of both vba_stringbuilder:Class - and the actual System.Text.StringBuilder: Documentation. Most of the descriptions below are excerpts from that Documentation.

StringBuilder:Class

Option Explicit
' Based off vba_stringbuilder:Class - http://blog.livedoor.jp/midorityo/archives/50744656.html
Private Type Members
    AppendsBufferSize As Long
    Builder As Object
    index As Long
End Type
Private AppendsBuffer() As String
Private m As Members

Private Sub Class_Initialize()
    Const DEFAULT_APPENDSBUFFER_SIZE = 10000
    Set m.Builder = CreateObject("System.Text.StringBuilder")
    setAppendsBufferSize DEFAULT_APPENDSBUFFER_SIZE
    m.index = -1
End Sub

Public Property Get Capacity() As Long
    Capacity = m.Builder.Capacity
End Property

Public Property Let Capacity(ByVal Length As Long)
    m.Builder.Capacity = Length
End Property

Public Property Get Length() As Long
    Length = m.Builder.Length
End Property

Public Property Let Length(ByVal lLength As Long)
    m.Builder.Length = lLength
End Property

Public Function MaxCapacity() As Long
    MaxCapacity = m.Builder.MaxCapacity
End Function

Public Function Append(ByRef Text As String, Optional repeatCount As Long = 0) As StringBuilder
    m.index = m.index + 1
    AppendsBuffer(m.index) = Text
    If repeatCount > 0 Then Me.Append Text, (repeatCount - 1)
    If m.index = m.AppendsBufferSize Then Compact
    Set Append = Me
End Function

Public Function AppendFormat(ByVal format As Variant, ParamArray Args() As Variant) As StringBuilder
    Dim newArgs() As Variant
    Dim j As Long
    Me.Compact

    If IsArray(Args(0)) Then
        newArgs = Args(0)
    Else
        ReDim newArgs(UBound(Args))
        For j = 0 To UBound(Args)
            newArgs(j) = Args(j)
        Next
    End If

    m.Builder.AppendFormat_4 format, newArgs
    Set AppendFormat = Me
End Function

Public Function InsertFormat(ByVal format As Variant, startIndex As Long, repeatCount As Long, ParamArray Args() As Variant) As StringBuilder
    Dim newArgs() As Variant, sb As New StringBuilder
    Dim j As Long
    If IsArray(Args(0)) Then
        newArgs = Args(0)
    Else
        ReDim newArgs(UBound(Args))
        For j = 0 To UBound(Args)
            newArgs(j) = Args(j)
        Next
    End If

    sb.AppendFormat format, newArgs
    Me.Insert startIndex, sb.ToString
    Set InsertFormat = Me
End Function

Public Function AppendLine(Optional ByRef Text As String, Optional repeatCount As Long = 0) As StringBuilder
    m.index = m.index + 1
    AppendsBuffer(m.index) = Text & vbNewLine
    If repeatCount > 0 Then Me.AppendLine Text, (repeatCount - 1)
    If m.index = m.AppendsBufferSize Then Compact
    Set AppendLine = Me
End Function

Public Sub Clear()
    Me.Length = 0
End Sub

Public Sub Compact()
    If m.index > -1 Then
        m.Builder.Append_3 Join(AppendsBuffer, vbNullString)
        m.index = -1
    End If
    ReDim AppendsBuffer(m.AppendsBufferSize)
End Sub

Public Function EnsureCapacity(ByVal Length As Long) As StringBuilder
    m.Builder.EnsureCapacity Length
    Set EnsureCapacity = Me
End Function

Public Function Insert(startIndex As Long, Text As String, Optional repeatCount As Long = 0) As StringBuilder
    Me.Compact
    m.Builder.Insert_2 startIndex, Text
    If repeatCount > 0 Then Me.Insert startIndex, Text, (repeatCount - 1)
    Set Insert = Me
End Function

Public Function Remove(ByVal startIndex As Long, ByVal Length As Long) As StringBuilder
    Me.Compact
    m.Builder.Remove startIndex, Length
    Set Remove = Me
End Function

Public Function Replace(oldValue As String, newValue As String, Optional startIndex As Long = -1, Optional repeatCount As Long = 0) As StringBuilder
    Me.Compact

    If startIndex > -1 And repeatCount > 0 Then
        m.Builder.Replace_2 oldValue, newValue, startIndex, repeatCount
    Else
        m.Builder.Replace oldValue, newValue
    End If
    Set Replace = Me
End Function

Public Sub setAppendsBufferSize(ByVal Length As Long)
    m.AppendsBufferSize = Length
    Me.Compact
End Sub

Function ToString(Optional ByVal startIndex As Long = -1, Optional ByVal Length As Long = 0) As String
    Me.Compact

    If startIndex > -1 And Length > 0 Then
        ToString = m.Builder.ToString_2(startIndex, Length)
    Else
        ToString = m.Builder.ToString
    End If
End Function

AppendFormat - Test

Sub TestStringBuilderAppendFormat()
    Dim sb As New StringBuilder
    'Test AppendFormat Numbers: https://docs.microsoft.com/en-us/dotnet/standard/base-types/standard-numeric-format-strings
    sb.AppendLine "Using Standard Numeric Format Strings - Currency (""C"") Format Specifier"
    sb.AppendFormat "{0:C2}", 123.456
    sb.AppendLine "Currency With Alignment Arguments"
    sb.AppendLine "   Beginning Balance           Ending Balance"
    sb.AppendFormat "   {0,-28:C2}{1,14:C2}", 16305.32, 18794.16
    sb.AppendLine
    sb.AppendLine "The Decimal (""D"") Format Specifier"
    sb.AppendFormat "{0:D}", 12345
    sb.AppendLine
    sb.AppendLine "8 Digit Format Specifier"
    sb.AppendFormat "{0:D8}", 12345
    sb.AppendLine
    sb.AppendLine "The Percent (""P"") Format Specifier"
    sb.AppendFormat "{0:P}", 0.2468013
    sb.AppendLine "8 Digit Format Specifier"
    sb.AppendFormat "{0:P8}", 0.2468013
    sb.AppendLine
    sb.AppendLine "Custom Tests" & vbNewLine & String(50, "*")

    sb.AppendLine "AppendFormat: Dates"
    sb.AppendFormat "Day ## {0:dd}, Day Name {0:dddd}, Month ## {0:MM}, Month Name {0:MMMM}, YYYY {0:yyyy}", Date
    sb.InsertFormat "Date {0}", 0, 0, "Formats: "

    sb.AppendLine "AppendFormat: ParamArray"
    sb.AppendFormat "Jack {0} Jill {1} up {2} hill {3} fetch {4} pail {5} water {6} fell {7} And {8} his {9} And {10} came {11} after", "and", "Went", "the", "To", "a", "of", "Jack", "down", "broke", "crown,", "Jill", "tumbling"

    sb.AppendLine "AppendFormat: Array"
    sb.AppendFormat "Jack {0} Jill {1} up {2} hill {3} fetch {4} pail {5} water {6} fell {7} And {8} his {9} And {10} came {11} after", Array("and", "Went", "the", "To", "a", "of", "Jack", "down", "broke", "crown,", "Jill", "tumbling")
    Debug.Print sb.ToString

End Sub

Output:

Date Formats: Using Standard Numeric Format Strings - Currency ("C") Format Specifier
$123.46Currency With Alignment Arguments
   Beginning Balance           Ending Balance
   $16,305.32                      $18,794.16
The Decimal ("D") Format Specifier
12345
8 Digit Format Specifier
00012345
The Percent ("P") Format Specifier
24.68 %8 Digit Format Specifier
24.68013000 %
Custom Tests
**************************************************
AppendFormat: Dates
Day ## 07, Day Name Thursday, Month ## 06, Month Name June, YYYY 2018AppendFormat: ParamArray
Jack and Jill Went up the hill To fetch a pail of water Jack fell down And broke his crown, And Jill came tumbling afterAppendFormat: Array
Jack and Jill Went up the hill To fetch a pail of water Jack fell down And broke his crown, And Jill came tumbling after

I found this project interesting enough that I created a new account after deleting mine 1 week ago. I am looking for anything that I might have missed or any errors in my implementation, any ideas on how to maximize the capacity, and any strategies for handling errors.