How can I call a macro repeatedly at fixed time intervals?
You can call a macro repeatedly at a set time interval using the OnTime method and the Call statement. To call a macro four times at five-second intervals, follow these steps: • Cut and paste the following code into a new module sheet: ‘ Module level declaration of icount, inumberofcalls. This line ‘ must be at the top of the module sheet Dim icount, inumberofcalls As Integer Sub StartOnTime() ‘ Initialize icount to 1. icount = 1 ‘ Initialize inumberofcalls to 4. inumberofcalls = 4 ‘ Select the range of cells for formatting. Range(“A2:A” & inumberofcalls + 1).Select ‘ Format the selected cells as time. Selection.NumberFormat = “h:mm:ss AM/PM” ‘ Start in cell A1. Range(“A1”).Select ‘ Put the word “Time” in cell A1. ActiveCell.Value = “Time” ‘ Start the OnTimeMacro. Call OnTimeMacro End Sub Sub OnTimeMacro() ‘ Run the RunEvery5seconds macro inumberofcalls times. If icount • Run the macro StartOnTime. The macro enters the time at which it ran into cells A2, A3, A4, and A5 on Sheet1 of the
