PDA

View Full Version : [SOLVED] VBA To Run Another Macro at An Interval



Ken1000
11-05-2007, 07:47 PM
Hi. Could you possibly help me to create proper VBA code that would read an interval from a cell, and then using that increment, start running a specified macro from then on, at that increment?

For instance, say cell C3 on Sheet1 is where that timing interval is stored. If Cell C3 has 15 in it, it means the timing interval is every 15 minutes.
(The timing interval may change from day to day.)

I need VBA code to read that value in cell C3, and immediately after reading it, run macro "XYZ", and then every interval after that, run the same macro, until the workbook is closed. (In this case, every 15 minutes.)

I have the following code, but do not now how to get the original calling macro to run macro "XYZ" at the specified interval, repeatedly.


xinterval = Range("Sheet1!C3").value
'Run XYZ immediately and then, continuoslu, at value of xinterval

Could you please help me by sharing code that would do this?

Thank you very, very much.

Ken1000

Bob Phillips
11-05-2007, 10:01 PM
In a standard code module



Public nTime As Double
Public nDelay As Double

Public Sub RunOntime()
Call XYZ
nTime = Now + TimeSerial(0, nDelay, 0)
Application.OnTime nTime, "RunOntime"
End Sub


In ThisWorkbook code module



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

Private Sub Workbook_Open()
nDelay = Worksheets("Sheet1").Range("C3").Value
Call RunOntime
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

Ken1000
11-06-2007, 04:03 AM
Thank you very much for replying so fast. I will try out this code later today. Again, many, many thanks.

Ken1000