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
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 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.
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
Code is on github, and below. (to find out how to embed code in your blog like this, see Step by Step Gas Publisher)
For more about this, see the excel liberation blog
For help and more information join our forum, follow the blog or follow me on Twitter
bruce mcpherson is licensed under a Creative Commons Attribution-ShareAlike 4.0 International License. Based on a work at http://www.mcpher.com. Permissions beyond the scope of this license may be available at code use guidelines