Excel和速度问题中的用户定义函数 [英] User Defined Functions in Excel and Speed Issues

问题描述

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

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

我的问题是，我可以构建没有UDF的Excel模型，当我运行模拟时，我可以在X小时内完成所有的计算。使用UDF时，模拟时间为3X小时以上。 （为了回答这个明显的问题，是的，我需要与UDF一起工作，因为如果我想对模型进行小的更改（比如说添加另一个资产类型（它是一个财务模型）），需要将近一天的时间来重塑模型没有UDF适应新的法律/财务结构，UDF需要大约20分钟来适应不同的财务结构。）

无论如何，我已经关闭屏幕更新，函数中没有复制和粘贴，使用Variant类型是最小的，所有数据都包含在一个表单中，我在获取内容之前将所有范围类型变量转换为数组。

除了获得更快的计算机或相当于使VBA代码/ Excel文件运行更快之外，我还可以做什么？请让我知道，如果这需要更多的澄清。

谢谢！

解决方案

你有没有考虑用一个宏替换UDF（每个输出单元被调用一次），这个宏可以在循环中的单元格范围内运行？

UDF设置/拆卸非常慢，每个UDF调用与其他UDF相同（例如，从重叠输入读取）的任何东西都是额外的努力。

我已经能够提高10-50x的性能 - 在不到一个月前的情况下，涉及到具有4000-30000 UDF调用的电子表格，用一个在几个命名范围上运行的单个宏替换它们。 / p>

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!

解决方案

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.

这篇关于Excel和速度问题中的用户定义函数的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！