timer misses by quite a lot in the first loop

[yxz152830]

Gurus,
I'm kinda exploring the timing functions in excel.
I was expecting below code to produce 3 sets of time series with an interval of 2 secs and starting at around 2 secs. However the first loop is always off by .5 sec. What would be the root cause? Thanks!

Sub car_run()
Dim track As Range
Dim car As Range
Dim time_per_block As Long
Dim speed As Long
Dim length As Long
speed = Range("b6").Value
length = Range("b2").Value

Set track = Range(Cells(10, 6), Cells(10, 30))

For x = 1 To 3
starttime = Timer
time_per_block = length / speed
For Each car In track
time_per_block = time_per_block + Timer
If car.Interior.ColorIndex = 3 Then
Do
Loop Until time_per_block <= Timer
time_per_block = Range("b2") / Range("b6")
car.Interior.ColorIndex = 6
car.Offset(0, 1).Interior.ColorIndex = 3
End If

Range("o100").End(xlUp).Offset(1, 0) = Timer - starttime
Next car
brush
Next x
End Sub

each column is one execution result

timer
2.484375​	2.425781​	1.550781​
4.484375​	4.425781​	3.550781​
6.488281​	6.425781​	5.550781​
8.484375​	8.425781​	7.550781​
10.48438​	10.42578​	9.550781​
12.48438​	12.42578​	11.55078​
14.48828​	14.42578​	13.55078​
16.48438​	16.42578​	15.55078​
18.48438​	18.42578​	17.55469​
20.48438​	20.42578​	19.55078​
22.48438​	22.42578​	21.55078​
24.48828​	24.42578​	23.55078​
26.48438​	26.42578​	25.55078​
28.48438​	28.42578​	27.55078​
30.48828​	30.42578​	29.55078​
32.48438​	32.42578​	31.55078​
34.48438​	34.42578​	33.55078​
36.48438​	36.42578​	35.55078​
38.48438​	38.42578​	37.55078​
40.48438​	40.42578​	39.55078​
42.48438​	42.42578​	41.55078​
44.48438​	44.42578​	43.55078​
46.48438​	46.42578​	45.55078​
48.48438​	48.42578​	47.55078​
50.48438​	50.42578​	49.55078​
1.996094​	1.992188​	1.996094​
3.996094​	3.992188​	3.996094​
5.996094​	5.992188​	5.996094​
7.996094​	7.992188​	7.996094​
9.996094​	9.992188​	9.996094​
11.99609​	11.99219​	11.99609​
13.99609​	13.99219​	13.99609​
15.99609​	15.99219​	15.99609​
17.99609​	17.99219​	17.99609​
19.99609​	19.99219​	19.99609​
21.99609​	21.99219​	21.99609​
23.99609​	23.99219​	23.99609​
25.99609​	25.99219​	25.99609​
27.99609​	27.99219​	27.99609​
29.99609​	29.99219​	30​
31.99609​	31.99219​	32​
33.99609​	33.99219​	33.99609​
35.99609​	35.99219​	35.99609​
37.99609​	37.99219​	37.99609​
39.99609​	39.99219​	40.00781​
41.99609​	41.99219​	42​
43.99609​	43.99219​	44​
45.99609​	45.99219​	46​
48​	47.99219​	47.99609​
50​	49.99219​	50​
1.988281​	1.992188​	1.992188​
3.992188​	3.992188​	3.992188​
5.988281​	5.992188​	5.992188​
7.988281​	7.992188​	7.992188​
9.988281​	9.992188​	9.992188​
11.98828​	11.99219​	11.99219​
13.99219​	13.99219​	13.99219​
15.98828​	15.99219​	15.99219​
17.99219​	17.99219​	17.99219​
19.98828​	19.99219​	19.99609​
21.98828​	21.99219​	21.99219​
23.98828​	23.99219​	23.99219​
25.98828​	25.99219​	25.99609​
27.98828​	27.99219​	27.99219​
29.98828​	29.99219​	29.99219​
31.98828​	31.99219​	31.99219​
33.98828​	33.99219​	33.99219​
35.98828​	35.99219​	35.99219​
37.98828​	37.99219​	37.99219​
39.98828​	39.99219​	39.99219​
41.98828​	41.99219​	41.99219​
43.98828​	43.99219​	43.99219​
45.98828​	45.99219​	45.99609​
47.98828​	47.99219​	47.99219​
49.98828​	49.99219​	49.99219​

 

[BSALV]

the moment, you start the timer isn't exactly at "000" milliseconds, that can be any value between 0 and 999, all of them having the same probability.
So 3 times around .5 was a lucky shot.

just for fun, i made this one, every 100 milliseconds a record.
If you then check for min, max and average, you notice that the 1st interval is 7 milliseconds longer then the rest, VBA had 2 extra lines (and to start the loop)
note : the precision of timer is not that good, believe the hundreds of milliseconds.

Sub car_run()
     interval = 100                                             '100 milliseconds
     Range("A1").Value = Timer
     For i = 2 To 100
          nexttime = IIf(nexttime = 0, Timer, nexttime) + interval / 1000
          Do
               DoEvents
          Loop Until nexttime <= Timer
          Range("A" & i).Value = Timer
     Next
End Sub

Map1
	A	B	C	D	E	F	G	H
1	15670,015625			average	0,100122	0,099958
2	15670,131836	0,116211		min	0,092773	0,092773
3	15670,224609	0,092773		max	0,116211	0,109375	0,006836
4	15670,319336	0,094727		max-min	0,023438	0,016602
5	15670,427734	0,108398
6	15670,522461	0,094727
7	15670,631836	0,109375
8	15670,724609	0,092773
9	15670,819336	0,094727
10	15670,927734	0,108398
11	15671,022461	0,094727
Blad5

Cell Formulas
Range	Formula
E1	E1	=AVERAGE($B$1:$B$100)
F1	F1	=AVERAGE($B$3:$B$100)
E2	E2	=MIN($B$1:$B$100)
F2	F2	=MIN($B$3:$B$100)
E3	E3	=MAX($B$1:$B$100)
F3	F3	=MAX($B$3:$B$100)
G3	G3	=+E3-F3
E4:F4	E4	=+E3-E2
B2:B11	B2	=+A2-A1

 

[yxz152830]

the moment, you start the timer isn't exactly at "000" milliseconds, that can be any value between 0 and 999, all of them having the same probability.
So 3 times around .5 was a lucky shot.

just for fun, i made this one, every 100 milliseconds a record.
If you then check for min, max and average, you notice that the 1st interval is 7 milliseconds longer then the rest, VBA had 2 extra lines (and to start the loop)
note : the precision of timer is not that good, believe the hundreds of milliseconds.

Sub car_run()
     interval = 100                                             '100 milliseconds
     Range("A1").Value = Timer
     For i = 2 To 100
          nexttime = IIf(nexttime = 0, Timer, nexttime) + interval / 1000
          Do
               DoEvents
          Loop Until nexttime <= Timer
          Range("A" & i).Value = Timer
     Next
End Sub

Map1
	A	B	C	D	E	F	G	H
1	15670,015625			average	0,100122	0,099958
2	15670,131836	0,116211		min	0,092773	0,092773
3	15670,224609	0,092773		max	0,116211	0,109375	0,006836
4	15670,319336	0,094727		max-min	0,023438	0,016602
5	15670,427734	0,108398
6	15670,522461	0,094727
7	15670,631836	0,109375
8	15670,724609	0,092773
9	15670,819336	0,094727
10	15670,927734	0,108398
11	15671,022461	0,094727
Blad5

Cell Formulas
Range	Formula
E1	E1	=AVERAGE($B$1:$B$100)
F1	F1	=AVERAGE($B$3:$B$100)
E2	E2	=MIN($B$1:$B$100)
F2	F2	=MIN($B$3:$B$100)
E3	E3	=MAX($B$1:$B$100)
F3	F3	=MAX($B$3:$B$100)
G3	G3	=+E3-F3
E4:F4	E4	=+E3-E2
B2:B11	B2	=+A2-A1

I didn't know doevents and just googled it. I was using merely

do loop until xxx< timer

,
what would be the differences in regards of results?

 

[BSALV]

for example you want the program to wait 10 seconds in this way, without doevents and you want to stop the loop with CTRL-Break.
Sometimes you 'll try it without being able to stop, your screen becomes light grey and all activity stops ... .