Solved: Automatically start the macro in 15mins

[kbsudhir]

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

[lucas]

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

[Bob Phillips]

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]

[kbsudhir]

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]

Do you have a problem with xld's code?

[kbsudhir]

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]

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]

so how do you define a procedure?

[kbsudhir]

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]

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]

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

[kbsudhir]

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

[Bob Phillips]

Is your module called trial as well by any chance?

[Bob Phillips]

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

[lucas]

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

[kbsudhir]

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

[kbsudhir]

Hi Guys Thanks.

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

Thanks Lucas and Xld

[lucas]

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

[lucas]

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

[kbsudhir]

Thanks once again