Solved: Automatically start the macro in 15mins

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

[VBA]Application.OnTime Now + TimeValue("00:00:15"), "my_Procedure"[/VBA]

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

[vba]

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

and then in the BeforeClose event procedure, add

[vba]

Application.OnTime nTime, "my_Procedure",, False
[/vba]

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.

Do you have a problem with xld's code?

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

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...

so how do you define a procedure?

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

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.

Ok I see....it doesn't keep repeating....

U have used "Option Explicit" can you please tell what exactly is it..??

Is your module called trial as well by any chance?

BTW the code should be like this.

In a standard code module

[vba]

Public nTime As Double

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

In ThisWorkbook

[vba]


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

Private Sub Workbook_Open()
trial
End Sub
[/vba]

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

You can call it again from the messagbox sub
[VBA]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[/VBA]

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

Thanks Lucas,
Can u please tell why and when option explicit is used

Hi Guys Thanks.:thumb

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

Thanks Lucas and Xld :bow:

Quote:

---

Originally Posted by kbsudhir

Thanks Lucas,
Can u please tell why and when option explicit is used

---

From the help files:

Quote:

---

If used, the Option Explicit statement must appear in a module before any procedures.
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.
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 of the variable is not clear.

---

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

When to use Option Explicit......always

Thanks once again