Whilst VBA's performance is very respectable, there are plenty of things that can have a surprising impact. Excel 2007 is especially vulnerable to some serious slowdowns if you don't take care how your code works.
Based on the principle that your application should do everything it needs to but no more, it is worth checking the settings of Excel and their potential impact on your code.
By default Excel is set to calculate automatically. To do this Excel uses its smart calc engine - it only calculate those cells that have been changed, or may be impacted by changes since it was last calculated. This is great, but it is worth bearing in mind the things that cause Excel to calculate:
- Entering/editing or deleting a cell value
- Pressing F9 to trigger a manual calculation
- Inserting or deleting rows or columns
- hiding or unhiding rows or columns
- setting or removing data filtering
- and a few others.
Be default Excel keeps the view you can see on the screen updated to reflect any changes. This is a good thing, otherwise you would not see the effects when you edit a worksheet. Roughly speaking screen updating happens whenever a calculate happens. And this can sometimes be a real drag on performance, especially if there are complex graphics or charts to redraw.
These two together encourage many Excel VBA devs to turn them off whilst their code runs to save a lot of time and wasted processing cycles. And the improvement can be significant. we have seen speed ups of over 100 times (eg it took several minutes before, after optimising it takes a couple of seconds). one potential problem though is that the settings are not returned to their state from before the code ran. Excel generally resets Screen updating when the VBA has finished so that is not so much of an issue.
The calculation setting is vital though. Many a serious error has been reported after someone Excel got inadvertently set to manual calc and the user did not notice when using the numbers.
State management code
Here are to procedure we use to manage Excel's state whilst our code runs
(copy and paste into its own module):
Option Private Module
Private mlCalcStatus As Long
Private mbInSpeed As Boolean
Public Sub speed()
On Error Resume Next
If Not mbInSpeed Then
Application.ScreenUpdating = False
Application.DisplayAlerts = False
Application.EnableEvents = False
mlCalcStatus = Application.Calculation
Application.Calculation = xlCalculationManual
mbInSpeed = True
Public Sub unspeed()
On Error Resume Next
Application.ScreenUpdating = True
Application.DisplayAlerts = True
Application.EnableEvents = True
If mbInSpeed Then
Application.Calculation = mlCalcStatus
Application.Calculation = xlCalculationAutomatic
mbInSpeed = False
You can download the code as a text file here and as a .bas module here.
Here is an example of how to use it from some commercial code:
Public Sub mnu_ListFormulaLinks()
On Error GoTo err_h
If Not ActiveWorkbook Is Nothing Then
MsgBox "Please open the workbook you wish to analyse", vbExclamation, gc_MsgBoxTitle
MsgBox "Error " & Err.Number