une


I have code that executes fine 99% of the time. The other 1% I get an overflow error (error 6) that relates to the following line;

startTime = Year(Now) * 365 * 24 * 3600 + Day(Now) * 24 * 3600 + Hour(Now) * 3600 + _
Minute(Now) * 60 + Second(Now)

startTime is declared as a double;

Dim startTime As Double

The value of startTime at this line (according to debugger) was 63292994696 when it caused the error.

Would coercing to a double help ie;

startTime = CDbl(Year(Now) * 365 * 24 * 3600 + Day(Now) * 24 * 3600 + Hour(Now) * 3600 + _
Minute(Now) * 60 + Second(Now))

I don't think it would hurt, but can't see why I would need to coerce to a double. The range of numbers output by the expression should be well within the range of a double type variable.

I added the coersion to double with CDbl(), but as the error was extremely rare and hard to reproduce, I am not sure if this helped or not.

Any ideas why this error occured

PS - I just got the same error after adding the "coercion to double" statements, so that is not the solution.




Re: Overflow (error 6)

Keithyboy1


It may not be the obvious and nothing to do with the Double data type. It could be an error within the expression itself. For example, the date functions (Year, Month etc) return NULL if the time argument is NULL, which obviously can't happen if you're passing Now(), but it's got to be something along those lines. Perhaps have each component part of that expression write to the debug window so if the error happens again, you can see what the individual components are equating to.




Re: Overflow (error 6)

une

Thanks, it is quite frustrating. How would I "have each component part of that expression write to the debug window" I have actually removed the part of the program that uses this statement completely until I can definitively work out what is happeneing. The big problem is that the occurrence of the error is so rare that it is hard to pin down. I am guessing it might have something to do with execution happening at a particular combination of the time values (eg on second(Now) = 0) or something, maybe. I tried executing it on the zero minute (on the hour) but no problem. Very confusing.





Re: Overflow (error 6)

Keithyboy1

You could write an On Error statement for that particular piece of code that, when the error occurrs, you write the component parts of your expression to the debug window.

e.g.

On Error GoTo ErrorHandler

startTime = Year(Now) * 365 * 24 * 3600 + Day(Now) * 24 * 3600 + Hour(Now) * 3600 + _
Minute(Now) * 60 + Second(Now)

On Error GoTo 0

ErrorHandler:

Debug.Print "Year component: " & Year(Now)

Debug.Print "Day component: " & Day(Now)

Debug.Print "Hour component: " & Hour(Now)

Debug.Print "Minute component: " & Minute(Now)

Debug.Print "Second component: " & Second(Now)

'Appropriate code to clean up





Re: Overflow (error 6)

une

Thanks, I will give it a shot.




Re: Overflow (error 6)

Keithyboy1

The only issue I foresee with this, albeit a very remote issue, is the fact that the value you are assigning to the double variable from your expression changes by the second. If the time taken from executing the expression to jumping to the ErrorHandler label and writing the expression parts to the debug window exceeds a second, the values written to the debug window will obviously not be the same as those that generated the error. But the speed at which it executes should literally be a fraction of a second so you should be ok.

Good luck!