Archive for May, 2014

XL2013x64: Extremely slow VBA in copied cells

Posted in Computer, Microsoft Excel, Software, Spreadsheet, Uncategorized, VBA on May 25, 2014 by giorgiomcmlx

As mentioned before, the execution speed of VBA UDFs in copied cells is painfully slow on my XL2013x64 PC. It takes a second or so to calculate the UDFs in a copied block of only about 10 cells. Yet, after being copied, the re-calculation of exactly these UDFs happens instantly; for example, if an input value is changed.

Switching XL into manual re-calculation mode (thanks to John Beyers for this hint) completely removed this slow-down, but I do not like to develop sheets this way. Then, I happened to come across this post by Charles Williams on a VBE window refresh bug in XL. Though it had obviously been there, I had not noticed the flickering of the borderline around the block of selected cells during UDF calculations until I finished reading this post. So I started to try to copy a small block of cells to a location that made the selected block of cells completely disappear from the view pane. And voila, VBA executes at full speed in automatic calculation mode.

Thus, MS seem to have built a refresh bug similar to that in the VBE window now into the selection indication process of XL2013x64, too.