如何在不使 pc 爬行的情况下在 VBA 中执行长时间运行的进程？
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/3440434/
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
How can I execute a long running process in VBA without making pc crawl?
提问by PICyourBrain
I have a VBA application that creates an instance of a COM object and then continuously polls the objects DataReadyproperty to see if there is new data. When new data is available it sticks the data into a spread sheet. The problem is this macro (vba sub routine) continually runs and this slows down excel to a crawl and makes the computer somewhat unusable while the process is running. Is there a way that I can start this process on a separate thread or do something like a .NET background worker?
我有一个 VBA 应用程序，它创建一个 COM 对象的实例，然后不断轮询对象的DataReady属性以查看是否有新数据。当新数据可用时，它会将数据粘贴到电子表格中。问题是这个宏（vba 子例程）不断运行，这会减慢 excel 的速度，并使计算机在进程运行时有些无法使用。有没有办法可以在单独的线程上启动此进程或执行类似 .NET 后台工作程序的操作？
My two attempts were to use a while loop like this..
我的两次尝试是使用像这样的 while 循环..
While(True)
If(myObject.DataReady)
Do some stuff here
End If
WEnd
and then this
然后这个
Sub GrabNewPoint()
If (myModule.NewDataReady_Receiver = True) Then
Do some stuff here...
End If
If (StopTest = False) Then
NextTime = Now() + TimeValue("00:00:20")
Application.OnTime NextTime, "GrabNewPoint"
End If
The second attempt definitly works better but it still slows things down considerably. Is there a better solution?
第二次尝试肯定效果更好，但它仍然大大减慢了速度。有更好的解决方案吗？
My COM object is a class library that I wrote in C#. I can add events that fire in the Class Library when data is ready but how do I listen for those events in the VBA program?
我的 COM 对象是我用 C# 编写的类库。当数据准备好时，我可以添加在类库中触发的事件，但如何在 VBA 程序中监听这些事件？
回答by bugtussle
Have you tried using DoEvents?
您是否尝试过使用 DoEvents？
While(True)
If(myObject.DataReady)
'your code here
End If
DoEvents
WEnd
回答by DonaldRay
I would think the best solution is to have the COM object raise a VBA event whenever data is ready.
我认为最好的解决方案是让 COM 对象在数据准备好时引发 VBA 事件。
If that is not an option (no control over COM object, etc.) then you will HAVE to spin the CPU. All you can do is increase the time interval between checking the DataReady property, which you already discovered in your second option. I would figure out just how fat you can increase the interval without losing functionality and leave it there.
如果这不是一个选项（无法控制 COM 对象等），那么您将不得不旋转 CPU。您所能做的就是增加检查 DataReady 属性之间的时间间隔，您已经在第二个选项中发现了该属性。我会弄清楚您可以在不失去功能的情况下增加间隔的程度并将其留在那里。
回答by Sam at TVentures
Try this, see if things improve.
试试这个，看看情况是否有所改善。
Just pause, let the CPU fly... key is to trap it here so it releases as long as you like
Public Sub App_Hard_Wait_DoEvents(dblSeconds As Double)
If dblSeconds = 0 Then Exit Sub
Dim varStart As Variant
varStart = Timer
Do While Timer < (varStart + dblSeconds)
DoEvents
Loop
End Sub
DO
Call App_Hard_Wair_DoEvents(10)
loop until (myObject.DataReady)
