vba Excel 中的用户定义函数和速度问题

User Defined Functions in Excel and Speed Issues

提问by B Rivera

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.

我有一个使用几乎所有 UDF 的 Excel 模型。有说，120 列和超过 400 行。计算先垂直完成，然后水平完成 --- 即首先完成第 1 列的所有计算，然后第 1 列的最终输出是第 2 列的输入，依此类推。在每一列中，我调用了大约六个或七个 UDF调用其他 UDF。UDF 通常输出一个数组。

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.

每个 UDF 的输入是一些变量、一些范围变量和一些双精度变量。范围变量在访问其内容之前在内部转换为数组。

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

我的问题如下，我可以在没有 UDF 的情况下构建 Excel 模型，并且当我运行模拟时，我可以在 X 小时内完成所有计算。当我使用 UDF 时，模拟时间是 3X 小时或更长时间。（要回答明显的问题，是的，我需要使用 UDF，因为如果我想对模型进行小的更改（例如添加另一种资产类型（它是财务模型）），则需要将近一天的时间重新制作模型如果不使用 UDF 来适应新的法律/财务结构，使用 UDF 大约需要 20 分钟来适应不同的财务结构。）

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.

无论如何，我关闭了屏幕更新，函数中没有复制和粘贴，Variant类型的使用最少，所有数据都包含在一张纸中，我在获取内容之前将所有范围类型变量转换为数组.

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.

除了获得更快的计算机或等效的使 VBA 代码/Excel 文件运行得更快之外，我还能做什么？如果这需要更多说明，请告诉我。

Thanks!

谢谢！

回答by Mark Nold

Couple of general tips.

几个一般提示。

1. Take your function and work out where the bottlenecks really are. See this question for the use of timerin 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 nota 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..eachthrough 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.

1. 拿出你的函数，找出真正的瓶颈在哪里。有关在 excel 中使用计时器的信息，请参阅此问题。我确定那里有 VBA 分析器......但你可能不需要走那么远。（注意：首先使用一个数据单元格执行此操作...）

2. 想想你的设计...... 400x120 的数据单元并不是很多。而要花上几个小时，那一定是痛苦的。（在过去，我在等待 1,000 秒VLOOKUPS()返回后破解了它）无论如何，也许不是拥有一堆 UDF，为什么不拥有一个简单的子例程，for..each通过该范围并执行您需要的操作。48,000 个细胞可能需要几秒钟或几分钟。然后，您可以将该子例程与用户的按钮或菜单项相关联。

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.

出于兴趣，我快速查看了选项 2 并创建了 MyUDF()，使用 subDoMyUDF()调用它进行活动选择对我来说比在每个单元格中都使用 UDF 快 10 倍。

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.

如果您MyUDF()用 UDF替换，这可能只会为您节省 4.5 分钟……但您可能还可以构建其他一些经济体。特别是如果您一遍又一遍地重复相同的计算。

回答by Charles Williams

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

Excel 处理 UDF 的方式存在一个减速错误。每次计算 UDF 时，Excel 都会刷新 VBE 标题栏（您可以看到它在闪烁）。对于大量 UDF，这非常慢。手动计算模式下的旁路非常简单：只需使用 Application.Calculate 之类的东西从 VBA 启动计算（您可以使用 OnKey 捕获 F9 等）。

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

有关更多详细信息，请参阅http://www.decisionmodels.com/calcsecretsj.htm。

回答by richardtallent

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（每个输出单元调用一次），该宏可以在循环中对一系列单元进行操作？

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

UDF安装/拆卸非常慢，什么每个UDF调用与其他的UDF（重叠的投入阅读，例如）共同确实成为额外的努力。

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.

这样做我已经能够将性能提高 10-50 倍——在不到一个月前遇到过一个涉及具有 4000-30000 个 UDF 调用的电子表格的情况，用一个在几个命名范围上运行的宏替换了它们。

回答by Henrik Sandell

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

确保从 VBA 而非电子表格开始重新计算，请参阅http://msdn.microsoft.com/en-us/library/aa730921.aspx#Office2007excelPerf_Overview和有关更快的 VBA 用户定义函数的部分。也就是说 Application.Calculate 比在电子表格中按 F9 快得多（在我的测试用例中是 100 倍）。

回答by Patrick Honorez

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

此外，尽量减少 VBA 和工作簿之间的交换。一次将一组单元格读取和写入数组的速度更快

MyArray = range("B2:B20000") 

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

而不是逐个单元格（对于每个...）。