Writing efficient VBA UDFs (Part 3) – Avoiding the VBE refresh bug – Updated

In Part 1 and Part 2 of Writing Efficient VBA UDFs I looked at some simple ways of changing the VBA code you write to make it run massively faster. In this post I look at a bug in Excel that slows down your UDFs and show you how to avoid it.

A few years back I was having trouble working out why VBA UDFs ran so much faster on my PC than on someone else’s system (I think it was Jan Karel Pieterse’s). Eventually I tracked it down to the fact that I had FastExcel installed. And further investigation showed that FastExcel made the VBA UDFs ran faster because the calculation was initiated by FastExcel using VBA.

It turned out that the underlying reason was a little bug in the EXCEL Visual Basic Editor (the VBE) : each formula that contains a UDF changes the VBE title bar to say “Running” whenever a UDF is being executed during the calculation, and then switches it back again when the UDF has finished.

The VBE Title Bar

The word [Running] gets inserted into the title bar after the name of the workbook. To make this happen requires the VBE to send a message to the Windows screen handler and the window containing the VBE gets refreshed:

This is a CPU intensive operation: on my system I created a volatile do-nothing UDF (using Excel 2010 32-bit):

Function VolUDF()
Application.Volatile
End Function

And entered it A1:A10000 (ten thousand cells). Then I resized the windows so that I could see both the Excel window and the VBE windows at the same time and triggered a calculation: I could see the VBE title bar flashing.

The calculation took 7.3 seconds.

Then I closed the VBE window and triggered another calculation.
This time it took 3.6 seconds.

Then I saved the workbook with the VBE window closed, closed Excel, reopened Excel and the workbook and triggered another calculation:
This time it took 1.1 seconds.

Then I initiated the calculation from VBA using Application.Calculate:
This time it took 0.058 seconds. That’s an improvement factor of 125.

And it still takes 0.058 seconds using Application.Calculate even with the VBE window open and visible.

Of course if you only have a few formulae using UDFs you will never notice this slowdown in calculation time, but on my fast system even 1000 formulas using UDFs will take an additional 0.7 seconds to calculate.

Bypassing the refresh bug

OK, so now you know what’s happening how do you avoid this problem? (By the way this bug is present in all versions of Excel from Excel 97 to Excel 2010 (both 32 bit and 64 bit).)

Trapping Calculation in Manual calculation mode.

If Excel is in Manual Calculation mode you can trap all the keystrokes that trigger a calculation, and initiate the calculation from your VBA code.

You need calculation subs and keytraps for

• Shift/F9 – Activesheet.Calculate
• F9 – Application.Calculate
• Ctrl/Alt/F9 – Application.CalculateFull
• Ctrl/Alt/Shift/F9 – Application.CalculateFullRebuild

In the ThisWorkbook module add the key trapping subs:

Private Sub Workbook_Open()
Application.OnKey "+{F9}", "SheetCalc"
Application.OnKey "{F9}", "ReCalc"
Application.OnKey "^%{F9}", "FullCalc"
Application.OnKey "+^%{F9}", "FullDependCalc"
End Sub

and in an ordinary module add the corresponding calculation subs:


Sub SheetCalc()
ActiveSheet.Calculate
End Sub
Sub ReCalc()
Application.Calculate
End Sub
Sub FullCalc()
Application.CalculateFull
End Sub
Sub FullDependCalc()
Application.CalculateFullRebuild
End Sub


(The equivalent of these procedures is built-in to the FastExcel add-in)

In Automatic Calculation Mode

Unfortunately I do not know of a way of bypassing the problem using VBA in Automatic Calculation Mode.
Closing the VBE window and reopening Excel will improve things substantially, but if you are creating workbooks with UDFs for other people to use you cannot control whether they will have the VBE open or not.

The only solution seems to be to use some non-vba technology such as a VB6 Automation Addin, .NET addin, or XLL addin, but each of these approaches has their own difficulties, which I plan to cover in a future post comparing Excel UDF Technologies.

Update

A useful idea about handling the problem in Automatic Mode comes from Francisco Aller Labandeira.

He suggests adding this code to the Workbook module.

Private Sub Workbook_SheetChange(ByVal Sh As Object, ByVal Target As Range)
If Application.Calculation = xlCalculationAutomatic Then
Application.Calculation = xlCalculationManual
Exit Sub
End If
Calculate
End Sub

This has the drawback that it won’t stop the first automatic calculation, but will catch subsequent calculations. You could also perhaps add some code to the Workbook BeforeSave event to switch back to Automatic if appropriate.

Conclusion:

If you need to use a large number (>1000) of formulas referencing VBA UDFs you will need to use Manual Calculation mode and add calculation key-trappers and handlers to your workbooks.