Codematic Excel developers logo
Codematic spreadsheet-excel development image

Rapid Application Development / Advanced Excel Development

 
Products for Excel
Commercial Products:
  - Classic Ribbon
  - Alt-FileSearch
  - Password Remover
Spreadsheet Quality Products
Free Products
Excel Development
Excel Development Index
Excel VBA
- VBA IDE (editor)
- VBA Training
- VBA Best Practice
- VBA Performance
- COM Performance
- VBA Security
Excel and Databases
Excel and Pivot Tables
Excel Add-ins
Worksheet Functions
Excel and xlls
Excel (in)security
Excel testing
Excel and .net
Excel External Links
Excel Developer Types
Professional Excel Development
Excel 2007
Excel 2010
Excel Development Archive
Spreadsheet Services
Spreadsheet Development
Spreadsheet Migration
Spreadsheet Maintenance
Spreadsheet Review
Spreadsheet Management
Resources
Excel User Confs
Consultant Profile
Book Reviews
Links
Other
Site Map

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:

Public Sub mnu_ListFormulaLinks()

   On Error GoTo err_h
  
   If Not ActiveWorkbook Is Nothing Then
      speed
      'do your stuff
   Else
      MsgBox "Please open the workbook you wish to analyse", vbExclamation, gc_MsgBoxTitle
   End If
  
exit_proc:
   unspeed
   Exit Sub

err_h:
   MsgBox "Error " & Err.Number
   Resume exit_proc

End Sub

Please contact us with any questions.

 

Upcoming Events:

25 January 2012 - UK Excel Developer Conference - London


Products for sale:

AltFileSearch

Office 2007 FileSearch replacement logo

New information about the missing FileSearch feature in Office 2007 and details of our pragmatic solution (Current price GBP 30.00)


wsUnprotector

worksheet password remover logo

Instant Excel worksheet protection remover and password recovery (Current price GBP 15.00)


Classic Ribbon Tab

classic ribbon for office 2007 logo

Add Excel 97/2000/2002/2003 compatible menu structure to Excel 2007
(Current Price GBP 10.00)


 

Products coming soon:

Link Manager

(Find and control external links in Excel Workbooks)

Due by Q1 2111.

XLAnalyst Pro

(Excel VBA based spreadsheet auditing tool)

Due before the end of 2111.

  ;-)
This page was last reviewed on December 21, 2011

©Codematic Ltd 1999-2011