PDA

View Full Version : Solved: Timer



mdmackillop
01-30-2006, 03:38 PM
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?:dunno

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

Ken Puls
01-30-2006, 03:58 PM
Hi Malcolm,

I thought Timer would be a Date, not Double?

mdmackillop
01-30-2006, 04:23 PM
Hi Ken,
It appears to be Single

Timer Function



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

Ken Puls
01-30-2006, 04:34 PM
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
01-30-2006, 05:06 PM
Okay, I made up this example, which shows the issue pretty well:

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

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
01-30-2006, 06:14 PM
Strange indeed. I didn't get any negative results with Malc's code but all negative with Ken's :dunno
Floating point maths and 21st century PCs - distant cousins at times :banghead:
M$ have lots of excuses here (http://support.microsoft.com/default.aspx?scid=kb;en-us;q78113) and, perhaps surprisingly :devil: 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
01-30-2006, 09:00 PM
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?:dunno

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
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. :devil:

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

Regards,
John :)

Ken Puls
01-30-2006, 09:23 PM
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
01-30-2006, 09:41 PM
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
01-31-2006, 03:42 PM
Just for info, here's a fuller result.

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





81576.843750000000000000000000000000
-0.000000000004192202140984590000
81576.843750000000000000000000000000

XLGibbs
01-31-2006, 04:14 PM
Hmm..


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


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
01-31-2006, 04:21 PM
I think I'll just write this off as a MS mystery. Thanks all for the contributions.

johnske
01-31-2006, 06:36 PM
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

:)