Speed Optimization Tips

If you are writing (not recording) macros that contain more than a few dozen lines of code and take more than a few seconds to execute, you can consider yourself a power macro programmer.   In this day of incredibly fast processors and massive amounts of RAM, hardware speed can usually overcome the sluggishness of inefficient or poorly written code.  But competent developers are never satisfied with this attitude, and will invest a little extra time making sure no processor cycles are wasted.

As your coding skills advance, and you take on bigger and bigger automation tasks in VBA, you will eventually get to the point where you need to optimize your code for speed.  This is especially true when your code will be used by other people.  If your users are forced to wait an unreasonable amount of time for a process to run, they may get annoyed, or start clicking around on Excel's menus or buttons to see what's going on.  This usually results in your macro running even slower, or worse: an Excel crash.

Presented here are a few strategies you can use to make sure you code runs quickly and your users are happy with the performance of your macros.

Tracking Execution Time
Say you have a complex Excel model with many specific methods (macros) that need to be run over the course of a user's interaction with the file.  If things appear to be running slowly, the first thing you need to do is figure out where the bottlenecks are.

A simple way to do this is to annotate your code with some debug feedback so you, as developer, can track the performance of the individual macros and functions.  This is done using the Debug.Print directive along with the Timer construct, as in the following code example:

Sub MyMethod()
  Dim tmr As Single
  tmr = Timer
  ... your code here ...
  Debug.Print "MyMethod took " & Timer - tmr & " seconds to run"
End Sub


If you put this code in each of your subroutines, you will quickly be able to see which ones took the longest, and you can focus your optimization efforts there.

Caching
John Carmack, master programmer and one of the founders of ID software (creators of the game Quake) was an early pioneer in the field of 3D rendering.  In the 90's when he was writing Quake, computers were running Pentium 1 processors and had 16 MB or ram on average.  In order for his game to be playable, John had to squeeze every last bit of performance from his code, and his most important strategy for this was Data Caching.  Caching refers to storing the data you need close at hand, so you don't have to go looking for it when you need it.

Think of a worker high on a construction scaffold.  He has his tools and supplies up there with him, so he doesn't have to climb down to the truck and get the next piece each time he does a bit of work.  Your Excel logic can be set up to act the same way - instead of searching for the piece of data you need on every iteration of a loop, find it and store it locally outside the looped code.

For example, this would be slow:


  For X = 1 to 30000
     Cells(x,1) = Range("MyVariable") + X
  Next


But this would be much faster, because we don't need to look up a Range variable 30,000 times:


  MyVar = Range("MyVariable")
  For X = 1 to 30000
     Cells(x,1) = MyVar + X
  Next


Keeping local references to cell values can be a big performance win in VBA, because there is a lot of overhead involved in going to the spreadsheet and extracting a value.  Any time you can remove this sort of lookup from inside a loop it's worth doing.

Screen Refresh and Recalculation
Another extremely helpful way to speed up your code is to turn off screen refresh and, if possible, workbook calculation.  Depending on what your code is doing, you may enjoy a 1,000 times speed improvement when suppressing screen updates while your macro executes.  Just be sure to turn it back on again after your macro is done.


Sub MyMethod()
 'turn off refresh and recalc
  Application.ScreenUpdating = False
  Application.Calculation = xlCalculationManual
  ... your code runs ...
 'turn it back on!
  Application.ScreenUpdating = True
  Application.Calculation = xlCalculationAutomatic
End Sub


Perceived Speed - providing progress feedback
Perhaps you have done everything possible to make sure your code is as snappy and efficient as possible, but it still takes several seconds to execute because of the sheer amount of data being manipulated.  To keep your users from dozing off or getting worried, you should provide some sort of feedback.  An easy way to do this is through the Excel status bar, and this works especially well when giving feedback on the progress of a loop, as in the following example:

  iTotal = 1000
  For idx = 1 to iTotal
     something happens...
    Application.StatusBar = "Running... "&Int((idx / iTotal)*100)&"% Complete"
  Next


Even though the speed of your code won't change, the users will actually think it is running faster because they can see that it's running, and how long it's going to take. Without this, they may wonder if your code has bogged down, and click the Launch button repeatedly - not good.

Conclusion
By taking some time to consider the experience your users are having while running your code, and following some of these strategies, you will be able to produce efficient macros that enhance the productivity of those who rely on your work.