速度，VBA VS Excel公式 [英] Speed, VBA VS Excel Formulas

问题描述

一般来说，高效的VBA代码公式 更快或高效的VBA代码。如果有一个任务可以让我们说，

Generally speaking are excel formulas faster or an efficient VBA Code. If there is a task that can be done by lets say

• 100％VBA代码或


• 50％的VBA和50％的公式

是否比第二个更好？我从速度的角度来看这个问题？

would it be better to go with the second one? I'm asking this from a speed perspective?

我写了很多VBA宏。最近我在我的项目中使用了更多的公式。我一直在想，这是否是使事情更有效的正确方法。

I write a lot of VBA macros. Lately I've been using a lot more formulas in my projects. I've been wondering if this is the correct approach to make things more efficient.

推荐答案

对于一个简单的函数内置Excel公式可能更快，因为它们最有可能用C / C ++编写，产生快速本机代码。

For a simple function a built-in Excel formula is probably faster since they are most certainly written in C/C++ which produce fast native code.

VBA代码定位.NET环境，必须进行解析和编译在它运行之前，这增加了一些开销。

VBA code target the .NET environment, and must be parsed and compiled before it is run, which add some overhead.

但是对于复杂的嵌套公式，由于Excel没有线索你的数据是什么样子的，它必须在操作之前检查类型，可能会遍历整个数组的值多次，做一些内存分配等。

But for complex nested formulae, since Excel has no clue what your data looks like, it has to check types before operations, might go through an entire array of value several times, do some memory allocation, etc..

在这种情况下，宏可能会更快，因为你可能有一个想法您的数据如何，您可能可以编写一些优化。

In that case a macro may be faster, since you may have an idea of what your data looks like and you may be able to write some optimizations.

但是您不能确定，性能可能会在不同配置或软件版本之间变化。确定的最好的方法是进行一些测试。

But you cannot know for sure, and the performance can change between different configurations or software versions. The best way to know for sure is to perform some tests.

这篇关于速度，VBA VS Excel公式的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！