Speed-up VBA in Excel

There are various means of adding some speed to your VBA code. Probably not the most obvious one, but try to keep track of types of variables, define them explicitly. There is an option, which you put at the beginning of the file which will help you with it

Option Explicit

Moreover, there are different functions versions which do the same. Most of the time you do not have to define explicit types of variables, VBA does that for you. But it takes time. Some of the functions have their faster versions, for example, Mid. It returns Variant, but if you use Mid$ you are using the function which returns String, and no converstion has to be done later. If you want to see how big difference does it make have a look at tests performed by Shamrock Software GmbH. In some cases Mid$ was twice faster than Mid. On the same site we read that, functions with a W (for wide) do not have to convert the VB-internal 16-bit Unicode characters to 8-bit ANSI codes or vice versa, they are a bit faster. Keep in mind that there are many function, which have their $ versions:

Chr$ ChrB$ *Command$ CurDir$ Date$ Dir$ Error$ Format$ Hex$ 
Input$ InputB$ LCase$ Left$ LeftB$ LTrim$ Mid$ MidB$ Oct$ 
Right$ RightB$ RTrim$ Space$ Str$ String$ Time$ Trim$ UCase$.

Another idea of speeding up the code execution is to turn off all the side code executions like updating screen, status bar, etc. A nice list of those was provided on Microsoft’s blog, see code snippets below. The idea is to save the state of sheet at the beginning of code execution, turn them off, and restore at the end. They do not mention what should happen in case of error, but you should keep that in mind and add a piece of code that would revert the previous state of Excel in case of error.

'' Get current state of various Excel settings;
'' put this at the beginning of your code
screenUpdateState = Application.ScreenUpdating
statusBarState = Application.DisplayStatusBar
calcState = Application.Calculation
eventsState = Application.EnableEvents
'' Note this is a sheet-level setting
displayPageBreakState = ActiveSheet.DisplayPageBreaks
'' Turn off some Excel functionality so your code runs faster
Application.ScreenUpdating = False
Application.DisplayStatusBar = False
Application.Calculation = xlCalculationManual
Application.EnableEvents = False
'' Note this is a sheet-level setting
ActiveSheet.DisplayPageBreaks = False
'' After your code runs, restore state;
'' put this at the end of your code
Application.ScreenUpdating = screenUpdateState
Application.DisplayStatusBar = statusBarState
Application.Calculation = calcState
Application.EnableEvents = eventsState
'' Note this is a sheet-level setting
ActiveSheet.DisplayPageBreaks = displayPageBreaksState

This is probably only a tip of an iceberg, but for sure it is a good place to start.

Leave a Reply