## Archive for January, 2012

### Writing Efficient UDFs Part 9 – An Example – Updated

January 31, 2012

Pedro wants to know how to speed up his UDF, which needs to calculate results for 35040 cells the minimum difference between the cell and a column of values of unknown length.

### Pedro’s UDF

```Function MinofDiff(r1 As Long) As Variant Dim r2 As Range Dim TempDif As Variant Dim TempDif1 As Variant Dim j As Long Dim LastRow As Long On Error GoTo FuncFail If r1 = 0 Then GoTo skip With Sheets("Dados") LastRow = .Cells(.Rows.Count, "P").End(xlUp).Row Set r2 = .Range("P8", "P" & LastRow) End With TempDif1 = Application.Max(r2) For j = 1 To LastRow – 7 If r1 >= r2(j) Then TempDif = r1 – r2(j) Else TempDif = r1 End If MinofDiff = Application.Min(TempDif, TempDif1) TempDif1 = MinofDiff Next j skip: Exit Function FuncFail: MinofDiff = CVErr(xlErrNA) End Function ```
There is a fundamental problem with Pedro’s UDF: it is referencing a range in column P without passing it in as a parameter, so if anything changes in column P the UDF could give the wrong answer because Excel will not recalculate it. Pedro has done this so that the UDF can dynamically adjust to the number of entries in column P.

On test data with 60000 entries in column P 20 calls to the UDF take 18.5 seconds on my laptop, so 34K calls would take about 9 hours to calculate! So why is it so slow?

• Every time the function is called (35K times) it finds the last row and the MAX value in column P: but this only needs to be done once.
• 35040 calls will hit the VBE refresh slowdown bug: so we need to bypass that.
• The For loop is referencing each cell value in column P (using R2(j) ) twice. Each reference to a cell is slow because there is a large overhead for each call out to the Excel object model.
• The UDF uses Worksheetfunction.Min to find out which of 2 values is smaller: its much quicker to compare the values using VBA If than invoking a worksheet function.

### The revised UDF

To solve the fundamental problem with the UDF I will pass it an additional parameter: a whole column reference to column P. Then the UDF can resize the range to the last cell containing data. (Another alternative would be to create a Dynamic Named Range for column P and pass that as a parameter.

To solve the first 2 slowdown problems the UDF will be made into an array formula UDF that returns an array of 35040 results.

To avoid referencing each cell in column P twice inside the loop, the UDF will get all the values from column P once, into a variant array and then loop on the variant array.
``` Function MinofDiff2(R1 As Range, R2 As Range) As Variant Dim R2Used As Range Dim vArr2 As Variant Dim vArr1 As Variant Dim vOut() As Double Dim TempDif As Double Dim TempDif1 As Double Dim D1 As Double Dim D2 As Double Dim TMax As Double Dim j1 As Long Dim j2 As Long Dim LastRow As Long ' On Error GoTo FuncFail ' ' handle full column ' LastRow = R2.Cells(R2.Rows.Count, 1).End(xlUp).Row Set R2Used = R2.Resize(LastRow - 7, 1).Offset(7, 0) ' ' get values into arrays ' vArr2 = R2Used.Value2 vArr1 = R1.Value2 ' ' find max ' TMax = Application.Max(R2Used) ' ' set output array to same size as R1 ' ReDim vOut(1 To UBound(vArr1), 1) ' ' loop on R1 ' For j1 = 1 To UBound(vArr1) TempDif1 = TMax D1 = vArr1(j1, 1) ' ' loop on R2 ' For j2 = 1 To (LastRow - 7) D2 = vArr2(j2, 1) If D1 >= D2 Then TempDif = D1 - D2 Else TempDif = D1 End If If TempDif < TempDif1 Then vOut(j1, 1) = TempDif Else vOut(j1, 1) = TempDif1 End If TempDif1 = vOut(j1, 1) Next j2 Next j1 MinofDiff2 = vOut skip: Exit Function FuncFail: MinofDiff2 = CVErr(xlErrNA) End Function ```

Because this is an array function you need to select the 35040 cells that you want to contain the answer, then type the formula into the formula bar =MinofDiff2(A1:A35040,P:P) and then press Ctrl/Shift/Enter to enter the formula as an array formula into the 35040 cells.

This revised UDF takes .222 seconds for 20 values, and completes the 35040 UDF calculations in 6.25 minutes, a speedup factor of over 80.

### Updated with Harlan Grove’s suggestions

Harlan Grove has pointed out several ways of speeding up the UDF. Here is a revised version implementing most of his suggestions. It is about 17% faster than my original version.

``` Function MinofDiff3(R1 As Range, R2 As Range) As Variant Dim R2Used As Range Dim vArr2 As Variant Dim vArr1 As Variant Dim vOut() As Double Dim TempDif As Double Dim TempDif1 As Double Dim D1 As Double Dim D2 As Double Dim TMax As Double Dim TMin As Double Dim j1 As Long Dim j2 As Long Dim LastRow As Long ' On Error GoTo FuncFail ' ' handle full column ' LastRow = R2.Cells(R2.Rows.Count, 1).End(xlUp).Row - 7 Set R2Used = R2.Resize(LastRow, 1).Offset(7, 0) ' ' get values into arrays ' vArr2 = R2Used.Value2 vArr1 = R1.Value2 ' ' find max & Min ' TMax = Application.Max(R2Used) TMin = Application.Min(R2Used) ' ' set output array to same size as R1 ' ReDim vOut(1 To UBound(vArr1), 1) ' ' loop on R1 ' For j1 = 1 To UBound(vArr1) TempDif1 = TMax D1 = vArr1(j1, 1) TempDif = D1 - TMax If D1 > TMax Then If TempDif < TMax Then vOut(j1, 1) = TempDif Else vOut(j1, 1) = TMax End If Else If D1 < TMin Then vOut(j1, 1) = D1 Else ' ' loop on R2 ' For j2 = 1 To LastRow D2 = vArr2(j2, 1) If D1 >= D2 Then TempDif = D1 - D2 Else TempDif = D1 End If If TempDif < TempDif1 Then TempDif1 = TempDif vOut(j1, 1) = TempDif1 Next j2 End If End If Next j1 MinofDiff3 = vOut skip: Exit Function FuncFail: MinofDiff3 = CVErr(xlErrNA) End Function ```

Harlan also points out that a version using QuickSort to sort R2 and Binary Search instead of the loop would be an order of magnitude faster!

### Excel 2010 Tables/Listobject: slow update and how to bypass

January 30, 2012

There was an interesting post on Stack Overflow recently about a performance problem when updating Excel 2007/2010 Tables/Listobjects. Certainly something strange is going on!

## Duplicating the Problem

1. Create a table by selecting a few cells (I used A1:A3) on a sheet (Sheet1) and using Format as Table on the Home tab

2. On a different sheet (Sheet2) create a few thousand (I used 10000) of the simplest formula you can think of (I used =”A”)

3. Create a VBA Sub that updates a different cell in the same sheet as the table:

``` Sub Updater() Dim j As Long Dim dTime As Double Application.ScreenUpdating = False Application.Calculation = xlCalculationManual dTime = Timer For j = 1 To 10000 Worksheets("Sheet1").Range("d5") = j Next j MsgBox Timer - dTime End Sub ```

4. Select a cell within the Table

5. Run the VBA Sub

On my system that takes 8.8 seconds.

## Bypassing the Problem

For this slowdown to occur each of the following conditions must be true:

• A cell within the Table must be selected
• The sheet containing the Table must be the Active Sheet
• The cell being updated must be on the same sheet as the table, but does not have to be within the table
• There must be a reasonable number of formulas in the workbook.

So change any or all of these conditions or delete all the formulas and the update will only take 0.5 seconds on my system.

## Whats actually happening?

The time taken is proportional mostly to the number and slightly to the size of the formulas in the workbook, but none of the formulas are actually being calculated.

So it seems to me that each time the cell is updated Excel is scanning all the formulas in the workbook as though they might need to be changed.

Maybe this has something to do with the automatic extension of formulas within a table when you add a new row or the fact that the Table definition and its associated Name has to change if you add a new row.

But the fact that the slowdown only occurs if the Table is on the active sheet means that I think this is a bug.

## Can you think of a better explanation?

### Writing efficient VBA UDFs (Part 8) – Getting the previously calculated value from the calling cells

January 8, 2012

If you have a UDF that depends on some slow-calculating resource you may want the UDF to mostly just return the values obtained  at the last calculation from the cells the UDF occupies, and only occasionally go and use the slow-calculating resource.

In Excel 2010 you can create efficient C++ XLL UDFs that execute asynchronously and multithreaded, which can be a very effective solution.

But how do you get the previous value from the cells calling a VBA UDF?

Lets suppose that you want to pass the UDF a parameter for the slow-calculating resource and a switch to tell it when to use the slow resource. You can set the switch (I am using a defined name called “RefreshSlow”)  and refresh the UDFs in a VBA sub like this:

Sub RefreshUDFs()
Dim lCalcMode As Long
lCalcMode = Application.Calculation
Application.Calculation = xlCalculationManual
Names(“RefreshSlow”).RefersTo = True
Calculate
Names(“RefreshSlow”).RefersTo = False
Application.Calculation = lCalcMode
End Sub

I will use a dummy function to simulate getting a slow resource:
``` Function GetSlowResource(vParam As Variant) As Variant Dim j As Long For j = 1 To 10000000 Next j GetSlowResource = Rnd() End Function ```
This function (ignores the parameter) and just (slowly) returns a random number.

There are several ways of getting the previously calculated value for a UDF: they each have advantages and disadvantages.

### Application.Caller.Value

You can use Application.Caller.Value, but this causes a circular reference that you have to switch on Iteration to solve. This is slow and can mask other unintentional circular refs, so its not recommended.

``` Function UDF1(vParam, Refresh) If Not Refresh Then UDF1 = Val(Application.Caller.Value2) Else UDF1 = GetSlowResource(vParam) End If End Function ```

### Application.Caller.Text

If you use Application.Caller.Text you don’t get the circular reference, but it retrieves the formatted value that is displayed in the cell as a string. So if the cell is formatted as a number with 2 decimal places the retrieved value will be truncated to 2 decimal places.
``` Function UDF2(vParam, Refresh) If Not Refresh Then UDF2 = Val(Application.Caller.Text) Else UDF2 = GetSlowResource(vParam) End If End Function ```
This solution will work OK if you can control the formatting or the function returns a string.

### Application.Caller.ID

You can use the Range.ID property to store and retrieve a string value within the UDF.
``` Function UDF3(vParam, Refresh) Dim var As Variant If Not Refresh Then UDF3 = Val(Application.Caller.ID) Else var = GetSlowResource(vParam) UDF3 = var Application.Caller.ID = var End If End Function ```
This works well, except that the Range.ID property is not stored in a Saved workbook, so the next time you open the workbook the retrieved value will be Blank/Zero.

### Using an XLM or XLL function to pass the Previous value to the UDF

Using XLM or XLL technology it is possible to create a non-multi-threaded command-equivalent function to retrieve the previous value.
Here is the code for an XLL+ function called PREVIOUS which has a parameter to make it Volatile or not Volatile.
(Command-equivalent functions default to Volatile but when using it to pass the previous value to a VBA UDF you generally want it to be non-volatile).
This function also works for multi-celled array formulae.
Edited following Keith Lewis comment.

``` CXlOper* PREVIOUS_Impl(CXlOper& xloResult, const CXlOper* Volatile_op) { // Input buffers bool Volatile; // Validate and translate inputs static CScalarConvertParams Volatile__params(L"Volatile", XLA_DEFAULT_ZERO|XLA_DEFAULT_EMPTY|XLA_DEFAULT_NONNUMERIC| XLA_DEFAULT_BLANK, 0, -1, true); XlReadScalar(*Volatile_op, Volatile, Volatile__params); // End of generated code //}}XLP_SRC // defined as a macro function defer recalc so that the func gets previous results CXlOper xloCaller,xlo; CXlOper arg; arg=true; if (!Volatile) arg=false; // set volatility of this function: 237 is the function number for volatile xlo.Excel(237,1,&arg); // Get caller. Fail if it is not a range of cells if ( ( xloCaller.GetCaller() != 0 ) || !xloCaller.IsRef() ) return CXlOper::RetError(xlerrNA); //coerce the caller ref xloResult.Coerce(xloCaller); return xloResult.Ret(); } ```

Then you can use this to pass the previous value to the UDF.
``` Function UDF4(vParam, Refresh, Previous) Dim var As Variant If Not Refresh Then UDF4 = Previous Else var = GetSlowResource(vParam) UDF4 = var End If End Function ```
The UDF is called from a formula like this =UDF4(“AAPL”,RefreshSlow,PREVIOUS(False))

This works well, but requires access to the XLL PREVIOUS function (Laurent Longre’s MOREFUNC addin has a similar function).

Its supposed to be possible to write a similar function using the old XLM Macro language, but I have not tried it.

## Conclusion

There are several ways of getting the previous value from the last calculation for a VBA UDF. But the best solution requires using a C++ XLL.

Special prize to the first person to write the XLM Macro Previous UDF!