Excel Timers

SetInterval("00:00:02")

Call Schedule() to start the timer. Embed calls to routines we want to execute at this interval in this subroutine.

Call Unschedule() to stop the timer

Option Explicit

Dim dNextEvent As Date

Dim dInterval As Date

Public Sub SetInterval(ByVal vInterval As Variant)

dInterval = CDate(vInterval)

End Sub

Public Sub Schedule()

'Example of how to display results: [A1].Value = Now

dNextEvent = Now + dInterval

Application.OnTime EarliestTime:=dNextEvent, _

Procedure:="Schedule", _

Schedule:=True

End Sub

Public Sub Unschedule()

Application.OnTime EarliestTime:=dNextEvent, _

Procedure:="Schedule", _

Schedule:=False

End Sub

Power Query

This method creates a table with the current timestamp. When the table changes it generates a worksheet change event which we can leverage to trigger other actions such as sending out emails or refreshing PivotTables.

Steps

• • 1. In Excel use menu option: Data > New Query > From other sources > Blank Query

    2. In PQ use menu option Advanced Editor and make this edit

       1. 1. let

             1. Source = DateTime.LocalNow

          2. in

• • • • • 1. Source

• • 3. Click Done

• 1. Click Invoke

  2. Click File > Close & Load

  3. In Excel click Table Tools Design > Properties

  4. Click small icon (connection properties) next to Name:

  5. Check Refresh Every and set to however many minutes you want

  6. Click OK twice to close properties dialog

Summary

Both methods allow us to continue working with Excel normally. US the VBA method if you are comfortable with VBA and can deal with Excel's trust warnings. Use the PQ method if you don't need the timer to be to-the-second accurate and would like to avoid VBA concerns.