Timer Not Working

[nniedzielski]

Hello-

I am running a macro that does an audit of a spreadsheet, im adding in functionality and timer code, but i cannot get the time to work, this is what i have so far, and right now the timer always says it ran in zero seconds, even if i step through the code to ensure it was longer than 0 seconds.

Option Explicit
Option Compare Text
Public Sub bulkPlanAudit()
    
    startTimer
    
    turnOffFunctionality
    
    auditSteps
    
    stopTimer
    
    turnOnfunctionality
    
    reportTime
    
End Sub
Public Sub turnOffFunctionality()
    Application.Calculation = xlCalculationManual
    Application.DisplayStatusBar = False
    Application.EnableEvents = False
    Application.ScreenUpdating = False
End Sub
Public Sub turnOnfunctionality()
    Application.Calculation = xlCalculationAutomatic
    Application.DisplayStatusBar = True
    Application.EnableEvents = True
    Application.ScreenUpdating = True
End Sub
Public Sub startTimer()
    Dim startTime As Double
    startTime = Timer
End Sub
Public Sub stopTimer()
    Dim secondsElapsed As Double
    Dim startTime As Double
    secondsElapsed = Round(Timer - startTime, 2)
End Sub
Public Sub reportTime()
    Dim secondsElapsed As Double
    MsgBox "Run time was " & secondsElapsed & " seconds", vbInformation
End Sub
Public Sub auditSteps()
    'this is where my main code will be
End Sub

 

[Logit]

Not quite certain what you are wanting other than the obvious .. a timer.

Here is timer code that goes in a Regular Module :

Option Explicit

Dim SchedRecalc As Date

Sub Recalc()

With Sheet1.Range("H3")

.Value = Format(Now, "mm/dd/yyyy - hh:mm:ss AM/PM")

End With

Call SetTime

End Sub

Sub SetTime()

SchedRecalc = Now() + TimeValue("00:00:01")

Application.OnTime SchedRecalc, "Recalc"

End Sub

Sub Disable()

On Error Resume Next

Application.OnTime EarliestTime:=SchedRecalc, Procedure:="Recalc", Schedule:=False

End Sub

Sub clr()

Disable

Sheet1.Range("H3").Value = " "

End Sub

Download workbook : Timer (2021-06-09T16_27_31.826).xlsm

 

[joeu2004]

the timer always says it ran in zero

Because startTime and secondsElapsed are local variables. Change your code as follows:

' put these declarations near the beginning of the module
Dim startTime As Double
Dim secondsElapsed As Double
[....]
Public Sub startTimer()
    startTime = Timer
End Sub
Public Sub stopTimer()
    secondsElapsed = Round(Timer - startTime, 2)
End Sub
Public Sub reportTime()
    MsgBox "Run time was " & secondsElapsed & " seconds", vbInformation
End Sub

Alternatively, since each function is a single statement, eliminate the global variables and those functions, and simply write:

Public Sub bulkPlanAudit()
    Dim startTime As Double
    Dim secondsElapsed As Double

    startTime = Timer   
    turnOffFunctionality   
    auditSteps   
    secondsElapsed = Round(Timer - startTime, 2)
   
    turnOnfunctionality   
    MsgBox "Run time was " & secondsElapsed & " seconds", vbInformation   
End Sub

Caveats:

1. Timer is updated only every 15.625 milliseconds (0.015625 seconds), by default. (Some applications change that frequency.) So, the elapsed time might appear to be zero if the timed code is completely between system clock updates.

2. Timer is reset to zero at midnight. So, additional code is needed if the code between setting startTime and secondsElapsed might be executed near midnight. Something like:

secondsElapsed = Round(Timer - startTime, 2)
If secondsElapsed < 0 Then secondsElapsed = secondsElapsed + 86400