VBA has a garbage collector that runs from time to time in order to free up memory that is no longer being used. in Objects and the garbage collector I showed how to teardown objects so they would become eligible for garbage collection.
s = vbnullstring for each c in collect s = s & c.text next c debug.print len(s) , s
- We keep re-copying an every increasing amount of text
- The garbage collector interrupts everything to do its work according to some rule, and/or we gradually use up memory.
This chart shows 100 iterations of a process similar to the above. See how each iteration takes longer than the previous one. I ran this a few times and always got the same kind of pattern. I suspect the little spikes are the garbage collection kicking in, since they are followed by a short relief period.
This can have a very significant effect on run times if the loops are large and the strings long.
The StringBuilder class
The cStringChunker timings – shown in red- barely register on this chart against the regular concatenation method.
Aside from being orders of magnitude faster , you can see that cStringChunker holds pretty consistent performance regardless of the string being concatenated.
You’ll see just a couple of red blips – this is when the cache it uses needs extending, and its possible that the garbage collector pays a visit.
How does it work
You can also use Mid$ on the left hand side of an assignment:
Dim s As String s = "abcdef" Debug.Print s Mid$(s, 2, 3) = "xxx" Debug.Print s Mid$(s, 3, 1) = "abcdefgh" Debug.Print s Mid$(s, 2, 3) = "y" Debug.Print s abcdef axxxef axaxef ayaxef
Notice that when Mid$ is on the left it doesn’t change the total length of the string it just replaces the specified number of characters. If the right hand side specifies fewer characters than are asked for, then only that number of characters is replaced; if it specifies more, only the number asked for is used.
dim chunk as cStringChunker set chunk = new cStringChunker for each c in collect chunk.add c.text next c debug.print chunk.size, chunk.content
dim s as string s = vbnullstring for each c in collect if s <> vbnullstring then s = s & "," s = s & c.text next c debug.print s, len(s)
cStringChunker has a .chop(n) method, where n (default 1) characters will be discarded from the end of the string. Note that it also returns itself from the add and chop operations so that multiple methods can be added together.
dim chunk as cStringChunker set chunk = new cStringChunker for each c in collect chunk.add (c.text).add(",") next c debug.print chunk.size, chunk.chop.content