Solved: Timer

[mdmackillop]

In another question, I added a timer to some simple code, but in some runs it is giving me negative values, eg -4.65E-12. Any thoughts as to why this would happen?

[VBA] Sub Colours()
Dim cel As Range
Dim tim As Double
tim = Timer
For Each cel In Intersect(Range("A:O"), ActiveSheet.UsedRange)
Select Case cel
Case Is = 1
cel.EntireRow.Interior.ColorIndex = 6
End Select
Next
[a1] = Timer - tim
End Sub
[/VBA]

[Ken Puls]

Hi Malcolm,

I thought Timer would be a Date, not Double?

[mdmackillop]

Hi Ken,
It appears to be Single

Timer Function



Returns a Single representing the number of seconds elapsed since midnight.

[Ken Puls]

Well go figure that. All my examples I have on file are variants, so I just guessed.

And so... did you run your test over midnight? VBAX says its 11:30PM your time...

[Ken Puls]

Okay, I made up this example, which shows the issue pretty well:

[vba]Sub TimeTest()
Dim x As Single
Dim FlashStart As Single
Dim SubStart As Single
SubStart = Timer
For x = 1 To 1000
FlashStart = Timer
Cells(x, 1).Value = Timer - FlashStart
Next
Cells(1001, 1).Value = Timer - SubStart
MsgBox "1000 iterations took " & Cells(1001, 1).Value & " seconds to complete."
End Sub[/vba]

I don't really get it either. Even if the numbers were being rounded due to insufficient decimal places, it a number should never be less than it's predecessor. Very strange indeed...

[Killian]

Strange indeed. I didn't get any negative results with Malc's code but all negative with Ken's
Floating point maths and 21st century PCs - distant cousins at times
M$ have lots of excuses here and, perhaps surprisingly a solution:

In some cases, you may be able to prevent rounding errors from affecting your work by using the Precision as displayed option. This option forces the value of each number in the worksheet to be the displayed value. To turn on this option, click Options on the Tools menu. On the Calculation tab, click to select the Precision as displayed check box.

[johnske]

In another question, I added a timer to some simple code, but in some runs it is giving me negative values, eg -4.65E-12. Any thoughts as to why this would happen?

[VBA] Sub Colours()
Dim cel As Range
Dim tim As Double
tim = Timer
For Each cel In Intersect(Range("A:O"), ActiveSheet.UsedRange)
Select Case cel
Case Is = 1
cel.EntireRow.Interior.ColorIndex = 6
End Select
Next
[a1] = Timer - tim
End Sub
[/VBA]

Hi Malcolm,

Perhaps your PC's running so fast it's exceeded the speed of light and travelled backwards in time, consequently the procedure is ending at a time before it started.... Keep your fingers clear of the keyboard so you don't get dragged into the time vortex.

Seriously though, I ran this and only got positive numbers - perhaps the format of cell A1?

Regards,
John

[Ken Puls]

Ahem...

In some cases, you may be able to prevent rounding errors from affecting your work by using the Precision as displayed option. This option forces the value of each number in the worksheet to be the displayed value. To turn on this option, click Options on the Tools menu. On the Calculation tab, click to select the Precision as displayed check box.

BS! Give it a shot...

It didn't seem to have any effect on my results at all. In addition, my calculation, even after setting it back, seems to be running slower. I'm hoping that I didn't mess something up by checking that box...

If you are going to do it, though, please time it using my routine before you set the option to on, then after, then after setting it back...

[XLGibbs]

In this case precision as displayed would be brutal since only the displayed decimals would be affected. Since the resulting calculation is often into the 8th decimal place...

This is not caused at all by the floating point error. I think it is caused more by the time of day, and may need to be normalized since the "First" value that sets the base (at least around 7:30 PM when I was messing with it) was close to 70,000. but the 8th decimal place resulting math....

I agree though...it should not result in a negative...umm...ever. Is it possible the loop occurs in the future? :0)

[mdmackillop]

Just for info, here's a fuller result.

[VBA]Sub Colours()
Dim cel As Range
Dim tim As Double
tim = Timer
[A1] = Timer
For Each cel In Intersect(Range("A:O"), ActiveSheet.UsedRange)
Select Case cel
Case Is = 1
cel.EntireRow.Interior.ColorIndex = 6
End Select
Next
[A2] = Timer - tim
[A3] = Timer
End Sub[/VBA]





81576.843750000000000000000000000000
-0.000000000004192202140984590000
81576.843750000000000000000000000000

[XLGibbs]

Hmm..

[VBA]
Sub Colours()
Dim cel As Range
Dim tim As Double
tim = Timer
[A1] = Timer
For Each cel In Intersect(Range("A:O"), ActiveSheet.UsedRange)
Select Case cel
Case Is = 1
cel.EntireRow.Interior.ColorIndex = 6
End Select
Next
[A2] = Timer - tim
[A3] = tim

End Sub[/vba]


produces this:

65442.40625000000000000000 Timer
-0.00000000000267874611 Timer -Tim in Code
65442.40600000000000000000 Tim

0.00024999999732244800 Timer-Tim Formula

strange indeed. I think my bank uses variations of this math.

[mdmackillop]

I think I'll just write this off as a MS mystery. Thanks all for the contributions.

[johnske]

Hi Malcolm,

Yes, can see prob now. When there is nothing to be found I got this for one trial

41318.691406250000000000000000000000
-0.000000000002327027459614330000
41318.691406250000000000000000000000