CODEWITHSUNDEEP
vbaexcel
Parth Bhatt
Parth Bhatt

Reputation: 19469

Creating a Macro in Excel which executes the other Macro every 2 seconds

I have a macro called UpdateMacro which updates my Database from my Excel

I want to create a macro called RepeatMacro where it executes the UpdateMacro every 2 seconds automatically and only Start and Stop Buttons are to be provided to start and Stop execution of the RepeatMacro.

How can it be done?

Upvotes: 1

Views: 829

Answers (2)

to StackOverflow
to StackOverflow

Reputation: 124686

Google for Application.OnTime

E.g.

Dim dtNextRunTime As Date
dtNextRunTime = Now + TimeSerial(0,0,2)
Application.OnTime dtNextRunTime, "MyProcedure", True

To clear a previously set procedure, you need to save the time at which it was scheduled )e.g. dtNextRunTime above), then use:

Application.OnTime dtNextRunTime, "MyProcedure", False

Here's a sample VB module with methods StartSchedule / StopSchedule to get you going:

Private m_dtScheduledTime As Date
Private m_lDelaySeconds As Long
Private m_bIsScheduled As Boolean

Private Sub DoWork()

    m_bIsScheduled = False

    ' ... do your work

    ' Reschedule at the same frequency once completed
    StartSchedule m_lDelaySeconds, "DoWork"

End Sub

Public Sub StartSchedule(ByVal DelaySeconds As Long)
    StopSchedule
    m_lDelaySeconds = DelaySeconds
    m_dtScheduledTime = Now + TimeSerial(0, 0, m_lDelaySeconds)
    Application.OnTime m_dtScheduledTime, "DoWork", True
    m_bIsScheduled = True
End Sub

Public Sub StopSchedule()
    If m_bIsScheduled Then
        Application.OnTime m_dtScheduledTime, "DoWork", False
        m_bIsScheduled = False
    End If
End Sub

Upvotes: 2

Jean-François Corbett
Jean-François Corbett

Reputation: 38500

This will run UpdateMacro every two seconds, assuming UpdateMacro takes less than two seconds to run.

Sub RepeatMacro()
    Dim lastRunTime
    Do
        lastRunTime = Now
        Range("A1") = "Last run: " & Format(lastRunTime, "hh:nn:ss")
        Call UpdateMacro
        DoEvents
        Application.Wait lastRunTime + TimeValue("00:00:02")
    Loop
End Sub

Sub UpdateMacro()
    Debug.Print "running UpdateMacro"
End Sub

EDIT To start and stop RepeatMacro from your sheet, make a start button and a stop button, and put the following code in your Sheet module. Also notice that I added a DoEvents in RepeatMacro above.

Private Sub StartButton_Click()
    Call RepeatMacro
End Sub

Private Sub StopButton_Click()
    MsgBox "Stopped."
    End
End Sub

Now just saying End is poor practice, but you get the idea.

Upvotes: 0

Related Questions