Timer Not Working

nniedzielski

Well-known Member
Joined
Jan 8, 2016
Messages
535
Office Version
  1. 2019
Platform
  1. Windows
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.

VBA Code:
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
 

Excel Facts

How to show all formulas in Excel?
Press Ctrl+` to show all formulas. Press it again to toggle back to numbers. The grave accent is often under the tilde on US keyboards.

Logit

Well-known Member
Joined
Aug 31, 2016
Messages
4,111
Not quite certain what you are wanting other than the obvious .. a timer.

Here is timer code that goes in a Regular Module :

VBA Code:
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

Banned user
Joined
Mar 2, 2014
Messages
3,080
Office Version
  1. 2010
Platform
  1. Windows
the timer always says it ran in zero

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

VBA Code:
' 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:

VBA Code:
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
 
Solution

Forum statistics

Threads
1,137,154
Messages
5,679,904
Members
419,861
Latest member
AceDaMace

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top