Timer difference in running the same code

yxz152830

Active Member
Joined
Oct 6, 2021
Messages
385
Office Version
  1. 365
Platform
  1. Windows
Gurus,
for below code, timer takes down 10 records but 2 records are 1 sec lower than the other 8. Is there any reason behind this or this is within the deviation range? Thank you.

VBA Code:
Sub pop()
For x = 1 To 10
st = Timer
d = Now + TimeValue("00:00:03")
For Each cell In Range(Cells(18, 1), Cells(18, 10))
Do
Loop Until d <= Now
d = Now + TimeValue("00:00:03")
cell.Interior.ColorIndex = 3
Next cell
st = Timer - st
Range("o100").End(xlUp).Offset(1, 0) = st
Range(Cells(18, 1), Cells(18, 10)).Clear
Next x
End Sub



timer
30.86328​
31.99609​
31.98828​
31.99609​
30.99609​
31.99609​
31.99609​
31.99609​
31.99609​
31.99609​
 

Excel Facts

Get help while writing formula
Click the italics "fx" icon to the left of the formula bar to open the Functions Arguments dialog. Help is displayed for each argument.
timer works in milleseconds, the other one is per second,
that's like an old clock that jumps every second forwards.
So that's why there can be a difference of max. 0.999 sec between both
 
Upvote 0
Solution
timer works in milleseconds, the other one is per second,
that's like an old clock that jumps every second forwards.
So that's why there can be a difference of max. 0.999 sec between both
which one is per second?
 
Upvote 0
VBA Code:
Sub chrono()
     For i = 1 To 10
          Nu = Now: T = Timer                                   'take the actual values for now (precision seconds, unit = 1 day) and timer (precision milliseconds, unit = sec)
          msec = T - (Nu - Date) * 86400
          MsgBox "the time is now is : " & vbLf & "normal time known in excel " & Format(Nu, "hh:mm:ss,000") & vbLf & "extra milliseconds : " & Format(msec, "0.000")
     Next
End Sub
if you really want precise milliseconds, then use another (more difficult) method, accuracy here is the 1st digit (hundredth of a second).
i toke the more difficult formula msec = T - (Nu - Date) * 86400 but msec= t- int(t) is as good. With the 1st one you have an extra unnecessary check.

Edit: i had to use nu and t in the next lines of the macro, because 1 line further, the timer is also a little bit further, or in case of running in debug mode even more.
 
Last edited:
Upvote 0
timer works in milleseconds, the other one is per second,
that's like an old clock that jumps every second forwards.
So that's why there can be a difference of max. 0.999 sec between both
is it Now or TimeValue that jumps per second?
 
Upvote 0
you add a timevalue (smallest part = sec) to a timestamp (smallest part = sec), so the sum is in seconds.
Timer is in milliseconds, but why should you use that ?
The only thing i can imagine is a small delay or an internal chronometer (for execution time of a macro)
 
Upvote 0
you add a timevalue (smallest part = sec) to a timestamp (smallest part = sec), so the sum is in seconds.
Timer is in milliseconds, but why should you use that ?
The only thing i can imagine is a small delay or an internal chronometer (for execution time of a macro)
now I got it! Last question please. if I want to make sure that the whole code is run at 30.0xxx secs. How do I adjust the code? Thanks!
VBA Code:
Sub pop()
For x = 1 To 10
st = Timer
d = Now + TimeValue("00:00:03")
For Each cell In Range(Cells(18, 1), Cells(18, 10))
Do
Loop Until d <= Now
d = Now + TimeValue("00:00:03")
cell.Interior.ColorIndex = 3
Next cell
st = Timer - st
Range("o100").End(xlUp).Offset(1, 0) = st
Range(Cells(18, 1), Cells(18, 10)).Clear
Next x
End Sub
 
Upvote 0
Gurus,
which timing is more accurate? Why is there a tiny difference? Thanks!
VBA Code:
Sub timetime()

Debug.Print Timer
Debug.Print (Now - Date) * 24 * 60 * 60

End Sub
 
Upvote 0
Gurus,
which timing is more accurate? Why is there a tiny difference? Thanks!
VBA Code:
Sub timetime()

Debug.Print Timer
Debug.Print (Now - Date) * 24 * 60 * 60

End Sub

If you are trying to return number of seconds elapsed since midnight with fractional portions of a second then TIMER is more accurate than (Now - Date) * 24 * 60 * 60.
 
Upvote 0

Forum statistics

Threads
1,213,483
Messages
6,113,919
Members
448,533
Latest member
thietbibeboiwasaco

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
Back
Top