Pause Macro
-
- 5StarLounger
- Posts: 1177
- Joined: 22 Jul 2013, 18:29
Pause Macro
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
-
- Administrator
- Posts: 78608
- Joined: 16 Jan 2010, 00:14
- Status: Microsoft MVP
- Location: Wageningen, The Netherlands
Re: Pause Macro
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.
Best wishes,
Hans
Hans
-
- gamma jay
- Posts: 25455
- Joined: 17 Mar 2010, 17:33
- Location: Cape Town
Re: Pause Macro
Have a look at this page for techniques to delay running code...
Regards,
Rudi
If your absence does not affect them, your presence didn't matter.
Rudi
If your absence does not affect them, your presence didn't matter.
-
- 5StarLounger
- Posts: 1177
- Joined: 22 Jul 2013, 18:29
Re: Pause Macro
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?
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?
-
- Administrator
- Posts: 78608
- Joined: 16 Jan 2010, 00:14
- Status: Microsoft MVP
- Location: Wageningen, The Netherlands
Re: Pause Macro
Hard to say. You could, however, do something like this:
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.
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
Best wishes,
Hans
Hans
-
- 5StarLounger
- Posts: 1177
- Joined: 22 Jul 2013, 18:29
Re: Pause Macro
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"
-
- Administrator
- Posts: 78608
- Joined: 16 Jan 2010, 00:14
- Status: Microsoft MVP
- Location: Wageningen, The Netherlands
Re: Pause Macro
The ... was intended to represent the code of Macro1. Just remove it.
Best wishes,
Hans
Hans
-
- BronzeLounger
- Posts: 1499
- Joined: 28 Feb 2015, 13:11
- Location: Hof, Bayern, Germany
Re: Pause Macro
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
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
I am having difficulty logging in with this account just now.
You can find me at DocAElstein also
You can find me at DocAElstein also
-
- Administrator
- Posts: 78608
- Joined: 16 Jan 2010, 00:14
- Status: Microsoft MVP
- Location: Wageningen, The Netherlands
Re: Pause Macro
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.
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.
Best wishes,
Hans
Hans
-
- BronzeLounger
- Posts: 1499
- Joined: 28 Feb 2015, 13:11
- Location: Hof, Bayern, Germany
Re: Pause Macro
Hi Hans,HansV wrote:...
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.
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”
Code: Select all
Let StrtTime = Now + TimeValue("00:00:05")
Do
DoEvents
Loop Until Now >= StrtTime
Call Macro2
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
I am having difficulty logging in with this account just now.
You can find me at DocAElstein also
You can find me at DocAElstein also
-
- Administrator
- Posts: 78608
- Joined: 16 Jan 2010, 00:14
- Status: Microsoft MVP
- Location: Wageningen, The Netherlands
Re: Pause Macro
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.
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.
Best wishes,
Hans
Hans
-
- BronzeLounger
- Posts: 1499
- Joined: 28 Feb 2015, 13:11
- Location: Hof, Bayern, Germany
Re: Pause Macro
Thanks Hans
Alan
Alan
I am having difficulty logging in with this account just now.
You can find me at DocAElstein also
You can find me at DocAElstein also
-
- BronzeLounger
- Posts: 1499
- Joined: 28 Feb 2015, 13:11
- Location: Hof, Bayern, Germany
Pause Macro Run Now and Again ;)
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
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
And in ThisWorkbook Code Module
Alan
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
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
Code: Select all
Option Explicit
Private Sub Workbook_Open()
Let StopCode = False
Call CheckSomethingNowAndAgain
End Sub
I am having difficulty logging in with this account just now.
You can find me at DocAElstein also
You can find me at DocAElstein also
-
- Administrator
- Posts: 78608
- Joined: 16 Jan 2010, 00:14
- Status: Microsoft MVP
- Location: Wageningen, The Netherlands
Re: Pause Macro
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:
and in the ThisWorkbook module:
Remark: setting StopCode to True in the Workbook_BeforeClose event procedure won't work, since the variable is cleared 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
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
Best wishes,
Hans
Hans
-
- BronzeLounger
- Posts: 1499
- Joined: 28 Feb 2015, 13:11
- Location: Hof, Bayern, Germany
Re: Pause Macro
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
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
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
_. That explains another mystery ... they got that wrong here:HansV wrote:....Remark: setting StopCode to True in the Workbook_BeforeClose event procedure won't work, since the variable is cleared when the workbook is closed.
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
I am having difficulty logging in with this account just now.
You can find me at DocAElstein also
You can find me at DocAElstein also