Odd (to me, anyway) timing problem

[msnyder701]

Hi all--So I have some code which opens IE to a particular search from a search engine (which takes awhile) and then either makes the page visible, or else reports that there were no search results (in which case the search result page contains the phrase "zero results").

The IE page is made not visible until the search is finished. Because the search takes time, I've inserted a function which displays a sort of countdown clock (made with a radar chart) that counts about 10 seconds and plays a sound at each second. The visible change each second is accomplished by changing the cells on another sheet. At the end of this, the clock is supposed to disappear. In the case of zero search results, a message box pops up stating that; if there *are* results, then the IE page is made visible.

My problem is that the message box pops up before the clock disappears, although once I click "OK" then the clock vanishes. I've tried lots of things to try and make sure that the clock is gone before the message box comes up, but to no avail. Can anyone help?

Sub aPrime()
On Error Resume Next
<code which assembles the URL goes here>

Set ie = CreateObject("internetexplorer.application")

ie.navigate "<URL goes here>"
ie.Visible = False

Call CountDown
Call ThereOrNot
        
End Sub



Function CountDown()
On Error Resume Next
Sheets("Sheet1").Select
ActiveSheet.Shapes.Range(Array("CountDownChart")).Visible = msoTrue
Application.Wait (Now + 1 / 86400)
With Selection

For i = 1 To 10
If i <> 1 Then Call PlaySound("C:\Windows\Media\Windows User Account Control.wav", 0&, &H0)
    If i = 10 Then
     Sheets("Sheet2").Cells(10, 1) = 1
     Sheets("Sheet2").Cells(1, 1) = 2
     Application.Wait (Now + 1 / 86400)
     Exit For
     End If
Sheets("Sheet2").Cells(i + 1, 1) = 2
Sheets("Sheet2").Cells(i, 1) = 1

Application.Wait (Now + 1 / 86400)
Next i
End With

Call PlaySound("C:\Windows\Media\Windows Ding.wav", 0&, &H0)
Application.Wait (Now + 1 / 86400)
ActiveSheet.Shapes.Range(Array("CountDownChart")).Visible = msoFalse

End Function


Function ThereOrNot()
msg = ie.document.body.innerhtml
AppActivate Application.Caption
If InStr(msg, "zero results") = 0 Then
        ie.Visible = True
        Else
            MsgBox ("Nothing found.")
            ie.Quit
        End If
End Function

[Shaggy Hiker]

Thread moved to Office Development, since it is a VBA question.

I edited the post to add [CODE][/CODE] tags to improve formatting and readability of the code. You can do this by pressing the # button and pasting code between the resulting tags. As you can see, the results are somewhat better, but far from perfect.

The problem is likely that the screen isn't getting a chance to redraw before the messagebox pops up. The process is keeping the system busy, so it can't process window paint messages, so the screen remains as it is at the end of the one function call right through the end of the second function call. I don't know what options you have available in VBA, but if you have an Application.DoEvents, then putting that between the call to CountDown and the call to ThereOrNot would likely solve the problem.

[msnyder701]

Hi Shaggy Hiker--Thanks for the tip about pasting in code!

Anyway, adding Application.DoEvents makes the countdown clock disappear even if the "Nothing found" message box is still present (whereas before, the countdown clock stayed **until** I acknowledged the message box). But it should still disappear **before** the message box pops up.

If you have another thought I'll try it, but otherwise don't spend any time on this; it's purely a matter now of esthetics and there's no point in you wasting brainpower.

Thanks again!

[msnyder701]

Huh. Well, now it seems to work just fine--clock disappears first. Thanks for your help!

[msnyder701]

Well, I finally figured it out. The problem was that when I called PlaySound, I passed it the parameter &H0 which tells the function to run synchronously. When I changed it to &H1 which is for asynchronous execution, it works perfectly.