use Excel Now() in VBA

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?
0
Utf
5/19/2010 4:23:01 PM
excel.programming 6508 articles. 2 followers. Follow

16 Replies
1179 Views

Similar Articles

[PageSpeed] 55

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.


"zip22" wrote:

> 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?
0
Utf
5/19/2010 4:31:01 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)



"Mike H" <MikeH@discussions.microsoft.com> wrote in message 
news:EC0EF511-3D2A-423F-98BA-82F0F76B68A6@microsoft.com...
> 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.
>
>
> "zip22" wrote:
>
>> 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? 

0
Rick
5/19/2010 4:40: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'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@discussions.microsoft.com> wrote in message 
news:DDCD95F2-FAD4-445B-9962-DAB0F797EF0E@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? 


0
Peter
5/19/2010 4:57:38 PM
[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@discussions.microsoft.com> wrote in message 
> news:DDCD95F2-FAD4-445B-9962-DAB0F797EF0E@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? 
> 
> 
> .
> 
0
Utf
5/19/2010 5:52:01 PM
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.


"Rick Rothstein" wrote:

> 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)
> 
> 
> 
> "Mike H" <MikeH@discussions.microsoft.com> wrote in message 
> news:EC0EF511-3D2A-423F-98BA-82F0F76B68A6@microsoft.com...
> > 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.
> >
> >
> > "zip22" wrote:
> >
> >> 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? 
> 
> .
> 
0
Utf
5/19/2010 5:59:01 PM
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@discussions.microsoft.com> wrote in message 
news:C86781FC-5C44-411A-9A76-8A1266F65319@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@discussions.microsoft.com> wrote in message
>> news:DDCD95F2-FAD4-445B-9962-DAB0F797EF0E@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?
>>
>>
>> .
>> 


0
Peter
5/19/2010 6:05:50 PM
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 



zip22 wrote:
> 
> [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@discussions.microsoft.com> wrote in message
> > news:DDCD95F2-FAD4-445B-9962-DAB0F797EF0E@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?
> >
> >
> > .
> >

-- 

Dave Peterson
0
Dave
5/19/2010 8:01:34 PM
I think it's a bit better self-documentation.

Dave Peterson wrote:
> 
> 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
> 
> zip22 wrote:
> >
> > [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@discussions.microsoft.com> wrote in message
> > > news:DDCD95F2-FAD4-445B-9962-DAB0F797EF0E@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?
> > >
> > >
> > > .
> > >
> 
> --
> 
> Dave Peterson

-- 

Dave Peterson
0
Dave
5/19/2010 8:24:42 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, 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@discussions.microsoft.com> wrote in message 
> news:C86781FC-5C44-411A-9A76-8A1266F65319@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@discussions.microsoft.com> wrote in message
> >> news:DDCD95F2-FAD4-445B-9962-DAB0F797EF0E@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?
> >>
> >>
> >> .
> >> 
> 
> 
> .
> 
0
Utf
5/19/2010 8:53:00 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

"zip22" <zip22@discussions.microsoft.com> wrote in message 
news:BA44495C-27F6-4BA6-9FC8-69FB4B291B50@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@discussions.microsoft.com> wrote in message
>> news:C86781FC-5C44-411A-9A76-8A1266F65319@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@discussions.microsoft.com> wrote in message
>> >> news:DDCD95F2-FAD4-445B-9962-DAB0F797EF0E@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?
>> >>
>> >>
>> >> .
>> >>
>>
>>
>> .
>> 


0
Peter
5/19/2010 9:17:09 PM
"Peter T" <peter_t@discussions> schrieb im Newsbeitrag 
news:OG6omR39KHA.5848@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.

0
Helmut
5/19/2010 9:29:22 PM
"Helmut Meukel" <NoSpam@NoProvider.de> wrote in message 
news:u7Fscp59KHA.5716@TK2MSFTNGP06.phx.gbl...
> "Peter T" <peter_t@discussions> schrieb im Newsbeitrag 
> news:OG6omR39KHA.5848@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.
> 


0
Peter
5/19/2010 9:42:19 PM
sorry about the double post

"Helmut Meukel" <NoSpam@NoProvider.de> wrote in message 
news:u7Fscp59KHA.5716@TK2MSFTNGP06.phx.gbl...
> "Peter T" <peter_t@discussions> schrieb im Newsbeitrag 
> news:OG6omR39KHA.5848@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



0
Peter
5/19/2010 10:04:26 PM
"Peter T" <peter_t@discussions> schrieb im Newsbeitrag 
news:el$lC959KHA.5716@TK2MSFTNGP06.phx.gbl...
> sorry about the double post
>
> "Helmut Meukel" <NoSpam@NoProvider.de> wrote in message 
> news:u7Fscp59KHA.5716@TK2MSFTNGP06.phx.gbl...
>> "Peter T" <peter_t@discussions> schrieb im Newsbeitrag 
>> news:OG6omR39KHA.5848@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. 

0
Helmut
5/19/2010 11:17:01 PM
"Helmut Meukel" <NoSpam@NoProvider.de> wrote in message 
news:%23fp0kl69KHA.5592@TK2MSFTNGP02.phx.gbl...
> "Peter T" <peter_t@discussions> schrieb im Newsbeitrag 
> news:el$lC959KHA.5716@TK2MSFTNGP06.phx.gbl...
>> sorry about the double post
>>
>> "Helmut Meukel" <NoSpam@NoProvider.de> wrote in message 
>> news:u7Fscp59KHA.5716@TK2MSFTNGP06.phx.gbl...
>>> "Peter T" <peter_t@discussions> schrieb im Newsbeitrag 
>>> news:OG6omR39KHA.5848@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



0
Peter
5/20/2010 9:31:29 AM
"Peter T" <peter_t@discussions> schrieb im Newsbeitrag 
news:Oksi98$9KHA.3840@TK2MSFTNGP02.phx.gbl...
>
> "Helmut Meukel" <NoSpam@NoProvider.de> wrote in message 
> news:%23fp0kl69KHA.5592@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.

0
Helmut
5/20/2010 9:49:00 PM
Reply:

Similar Artilces:

Pasting data from Excel
Hello everyone, I'm not sure if I should be posting this question here or in the Excel forum but here goes. Is it possible to copy data from multiple cells in Excel and then paste them into multiple lines of the criteria section of an Access query? For example, Given cells and values: A1- 1 A2- 2 A3- 3 I would like to be able to copy this data from Excel and paste it into an Access query like : Criteria: 1 or: 2 3 I am using Access 2002 SP3 and Exc...

Excel Drop Down Box
I'm trying to edit an excel worksheet that has drop down boxes. However, the drop down boxes are not typical forms. These drop dow boxes appear to be normal cells (They contain text). When I click o the box, a little gray box shows up w/ a down arrow to the right of th cell. However, if you right click on the cell, there aren't an property options that are displayed. I was wondering if anybody had any idea what kind of drop down box thi is. How can I edit or create one -- Message posted from http://www.ExcelForum.com It sounds like it's under Data|Validation. chris313 wr...

how to select multiple text boxes in excel for formatting
I am trying to select multiple text boxes for formatting the font but seem unable to select all of them other than to click on each one individually. Is there an easy way to select all of the text boxes at once? To select multiple objects on the sheet -- Click on one object Hold the Ctrl key, and click on additional objects To select all the objects on the sheet -- Choose Edit>Go To, click Special Select Objects, click OK Or, to work with specific objects, you can add the 'Select Multiple Objects' tool to one of your toolbars: Choose Tools>Customize Select the Commands tab...

How to use outlook address in Excel
Hello, I have an Excel sheet which I use as an invoicing-application. I would like to retrieve address-data from Outlook where I keep all my contact-data of my customers. So, I want to select a customer from my Outlook contactlist when I am writing a new invoice in Excel. In Word, I have a macro which does this, but unfortunately the Application.GetAddress does not work in Excel. Can somebody help me ? "Henny Slokker" wrote: > Hello, > > I have an Excel sheet which I use as an invoicing-application. I would like > to retrieve address-data from Outlook where I...

IE8 uses 95+% of cpu after update to sp3
I just updated to XP sp3 and did updates after that and IE8 is running so slow. I loaded task manager to see what was happening and IE8 was using up to 99%. My system was a slow XP sp2 but did a disk clean and defrag and it was running a lot faster. Apps load and run faster after sp3 but not IE8. Thanks for any suggestions mx5 wrote: > I just updated to XP sp3 and did updates after that and IE8 is > running so slow. I loaded task manager to see what was happening > and IE8 was using up to 99%. > > My system was a slow XP sp2 but did a disk clean and defrag and...

excel margin issues on landscape
When I print a spreadsheet I cant get it to print to the full page - it prints smaller unlike older excel program. Also when i set the margins for a spreadsheet the left hand margin wont move over to the edge of page like right hand side? In Page Setup: If you are using the Scaling option to print to a certain number of pages wide by pages tall and/or you are using the columns to repeat at left, try: - clearing the number of pages tall value (so that it is blank), and/or - if you are printing to one page wide, remove the columns to repeat at left Simon "Peter MB" wrote: >...

Excel macro list
In Excel 2003 I used to be able to list all macros in a workbook by pressing Alt+F8. Now all I get is a series of ribbon help letters... What's changed? Is there still a way of accessing macros via Alt+F8? Any suggestions appreciated. Hi, ALT+F8 works for me in E2007. What do you mean by 'I get is a series of ribbon help letters' Mike "pbaker6" wrote: > In Excel 2003 I used to be able to list all macros in a workbook by pressing > Alt+F8. Now all I get is a series of ribbon help letters... What's changed? > Is there still a way of...

Can I only allow printing to pdf in Excel?
I have created a template in Excel which has been set up so that the layout is perfect when printing to pdf (which is how the document will mostly be used) but the layout changes if printing direct to our printer. Is there a way that I can ONLY allow printing to pdf from this document? Hello You may consider using some VBA to achieve this. One way is to use the Workbook_BeforePrint event and specify the pdf printer in the PrintOut method, eg: Private Sub Workbook_BeforePrint(Cancel As Boolean) ActiveSheet.PrintOut copies:=1, ActivePrinter:="CutePDF Writer on CPW2:" End Sub Pl...

VB: using a string to set a range object?
I'm a bit new to the excel "range" object type. I was suprised to see that while I can do: dim chunk as Range chunk = .Range(A5:B6) I apparently cannot do: dim chunk as Range dim stuff as string string = "A5:B6" chunk= .Range(string) How can I concatenate up a string describing a range, and then use it to define a range object's target cells? - Ross. Oops, I meant chunk = .Range("A5:B6") in the first example - I forgot the quotes. R. "RGK" <nothanks@nospam.go> wrote in message news:RqydnSWzbu_OEZbeRVn-2A@...

query will not write to excel
I have set up a query to a Foxpro .dbf file in a database from excel. When I tell Excel to import the data it it appears to be working but never seems to return the data. Running the same query via msquery.exe returns the data with no problem. Can anyone tell me what the problem is ? ...

How to use CSplitterWnd for Dynamic Nested Splitter window?
Using VC++ (.NET) I am trying to implement a Dynamic Nested Splitter window using CSplitterWnd. I haven't found any documentation that explains how to get this to work properly and the only examples I have found also do not work properly. There are plenty of examples of Static Nested Splitter windows, but I need an example of a Dynamic one. The static splitter windows won't work for my requirements. Are there any examples or documentation that explains how to properly implement a Dynamic Nested Splitter Window using CSplitterWnd? Thanks, Dave Hi Dave, > Using VC++ (.NE...

EXCEL FORMULA #28
Good afternoon, I'm trying to fine a formula which would show me how much money I would save on a mortgage if I were to pay additional principal each month--in addition to paying the additional principal how long would it take to pay off. I'm looking at a 160k mortgage at 7.5 for 30 years. I'll like to pay this off as soon as possible by paying additional principal each month. There are tons of free templates at: http://office.microsoft.com/en-us/default.aspx Maybe you'll find something you like. Kam1999i wrote: > > Good afternoon, > > I'm ...

OLE: Excel.Application
Hello, in VB.Net, I use Excel to display results : dim xl as new Excel.Application // creates an Excel process // snip (putting values into cells) xl.Visible = true If the user closes the Excel file and then my program, the Excel process is killed in memory, which is good. If the user closes my program first and then the Excel file, the Excel process remains in memory ! How can I make sure the process will be killed ? Thanks ! Hi You need to set xl.quit (and before that ensure that excel doesn't halt and ask things like "save changes?" on quitting) somewhere in your p...

Export relationship information from Visio to Excel
Hello all, Is there a way to export information regarding relationships from a visio diagram to an excel spreadsheet? In addition, is there a way to tell the excel spreadsheet to eliminate or change a relationship and for that action to be applied onto the visio diagram? thanks, ivan as a general answer I'd have to say "no, not without custom code". You didn't define what you meant as a relationship. al "Ivan Salas" <IvanSalas@discussions.microsoft.com> wrote in message news:6332A594-E4AF-4E8B-BA2D-7A4BC17962B3@microsoft.com... > Hello all, > &...

startup excel euro symbol
when i digit € symbol inside any application excel 2007 automacic startup and for me is impossible to use this symbol anywhere, i use windows xp professional ..have you a response to solve this problem? thanks ...

Excel 2003 Print Issue
I have created a spreadsheet to help with a university engineering assignment and I have added a worksheet that is basically an automatically generated report of all the calculations. I have set the Print Area up in such a way so that the results are printed out in well defined pages (e.g. page 1: title page, page 2: summary of input variables, page 3: summary of calculation results etc). The report is arranged vertically in the worksheet, so the pages are 'stacked' on top of each other. It prints out fine in Excel 2000 and 2002 but I recently upgraded to Excel 2003 and now find tha...

Coding Convention for using Binary FlagWords?
Hi all --- I'm thinking of putting the .Tag property to use by using a binary ("bitwise") encoded flag word. Over time I've come across different possible uses for .Tag but have never standardized how I use it. Has someone developed a convention for "parsing" binary flag words? A simple IF works if I only need to test for one flag, but if I need to check for the presence of multiple bits the only construct that comes to mind is a series of IF statements .... something along the lines of: Enum FlagWord FlagA = 1 FlagB = 2 FlagC = 4 .......

Using Outlook client for CRM 3.0 in a remote office
I need to set up access to the corporate CRM from several remote offices. All of them have VPN connections (Windows or Checkpoint). What are the steps required to configure remote clients to be able to use CRM features in Outlook. Remote users can connect to CRM through Internet Expolorer. Thanks. Assuming they are connected, the isntallation over a VPN connection should not be any different than a typicaly installation. If the connection is slow though, the first synch & Go Offline process will be noticiably slower though. -- Matt Parks MVP - Microsoft CRM "mkatsev"...

using std::deque in multiple threads
I have a thread that listens on a message queue and populates a std::deque with events from that message queue, but only holds the latest 100. In my main thread, I want to populate a listbox with the information from that deque. So, it ends up being that one thread can add/remove items from the deque while another thread is trying to iterate through them. How can I make this thread-safe? Thanks, PaulH The code looks a bit like this: std::deque<MESSAGE_TYPE> m_dequeMessages; CMyClass::MessageThread() { //... while (ReadMsgQueue(hMsgQueue, &msg,...)) { m_dequeMessages.pu...

excel 2000 message
excel 2000 message - 'cannot use object linking and embedding' Were they hit by the MSBlast worm? One poster (Lutz Meyer) guessed that this was the cause of his problems. I haven't seen any confirmation/denial, but you may want to read his post: http://groups.google.com/groups?threadm=3F3971AF.FA4490F5%40msn.com Post back with your results. I'm curious if that was the problem. (It's come up quite a few times since MSBlast hit.) bill bootle wrote: > > excel 2000 message - 'cannot use object linking and > embedding' -- Dave Peterson ec35720@msn.c...

Excel Graphing Line References off when chart is a sheet.
I have noticed that when any graph is created in EXCEL and you hover you mouse over the dataline you receive that corect response. If you convert the chart to a sheet, the hover of the data line is now not representative of the the y axis directly below it. The data being graphed is correct now the hover represents the "series" (x-Axis) correctly but does not represent the "Point" (y-axis) correctly at all. Tne Y-axis datapoint reference is wrong. Any help? ...

Editable Excel Spreadsheet Online?
Hi, I tried to recent find information on this, but could find very little. How difficult would it be to host an excel spreadsheet online where visitors to the site can directly view and edit it? Right now, I can upload the spreadsheet to our web site and visitors can view it, but if they edit it, they can only save it to their local drive. I would like the users to be able to save the copy on the server. What would be involved in something like this? I assume for starters (if it's do-able) we'd need Windows hosting (we're not hosting ourselves) and some ASP support. Any de...

Use a wildcard within edit/replace
Hello Is there a method of using a wildcard function within edit/replace (in Excel 2003) so that I can tweak the way a formula works? The example I have is the following formula =IF(ISERROR(VLOOKUP($A8,DataImport!$A:$J,5,FALSE)),"",VLOOKUP($A8,DataImport!$A:$J,5,FALSE)) which I would like to change to =IF(ISERROR(VLOOKUP(TEXT($C8,"0000),DataImport!$D:$E,2,FALSE)),"",VLOOKUP(TEXT($C8,"0000"),DataImport!$D:$E,2,FALSE)) The issue is (I think) that this formula is repeated many times over in one column over a number of worksheets - therefo...

Wordwrapping a long sentence using F2, how to end the process?
Using Excel 2002...I pasted in a long sentence, and it goes off the right side of the screen. I know that I can make it word wrap by hitting F2. I've done that and it works nicely. But I was told that I could end this process by hitting Alt-Enter, and that doesn't work... all that happens after Alt-Enter is a blank new row opens up below the word-wrapped rows. If I hit Alt-Enter again, another blank row opens up below. How can I end this F2 word-wrap and go on with other business. John alt-enter is used to force a new line in a cell. If you want to wrap the text a...

how do I delete documents from the start list in word and excel?
how do I delete documents from the start list in word and excel? You can not clear it whenever you want. You can however set the no of file names to be displayed to 0 which clears the list... In Excel 2003 Tool->Options->General Enter 0 against 'Recently Used File List' of clear the check box. Click 'OK' Word has a similar option. For 2007 versions or if you want to play with Registry Settings (not advised unless you understand it well) see http://www.mydigitallife.info/2008/01/13/how-to-clear-and-delete-recent-documents-list-in-office-2007-word-excel-p...