Excel自定义函数UDF引起的问题

最新推荐文章于 2024-07-24 15:21:55 发布

culiao2169

最新推荐文章于 2024-07-24 15:21:55 发布

阅读量1.2k

点赞数

文章标签： python java 编程语言 linux 人工智能

原文链接：https://contexturesblog.com/archives/2020/01/09/excel-custom-function-udf-causing-problems/

版权

Excel macros ran really slowly in a workbook that someone sent to me. They said it was an Excel custom function (UDF) causing problems. The UDF ran for no apparent reason, and slowed other macros down. Here's what I did to make things a bit faster.

Excel宏在某人发送给我的工作簿中的运行速度非常慢。他们说这是引起问题的Excel自定义函数(UDF)。 UDF没有明显的原因运行，并且减慢了其他宏的速度。这是我做的一些事情，目的是使事情更快一些。

用户定义功能(UDF) (User Defined Function (UDF))

The custom function converts numbers to words. For example, 22.50 would be converted to the written words, "Twenty-Two Dollars and Fifty Cents".

自定义函数将数字转换为单词。例如，将22.50转换为书面文字“二十二美元和五十美分”。

You can see the code for the custom function, named SpellNumber, on the Convert Numbers Into Words page of the Microsoft site.

您可以在Microsoft网站的将数字转换成单词页面上看到名为SpellNumber的自定义函数的代码。

寻找瓶颈 (Finding the Bottleneck)

When I stepped through one of the workbook's macros, it cleared a range of several hundred cells.

当我逐步浏览工作簿的一个宏时，它清除了数百个单元格的范围。

At that line of code, it jumped to the SpellNumber function, and ran through that function.

在那行代码中，它跳到了SpellNumber函数，并运行了该函数。

And then it did the same thing, again and again. After a few loops, I stopped the macro.

然后它一次又一次地做同样的事情。几次循环后，我停止了宏。

My guess is that it was recalculating some or all of the cells that use the SpellNumber function. No wonder the macros ran slowly!

我的猜测是它正在重新计算使用SpellNumber函数的部分或全部单元格。难怪宏运行缓慢！

Why was the SpellNumbers function running so frequently? Was there any way to stop it from running unnecessarily?

为什么SpellNumbers函数如此频繁地运行？有什么方法可以阻止它不必要地运行？

情况很复杂 (It's Complicated)

I'm not a calculation expert, but Excel performance expert, Charles Williams, shows how dependencies and recalculations work with formulas and UDFs. Even with his very simple example, things get complicated!

我不是计算专家，但Excel性能专家Charles Williams 展示了依赖项和重新计算如何与公式和UDF一起使用。即使有一个非常简单的例子，事情也会变得复杂！

Imagine trying to track all the dependencies in someone else's large, complicated workbook!

想象一下，尝试跟踪别人的大型复杂工作簿中的所有依赖项！

TIP: Charles sells a FastExcel add-in, that makes it quick and easy to find performance problems in your workbooks. I have a copy, but it wasn't installed on the laptop I was using. Read more about in this FastExcel review on my site. (These are affiliate links)

提示： Charles提供了一个FastExcel加载项，可让您轻松快捷地在工作簿中查找性能问题。我有一个副本，但是没有安装在我使用的笔记本电脑上。在我的网站上的FastExcel评论中了解更多信息。 (这些是会员链接)

公式在哪里？ (Where Are the Formulas?)

I did a quick Find in the workbook, and the SpellNumber function is only used on 3 sheets. There were 350-400 rows on each sheet, with the function used in 2 columns, and twice in each formula.

我在工作簿中进行了快速查找，并且SpellNumber函数仅用于3张纸上。每张纸上有350-400行，该函数在2列中使用，在每个公式中使用两次。

So, if we do some arithmetic, there are 400x2x2x3 = 4800 calculations!

因此，如果我们做一些算术运算，则有400x2x2x3 = 4800个计算！

I did a quick test with a global variable (myCount), to count how many times it looped through the UDF during the main macro.

我使用全局变量(myCount)进行了快速测试，以计算它在主宏中通过UDF循环的次数。

It was a big number! myCount= 3438

这是一个很大的数字！ myCount = 3438

关掉东西 (Turn Things Off)

Unfortunately, I couldn't find any internet advice on how to stop a UDF from running unnecessarily.

不幸的是，我找不到有关如何阻止UDF不必要运行的任何互联网建议。

The first thing that I tried was putting a couple of lines at the start of the macro, to turn off events, and screen updating.

我尝试的第一件事是在宏的开头放置几行，以关闭事件，并更新屏幕。

That might have made the macro run a bit faster, but it still looped through the UDF countless times.

这样做可能会使宏运行得更快一些，但它仍然无数次遍历UDF。

竖起停车牌 (Put Up a Stop Sign)

For my next test, I added a "stop sign" for the SpellNumber UDF.

在下一个测试中，我为SpellNumber UDF添加了一个“停止符号”。

- First, I created a global variable named bNumWdOff.
  首先，我创建了一个名为bNumWdOff的全局变量。
- Next, at the top of the SpellNumber UDF, I added a line to check the bNumWdOff variable
  接下来，在SpellNumber UDF的顶部，我添加了一行以检查bNumWdOff变量

If bNumWdOff=True then Exit Function

Finally, in the other macro, I added code to change the variable to True at the start, and change it to False at the end.
最后，在另一个宏中，我添加了代码，以在开始时将变量更改为True，并在结束时将其更改为False。

bNumWdOff = True

That sped things up a bit. The code still went to the SpellNumber function countless times, but didn't loop through it.

这加快了速度。该代码仍然无数次到达SpellNumber函数，但没有循环通过它。

关闭计算 (Turn Off Calculation)

My final test was to turn off calculation. The main macro was adding formulas to a sheet, so I couldn't turn off calculation entirely.

我的最终测试是关闭计算。主要的宏是在工作表中添加公式，所以我无法完全关闭计算。

Instead, I turned off calculation for the 3 sheets that use the UDF.

相反，我关闭了使用UDF的3张纸的计算。

Then, at the end of the main macro, I added these steps

然后，在主宏的末尾，我添加了以下步骤

set bNumWdOff to True, so it won’t go through the entire SpellNumber UDF
将bNumWdOff设置为True，这样就不会遍历整个SpellNumber UDF
turn on calculation for the 3 sheets
打开3张纸的计算
set bNumWdOff to False
将bNumWdOff设置为False

UDF引起的问题 (UDF Causing Problems)

As you can see, a custom function (UDF) can slow down macros in your workbook.

如您所见，自定义函数(UDF)会减慢工作簿中的宏。

The UDF also ran when I filtered the columns that used the custom function.

当我过滤使用自定义函数的列时，也会运行UDF。

I'm sure there are lots of other things that trigger the UDF too. Other calculations? Changing cell values? What else?

我确定还有很多其他因素也会触发UDF。其他计算？更改单元格值？还有什么？

自定义功能替代 (Custom Function Alternative)

In the workbook that was sent to me, the numbers that were being converted to words weren't very big, and they were all whole numbers.

在发送给我的工作簿中，被转换为单词的数字不是很大，它们都是整数。

In this case, instead of using a custom function, you could create a table of numbers, from zero to 100. Then, in the second column, put the written word(s) for each number.

在这种情况下，您可以创建一个从零到100的数字表，而不是使用自定义函数。然后，在第二列中，为每个数字写一个或多个单词。

Then, in the cells where you want to show written words, use an INDEX/MATCH formula to pull the words from the lookup table.

然后，在要显示书面单词的单元格中，使用INDEX / MATCH公式从查找表中提取单词。

使用宏转换数字 (Use a Macro to Convert Numbers)

Another option is to convert the numbers to words with a macro, instead of a custom function. It could put the words in the cells as static values, instead of a formula.

另一种选择是使用宏而不是自定义函数将数字转换为单词。可以将单词作为静态值而不是公式放在单元格中。

Run the macro when needed, and you won't have formulas with custom functions slowing down your workbook.

在需要时运行宏，您将不会获得带有自定义函数的公式，这会降低工作簿的速度。