TimeValue of less than 1 second

[Frankie_The_Flyer]

Hi
I have a pair of drag racing style light sets for a project with 5 lights to each set. The lights run up from a green through three amber to a red before counting down again with approximately 1 second delay between each light changing.
I want to make them so the run up is quicker than the run down leaving the run down at 1 second.

I'm using

Please Login or Register  to view this content.

.

To make them change at half second intervals I've divided the time by /2.

Please Login or Register  to view this content.

. but it seems the run up is too fast and the amber lights don't light up.

I'm now using /1.3 (equating to about 3/4 of a second) which allows the lights to "light up" at 1/3 but it's still a bit slow.

Any ideas how I can increase the speed of the run up and still see each light light up please? Example workbook attached with the code in the "RunLights" module and relevant code lines marked by '<<<<<<<<<<<

Thanks

Frankie

[Pete_UK]

Try it with TimeValue("0:00:00.6"), where you are using tenths of a second.

Hope this helps.

Pete

[Frankie_The_Flyer]

Thanks for the response Pete. Makes sense but unfortunately using the 00.6 throws up a runtime error 13 - Type missmatch

[Frankie_The_Flyer]

No further feedback on this and I can't find anything that works for me on the Internet, so presume it's not possible to achieve.
Just have to invent a work around!!

Thanks all

Frankie

[bakerman2]

How about this.

[curiouscat408]

Application.Wait (Now + TimeValue("0:00:01")/2 )
but it seems the run up is too fast and the amber lights don't light up

More generally, write:

Application.Wait CDbl(Now + #0:00:01#/2)

The problem is: Now+#0:0:1#/2 is a type Date expression. And Application.Wait rounds type Date expressions to the second.

Depending on the time of day, the fractional second part of Now+#0:0:1#/2 is often infinitesimally less than 0.5 sec.

So, Application.Wait rounds down, resulting in no delay.

(Interestingly, Application.Wait rounds down even when the fractional part of Now+#0:0:1#/2 is infinitesimally greater than 0.5 sec. TBD)

However, Application.Wait uses the full precision of type Double expressions.

BTW, VBA Now returns time rounded down to the second.

In your code, that probably reduces the first delay. And it might reduce delays following delays that are not a multiple of 1 sec, like the example above.

PS.... #0:0:1# is just a constant form of TimeValue("0:0:1"). VBA __formats__ it as #12:00:01 AM#. But they are equivalent. FYI, we can use the same constant form to encode an entire date; for example, #8/26/2021 12:34:56#.

[Frankie_The_Flyer]

Thanks for the response bakerman2. I guess that as per curiouscat408s response, I'm not going to get a half second run up as the system just rounds it up. Wortth a try though.

Thanks again

Frankie

[Frankie_The_Flyer]

Thanks for taking the time to explain things curiouscat408. Much appreciated.
Maybe the next step is to write to Microsoft and ask them to stop rounding time up!! (yeah! Right!!)

Cheers

Frankie

[bakerman2]

If you want 0.5s you get 0.5s.

[curiouscat408]

Maybe the next step is to write to Microsoft and ask them to stop rounding time up!! (yeah! Right!!)

I know you're kidding. But just a minor correction: Application.Wait does not always round up [sic] time.

It rounds time up or down, depending on the fraction of a second in the "usual manner".

(But I wonder if for VBA, the "usual manner" is banker's rounding, not the arithmetic rounding that we expect from Excel ROUND and VBA WorksheetFunction.Round. TBD.)

And I want to reiterate: that is only for type Date expressions.

So, there is no need for MSFT to stop rounding time.

We can avoid the rounding by coercing the time arithmetic to type Double using CDbl, as I demonstrated, or by using a type Double variable.

------

BTW....

BTW, VBA Now returns time rounded down to the second.

In your code, that probably reduces the first delay. And it might reduce delays following delays that are not a multiple of 1 sec

So, I prefer the following method:

Application.Wait CDbl(Date + (Timer + 1)/86400)

However, if the code might be executed around midnight, it might be prudent to write:

Please Login or Register  to view this content.

Usually, the loop is executed only once. At most, it would execute twice, if the midnight clock interrupt occurs between calling Date and Timer.

Of course, "+ 1" can be any multiple or fraction of a second.

Caveat: According to VBA documentation: ``In Microsoft Windows the Timer function returns fractional portions of a second. On the Macintosh, Timer resolution is one second``. I wonder if the Mac limitation is still true. But I have no way to test it. If it is true, it simply means that Date + Timer/86400 is no better than Now on the Mac.

[curiouscat408]

If you want 0.5s you get 0.5s.

Some examples of your code changes appear to be:

Please Login or Register  to view this content.

Alternatively, as I wrote in a previous response that bakerman2 had not seen yet:

Application.Wait CDbl(Date + (Timer + 0.5)/86400)

and

Application.Wait CDbl(Date + (Timer + 1/1.3)/86400)

See my caveats in the previous response about execution across midnight and the use of Timer on the Mac.

BTW, Frankie, I wonder if 1/1.3 is meant to be 1/(4/3) = 3/4 = 0.75.