Strings and the garbage collector in VBA : optimizing string concatenation

I find myself doing a fair bit of VBA at the moment, It was a little adjustment coming back, but I suddenly noticed that string concatenations were taking a long time.

For example, this would seem to take exponentially longer to do as the loop got longer.

For i = 1 to something
  s = s & somestring
next i

I remember reading an article on Browser optimization (unfortunately I cant remember where now) where the subject of garbage collection and strings was discussed, in particular the behavior of the garbage collector when a certain amount of strings had been allocated. Looking into that in VBA, I tried a little test concatenating strings within a loop, and output the timing results, (using a timer I adapted from one I found in this MSDN article by Charles Williams)

Private Sub testString()
    Dim c As cProgressTimer, d As Double, s As String, a() As Double, _
        i As Long, r As Range, j As Long, _
        aString As String
    Set c = New cProgressTimer
    Const howMany = 100
    Const howInner = 80
    ReDim a(1 To howMany, 1 To 1)
    s = vbNullString
    With c
        For i = 1 To howMany
            d = .cMicroTimer
            aString = Space(2 * howMany – i)
            For j = 1 To howInner
                s = s & aString
            Next j
            a(i, 1) = .cMicroTimer – d
        Next i
    End With

    With Range(“timing!a2”)
        .Worksheet.Cells.ClearContents
        .Offset(-1).value = “string concat”
        .Resize(howMany).value = a
    End With
End Sub

This confirmed the problem. What should have been a straight line (repeating the same operation over and over), was actually taking longer and longer each iteration. Here’s how long it took to perform each of 100 iterations.

The reason was spelled out in this msdn article – aptly called ‘do not concatenate strings inside loops’, which advocates the use of the StringBuilder class. The problem is that it doesn’t exist in VBA, so I decided to write  a cStringChunker as below. You can find out more detail about how this works at Excel Liberation.

http://xliberation.com/p/gaspubcontainer.html?library=5102369&source=gist
Now here’s the same test module, this time using this class to concatenate strings. So not only is this faster, but it’s also consistent.

Here’s the same test slightly tweaked to  use the chunker instead of string concatenation.

Private Sub testChunker()
    Dim c As cProgressTimer, d As Double, s As String, a() As Double, _
        i As Long, r As Range, j As Long, chunker As cStringChunker, _
        aString As String
     
    Set c = New cProgressTimer
    Const howMany = 100
    Const howInner = 80
    ReDim a(1 To howMany, 1 To 1)
    Set chunker = New cStringChunker
    s = vbNullString
    With c
        For i = 1 To howMany
            d = .cMicroTimer
            aString = Space(2 * howMany – i)
            For j = 1 To howInner
                chunker.add aString
            Next j
            a(i, 1) = .cMicroTimer – d
        Next i
    End With
 
    With Range(“timing!b2”)
        .Resize(howMany).value = a
        .Offset(-1).value = “chunk”
    End With
 
 
End Sub

Here are the methods and properties for the cStringChunker class (see here to find out how create documentation like this automatically)
http://xliberation.com/googlecharts/googlecstringchunker.html
Happy Chunking. For more stuff like this, visit the Excel liberation site.

http://xliberation.com/b/but.html

Author: bm082975

1 thought on “Strings and the garbage collector in VBA : optimizing string concatenation

Leave a Reply

Your email address will not be published. Required fields are marked *