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.


Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s

%d bloggers like this: