Allow user to kill process during wait in VBA

Question

I have written a program to print multiple .pdfs with varying file extensions off of an Excel spreadsheet list.
The problem is that it takes anywhere from 30 seconds to a minute for the printer to receive the pdf after the line: Application.SendKeys "^p~", False is called.
To get by this I used Application.Wait (Now + TimeValue("0:01:03")) to wait for a minute (plus 3 seconds just to be safe) before closing the file. To me there seems like there should be a better way than just causing the program to wait, so I looked around a little and found a question about this lovely gem known as Application.OnTime.

I tried a sample of one of the answers:

Sub test2()
    ActiveSheet.Cells(1, 1).Value = ActiveSheet.Cells(1, 1).Value + 1
    Application.OnTime Now + TimeValue("00:00:5"), "test2"
End Sub

However when I tried to stop the above code it kept going on an infinite loop and I was unable to stop it until I killed excel using the windows task manager.

I would like to be able to add in a little message box or something of the sort so that the user can click in between the wait time.
So that while the program is waiting for a minute, the user can either manually click and start the program on the next pdf, or click another button to exit if they need to stop printing early. Something like this:

Sub pdfPrinter()
    '...
    'Insert all the other code here
    '...
    Application.SendKeys "^p~", False
    Application.OnTime Now + TimeValue("00:01:02"), "pdfPrinter"
    continue= MsgBox("Click Retry to print again, or cancel to stop printer.", vbRetryCancel)
    If continue = vbRetry Then
        Call pdfPrinter
    ElseIf continue = vbCancel Then
        Exit Sub
    End If
End Sub

i think you may want to [simulate multi-threading](http://www.excelhero.com/blog/2010/05/multi-threaded-vba.html) — , Jun 20 '13 at 12:53
@mehow: Application.OnTime is indeed asyschronous and runs in the different Thread of the main program. Sadly Implementing multi-threading in VBA/VB6 is very unstable and needs direct minipulation of windows API. — S Nash, Jun 20 '13 at 13:09

score 3 · Accepted Answer · answered Jun 20 '13 at 13:04

3

Application.OnTime(unlike Application.Wait) is Asynchronous

so code after

Application.OnTime Now + TimeValue("00:01:02"), "pdfPrinter"

Runs Immediately.

If you want to print another PDF you need to call Application.OnTime with a different schedule of the 1st one.

Now, canceling Application.OnTime, when it is already started is a different story:

To do that you need to store that time that the respective function is scheduled to run and then Cancel it using the following code:

Application.OnTime Now + TimeValue("00:00:50"), "test", schedule:=False

answered Jun 20 '13 at 13:04

S Nash

2,363
3
34
64

Wow thanks for the quick answer! A quick question though: What would the code look like to store the scheduled time? – hammythepig Jun 20 '13 at 15:19
Opps another question: and how could I close a message box opened after the line `Application.OnTime Now + TimeValue("00:01:02"), "pdfPrinter"` If the time hits and the user has not closed the message box themselves? I dont want 50 messageboxes to appear if the program is left alone, and i want it to keep printing without stopping if the message box comes up – hammythepig Jun 20 '13 at 15:33
Wow thanks for the quick answer! A quick question though: What would the code look like to store the scheduled time? This needs some calculation on your side. for example You need to fiugure out what time, Now()+ TimeValue("00:00:50") is. To do this take a look at Excel's built in functions(DateAdd) – S Nash Jun 21 '13 at 11:25

Allow user to kill process during wait in VBA

1 Answers1