vba 如何让excel“等待”单元格的值改变？
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/15737219/
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 to ask excel to "wait" for a cell's value to change?
提问by user1871453
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
我正在调用第三方远程调用来获取数据。调用此函数后，数据将从单元格（1,1）填充到单元格（10,1）我的代码是这样的
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?
但是，我的代码会挂在 while 循环中。那么如何处理这个问题呢？
回答by Joshua Honig
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.
您当前在 while 循环中旋转的方法不是一个好主意，因为它消耗了所有可用资源来做……什么都不做。或者更确切地说，它cells(10,1)<>"result"尽可能快地一遍又一遍地执行比较。并且用户界面在此期间挂起。
In short, you're finding that VBA does not lend itself well to asynchronous or multithreaded programming.
简而言之，您会发现 VBA 不太适合异步或多线程编程。
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.
值得庆幸的是，Excel 确实为您提供了一种内置的方法来完成此操作，只需稍加努力：Application.OnTime 方法。它允许您安排延迟调用方法，同时保持 UI（和任何其他 VBA 代码）响应和可用。您可以使用它来实现某种计时器。
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.
这是您案例的基本方法。它每 1 秒检查一次测试单元格的值。如果该值没有改变，它会安排自己在另一秒钟内再次检查。如果值为“result”，它会调用另一个子程序，您可以在其中放置代码来处理结果。
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
回答by glh
While the vba code is running i do not expect any other cells to change unless you are doing something in the loop.
当 vba 代码正在运行时，除非您在循环中执行某些操作，否则我不希望任何其他单元格发生更改。
I would either use an if function and call this macro when you want to check the last cell. I can elaborate if you need.
当您想检查最后一个单元格时，我会使用 if 函数并调用此宏。如果你需要，我可以详细说明。
Or, utilise the DoEventsvba function / method in the loop if you envisage the data changing while the loop is running. This function will enable, i believe, the application to still run and update the cells while this macro is running.
或者，DoEvents如果您设想在循环运行时数据发生变化，则在循环中使用vba 函数/方法。我相信，此功能将使应用程序在此宏运行时仍能运行并更新单元格。
After some testing: I now suspect the DoEventsfunction will work the best for you.
经过一些测试：我现在怀疑该DoEvents功能最适合您。
