Selected Answer
The system you are looking for consists of a total of 5 procedures plus a publicly declared variable of double type, to hold a time value. The following 3 procedures (and the public variable) should be in a standard code module, Module1 in the attached workbook.
Option Explicit
Public RunWhen As Double
Sub StartTimer()
' May 01, 2019
RunWhen = Now + TimeSerial(0, 1, 0) ' set hours, minutes, seconds
Application.OnTime EarliestTime:=RunWhen, Procedure:="MyProcedure", Schedule:=True
End Sub
Sub StopTimer()
' May 01, 2019
On Error Resume Next
Application.OnTime EarliestTime:=RunWhen, Procedure:="MyProcedure", Schedule:=False
End Sub
Sub MyProcedure()
' May 01, 2019
Sheet1.Cells(1, 1).Value = Now()
Call StartTimer
End Sub
The other two procedures, listed below, must be pasted to the pre-existing class module called ThisWorkbook.
Private Sub Workbook_Open()
' May 01, 2019
MyProcedure
End Sub
Private Sub Workbook_BeforeClose(Cancel As Boolean)
' May 01, 2019
StopTimer
End Sub
The work horse in the system is the procedure called MyProcedure. You can change both its name and its function. In this example the current time is written to cell A1 of Sheet1 in the ActiveWorkbook. Observe that the sheet is addressed by its CodeName which doesn't change when the tab is renamed. You might use the tab name instead: ThisWorkbook.Worksheets("Sheet1").Cells(1, 1).Value = Now()
Sub Workbook_Open runs automatically when the workbook is opened. It calls the procedure MyProcedure which updates A1 (you might use Range("A1") instead of Cells(1,1)) and then calls the procedure StartTimer in its last line. This procedure sets the application's OnTime method to run MyProcedure again after one minute. You can set a different interval or call a procedure of another name.
This loop, MyProcedure - StartTime - MyProcedure - StartTime - repeats indefinitely. It would even repeat after closing the workbook. Hence the Sub Workbook_BeforeClose which runs automatically when the workbook is closed. It calls the procedure StopTimer which ends the loop. Be sure that it specifies the same procedure (here "MyProcedure") as StartTimer. Technically, StartTimer could be a Private procedure in the ThisWorkbook module because it isn't called anywhere else. In the attached working sample workbook I placed it in the standard code module next to the StartTimer procdure because they are a logical pair.
