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 20 May 2010 05:31

"Helmut Meukel" <NoSpam(a)NoProvider.de> wrote in message
news:%23fp0kl69KHA.5592(a)TK2MSFTNGP02.phx.gbl...
> "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.

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



From: Helmut Meukel on 20 May 2010 17:49
"Peter T" <peter_t(a)discussions> schrieb im Newsbeitrag
news:Oksi98$9KHA.3840(a)TK2MSFTNGP02.phx.gbl...
>
> "Helmut Meukel" <NoSpam(a)NoProvider.de> wrote in message
> news:%23fp0kl69KHA.5592(a)TK2MSFTNGP02.phx.gbl...
>> 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.

First  |  Prev  | 
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?