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

excel, vba

Solution

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

Problem

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?

Original source