Pause Macro

[JoeExcelHelp]

I have a few macros that I'm calling and was curious if a macro can be created to pause in between calls to allow the WB to update?.. hope this makes sense Thank You

[HansV]

What do you mean by update? Unless you set calculation to manual, Excel will continuously update formulas, so there should be no need to pause.

[Rudi]

    
Have a look at this page for techniques to delay running code...

[JoeExcelHelp]

This may be my own paranoia but
because sheets have embedded formulas and all my "call" macro results are dependent the previous
my concern is the next macro will run before the previous data populates
Is that possible?

[HansV]

Hard to say. You could, however, do something like this:

Code: Select all

Sub Macro1()
    ' your code here
    ...
    Application.OnTime Now + TimeSerial(0, 0, 2), "Macro2"
End Sub

Sub Macro2()
    ' your code here
    ...
    Application.OnTime Now + TimeSerial(0, 0, 2), "Macro3"
End Sub

Sub Macro3()
    ' your code here
    ...
End Sub

In this example, Macro2 will start 2 seconds after Macro1 has finished, and Macro3 will start 2 seconds after Macro2 has finished. You can change the interval of course, and extend the chain if required.

[JoeExcelHelp]

would this work with calling a macro?.. also what are the 3 dots :)

Code: Select all

Sub Macro1()
    Call Module1.Test1
    ...
    Application.OnTime Now + TimeSerial(0, 0, 2), "Macro2"
End Sub

Sub Macro2()
    Call Module2.Test2
    ...
    Application.OnTime Now + TimeSerial(0, 0, 2), "Macro3"

[HansV]

The ... was intended to represent the code of Macro1. Just remove it.

[Doc.AElstein]

Hi JoeExcelHelp,
I tend to be a bit paranoid ( mostly due to my novice level ) about if things are going to quick and tripping over themselves. Unless speed is very important to me, I liberally use the simplest
Application.Wait (Now + TimeValue("00:00:03")) 'Wait for 3 seconds
aroung my code. I can understand this one easy. – I am waiting until a specific time of Day which is equal to the time Now ( that is to say when the program passes code line, until 3 seconds after ). So in simple English that even I can understand, that was a wait, or pause, in my code of 3 seconds.
This
Application.Wait (Now + TimeValue("00:01:58")) 'Wait for 1 minute and 58 seconds
waits or pauses for 1 minute and 58 seconds.

The article Rudi suggested summarizes very nicely all the ways to pause. I have used the others ways suggested there. Mostly when I have had to wait or time things for under 1 second. ( The only disadvantage I can see of using Now or .Wait is that you cannot go under 1 second I think.)

I am a bit nervous about using these “API” things which I do not really understand. I thought I even read somewhere that the “kernel32” Library thing was not done through API interface thing and instead went “direct” to the required library, - but that is all a grey computer area to me.

Anyway I recommend you read that article, it is summaries nicely.
( But it did not catch the Application.OnTime from Han’s which he has used in a similar fashion to the Application.Wait – If I have understood correctly , then this

Application.OnTime Now + TimeSerial(0, 0, 5), "Macro2"

and this

Application.Wait (Now + TimeValue("00:00:05"))
Call Macro2

are similar, both doing the same in end effect: - They both wait 5 seconds before starting Macro2

Alan

[HansV]

I think it's safer to use Application.OnTime.

Application.Wait suspends all Microsoft Excel activity until the specified moment.

Application.OnTime, on the other hand, schedules the macro to be run at the specified moment, and lets Excel get on with what it's doing in the meantime.

[Doc.AElstein]

...
Application.Wait suspends all Microsoft Excel activity until the specified moment.
Application.OnTime, on the other hand, schedules the macro to be run at the specified moment, and lets Excel get on with what it's doing in the meantime.

Hi Hans,
Thanks, that is a good point.
So if you are specifically wanting to run a macro, then

Code: Select all

Application.OnTime EarliestTime:=(Now + TimeValue("00:00:05")) , Procedure:= “Macro2”

Is the equivalent of like

Code: Select all

Let StrtTime = Now + TimeValue("00:00:05")
    Do
        DoEvents
    Loop Until Now >= StrtTime
Call Macro2

Have I got the right ? or is there any other difference in the two?

Alan

P.s. Do you know if the Lib "kernel32" is an API “call” thing, or a “direct” call, … not that I am too sure what that all means – as I understand it with a “direct call” thing you go directly to the Library somehow.
With the API “call” thing you go to some internationally agreed and contributed to “Interface” thing.
As I understand it the direct call is simpler but if the thing you are “calling” changes a bit in the future , then any code you wrote to “call” it might mess up.
The API is some International Interface Standard thing which people using agree to update when they change anything that stuff of theirs uses it. So if you go there instead your code is more likely to work in the future when the supplier of the Library or whatever changes anything
_ -- is that as a Layman’s explanation approximately correct?

The reason I shy away from using those is that I sometimes hear of Bugs caused by API calls in VBA or Excel

[HansV]

Using Application.OnTime is much more efficient than using a loop with DoEvents since OnTime uses compiled code instead of a VBA loop. The overhead is much less.

There are situations in which using a Windows API call is useful or even indispensable, but I'd prefer to use Application.OnTime here. (I suspect that Application.OnTime uses API calls but I don't know how it has been programmed).

Windows API is not some kind of international standard. It is a way to use the core Windows functionality in a programming language such as VBA. See Windows API.

[Doc.AElstein]

Thanks Hans
Alan

[Doc.AElstein]

Hi,
I was just passing to pick up The Application.OnTime which I saw the first time here.
I just thought a quick note in the Thread might not go a miss of a very simple but neat use of it

I had been considering ways of checking thinks from time to time...
http://www.excelforum.com/excel-program ... macro.html" onclick="window.open(this.href);return false;
The following simple code
Sub CheckSomethingNowAndAgain()
does that.
The first time you run the code it sets the code to call itself in a few seconds. In real use you would replace the Message box with what you want checked or done. I guess the obvious use would be to do your own custom saving of stuff from time to time
To stop the code you run the Sub TurnMeOff() code
To Turn it back on you run the Sub TurnMeOn() code and run the main Sub CheckSomethingNowAndAgain()

These codes in a normal code module

Code: Select all

 Option Explicit
Private StopCode As Boolean
Sub CheckSomethingNowAndAgain()
    If StopCode = True Then Exit Sub ' Run Code TurnMeOff or Ctrl+Break ( Strg+Pause )
 Application.OnTime Now + TimeValue("00:00:10"), "CheckSomethingNowAndAgain" ' Run Code CheckSomethingNowAndAgain at a Time of Now+10 seconds
 MsgBox prompt:="Hello, just checking before Bedtime"
End Sub
Sub TurnMeOff()
 Let StopCode = True
End Sub
Sub TurnMeOn()
 Let StopCode = False
End Sub








'    http://www.eileenslounge.com/viewtopic.php?f=27&t=25140
'    http://www.excelforum.com/excel-programming-vba-macros/1164596-copying-cell-value-s-to-another-cell-s-every-day-at-3pm.html
'    https://www.experts-exchange.com/questions/22891939/Pasting-all-item-on-clipboard-to-an-excel-sheet-using-vba.html
 

Alternatively to set the thing off when you open the Workbook with the code in it, then this would be one way to do that: these codes in a normal code module

Code: Select all

 Option Explicit
Public StopCode As Boolean
Sub CheckSomethingNowAndAgain()
    If StopCode = True Then Exit Sub ' Run Code TurnMeOff or Ctrl+Break ( Strg+Pause )
 Application.OnTime Now + TimeValue("00:00:10"), "CheckSomethingNowAndAgain" ' Run Code CheckSomethingNowAndAgain at a Time of Now+10 seconds
 MsgBox prompt:="Hello, just checking before Bedtime"
End Sub
Sub TurnMeOff()
 Let StopCode = True
End Sub

And in ThisWorkbook Code Module

Code: Select all

Option Explicit
Private Sub Workbook_Open()
 Let StopCode = False
 Call CheckSomethingNowAndAgain
End Sub

Alan

[HansV]

One thing to be aware of: the timer used by Application.OnTime is independent of the workbook. So if the timer is still active when the workbook is closed, it will run the macro at the scheduled time, and to do so, it will reopen the workbook. This is not desirable in most cases, so it's best to stop the scheduler when the workbook is closed.

In the standard module:

Code: Select all

Public StopCode As Boolean
Public dtmNext As Date ' to keep track of the scheduled time

Sub CheckSomethingNowAndAgain()
    If StopCode = True Then Exit Sub
    dtmNext = Now + TimeValue("00:00:10")
    MsgBox prompt:="Hello, just checking before Bedtime"
    Application.OnTime dtmNext, "CheckSomethingNowAndAgain"
End Sub

Sub TurnMeOff()
    StopCode = True
End Sub

and in the ThisWorkbook module:

Code: Select all

Private Sub Workbook_BeforeClose(Cancel As Boolean)
    On Error Resume Next
    ' Stop the scheduler
    Application.OnTime dtmNext, "CheckSomethingNowAndAgain", , False
End Sub

Private Sub Workbook_Open()
    StopCode = False
    Call CheckSomethingNowAndAgain
End Sub

Remark: setting StopCode to True in the Workbook_BeforeClose event procedure won't work, since the variable is cleared when the workbook is closed.

[Doc.AElstein]

Hi Hans,
Ahh thanks, I confess I was puzzled why the Workbook sometime reopened. If I disabled the macros on it reopening then closed it that seemed to cure that problem, but I see now what you have shown is the correct way to do it
Thanks, I will pass that jem on where I am using that code.
http://www.excelforum.com/showthread.ph ... ost4530460" onclick="window.open(this.href);return false;

_.. and

....Remark: setting StopCode to True in the Workbook_BeforeClose event procedure won't work, since the variable is cleared when the workbook is closed.

_. That explains another mystery ... they got that wrong here:
https://www.experts-exchange.com/questi ... g-vba.html" onclick="window.open(this.href);return false;
_.. which again caused the puzzling re-opening thing.

That clears that all up nicely, thanks a lot

Alan