use Excel Now() in VBA

[zip22]

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?

 

[Mike H]

Hi,

You could use the 'timer' function in vb which measures elapsed time and
providing you not using a Mac it will return the fractional part of a second.

Start = Timer
For x = 1 To 10000000: Next
elapsedtime = Timer - Start
--
Mike

When competing hypotheses are otherwise equal, adopt the hypothesis that
introduces the fewest assumptions while still sufficiently answering the
question.

 

[Rick Rothstein]

Using the Timer function can present problems every now and then. Here is a
link in which someone pointed out what they thought was a bug in using the
Timer function and, if you scroll down, you will see a reply which explains
some of the problems in using the Timer function and offers a much more
reliable, although not completely perfect (note the 49.7 day roll-over),
alternative method.

http://us.generation-nt.com/answer/possible-vb6-timer-function-bug-help-189198111.html?page=2

 

[Peter T]

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]

[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)

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

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?

.

 

[Mike H]

Rick,

Thanks for the link, I wasn't aware of any issues with it until now.
--
Mike

When competing hypotheses are otherwise equal, adopt the hypothesis that
introduces the fewest assumptions while still sufficiently answering the
question.

 

[Peter T]

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

[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)

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

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?

.

 

[Dave Peterson]

The [] means that you're going back to excel to evaluate that expression.

It turns out to be a quicker trip if you actually use:
range("A1").value = application.evaluate("now()")

Personally, I think I'd use something like:

Dim myCell As Range
Set myCell = ActiveSheet.Range("d1") 'my test cell
With myCell
.NumberFormat = "mmm dd, yyyy hh:mm:ss.000"
.Formula = Now
.Value2 = .Value2 'convert to values
End With

I think

[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)

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

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?

.

 

[Dave Peterson]

I think it's a bit better self-documentation.

The [] means that you're going back to excel to evaluate that expression.

It turns out to be a quicker trip if you actually use:
range("A1").value = application.evaluate("now()")

Personally, I think I'd use something like:

Dim myCell As Range
Set myCell = ActiveSheet.Range("d1") 'my test cell
With myCell
.NumberFormat = "mmm dd, yyyy hh:mm:ss.000"
.Formula = Now
.Value2 = .Value2 'convert to values
End With

I think

[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)

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



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?


.

 

[zip22]

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)

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

[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)

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



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?


.

.

 

[Peter T]

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

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)

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

[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)



:

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



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?


.

.

 

[Helmut Meukel]

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.

 

[Peter T]

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.

 

[Peter T]

sorry about the double post

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

 

[Helmut Meukel]

sorry about the double post

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.

 

[Peter T]

sorry about the double post

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.

I didn't read the OP obviously thought they were the same at all. Indeed he
appeared to have a very good understanding of the difference between the
respective Excel and VBA Now functions and their respective resolutions. He
wanted to use Excel's function in VBA. I confirmed Excel's Now was to 1/100
sec and showed him one way (of three alternatives to the way he was already
using) to call it in VBA.

I don't see that as a "wrong, very wrong" answer to the OP's question.

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.

I didn't refer to VBA's function. I agree the Date data-type can handle a
much larger range of values than I implied, though it can't handle the range
of a Double or even a Long (with large +/- values)

Regards,
Peter T

 

[Helmut Meukel]

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.

I didn't refer to VBA's function. I agree the Date data-type can handle a much
larger range of values than I implied, though it can't handle the range of a
Double or even a Long (with large +/- values)

Regards,
Peter T

Peter,

Here is the official statement from Microsoft:
| Microsoft Office XP Developer
|
| The Date Data Type
|
| Microsoft® Visual Basic® for Applications (VBA) provides the
| Date data type to store date and time values. The Date data type
| is an 8-byte floating-point value, so internally it is the same as the
| Double data type. The Date data type can store dates between
| January 1, 100, and January 1, 9999.
|
| VBA stores the date value in the integer portion of the Date data
| type, and the time value in the decimal portion. The integer portion
| represents the number of days since December 30, 1899, which
| is the starting point for the Date data type. Any dates before this
| one are stored as negative numbers; all dates after are stored as
| positive values. If you convert a date value representing December
| 30, 1899, to a double, you'll find that this date is represented by
| zero.
|
| The decimal portion of a date represents the amount of time that
| has passed since midnight. For example, if the decimal portion of
| a date value is .75, three-quarters of the day has passed, and the
| time is now 6 P.M.
|
| Because the integer portion of a date represents number of days,
| you can add and subtract days from one date to get another date.
|

As stated, dates before December 30, 1899 are negative values.
The stated limitations are due to the conversion routines used by
VB/VBA to transform a date string into the internal representation
(a double).
For conveniance, you can enter a two-digit year and it's expanded
to a four-digit year. This prevents you from entering dates before
January 1, 100. These conversion routines are also limited to
positive year values, they can't handle years like 333 B.C.
And they can't handle 5-digit years.

BTW, the upper limit is wrong, a Date variable *can* store
12/31/9999. Try it!

To avoid problems with years outside the limits, VB/VBA even
throws a "runtime error 6" if you try to add or subtract a value that
would give a result outside of the limits.
So, yes, the range is artifically limited but *not* the accuracy, the
resolution. Here the Date data type is identical to the Double.
And the original problem was resolution (fractions of seconds).
Using a Double would gain nothing.

Helmut.