VBA speed in XL2013x64 on Win7x64

When playing with my newly acquired XL2013x64 and xNumbers on a i7-16GB-RAM PC running Win7x64prof , I experienced a dramatic loss of speed as compared to that I used to observe with XL2010x64 on my old i5-4GB-RAM PC running an identical OS. Some search on the Web proved that this phenomenon is wide-spread, see e.g. here.

For example, when copying the formula =xSqr(xAdd(xMult("2";B4;200);"";200);200) (in cell c4; b4:b105 contain 1,2,3,…,101) down a hundered times over existing data in the range c5:c105, it takes an unbelievable amount of 7 seconds before the results appear. Moreover, this time span is nearly independent from the multi-threading settings. On the contrary, erasing all the data in cells c5:c105 manually before copying down the formula makes the results appear instantly. This phenomenon is reproducible, and it does not depend sensitively on the kind of a formula, at least on my PCs.

So quite obviously, writing to a non-emtpy cell is the primary cause for the observed speed penalty of VBA in XL2013. At the moment, I guess XL2013 saves any content it finds in a cell that is to be overriden due to some built-in web collaboration features. I’ll have to figure out how to switch them off in case my guess is correct.


Comments are closed.