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.

enter image description here

enter image description here

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.

• Phrancis: Nice to have you back, and nice question!