VBA -为什么VBA UDF与原生Excel函数相比如此缓慢？

Question

假设我编写以下VBA UDF：

Function TestFunction()
    TestFunction = 0
End Function

然后我将它用于我的工作表的前100000行。它需要几分钟才能执行。

相反，如果我对相同的行数使用use ()，执行只需要3-4秒。

谁能告诉我为什么，有没有一种方法可以加速UDF？

谢谢!

Answer 1

有几个原因。

VBA函数需要在UI/主线程之外按顺序运行，编译后的p代码需要由VBA运行时解释。

本机函数是本机函数。它们(可能是AFAIK，它们是用C++编写的)已经编译成易于执行的机器码，不需要重新编译和/或解释。一些本机函数还可以利用多线程和“后台”计算。

至于加速你的UDF，我们需要看到你的UDF。一个除了赋值文字返回值之外什么也不做的函数，没有太多的优化空间，不是吗？

UDF很棒。但它们不是银弹。如果我想把值0写成A1:A1000000，我会写Sheet1.Range("A1:A1000000").Value = 0，这几乎是瞬间完成的。

如果您要计算数十万个UDF，请考虑查看宏而不是UDF。

Answer 2

这有许多不同的原因。

对于一个VBA，UDF是解释的，而本地Excel工作表函数是编译的。例如，如果你把你的VBA代码编译成VB6，你会得到很大的速度提升。VBA和VB6代码要么相同，要么几乎完全相同。因此，速度大幅提高的原因只是因为VB6代码是编译的，而不是像VBA代码那样进行解释。

VBA代码也不会生成与Excel相同类型的工作表函数。例如，VBA UDF缺少工作表智能感知。您不能通过VBA以任何方式获得它。但是，您可以通过其他地方的外部加载项(例如Excel DNA)获取它。

另一个原因是VBA不是编写高性能UDF的最佳API。这就是C API。但是C API比VBA更难编写UDF。

还有许多其他因素可能会影响速度，比如底层硬件，或者在UDF中使用的算法。如果不看你的代码，就很难给你有用的建议。

您确定需要UDF吗？与宏相比，UDF唯一的优势(至少我知道的)是它们在被调用后不会删除undostack，而大多数宏都是这样做的。它们可以动态重新计算，而您必须在宏被调用后不断地重新运行它们(除非您正在使用工作表事件或其他什么东西)。

如果您正在对一个单元格范围进行大量的计算，那么将该范围写入一个数组，在VBA中对其进行操作，然后再将其写回该范围可能会更好。