Prev: Sheet change event to return sheet name just left
Next: How to update .xlam file (addin) 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 datatype 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 datatype 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 8byte floatingpoint 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, threequarters 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 twodigit year and it's expanded to a fourdigit 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 5digit 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 (addin) while it is in use? 