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 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 2013 (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.
Very interesting Charles, I’ve heard you talk about this before, but didn’t really understand what the bug was – now I do, thanks, it’s a good one to know about, and 125 x performance increase would be nice 😉
Thanks
Ross
Ross, its another reason why technologies such as Excel-DNA are fast compared to VBA.
According to Microsoft the problem is in the VBE code rather than Excel or VBA itself: so I am not exactly holding my breath waiting for a fix.
This was just amazing!
Is i possible to set the calculation mode to manual on open and
give the user a message that it is set to manual?
Depends exactly what you want to do. Excel sets its initial calculation mode from the first non-template, non-addinn, visible workbook opened. You can add some code to the workbook open to set Calc to Manual, but the workbook may be calculated before the open event fires.
Hi Charles. Here’s a potentially related question. I’m playing around with a simple UDF to generate random sample data, called randStr. As the name suggests, randStr(x) returns random alphanumeric characters of length x.
I noticed that if I enter this udf into all 1.4m rows of Excel using the vba line Range(“b1”).Resize(5000, 1).Formula = “=randStr(10)” then it takes around 9 seconds.
If I then try to enter it manually into all cells by using the Ctrl + Enter key combo, then it takes a hell of a lot longer. How long? Don’t know…I got tired of waiting, and restarted my PC.
Putting Excel into manual calc mode made no difference.
Any idea why that would be?
Here’s the UDF
Public Function randStr(ByRef strLength As Long) As String
Dim b() As Byte, keyArr() As Byte
Dim i As Long, lngUpper As Long
If Not CBool(strLength) Then Exit Function
Let keyArr = "0123456789ABCDEFGHIJKLMNOPQRSTUVWXYZabcdefghijklmnopqrstuvwxyz"
Let lngUpper = (UBound(keyArr) + 1) \ 2
ReDim b(1 To strLength * 2)
For i = 1 To UBound(b) Step 2
Let b(i) = keyArr(Int(lngUpper * Rnd + 1) * 2 - 2)
Next
Let randStr = b
End Function
I forgot to say that FastExcel tells me it calculated the range in 557.28 MS and automatic recalc was .546 seconds. I can’t remember what the difference is between these two metrics.
Aside 1: I wonder why one gets displayed as 557MS and the other gets displayed as .546 seconds.
Aside 2: By the way, I saw the numbers change twice when I pushed the FastExcel ‘Calcuate Selected Range’ button. Is this normal?
I think my next post will be about exploring Range.calculate.
In automatic mode RangeCalc recalculates the selected cells: this leaves then dirtied so if you are in automatic mode then this triggers a recalculation. Hence the cells will change twice.
The fastest method of filling a large range of cells with a formula is to use FillDown
Sub testing()
Application.Calculation = xlCalculationManual
Range(“B1”) = “=randStr(10)”
Range(“B1:B1048576”).FillDown
End Sub
Sub recalc()
Application.ScreenUpdating = False
Calculate
End Sub
Sub Testing takes 0.9 secs on my PC and Recalc takes 8 secs.
If you use Ctrl + enter you are NOT initiating the recalc from VBA so you hit the VBE refresh bug even in Manual mode.
Thanks for replying, Charles. So this definitely is the due to the VBE refresh bug, it seems. And while closing the VBE, resaving, opening, and entering the formula speeds things up, it still goes very very slow.
I guess the lesson is if you are going to fill large ranges with UDFs, insert them into the worksheet with VBA.
If you switch to Manual Calc mode and copy the formula then paste it intp an entire column you don’t hit the VBE refresh bug. looks to me like ctrl+enter actually does it cell-by-cell
Charles, it’s far too late to be playing with Excel in your neck of the woods. Step away from the keyboard, and crack open a Pinot!
Well that’s interesting. You learn something every day. Thanks for your help.
Pingback: XL2013x64: Extremely slow VBA in copied cells | carolomeetsbarolo
This was so helpful! I also added manual calculation settings on workbook’s BeforeSave and Open functions. This prevents from everything being recalculated constantly.
Moreover: I’m trying to optimize this function, its an arithmetic expression tan allows me to measure the distance between to numbers, relative to the sum of each other, without approaching to infinity if the denominator approaches zero.
Does anybody have any tips? I’m trying to get rid of the “if” mathematically, but I was not able yet to do so. So I started looking for ways to improve the code itself.
Function ErrorIntegral(NumA As Double, NumB As Double) As Double
If NumA = 0 And NumB = 0 Then
ErrorIntegral = 0
Exit Function
End If
If NumA * NumB < 0 Then
ErrorIntegral = 2 – Abs(Abs(NumA) – Abs(NumB)) * ((NumA ^ 2) + 4 * Abs(NumA * NumB) + (NumB ^ 2)) / (Abs(NumA) + Abs(NumB)) ^ 3
Else
ErrorIntegral = Abs(Abs(NumA) – Abs(NumB)) * ((NumA ^ 2) + 4 * Abs(NumA * NumB) + (NumB ^ 2)) / (Abs(NumA) + Abs(NumB)) ^ 3
End If
End Function
Nice article. It got me motivated to find a solution. I’d argue about the speed of the messages being sent to the VBE Window – most likely other state changes happend in the background.
For those interested in a quick workaround go to my GitHug project: https://github.com/cristianbuse/VBA-FastExcelUDFs, as long as you don’t mind having Application.CalculationInterruptKey = xlAnyKey.