Excel VBA 使脚本异步
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/24574884/
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
Excel VBA make a script asynchronous
提问by Divin3
I have a script that can ping a list of computers and change their background color depending after the result it gets.
我有一个脚本可以 ping 计算机列表并根据它获得的结果更改它们的背景颜色。
My problem is, that it blocks the entire excel file while it runs.
我的问题是，它在运行时阻止了整个 excel 文件。
So my question is, how can I make it to run async?
所以我的问题是，我怎样才能让它异步运行？
Here is the code:
这是代码：
'ping
Function sPing(sHost) As String
Dim oPing As Object, oRetStatus As Object
Set oPing = GetObject("winmgmts:{impersonationLevel=impersonate}").ExecQuery _
("select * from Win32_PingStatus where address = '" & sHost & "'")
For Each oRetStatus In oPing
If IsNull(oRetStatus.StatusCode) Or oRetStatus.StatusCode <> 0 Then
sPing = "timeout" 'oRetStatus.StatusCode <- error code
Else
sPing = sPing & vbTab & oRetStatus.ResponseTime & Chr(10)
End If
Next
End Function
Sub pingall_Click()
Dim c As Range
Dim p As String
Application.ScreenUpdating = True
For Each c In ActiveSheet.Range("A1:N50")
If Left(c, 7) = "172.21." Then
p = sPing(c)
If p = "timeout" Then
c.Interior.ColorIndex = "3"
ElseIf p < 16 And p > -1 Then
c.Interior.ColorIndex = "4"
ElseIf p > 15 And p < 51 Then
c.Interior.ColorIndex = "6"
ElseIf p > 50 And p < 4000 Then
c.Interior.ColorIndex = "45"
Else
c.Interior.ColorIndex = "15"
End If
End If
Next c
Application.ScreenUpdating = False
回答by Bathsheba
You can't do too much about this unfortunately since VBA runs in a single thread.
不幸的是，由于 VBA 在单个线程中运行，因此您对此无能为力。
You can however introduce a degree of responsiveness by putting
但是，您可以通过放置来引入一定程度的响应能力
VBA.DoEvents()
in various places in your code, ideally in the tight loops. In your case, put them just after the lines containing For. This pauses the VBA and flushes the event queue which will have the effect of making Excel responsive.
在代码中的不同位置，最好是在紧密循环中。在您的情况下，将它们放在包含For. 这将暂停 VBA 并刷新事件队列，这将具有使 Excel 响应的效果。
(Toggling the screen updating is a bad idea since you might leave things in a bad state if the function terminates unexpectedly. I'd remove the lines that do that if I were you.)
（切换屏幕更新是一个坏主意，因为如果函数意外终止，您可能会使事情处于不良状态。如果我是您，我会删除这样做的行。）
回答by Robert Co
Excel can calculate "asynchronously". Call sPingas a function.
Excel 可以“异步”计算。sPing作为函数调用。
I'm not sure why your range is A1:N50. I assume one of the columns is the IP address, which I will assume as A. So your formula in column M will look like =sPing(A1).
我不确定为什么你的范围是 A1:N50。我假设其中一列是 IP 地址，我将其假设为 A。所以您在列 M 中的公式将如下所示=sPing(A1)。
As for the color coding, you can use conditional formatting.
至于颜色编码，您可以使用条件格式。
