Prev: Sheet change event to return sheet name just left
Next: How to update .xlam file (add-in) while it is in use?
From: Peter T on 19 May 2010 17:17
Maybe something like this then -

Sub SplitTime()

With Range("A1")
Range("B1") = .Value
.Formula = "=NOW()"
.Value = .Value
End With

End Sub

Sub NumFormat()
Range("A1:B1").NumberFormat = "hh:mm:ss.00"
End Sub

Regards,
Peter T

"zip22" <zip22(a)discussions.microsoft.com> wrote in message
news:BA44495C-27F6-4BA6-9FC8-69FB4B291B50(a)microsoft.com...
>I am putting together a sort of split timer, but seeing the previous value
> where the timer was last stopped is useful. Simplified, I have a value in
> cell a1 that is the time the stopwatch was last stopped. When I press a
> button (run the macro), the split time between a1 and now is entered into
> b1.
> A1 is reset to now. Press the button again and the split time time is
> entered into b2. Press again, and b3 is filled in, etc
>
> Comparing the current time to cell a1 lets the person know the approximate
> running split time. It is better to keep this slightly inaccurate, and
> not
> use a running timer. A running timer may enourage trying to match the
> previous split time instead of accurately watching the event. The rough
> idea
> that the previous end time and the current system time gives is a good
> enough
> measure.
>
> Accuracy to one hundredth of a second is acceptable.
>
> looking at "timer" in VBA, shouldn't
> now and timer match? (For the time portion anyways)
>
> On my system, they currently differ by 0:20:12 and it is drifting higher
>
>
> "Peter T" wrote:
>
>> Generally it's best to avoid square brackets. Difficult to answer your
>> main
>> question though until you give some information about what I asked you
>> previously.
>>
>> Regards,
>> Peter T
>>
>> "zip22" <zip22(a)discussions.microsoft.com> wrote in message
>> news:C86781FC-5C44-411A-9A76-8A1266F65319(a)microsoft.com...
>> > [now()] did what I was looking for. I don't think it was a data type
>> > issue.
>> > The cells are set to "mm:ss.00"
>> >
>> > range("A1") = Now
>> > always rounds down to the second
>> >
>> > range("A1") = [Now()]
>> > gives me hundredths of a second
>> >
>> > after looking into the square brackets, it looks like i can also use
>> > [A1]=[Now()]
>> >
>> > This looks like it will be more straightforward to code. Is there any
>> > downside to using this instead of timer? (if I am happy with hundredths
>> > of
>> > a
>> > second)
>> >
>> >
>> >
>> > "Peter T" wrote:
>> >
>> >> What's the purpose, IOW do you want a timer or do you want to know the
>> >> actual time, and in either case to what resolution.
>> >>
>> >> FWIW Now() normally gets coerced to one second in cells due to the
>> >> Date
>> >> type
>> >> conversion. However it's actual resolution is to 1/100 sec (at least
>> >> in
>> >> my
>> >> light testing) so maybe simply -
>> >> dim x as double
>> >> x = [now()]
>> >>
>> >> Despite the timer bug Rick mentioned, I've never had a problem with
>> >> it.
>> >> So
>> >> for quick testing where a resolution of about 1/20 sec is enough I use
>> >> VBA's
>> >> Timer function. For higher resolution there are various APIs, eg
>> >> GetTickCount (that also has a rollover but it's never bit me!).
>> >>
>> >> Regards,
>> >> Peter T
>> >>
>> >>
>> >>
>> >> "zip22" <zip22(a)discussions.microsoft.com> wrote in message
>> >> news:DDCD95F2-FAD4-445B-9962-DAB0F797EF0E(a)microsoft.com...
>> >> > Excel's Now() function has a resolution down to hh:mm:ss.00 where
>> >> > the
>> >> > VBA
>> >> > Now
>> >> > function only has hh:mm:ss. I need the accuracy of the excel now
>> >> > function
>> >> > in
>> >> > a macro but
>> >> >
>> >> > application.worksheetfunction.now
>> >> >
>> >> > does not work. My workaround at this point is referencing a cell
>> >> > with
>> >> > "=now()" in it, but before I reference it I have to use
>> >> > application.calculate
>> >> > so it updates. Is there a better way to do this?
>> >>
>> >>
>> >> .
>> >>
>>
>>
>> .
>>


From: Helmut Meukel on 19 May 2010 17:29
"Peter T" <peter_t(a)discussions> schrieb im Newsbeitrag
news:OG6omR39KHA.5848(a)TK2MSFTNGP06.phx.gbl...
>
> FWIW Now() normally gets coerced to one second in cells due to the Date type
> conversion. However it's actual resolution is to 1/100 sec (at least in my
> light testing) so maybe simply -
> dim x as double
> x = [now()]
>


Wrong, dead wrong.

Try the same in VB6:
Time() and Now() don't return any fractions of a second.
Time() and Now() use identical code in VB6 and VBA because
it's in the very same DLL: MSVBVM60.DLL

The date data type is internally a double, where the integer part
is the day - starting with 12/30/1899 as day 0 -
and the fractional part is the time - starting at midnight with .0000
Thus .25 is 6:00 AM, .75 is 6:00 PM
Being internally a double, a date data type could hold fractions of
seconds.

The Excel spreadsheet function Now() has the same name as the
VBA function but is more accurate.
If you use the brackets VBA will use the excel function instead of
its own function.
Excel has it's own date/time functions because they were first there.
VBA was added to Excel with Excel 95.

Helmut.

From: Peter T on 19 May 2010 17:42

"Helmut Meukel" <NoSpam(a)NoProvider.de> wrote in message
news:u7Fscp59KHA.5716(a)TK2MSFTNGP06.phx.gbl...
> "Peter T" <peter_t(a)discussions> schrieb im Newsbeitrag
> news:OG6omR39KHA.5848(a)TK2MSFTNGP06.phx.gbl...
>>
>> FWIW Now() normally gets coerced to one second in cells due to the Date
>> type conversion. However it's actual resolution is to 1/100 sec (at least
>> in my light testing) so maybe simply -
>> dim x as double
>> x = [now()]
>>
>
>
> Wrong, dead wrong.
>
> Try the same in VB6:
> Time() and Now() don't return any fractions of a second.
> Time() and Now() use identical code in VB6 and VBA because
> it's in the very same DLL: MSVBVM60.DLL
>
> The date data type is internally a double, where the integer part
> is the day - starting with 12/30/1899 as day 0 -
> and the fractional part is the time - starting at midnight with .0000
> Thus .25 is 6:00 AM, .75 is 6:00 PM
> Being internally a double, a date data type could hold fractions of
> seconds.
>
> The Excel spreadsheet function Now() has the same name as the
> VBA function but is more accurate.
> If you use the brackets VBA will use the excel function instead of
> its own function.
> Excel has it's own date/time functions because they were first there.
> VBA was added to Excel with Excel 95.
>
> Helmut.
>


From: Peter T on 19 May 2010 18:04
sorry about the double post

"Helmut Meukel" <NoSpam(a)NoProvider.de> wrote in message
news:u7Fscp59KHA.5716(a)TK2MSFTNGP06.phx.gbl...
> "Peter T" <peter_t(a)discussions> schrieb im Newsbeitrag
> news:OG6omR39KHA.5848(a)TK2MSFTNGP06.phx.gbl...
>>
>> FWIW Now() normally gets coerced to one second in cells due to the Date
>> type conversion. However it's actual resolution is to 1/100 sec (at least
>> in my light testing) so maybe simply -
>> dim x as double
>> x = [now()]
>>
>
>
> Wrong, dead wrong.
>
> Try the same in VB6:
> Time() and Now() don't return any fractions of a second.
> Time() and Now() use identical code in VB6 and VBA because
> it's in the very same DLL: MSVBVM60.DLL
>
> The date data type is internally a double, where the integer part
> is the day - starting with 12/30/1899 as day 0 -
> and the fractional part is the time - starting at midnight with .0000
> Thus .25 is 6:00 AM, .75 is 6:00 PM
> Being internally a double, a date data type could hold fractions of
> seconds.
>
> The Excel spreadsheet function Now() has the same name as the
> VBA function but is more accurate.
> If you use the brackets VBA will use the excel function instead of
> its own function.
> Excel has it's own date/time functions because they were first there.
> VBA was added to Excel with Excel 95.
>
> Helmut.

What is it you think I said that is wrong. I didn't mention anything about
VBA's Now function, only that Excel's Now() has a resolution of 1/100sec in
my light testing (see below)

Sub test()
Dim b As Boolean, x#, y#
Const sec# = 1 / (24& * 60 * 60)

x = [Now()]
b = True
While b
y = [now()]
b = x = y
Wend
Debug.Print sec / (y - x) ' about 100

End Sub

If you change [Now()] to Now I expect the debug will be about 1, ie VBA's
Now has a resolution of 1 second (not sure why you say you can return a
higher resolution VBA's Now)

FWIW I am well aware that the square brackets example I posted Evaluates
Excel's NOW() function.

Regards,
Peter T



From: Helmut Meukel on 19 May 2010 19:17
"Peter T" <peter_t(a)discussions> schrieb im Newsbeitrag
news:el$lC959KHA.5716(a)TK2MSFTNGP06.phx.gbl...
> sorry about the double post
>
> "Helmut Meukel" <NoSpam(a)NoProvider.de> wrote in message
> news:u7Fscp59KHA.5716(a)TK2MSFTNGP06.phx.gbl...
>> "Peter T" <peter_t(a)discussions> schrieb im Newsbeitrag
>> news:OG6omR39KHA.5848(a)TK2MSFTNGP06.phx.gbl...
>>>
>>> FWIW Now() normally gets coerced to one second in cells due to the Date type
>>> conversion. However it's actual resolution is to 1/100 sec (at least in my
>>> light testing) so maybe simply -
>>> dim x as double
>>> x = [now()]
>>>
>>
>>
>> Wrong, dead wrong.
>>
>> Try the same in VB6:
>> Time() and Now() don't return any fractions of a second.
>> Time() and Now() use identical code in VB6 and VBA because
>> it's in the very same DLL: MSVBVM60.DLL
>>
>> The date data type is internally a double, where the integer part
>> is the day - starting with 12/30/1899 as day 0 -
>> and the fractional part is the time - starting at midnight with .0000
>> Thus .25 is 6:00 AM, .75 is 6:00 PM
>> Being internally a double, a date data type could hold fractions of
>> seconds.
>>
>> The Excel spreadsheet function Now() has the same name as the
>> VBA function but is more accurate.
>> If you use the brackets VBA will use the excel function instead of
>> its own function.
>> Excel has it's own date/time functions because they were first there.
>> VBA was added to Excel with Excel 95.
>>
>> Helmut.
>
> What is it you think I said that is wrong. I didn't mention anything about
> VBA's Now function, only that Excel's Now() has a resolution of 1/100sec in my
> light testing (see below)
>
> Sub test()
> Dim b As Boolean, x#, y#
> Const sec# = 1 / (24& * 60 * 60)
>
> x = [Now()]
> b = True
> While b
> y = [now()]
> b = x = y
> Wend
> Debug.Print sec / (y - x) ' about 100
>
> End Sub
>
> If you change [Now()] to Now I expect the debug will be about 1, ie VBA's Now
> has a resolution of 1 second (not sure why you say you can return a higher
> resolution VBA's Now)
>
> FWIW I am well aware that the square brackets example I posted Evaluates
> Excel's NOW() function.
>
> Regards,
> Peter T
>


Peter,
the OP complained about the inaccurate Now() in VBA compared to
the high accuracy of NOW() when used in a formula in the spreadsheet.
He obviously thought they were the same because they have the
same name.

That inaccuracy is an issue of the VBA function, not the data type.
You can still declare your variable as Date and get the higher accuracy:
dim x as Date
x = [now()]
That's *if* you use the spreadsheet function. The VBA date data type
is internally a double and can therefore contain values with fractions of
seconds.

Helmut.

First  |  Prev  |  Next  |  Last
Pages: 1 2 3 4
Prev: Sheet change event to return sheet name just left
Next: How to update .xlam file (add-in) while it is in use?