Excel 32

[glyn8]

I can create a long string variable in VBA by repeatedly appending short string variables like this:

   sMyShortString = "a"

   For i = 1 to 100

       sMyLongString = sMyLongString & sMyShortString

   Next i

 

This works nice and quick, but a weird thing happens when you get to a string length of around 270,000 - the rate at which the code runs suddenly drops by a factor of 10.

I have tested this on Excel 2010 and Excel 2016 on a variety of machines, and the performance hit always happens at the same string length.

Does anyone know why this happens? 

[mariushm]

When you declare a variable as string, the runtime will reserve some amount of memory in advance, like let's say 4KB - 32 KB

As you add text to the end of the string, the data is simply written to the memory address.

At some point, the lenght of what you want to put in the variable exceeds the amount of available bytes, so the the runtime has to "resize" the amount of memory reserved for that variable. So it allocates a new chunk of memory, that's a bit larger than previous amount, copies content of original variable into new location and kills the old variable and then writes what you want to write there.

so it should be

4 KB - 8 KB - 12 KB - 16 KB ...

But they probably optimized it, assuming most excel stuff will not go over some threshold, like 512KB . So instead of just increasing the amount by some small value like 4K (and cause a lot of memory allocations and copies), it's probably doing something like this :

4KB - 8 KB - 16 KB - 32 KB - 64 KB - 128 KB - 256 KB - 512 KB - 512+64 KB  - 512 + 64 KB + 64 KB - 512 + 64 + 64 + 64 .... and so on...

 

So at around at around 260k ( ~ 512 KB because most likely internally excel uses UTF-16, 2 bytes per character) things slow down because you lose the optimization - instead of doubling 512 to 1MB , then 1 MB to 2 MB and so on, the internals probably stop and switch to adding 4..64 KB and the array gets resized way more often and you have a lot more "memory copy"-es