PDA

View Full Version : Solved: Automatically start the macro in 15mins



kbsudhir
10-31-2007, 09:24 AM
Hi All,

I want to start the macro after 15mins after it stops first time automatically.

As I don't have to go and manually start each time.

Is this possible..????

If yes, then please provide me the code for the same.


Thanks
Sudhir :help

lucas
10-31-2007, 09:32 AM
Application.OnTime Now + TimeValue("00:00:15"), "my_Procedure"

Bob Phillips
10-31-2007, 12:10 PM
I think he wants to do this continually.

If so, create a Public variable called say nTime of type Double

at the end of your my_Procedure macro, add



nTime = Now + TimeValue("00:00:15")
Application.OnTime nTime, "my_Procedure"


and then in the BeforeClose event procedure, add



Application.OnTime nTime, "my_Procedure",, False

kbsudhir
10-31-2007, 01:02 PM
I am initiating the macro on a button click. Once it is over then it should automatically start after 15mins every time, till the file is closed.

TonyJollans
10-31-2007, 01:17 PM
Do you have a problem with xld's code?

kbsudhir
10-31-2007, 01:58 PM
I am getting the error "Ambiguous name detected: trial" Trial is the name of the procedure.

Here I am displaying a msgbox, which once done should be diplayed after every 15mins.

Please help

Code:


Public Sub trial()
Dim nTime As Double
MsgBox ("Hello")
nTime = Now + TimeValue("00:00:30")
Application.OnTime nTime, "trial"

End Sub

lucas
10-31-2007, 02:44 PM
Ambiguous name usually means that you have two subs with the same name....

In the sub you posted in post #6 you have a sub named trial....and you are calling a procedure within it named "trial".....that is probably the problem...

figment
10-31-2007, 03:06 PM
so how do you define a procedure?

kbsudhir
10-31-2007, 03:18 PM
Thanks Lucas,

I have made some changes after ur advise, still facing the same error.
Now I am calling trial from another procedure. Attaching code for ur reference.

Please let me know how to go about it.

Sub trial()
MsgBox ("Hello")
End Sub
Sub automate()
Dim nTime As Double
trial
nTime = Now + TimeValue("00:00:30")
Application.OnTime nTime, "trial"

End Sub

lucas
10-31-2007, 03:27 PM
Runs ok for me ......I changed it to 10 seconds because I'm impatient

run automate and you get the messagebox.
close the messagebox and wait 10 seconds.....messagebox again.

if you're still having problems i would guess that you have another sub named trial or automate....

see attached.

lucas
10-31-2007, 03:34 PM
Ok I see....it doesn't keep repeating....

kbsudhir
10-31-2007, 03:44 PM
U have used "Option Explicit" can you please tell what exactly is it..??

Bob Phillips
10-31-2007, 03:46 PM
Is your module called trial as well by any chance?

Bob Phillips
10-31-2007, 03:50 PM
BTW the code should be like this.

In a standard code module



Public nTime As Double

Sub trial()
MsgBox ("Hello")
nTime = Now + TimeValue("00:00:30")
Application.OnTime nTime, "trial"
End Sub


In ThisWorkbook




Private Sub Workbook_BeforeClose(Cancel As Boolean)
Application.OnTime nTime, "trial", , False
End Sub

Private Sub Workbook_Open()
trial
End Sub


This is workbook event code.
To input this code, right click on the Excel icon on the worksheet
(or next to the File menu if you maximise your workbooks),
select View Code from the menu, and paste the code

lucas
10-31-2007, 03:54 PM
You can call it again from the messagbox sub
Option Explicit
Public nTime As Double
Sub trial()
MsgBox ("Hello")
automate
End Sub
Sub automate()
'trial
nTime = Now + TimeValue("00:00:05")
Application.OnTime nTime, "trial"
End Sub

Problem is it is not looping Bob...so I just called automate again each time from the sub trial

kbsudhir
10-31-2007, 04:05 PM
Thanks Lucas,
Can u please tell why and when option explicit is used

kbsudhir
10-31-2007, 04:14 PM
Hi Guys Thanks.:thumb

Now I got to know more than one way of doing this.

Thanks Lucas and Xld :bow:

lucas
10-31-2007, 04:26 PM
Thanks Lucas,
Can u please tell why and when option explicit is used

From the help files:


If used, the Option Explicit statement must appear in a module before any procedures (javascript:hhobj_7.Click()).
When Option Explicit appears in a module, you must explicitly declare all variables using the Dim, Private, Public, ReDim, or Static statements. If you attempt to use an undeclared variable name, an error occurs at compile time (javascript:hhobj_8.Click()).
If you don't use the Option Explicit statement, all undeclared variables are of Variant type unless the default type is otherwise specified with a Deftype statement.
Note Use Option Explicit to avoid incorrectly typing the name of an existing variable or to avoid confusion in code where the scope (javascript:hhobj_9.Click()) of the variable is not clear.



It helps avoid misnamed variables, etc. by throwing an error.....
It is a good code practice

lucas
10-31-2007, 04:27 PM
When to use Option Explicit......always

kbsudhir
11-01-2007, 11:21 AM
Thanks once again