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:

VBE Title Bar with [Running}

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

Function VolUDF()
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()
End Sub
Sub ReCalc()
End Sub
Sub FullCalc()
End Sub
Sub FullDependCalc()
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.


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
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.


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.

This entry was posted in UDF, VBA. Bookmark the permalink.

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

  1. ross says:

    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😉


  2. fastexcel says:

    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.

  3. Dave says:

    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?

    • fastexcel says:

      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.

  4. Jeff Weir says:

    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)
    Let randStr = b
    End Function

  5. Jeff Weir says:

    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?

  6. fastexcel says:

    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.

  7. fastexcel says:

    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)”
    End Sub
    Sub recalc()
    Application.ScreenUpdating = False
    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.

  8. Jeff Weir says:

    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.

  9. fastexcel says:

    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

    • Jeff Weir says:

      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.

  10. Pingback: XL2013x64: Extremely slow VBA in copied cells | carolomeetsbarolo

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s