vba excel vba中的并发和多线程
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/14677866/
Warning: these are provided under cc-by-sa 4.0 license. You are free to use/share it, But you must attribute it to the original authors (not me):
StackOverFlow
concurrency and multi-threading in excel vba
提问by user511792
So I searched online and most answers I got said that there is no concurrency in Excel. But some posts say that EXCEL 2007 offers multi-threading for faster calculations. I'm really confused, is it possible or not?
所以我在网上搜索，我得到的大多数答案都说 Excel 中没有并发。但有些帖子说 EXCEL 2007 提供多线程以加快计算速度。我真的很困惑，这可能还是不可能？
In my workbook, there are some cells are calculated using user defined functions. These cells are updated through a RTD server once per second. So does this mean that these functions appear to be re-calculated paralleled, but in fact, Excel just schedules them sequentially? Does it make a difference if the computer has multiple cores or not?
在我的工作簿中，有一些单元格是使用用户定义的函数计算的。这些单元每秒通过 RTD 服务器更新一次。那么这是否意味着这些函数看起来是并行重新计算的，但实际上，Excel只是按顺序调度它们？计算机是否有多个内核有区别吗？
If, in fact, these functions are running sequentially, would the following two be equivalent? In terms of performance and speed.
如果，事实上，这些函数是按顺序运行的，那么下面两个是否等价？在性能和速度方面。
1)
1)
cell1 = userFunc(1, switch1)
cell2 = userFunc(2, switch2)
cell3 = userFunc(2, switch3)
and 2)
和 2)
cell = func()
where func() is
public function
if(switch1) call userfunc1
if(switch2) call userfunc2
....
end function
And let's assume all the necessary adjustments to the functions are made. I mean, would the first one be faster?
让我们假设对功能进行了所有必要的调整。我的意思是，第一个会更快吗？
Thanks a lot!!!
非常感谢！！！
UPDATE: So if there's no real concurrency in VBA, does that mean functions are called sequentially and shared global variables can be accessed without locking problem? I have an API object that's used by virtually every function, so can I shared it instead of initialize it everytime I use it? This object is just an API used to call some static functions, there's no modification or anything to the object itself.
更新：因此，如果 VBA 中没有真正的并发性，这是否意味着按顺序调用函数并且可以访问共享全局变量而不会出现锁定问题？我有一个几乎每个函数都使用的 API 对象，所以我可以共享它而不是每次使用时都初始化它吗？这个对象只是一个用于调用一些静态函数的API，对象本身没有任何修改或任何东西。
回答by Charles Williams
Yes Excel 2007 and later versions support multi-threaded calculation using multiple cores, but as Tim says - its not possible to write Multi-Threaded UDFs using VBA.
For good UDF performance you really need to be using the XLL interface and exploiting multi-threading.
是的，Excel 2007 及更高版本支持使用多核的多线程计算，但正如 Tim 所说 - 无法使用 VBA 编写多线程 UDF。
为了获得良好的 UDF 性能，您确实需要使用 XLL 接口并利用多线程。
You can create multi-threaded XLL UDFs in .NET C# or VB.Net using Excel DNA (free) or Addin Express (costs).
VSTO does not support UDFs, and .Net automation UDFs are very slow
For C++ XLL UDFs I recommend Planatech XLL+.
您可以使用 Excel DNA（免费）或 Addin Express（收费）在 .NET C# 或 VB.Net 中创建多线程 XLL UDF。
VSTO 不支持 UDF，并且 .Net 自动化 UDF 非常慢
对于 C++ XLL UDF，我推荐 Planatech XLL+。
