Optimize Your VBA Code

Have you ever created a VBA module only to find that the subroutines and functions therein were incredibly slow?

There are a few things you want to look for in order to speed up your code.  Using a simply google search, you can find information about more advanced things like Storing data in a variant array (don't loop through cells!). However, I'm going to show you a few simpler techniques that I use when coding in VBA.

The first thing I do is use optimizing code. I have written two very simple subroutines which I call at the beginning and end of my code respectively.

First:

 Sub Optimize ()  
   'Run this before the slow code runs  
   Application.ScreenUpdating = False  
   Application.Calculation = xlCalculationManual  
   Application.DisplayAlerts = False  
   Application.AskToUpdateLinks = False  
 End Sub  

and then:

 Sub deOptimize()  
   'Stops Optimization, run before exiting sub or function  
   '(after code runs)  
   Application.ScreenUpdating = True  
   Application.Calculation = xlCalculationAutomatic  
   Application.DisplayAlerts = True  
   Application.AskToUpdateLinks = True  
 End Sub  

The important thing to note here is that you MUST run the deOptimize sub after  you are done. Otherwise, the user will run into some very annoying problems.

Another thing I do is look for points where my code calls for something else to happen. That could be running additional subroutines or functions, or calls to the windows API.

When you run code that is process intensive, Excel has a tendency to hang. A user might think that Excel is unresponsive and choose to end it.  Sometimes while Excel is hanging, it is also trying to complete another task in addition to your code.  (This can happen during long loops, for example.)

The solution is simple, but you must be careful with how often you use it. It is a simple line:

 DoEvents  

This will pause your code for a brief moment while the computer uses its processing power to process other things it is doing.  This will then open up more processing power for your code and can speed up certain processes. When used excessively, however, it can have the opposite effect and slow it down.

There have been big speed problems when it comes to Pivot Tables and Camera objects. If you need to use these things, you may not have a good way of speeding things up. Make sure you aren't calculating your Pivot Tables each time the smallest update occurs. Camera objects have no mercy, and it is better to avoid them and go with better "Dashboard" tools instead.