High-Performance VBA String Concatenation with clsStringBuilder

Concatenating strings with ampersands in VBA is slow and inefficient:

Standard String Concatenation in VBA is Slow and Inefficient

A deep dive into the inner workings of string concatenation in VBA and why appending a single character to a long string can be so expensive.

No Longer SetMike Wolfe

Most of the time it's not a problem.  Users don't typically notice the few extra milliseconds it takes to do a handful of string concatenations the "slow" way.  In tight loops or when dealing with large strings, though, users do perceive the poor performance.

The typical way to speed up code in these situations is to use a string building class, such as Adam Waller's clsConcat.

clsConcat: Blazing-Fast String Building Performance in VBA

You don’t need to understand how to pre-allocate a memory buffer in VBA to take advantage of this blazing-fast string builder class.

No Longer SetMike Wolfe

clsBuildString: Another String-Building Class

In response to my earlier article on clsConcat, a reader wrote in with a link to a different string-building class module: clsBuildString, by Experts Exchange user aikimark:

VB/VBA Fast String Concatenate Class | Experts Exchange

Learn more about VB/VBA Fast String Concatenate Class from the expert community at Experts Exchange

Experts Exchangeaikimark

Toward the bottom of the article I linked above, there is a link you can click to download the class module and import it into your own code.

How It Works (via Claude-2-100k)

As with clsConcat, I used Claude-2-100k to document how the class module works.  I also asked it to do a side-by-side comparison between the two class modules. Here's our conversation:

[Mike]: Summarize the attached class module, clsBuildString

Here is a summary of the clsBuildString class module:

Overall purpose:
This class provides functionality to build up a string from multiple parts by appending strings together. It allows setting an initial string value, appending additional strings, and retrieving the final concatenated string.

Procedures:

Text Property Get:
Returns the final concatenated string. Joins all the appended strings together using the delimiter and returns the result.

Text Property Let:
Sets the initial/base string value by clearing any existing strings and adding the new value.

Add Method:
Appends an additional string to the internal collection.

Class_Initialize:
Called when the class is instantiated. Initializes the internal collection.

Class_Terminate:
Called when the class is destroyed. Clears the internal collection.

Delim Property Get:
Returns the current delimiter string.

Delim Property Let:
Sets the delimiter string to use when joining the strings.

Compare and contrast the clsBuildString class module with the attached class module, clsConcat, an alternative VBA string-building class.

Here is a comparison of the clsBuildString and clsConcat VBA string-building classes:

Similarities:

• Both classes allow building up strings by appending/concatenating multiple parts
• They provide methods to add strings, retrieve the final string, and clear the buffer

Differences:

String Storage:

• clsBuildString uses a VBA Collection object to store the string parts
• clsConcat uses a VBA array of strings to store the concatenated string across multiple "pages"

Memory Management:

• clsConcat manages string memory by preallocating "pages" and concatenating strings across pages to avoid reallocations
• clsBuildString relies on default VBA string memory management

Access Methods:

• clsBuildString has a Text property for get/set and an Add method
• clsConcat has Add, GetStr, and MidStr methods to access the string

Additional Features:

• clsConcat includes methods like Remove, RightStr, Length for more manipulation
• clsBuildString has Delim property to set custom delimiter

Performance:

• clsConcat may have better performance for large strings by managing memory
• clsBuildString performance may degrade for large numbers of Append operations

In summary, clsConcat implements more complex memory management and additional methods compared to clsBuildString's simpler Collection-based approach. clsConcat would be preferable for very large string concatenation while clsBuildString offers a simpler API.

Sample Usage

In my clsConcat article, I asked Claude to provide a usage sample.  Rather than prompt Claude for a usage sample for clsBuildString, I copied the TestConcat() function from the clsConcat article and manually adjusted it to use equivalent methods from clsBuildString.  

Here's the rewritten sample code.  I tested it to ensure it produces the same output as clsConcat, but I did not test its performance side-by-side:

Sub TestBuildString()

    With New clsBuildString
    
        ' Set page size
        'myConcat.SetPageSize 2000, 10  '(Not applicable to clsBuildString)
        
        ' Add strings
        .Add "Hello"
        .Add "World!"
        
        ' Add more strings
        .Add "This is a test."
        .Add "Here is another sentence."
        .Add "And one more."
        
        ' Check length
        Debug.Print Len(.Text)
        
        ' Get full string
        Debug.Print .Text
        
        ' Get substring
        Debug.Print Mid$(.Text, 10, 5)
        
        ' Get ending
        Debug.Print Right$(.Text, 10)
        
        ' Remove characters
        Debug.Print Left$(.Text, Len(.Text) - 10)
        
        ' Clear buffer
        .Text = vbNullString
        
        ' Add more strings
        .Add "New string"
        
        ' Get string again
        Debug.Print .Text
    
    End With

End Sub

A Call for Other String-Building Classes

Post links in the comments section if you have a preferred string-building class module.

If we get a few, it might be interesting to compare their performance side-by-side.  That said, writing good benchmarking code is one of those things that's a lot more difficult to do well than one would think.  There's also the added complication that clsConcat allows you to tweak its buffer sizes to fine-tune performance.  Trying to account for that feature while attempting to run an apples-to-apples comparison of the two classes is a fool's errand.  (That particular rabbit hole is one down which I have no desire to go tonight.)

Cover image created with Microsoft Designer