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

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 2

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.