How can I execute a long running process in VBA without making pc crawl?

Question

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?

Answer 1

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.

Answer 2

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)

Answer 3

Have you tried using DoEvents?

While(True)
    If(myObject.DataReady)
        'your code here
    End If
    DoEvents
WEnd