Excel - use Excel Now() in VBA

Asked By zip22 on 19-May-10 12:23 PM
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
so it updates.  Is there a better way to do this?




Mike H replied to zip22 on 19-May-10 12:31 PM
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 replied to Mike H on 19-May-10 12:40 PM
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

--
Rick (MVP - Excel)
Peter T replied to zip22 on 19-May-10 12:57 PM
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 is 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 have 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 is never bit me!).

Regards,
Peter T
zip22 replied to Peter T on 19-May-10 01:52 PM
[now()] did what I was looking for.  I do not 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)
Mike H replied to Rick Rothstein on 19-May-10 01:59 PM
Rick,

Thanks for the link, I was not 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 replied to zip22 on 19-May-10 02:05 PM
Generally it is 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
Dave Peterson replied to zip22 on 19-May-10 04:01 PM
The [] means that you are 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




--

Dave Peterson
Dave Peterson replied to Dave Peterson on 19-May-10 04:24 PM
I think it is a bit better self-documentation.


--

Dave Peterson
zip22 replied to Peter T on 19-May-10 04:53 PM
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, should not
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 replied to zip22 on 19-May-10 05:17 PM
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
Helmut Meukel replied to Peter T on 19-May-10 05:29 PM
Wrong, dead wrong.

Try the same in VB6:
Time() and Now() do not return any fractions of a second.
Time() and Now() use identical code in VB6 and VBA because
it is 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 is own date/time functions because they were first there.
VBA was added to Excel with Excel 95.

Helmut.
Peter T replied to Helmut Meukel on 19-May-10 05:42 PM
Peter T replied to Helmut Meukel on 19-May-10 06:04 PM
sorry about the double post


What is it you think I said that is wrong. I did not 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 replied to Peter T on 19-May-10 07:17 PM
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 replied to Helmut Meukel on 20-May-10 05:31 AM
I did not 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 do not see that as a "wrong, very wrong" answer to the OP's question.


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

Regards,
Helmut Meukel replied to Peter T on 20-May-10 05:49 PM
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 will 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 is 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 cannot handle years like 333 B.C.
And they cannot 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.