Rapid Application Development / Advanced Excel Development

Excel VBA Performance

An incredible amount can be achieved in Excel using native Excel technology only. Often though to get that extra bit of functionality or usability, VBA (or some other programming) is needed.

VBA Performance

There seems generally to be some confusion about what VBA is and what its uses and limitations are.

VBA is based on the old (no longer fully supported) Visual Basic 6.0. It is powerful, fast and flexible. It is partly compiled rather than purely interpreted as some people think.

All our experience suggests there is NO significant performance gain in moving from VBA to VB6, for example for COM/Automation add-ins, but it is sure to depend on exactly what you are doing. If anyone has an example of VB6 being faster than VBA that we can test contact us.

There are 2 aspects to performance in programming Excel

1. The cost of interacting with Excel (eg Range.Value)
2. The language performance itself (eg someValue = someValue + 1)

Performance in both of these areas is very similar between VBA and VB6 COM Add-ins. COM add-ins (DLLs) run in-process with Excel, just like VBA so the overhead of working with Excel objects is similar. VBA and VB6 are basically the same product and execution speed of the actual language seems to be similar. It is also possible to create an add-in that runs out of process using VB6. Stability is improved in this case as Excel is protected from problems in the add-in, but communicating between processes is expensive.

COM (Component Object Model), also known as Activex, is a powerful and flexible (and fairly safe) way for pieces of software to communicate with each other. Unfortunately its quite expensive performance wise. Excel, VBA, VB6 etc use this approach to communicate.

Excel has another interface. Its at a lower level and more complex to work with, but it is dramatically faster - its called the Excel C API (Application Programming Interface). Unfortunately VB cannot work with this interface, and generally C or C++ is used instead. C languages also execute many tasks significantly faster than VB languages, so this route has a double benefit. The main costs are increased development time, and increased (?) developer skills, certainly different skills anyway. One drawback of the C API approach is it that it is not as fault tolerant as the VB/COM approach, the lack of checking that improves performance, also means errors are likely to crash Excel.

VBA Performance

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.

Application.Calculation

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.

Application.Screenupdating

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.

Speed Improvements

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 Explicit
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
   Else
      'we are already in speed - don't do the settings again
   End If
End Sub


Public Sub unspeed()
   On Error Resume Next
   Application.ScreenUpdating = True
   Application.DisplayAlerts = True
   Application.EnableEvents = True
   If mbInSpeed Then
      Application.Calculation = mlCalcStatus
   Else
      'this shouldn't be happening - put calc to auto - safest mode
      Application.Calculation = xlCalculationAutomatic
   End If
   mbInSpeed = False
End Sub

 

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:

Please contact us with any questions.