8

I have a script that can ping a list of computers and change their background color depending after the result it gets.

My problem is, that it blocks the entire excel file while it runs.

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
Divin3
  • 538
  • 5
  • 12
  • 27

3 Answers3

14

You can't do too much about this unfortunately since VBA runs in a single thread.

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.

(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.)

Bathsheba
  • 231,907
  • 34
  • 361
  • 483
  • If something goes wrong, the script will pop up an error, since there is no "On Error Resume Next" in it. But I take your advice, so I took it out. Also I will try to apply your code. Hope it works :-) – Divin3 Jul 04 '14 at 13:21
  • 1
    The DoEvents made it a bit faster, but it still blocks while it is waiting for a response from offline addresses. – Divin3 Jul 04 '14 at 14:23
  • 1
    Is this all we can do? – Divin3 Jul 04 '14 at 14:25
  • Thanks this helped me a lot. It was just the solution I was looking for, i used it like so: call subroutineX() call VBA.DoEvents() – VeldMuijz Jun 11 '15 at 11:54
3

Excel can calculate "asynchronously". Call sPing as a function.

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).

As for the color coding, you can use conditional formatting.

Robert Co
  • 1,715
  • 8
  • 14
  • But it is called as a function `p = sPing(c)` where c is the ip addres. And I use A1:N50 because I use a dynamic script with multiple sheets, and more inforomation. The ip addreses are recognized and colored depending of the ping value. – Divin3 Jul 05 '14 at 16:07
  • 2
    Are you implying that A1:N50 is a 2D grid, where each cell in it contains IP addresses? If not, what I am suggesting is to is use your function in the Excel workbook itself, you can call your own function in a formula. – Robert Co Jul 05 '14 at 16:12
  • It has multiple tables in it that contain ip addreses on random places on the sheet. When I started to write my script, I have tried it for the whole workbook but the excel has blocked. I only have 3 weeks of experience in vba, so I found it easyer this way. But I will experiment with your suggestion, and thank you for the tip! :-) – Divin3 Jul 05 '14 at 18:11
  • 2
    Free tip: make your VBA custom function `volatile`, so it get's calculated continuously. Otherwise once it's calculated it will remain same. – JCM Feb 08 '17 at 14:55
0

While strictly speaking you cannot make Excel to behave as you need, there is a trick to work around it. The solution is to create another Excel instance. Then you can run the macro in one of the instances and work independently in the other one. You can open another Excel instance from the Run prompt (press Windows + R) and then type Excel /x, then Enter

Sanya
  • 1