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.

'gistThat@mcpher.com :do not modify this line - see ramblings.mcpher.com for details: updated on 5/3/2014 6:30:52 PM : from manifest:7471153 gist https://gist.github.com/brucemcpherson/5102369/raw/cStringChunker.cls
' stringChunker class for VBA because string concat takes ages
Option Explicit
' v1.06  5102369
Private pContent As String
Private pSize As Long
' minimum amount to increment by each time
Const defaultChunkSize = 64
Public Property Get size() As Long
    ' this is how much content is real
    size = pSize
End Property
Public Property Get content() As String
    ' return the real part of the content
    If pSize > 0 Then
        content = getLeft(size)
    Else
        content = vbNullString
    End If
End Property
Public Property Get getLeft(howMany As Long) As String
    ' return the left part of the content
    ' c.getLeft(howmany) is equivalent to left(c.content,howmany), but avoids extra assignment
    getLeft = getMid(1, howMany)
End Property
Public Property Get getRight(howMany As Long) As String
    ' return the right part of the content
    ' c.getRight(howmany) is equivalent to right(c.content,howmany), but avoids extra assignment
    getRight = getMid(pSize - howMany + 1, howMany)
End Property
Public Property Get getMid(startPos As Long, Optional howMany As Long = -1) As String
    ' extract from content
    ' c.getMid(startPos,howmany) is equivalent to mid(c.content,startPos, howmany), but avoids extra assignment
    Dim n As Long
    Debug.Assert startPos > 0 And startPos <= pSize
    n = howMany
    If n = -1 Then
        n = pSize - startPos + 1
    End If
    n = minNumber(pSize - startPos + 1, n)
    If n > 0 Then
        getMid = Mid(pContent, startPos, n)
    Else
        getMid = vbNullString
    End If
End Property
Public Property Get self() As cStringChunker
    ' convenience for with in with
    Set self = Me
End Property
Public Function clear() As cStringChunker
    ' easy to clear out.. may as well keep the same buffer going
    pSize = 0
    Set clear = Me
End Function
Public Function uri(addstring As String) As cStringChunker
    Set uri = add(URLEncode(addstring))
End Function
Public Function toString() As String
    toString = content()
End Function
Public Function add(addstring As String) As cStringChunker
    Dim k As Long
    ' add some content to end
    k = Len(addstring)
    If k > 0 Then
        adjustSize (k)
    
        Mid(pContent, size + 1, k) = addstring
        pSize = size + k
    End If
    Set add = Me
End Function
Public Function addLine(addstring As String) As cStringChunker
    Set addLine = add(addstring).add(vbCrLf)
End Function
Public Function insert(Optional insertString As String = " ", _
                    Optional insertBefore As Long = 1) As cStringChunker
    'default position is at beginning, insert a space
    'c.insert("x",c.size+1) is equivalent to c.add("x")
    
    If insertBefore = pSize + 1 Then
        Set insert = add(insertString)
        
    Else
        ' 'todo .. how to handle programming errors?
        Debug.Assert insertBefore > 0 And insertBefore <= pSize
        
        ' regular string concatenation is better since there is overlap
        pContent = getLeft(insertBefore - 1) & insertString & getMid(insertBefore)
        pSize = Len(pContent)
        Set insert = Me
            
    End If
    Set insert = Me
End Function
Public Function overWrite(Optional overWriteString As String = " ", _
                    Optional overWriteAt As Long = 1) As cStringChunker
    'default position is at beginning, overwrite with a space
    Dim k As Long
    k = Len(overWriteString)
    If k > 0 Then
        ' 'todo .. how to handle programming errors?
        Debug.Assert overWriteAt >= 0
        '' we'll allow overwrite to extend past end, be greedy
        adjustSize (k)
        pSize = maxNumber(pSize, k + overWriteAt - 1)
        
        Mid(pContent, overWriteAt, k) = overWriteString
        
    End If
    Set overWrite = Me
End Function
                        
Public Function shift(Optional startPos As Long = 1, _
                Optional howManyChars As Long = 0, _
                Optional replaceWith As String = vbNullString) As cStringChunker
    ' shift by howmany chars .. negative= left, positive = right
    'TODO how to deal with programming errors? message, raise error, assert?
    Dim howMany As Long
    
    howMany = howManyChars
    If howMany = 0 Then
        howMany = Len(replaceWith)
    End If
        
    Debug.Assert howMany + startPos > 0
    Debug.Assert startPos <= pSize And startPos > 0
    
    ' make space
    If howMany <> 0 Then

        If howMany > 0 Then
        ' its a right shift, use insert
            Set shift = insert(Space(howMany), startPos)
        Else
            ' a left shift
            If startPos > 1 Then
                ' we can do an overwrite
                overWrite getMid(startPos + howMany, pSize - startPos + 1), startPos
                pSize = pSize + howMany
            End If
        
        End If
    End If
    
    Set shift = Me
End Function
Public Function chop(Optional n As Long = 1) As cStringChunker
    ' chop n charaters from end of content
    pSize = maxNumber(0, pSize - n)
    Set chop = Me
End Function
Public Function chopIf(t As String) As cStringChunker
    ' chop if its t
    Dim k As Long
    k = Len(t)
    If k <= pSize Then
        If getRight(k) = t Then
            chop (k)
        End If
    End If
    Set chopIf = Me
End Function
Public Function chopWhile(t As String) As cStringChunker
    ' chop if its t
    Dim k As Long, x As Long
    
    Set chopWhile = Me
    x = pSize
    While chopIf(t).size <> x
        x = pSize
    Wend

End Function
Private Function maxNumber(a As Long, b As Long) As Long
    If a > b Then
        maxNumber = a
    Else
        maxNumber = b
    End If
End Function
Private Function minNumber(a As Long, b As Long) As Long
    If a < b Then
        minNumber = a
    Else
        minNumber = b
    End If
End Function
Private Function adjustSize(needMore As Long) As cStringChunker
    Dim need As Long
    need = pSize + needMore
    If Len(pContent) < need Then
        pContent = pContent & Space(needMore + maxNumber(defaultChunkSize, Len(pContent)))
    End If
    Set adjustSize = Me
End Function
Private Sub class_initialize()
    pSize = 0
    pContent = Space(defaultChunkSize)
End Sub

 

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)

Happy Chunking. For more stuff like this, visit the Excel liberation site.

About brucemcp 225 Articles
I am a Google Developer Expert and decided to investigate Google Apps Script in my spare time. The more I investigated the more content I created so this site is extremely rich. Now, in 2019, a lot of things have disappeared or don’t work anymore due to Google having retired some stuff. I am however leaving things as is and where I came across some deprecated stuff, I have indicated it. I decided to write a book about it and to also create videos to teach developers who want to learn Google Apps Script. If you find the material contained in this site useful, you can support me by buying my books and or videos.

1 Comment

Comments are closed.