Similar to selecting ranges and objects to perform an action in the sheet, an explicit reference to the sheet also slows down processing. For example, the following code references the same cell (value) six times: Function Return Fee Slow() Select Case Range("I4") Case 1 Return Fee = Range("I4") * 10 Case 2 Return Fee = Range("I4") * 20 Case 3 Return Fee = Range("I4") * 30 Case 4 Return Fee = Range("I4") * 40 Case 5 Return Fee = Range("I4") * 50 End Select Msg Box Return Fee, vb OKOnly End Function At the very least, Return Fee Slow() makes two explicit references to I4.
It's not changing the value, it's using the value in a simple expression.
You can optimize your code by: In this article, I'll show you how to make simple changes to your code to optimize it for speed.
I'm using Excel 2016 on a Windows 10 64-bit system, but these tips will work in older versions.
The sheet reference is necessary only if you want to run the macro outside of the sheet (Divisions, in this case).But we have fancy new ribbon icons, so all is well, right?Next I learn that Excel uses SHA1 to encrypt spreadsheets instead of the previous method, which I assume was some sort of XOR with a one-character password.Recently a client of mine upgraded to 2013 with, let’s say, disasterous consequences.First, there’s the Single Document Interface (SDI). If you have two linked documents open and you calculate, both documents go into the calculation tree.The first thing I did was get stingy about my protection.Whereas before I was pretty liberal, protecting and unprotecting whenever I needed to, now I’m unprotecting and reprotecting exactly one time. As an experiment, I removed all protection from the templates and removed all the unprotecting and reprotecting from my code. That cuts it in half, although I can’t release this into the wild with no protection.To disable updates to the Status Bar, use the Display Status Bar property as follows: If your macro is analyzing a lot of data, consider setting the Calculation property to Manual while the macro is running. Calculation = xl Calculation Automatic Macros can trigger unnecessary event procedures. In short, you simply combine the two statements and delete the Select method and the Selection object.That way, the workbook won't recalculate unless you force it to by pressing F9. Display Status Bar = False 'macro code Application. For instance, entering a value into a cell triggers the Worksheet_Change event. The optimized code is more efficient and less prone to runtime errors.Specifically, built-in updating features and explicit references to the sheet or a range will slow down your code.Admittedly, with today's fast systems, simple macros won't always need optimization.