How do I make macro run every minute?

[Legacy 76526]

Hi All,

I have a macro called MyMacro which I would like to run every minute starting from 08:00:00 and finish at 14:00:00.

Thanks for any assistance.

 

[Legacy 76526]

Thanks Yard. Most helpful.

 

[slomobonobo]

Might be worth a look:

http://www.tek-tips.com/viewthread.cfm?qid=821009

 

[Legacy 76526]

For those who are interested the following works better as it doesnt use the Windows API timer. This macro also allows the user to switch worksheets without it reverting back to the other worksheet every 60 seconds. Calling Start will have the macro run every minute from 8am to 2 pm. Call StopTimer to kill it.

Public RunWhen As Date
Public Const cRunIntervalSeconds = 60 'adjust to the time you wish
Public Const cRunWhat = "TheSub"

Sub StartTimer()
    RunWhen = Now + TimeSerial(0, 0, cRunIntervalSeconds)
    Application.OnTime EarliestTime:=RunWhen, Procedure:=cRunWhat, _
        Schedule:=True
End Sub

Sub TheSub()
If ActiveSheet.Name = "MySheetName" Then
    MyMacro
    StartTimer
   Else: StartTimer
End If
End Sub

Sub StopTimer()
    On Error Resume Next
    Application.OnTime EarliestTime:=RunWhen, Procedure:=cRunWhat, _
        Schedule:=False
End Sub

Sub Start()
    Application.OnTime Date + TimeSerial(8, 0, 0), "StartTimer", Date + TimeSerial(14, 0, 0), Schedule:=True
    End Sub
    
Sub Finish()
    Application.OnTime Date + TimeSerial(14, 0, 30), "StopTimer", Date + TimeSerial(14, 1, 0), Schedule:=True
    End Sub

If only there was a way to make cRunIntervalSeconds call a cell value. Have tried
Public Const cRunIntervalSeconds = cells("A1").value, but all I get is a Mismatch error.

 

[tusharm]

Check Excel VBA help for information on the different data types.

Why would I declare it as Date? Because that's how I want to use the variable.

Can someone tell me what the difference between "RunWhen as Date" and "RunWhen as Double" is please. What is meant bt Double?

Thanks for assitance.

Brad

 

[tusharm]

{snip}
If only there was a way to make cRunIntervalSeconds call a cell value. Have tried
Public Const cRunIntervalSeconds = cells("A1").value, but all I get is a Mismatch error.

Check Excel VBA help to see what it means to declare something as a Const.

In your case, I'd consider

Sub StartTimer()
    Dim RunInterval As Integer
    
    RunInterval = cRunIntervalSeconds
    On Error Resume Next
    RunInterval = Application.Workbooks("MyWorkbook").Worksheets("MyWorksheet").Range("MyTimeInterval").Value
    On Error GoTo 0

    RunWhen = Now + TimeSerial(0, 0, RunInterval)
    Application.OnTime EarliestTime:=RunWhen, Procedure:=cRunWhat, _
        Schedule:=True
    End Sub