How can I execute a long running process in VBA without making pc crawl?
I have a VBA application that creates an instance of a COM object and then continuously polls the objects DataReady property 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?
My two attempts were to use a while loop like this..
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?
你有没有尝试过使用DoEvents？
While(True)
If(myObject.DataReady)
'your code here
End If
DoEvents
WEnd
I would think the best solution is to have the COM object raise a VBA event whenever data is ready.
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.
试试这个，看看事情是否改善。
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)