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.

 

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)

 

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.

 

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.

 

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 223 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

Leave a Reply

Your email address will not be published.


*


one + 14 =