As you probably know, the “&” operator is rather slow, especially with long strings. When you have to repeatedly append chucks of characters to the same variable, you can speed up your code using a simple trick based on the Mid$ command. The idea is that you pre-allocate a buffer longenough to accomodate for the result of your operation. Here’s an example of this technique.
Suppose you want to create a string given by appending the first 10000integers, e.g. “1 2 3 4 5 6 7 … 9999 10000”. This is the easiest way to do so:
res = ""For i = 1 to 10000: res = res & Str(i): Next
The problem with the standard approach is that the res variable is reallocated 10000 times. This is a better way to reach the same result:
Dim res As StringDim i As LongDim index As Long' prepare a buffer long enoughres = Space(90000)' this points to where we want to insert the stringindex = 1' here we goFor i = 1 to 10000 substr = Str(i) length = Len(substr) ' stuff the string inside the result variable Mid$(res, index, length) = substr ' advance index index = index + lengthNext' drop extra charsres = Left$(res, index - 1)
The standard code takes 2.2 seconds on my 333 MHz system computer, while the smart way takes only 0.08 seconds, so it’s 25+ times faster.