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.
There's another problem related to strings. When you concatenate a string, it creates a new copy of the concatenated versions.
Consider this loop
Each time that s is appended to, a new version of s gets created by copying the old s and and the next text to a new area of memory. That leaves the old s hanging around, waiting for the garbage collector to arrive to free up the memory. So 2 bad things are happening
In VB there is a StringBuilder class which allegedly deals with this problem. It doesn't exist in VBA, so I created something similar with i've called the cStringChunker. Here is the result of the two methods together, with the StringChunker in red.
To make this possible, you have to be able to add characters in place to a string to encourage it not to replicate. I found the key to this in this wikibook article, from which I quote this.....
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.
So Mid() can be on the left. And it replaces characters in place inside a string. Therefore if you always have a string big enough to take the concatenation required, you will never need to make a new copy of it. Here is the code rewritten using the
cStringChunker which is based on that principle.
A cache is maintained into which the added string is inserted. If the cache needs to be extended, it extends first by a modest amount, then increasing amounts depending on the current content size. That means it only very occassionally needs to go off and extend itself, avoiding the garbage collector's attention and unecessary copying, yet keeping the allocation modest for smaller string operations.
With such a class we can simplify some common string things, taking advantage of the efficiency of 'not having to make a copy'. Let's take this example - a common problem - making a comma separated list
One way to do it, would be this, but as we've already established, it would keep generating bigger and bigger versions of s. We also have that pesky IF. And what if the first element was blank ? the result would have too few commas.
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.
For more on this topic , see the excel liberation blog
For help and more information join our forum, follow the blog, follow me on twitter
Services > Desktop Liberation - the definitive resource for Google Apps Script and Microsoft Office automation > Optimization >