Excel C＃Interop与Excel VBA

I am in charge of finding bottlenecks in a Excel Add-in. Some of the workbooks calculate extremely slowly. I have the experience of working with VBA code, and the older versions of our product used all VBA code to execute calculations. However, in the newer versions of the product, a lot of the calculations are being done through the add-in, which is build on top of ASP.NET C# using Excel.Interop libraries. My question is this: Do interop libraries just run slower then code that resides directly in a workbook in the VBA? From my experience, all projects using Excel.Interop run extremely slow on intense calculations. If so, does anyone have any advice on speeding up Excel.Interop processes and functions?

我负责在Excel加载项中找到瓶颈。有些工作簿的计算速度非常慢。我有使用VBA代码的经验，我们产品的旧版本使用所有VBA代码来执行计算。但是，在较新版本的产品中，许多计算都是通过加载项完成的，该加载项是使用Excel.Interop库构建在ASP.NET C＃之上的。我的问题是这样：互操作库是否比直接驻留在VBA中的工作簿中的代码运行得慢？根据我的经验，使用Excel.Interop的所有项目在激烈计算时运行速度极慢。如果是这样，有没有人有任何关于加快Excel.Interop过程和功能的建议？

Thanks.

谢谢。

1 个解决方案

#1

Interop has a significantly higher overhead than VBA for object model calls and data transfer calls. Its worth working hard to minimise the number of calls. You may want to look at my blog post comparing Excel UDF technologies: UDF Technology Choices

对于对象模型调用和数据传输调用，Interop的开销明显高于VBA。值得努力减少通话次数。您可能希望查看我的博客文章，比较Excel UDF技术：UDF技术选择

If you are able to use Addin-Express or Excel DNA I would recommend both products.
For general advice on optimising calculation speed I recommend my website
or my MSDN white papers .

如果您能够使用Addin-Express或Excel DNA，我会推荐这两种产品。有关优化计算速度的一般建议，我推荐我的网站或我的MSDN白皮书。