VBA Blogs: Stop Reticulating Splines! - Part 1

Sometimes a Macro is still INCREDIBLY slow to run and you’re still not sure why.  Obviously, it is “Reticulating Splines”! But if the code is extensive, does a lot of number crunching and movement how do we speed it up?

Well there are a few ways that you can do this! Over the next few weeks we’ll look at a few tips on how you can do that.

No Calculations!

You might be “Calculating Llama Expectoration Trajectories” which require a lot of numbers being copied back and forth over the spreadsheet. Each time a new value is entered in a spreadsheet, Excel recalculates all the cells that refer to it. Same thing happens in VBA. Excel’s calculation engine is still running in the background and every time VBA writes to the Workbook. It is highly recommended that you turn OFF automatic calculations before running the steps in the Macro.

You could just turn it off in the Formula tab of the Ribbon but that’s a manual action. We could easily automate this using the command:

This will turn off automatic calculations! If you do need to Excel to recalculate Cell values for a later step in the program you can just simply add the line:

Top tip: This can be limited to even a specific Range or Worksheet as Calculate is also an object Method (e.g. Range(“A1”).Calculate).

But remember, you’ll need to turn it back on at the end!

For automatic calculation simply use:

Or if you prefer the mode Automatic (except for Data Tables) use:

However, given that we do not know where our macro may end up or what the end users preferred settings are, the best practice way would be to store the Calculation property of the Application and revert it at the end.

Next week we'll go over some more optimisation tricks so you can stop reticulating splines!