Why does Application.Wait get a compile error: "Invalid Qualifier"

Question

I am trying to insert a pause into Excel VBA code. The following code generates a compile error: "Invalid Qualifier".

The function Pause() is trying to combine the Application.Wait method with DoEvents to get the best of both worlds; Excel will continue to process keystrokes and mouse clicks while not eating up CPU resources as it waits.

Background:

http://www.fmsinc.com/microsoftaccess/modules/examples/avoiddoevents.asp http://www.cpearson.com/excel/WaitFunctions.aspx

http://vbadud.blogspot.com/2008/03/sleep-function-in-excel-vba.html

Public Declare Function GetTickCount Lib "kernel32" () As Long
' A DoEvents loop uses CPU power.
' Application.Wait suspends all keyboard and mouse actions.
' This function is a hybrid to combine the best of both worlds.
Public Sub Pause(Optional Timeout As Single = 5)
    Dim EndTime

    EndTime = GetEndTick(Timeout)
    Do While GetTickCount() <= EndTime
        DoEvents
        Application.Wait DateAdd("s", 0.1, Now)
    Loop
End Sub

' Timeout is in seconds.
Function GetEndTick(Timeout)
    GetEndTick = GetTickCount() + Timeout * 1000
End Function

Your code runs perfectly for me exactly as posted. Where are you running it from - within Excel? — Rory, Feb 03 '15 at 10:03
That's strange. I opened another instance of Excel and copied the code and it works fine. But it's not working in the original. When I click 'Run", 'Compile...' I get the error. I even tried restarting Excel but it didn't help. It's quite puzzling. — ChaimG, Feb 03 '15 at 20:57
I tried other methods and properties of the Application object such as `Application.CalculateFull` and I get the same error when compiling. — ChaimG, Feb 03 '15 at 21:05
That's it! There was a Enum variable type with 'Application' as one of the choices. Removing it solved the problem. — ChaimG, Feb 04 '15 at 22:28

score 0 · Answer 1 · answered Feb 04 '15 at 06:30

To answer my question for some reason the Application object is not being recognized. I used the following workaround successfully:

Worksheets.Application.Wait DateAdd("s", 0.1, Now)

However I have discovered that the Worksheets.Application.Wait takes up CPU resources which defeats the purpose. Instead here is a working Pause function:

#If VBA7 Then
    ' Insert PtrSafe for Excel 64 as per http://stackoverflow.com/a/5507370/2529619 
    Private Declare PtrSafe Sub Sleep Lib "kernel32" (ByVal dwMilliseconds As LongPtr)
    Private Declare PtrSafe Function GetTickCount Lib "kernel32" () As Long
#Else
    Private Declare Sub Sleep Lib "kernel32" (ByVal dwMilliseconds As Long)
    Private Declare Function GetTickCount Lib "kernel32" () As Long
#End If

' "Pause" code execution for "Timeout" seconds.
'
' Excel will continue to process the keyboard and mouse clicks at least once a second.
' This Pause function will not take up lots of CPU resources.
' 2015.02.04 -- Chaim Gewirtz
Public Sub Pause(Optional Timeout As Single = 5)
    Dim EndTick

    ' There are 1000 'ticks' in a second.
    EndTick = GetTickCount() + Timeout * 1000
    Do While GetTickCount() <= EndTick
        ' Process keyboard and mouse events while waiting.
        DoEvents

        ' If there is at least one more second to pause then...
        If EndTick - GetTickCount() > 1000 Then
            ' "Wait" for one second without using CPU resources.
            Sleep 1000
        End If
    Loop
End Sub

score -1 · Answer 2 · answered Feb 03 '15 at 05:54

-1

Visual Basic - all variations - use end of line as the end of statement marker. This contrasts with C# where semi-colon is used as a statement terminator, and with Delphi where semi-colon is used as a statement separator.

So, this line:

Application.Wait DateAdd("s", 0.1, Now)

is treated as a single statement, and it doesn't make sense. The best that the compiler can do is to think that you're wanting to use Application.Wait as a qualifier, but it doesn't understand it.

Change the code to this:

Application.Wait 
DateAdd("s", 0.1, Now)

and it should at least execute. Though, whether it's good practice is another question.

answered Feb 03 '15 at 05:54

Bevan

43,618
10
81
133

The line as posted is entirely correct since Wait expects a date/time parameter to be passed to it. – Rory Feb 03 '15 at 10:02
Bevan means that `Application.Wait DateAdd("s", 0.1, Now)` is equivalent to `Call Application.Wait(DateAdd("s", 0.1, Now))` – ChaimG Feb 03 '15 at 20:03

Why does Application.Wait get a compile error: "Invalid Qualifier"

2 Answers2