User Defined Functions in Excel and Speed Issues

Question

I have an Excel model that uses almost all UDFs. There are say, 120 columns and over 400 rows. The calculations are done vertically and then horizontally --- that is first all the calculations for column 1 are done, then the final output of column 1 is the input of column 2, etc. In each column I call about six or seven UDFs which call other UDFs. The UDFs often output an array.

The inputs to each of the UDFs are a number of variables, some range variables, some doubles. The range variables are converted to arrays internally before their contents are accessed.

My problem is the following, I can build the Excel model without UDFs and when I run simulations, I can finish all computations in X hours. When I use UDFs, the simulation time is 3X hours or longer. (To answer the obvious question, yes, I need to work with UDFs because if I want to make small changes to the model (like say add another asset type (it is a financial model)) it takes nearly a day of remaking the model without UDFs to fit the new legal/financial structure, with UDFs it takes about 20 minutes to accommodate a different financial structure.)

In any case, I have turned off screen updating, there is no copying and pasting in the functions, the use of Variant types is minimal, all the data is contained in one sheet, i convert all range type variables to arrays before getting the contents.

What else can I do other than getting a faster computer or the equivalent to make the VBA code/Excel file run faster? Please let me know if this needs more clarification.

Thanks!

Answer 1

Control and minimize recalculations with

wks.EnableCalculation = False

or

Application.Calculation = xlCalculationManual

Also, minimize the exchanges between VBA and the workbooks. It is faster to read and write a blocs of cells at once into an array

MyArray = range("B2:B20000")

rather than cell by cell (for each...).

Answer 2

Couple of general tips.

1. Take your function and work out where the bottlenecks really are. See this question for the use of timer in excel. I'm sure there are VBA profilers out there... but you probably don't need to go that far. (NB: do this with one cell of data first...)

2. Think about your design... 400x120 cells of data is not a lot. And for it to take hours that must be painful. (In the past i've cracked it after waiting a minute for 1,000s of VLOOKUPS() to return) anyway maybe instead of having having a stack of UDFs why not have a simple subroutine that for..each through the range and does what you need it to do. 48,000 cells could take seconds or maybe just minutes. You could then associate the subroutine with a button or menu item for the user.

Out of interest i had a quick look at option 2 and created MyUDF(), using the sub DoMyUDF() to call it for the active selection worked 10x faster for me, than having the UDF in each and every cell.

Option Explicit



Function MyUDF(myVar As Variant) As Variant
  MyUDF = myVar * 10
End Function

Sub DoMyUDF()
  Dim r As Range
  Dim c As Variant

  Dim t As Single
  t = Timer


  If TypeName(Selection) <> "Range" Then
    Exit Sub
  End If


  Set r = Selection.Cells

  Application.DisplayStatusBar = True

  For Each c In r
    c.Value = MyUDF(c.Value)

    Application.StatusBar = "DoMyUDF(): " & Format(Timer - t, "#0.0000ms")
  Next

  Debug.Print "DoMyUDF(): " & Format(Timer - t, "#0.0000ms")

End Sub

If you replace MyUDF() with your UDF this may only save you 4.5 minutes... but it's possible there are some other economies you can build in. Especially if you are repeating the same calcs over and over again.

Answer 3

There is a slowdown bug in the way Excel handles UDFs. Each time a UDF gets calculated Excel refreshes the VBE title bar (you can see it flicker). For large numbers of UDFs this is very slow. The bypass is very simple in Manual Calculation mode: just initiate the calculation from VBA using something like Application.Calculate (you can trap F9 etc with OnKey).

see http://www.decisionmodels.com/calcsecretsj.htm for some more details.

Answer 4

Have you considered replacing the UDF (which gets called once per output cell) with a macro, which can operate on a range of cells in a loop?

UDF setup/teardown is very slow, and anything each UDF call does in common with other UDFs (reading from overlapping inputs, for example) becomes extra effort.

I've been able to improve performance 10-50x doing this--had a situation less than a month ago involving a spreadsheet with 4000-30000 UDF calls, replaced them with a single macro that operates on a few named ranges.

Answer 5

Make sure you start the recalc from the VBA and not from the spreadsheet, see http://msdn.microsoft.com/en-us/library/aa730921.aspx#Office2007excelPerf_Overview and the section about Faster VBA User-Defined Functions. That is Application.Calculate is much faster (in my test case 100 times) than pressing F9 in the spreadsheet.

Answer 6

Hello

there are some profiling tools here you might find useful to track down your problem

https://sites.google.com/a/mcpher.com/share/Home/excelquirks/proctimer/automatic-profiling

bruce