how to ask excel to "wait" for a cell's value to change?

Question

I'm calling a third party remote call to get data. after calling this function,datas will be filled from cells(1,1) to cells(10,1) my code is like this

application.caculation = xlmanual
cells(1,1) = "some remotefunction"
application.calculate
while cells(10,1)<>"result"

wend
'if it goes here means we get the data
deal_with_data

however,my code would hang in the while loop. so how to deal with this issue?

Answer 1

Your current approach of spinning in a while loop is not a good idea because it consumes all available resources to do...nothing. Or rather, it executes the comparison cells(10,1)<>"result" over and over as quickly as it can. And the user interface hangs in the meantime.

In short, you're finding that VBA does not lend itself well to asynchronous or multithreaded programming.

Thankfully Excel does give you a built in way to accomplish this, with a little effort: The Application.OnTime method. It lets you schedule a delayed call to method, keeping the UI (and any other VBA code) responsive and available in the meantime. You can use it to implement a sort of timer.

Here's the basic approach for your case. It checks the value of your test cell every 1 second. If the value has not changed, it schedules itself to check again in another second. If the value is "result" it calls another sub, where you would put the code to handle the results.

Public Sub CallRemoteFunc()
    Application.Calculation = xlManual
    Cells(1, 1) = "some remotefunction"
    Application.Calculate
    Call WaitForUpdate
End Sub

Public Sub WaitForUpdate()
    If Cells(10, 1) <> "result" Then
        Debug.Print "Still waiting"
        Application.OnTime Now + TimeValue("00:00:01"), WaitForUpdate
    Else
        Call DoStuffWithData
    End If
End Sub