CODEWITHSUNDEEP
excelloopsmacrosvba
Josh
Josh

Reputation: 137

Excel 2007 VBA To Pause Macro

I currently have a macro that reaches out to a directory and pulls some data into my spreadsheet. Originally I just had a button to bring the information in and a button to erase everything but needs have changed and I now need it to automatically run the macro to clear the existing data and then pull new data every hour or so.

Looking through the different ways I could potentially pause the macro is appears that a loop is the best way to go for me because it will allow me to still interact with the spreadsheet while the wait is running.

All that said, here is what I currently have which seems to simply loop through everything without ever pausing at all.

At the end of my macro I use the following

Call DelayOneHour 
Call RepActiveTicketsDelete 
Call DelayTwoSeconds
Call RepActiveTicketsDataGrab

And here are the loops

Sub DelayOneHour()

    Dim NowTick As Long
    Dim EndTick As Long

    NowTick = Now
    EndTick = Now + TimeValue("0:01:00")

    Do Until NowTick >= EndTick
        DoEvents
        NowTick = Now()
    Loop

End Sub

Sub DelayTwoSeconds()

    Dim NowTick As Long
    Dim EndTick As Long

    NowTick = Now
    EndTick = Now + TimeValue("0:01:00")

    Do Until NowTick >= EndTick
        DoEvents
        NowTick = Now()
    Loop

End Sub

Thanks for looking!

Upvotes: 0

Views: 292

Answers (1)

David Zemens
David Zemens

Reputation: 53623

I'd consider Application.OnTime as a way to effectively schedule a procedure to run at a later time. This frees up the runtime environment (i.e., so you could run other macros from other workbooks during the intervening 1 hour, etc).

Instead of:

Call DelayOneHour 
Call RepActiveTicketsDelete 
Call DelayTwoSeconds
Call RepActiveTicketsDataGrab

Do this:

'Run the specified procedure in one hour:
Application.OnTime Now + TimeValue("00:60:00"), "RepActiveTicketsDelete"
Exit Sub

Then, at the end of the RepActiveTicketsDelete procedure, you scheduled the RepActiveTicketsDataGrab procedure:

'Run the specified procedure in two seconds:
Application.OnTime Now + TimeValue("00:00:02"), "RepActiveTicketsDataGrab"
Exit Sub

Upvotes: 2

Related Questions