Prev: Sheet change event to return sheet name just left
Next: How to update .xlam file (add-in) while it is in use?
From: zip22 on 19 May 2010 12:23
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?
From: Mike H on 19 May 2010 12:31
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?
From: Rick Rothstein on 19 May 2010 12:40
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(a)discussions.microsoft.com> wrote in message
news:EC0EF511-3D2A-423F-98BA-82F0F76B68A6(a)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?

From: Peter T on 19 May 2010 12:57
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(a)discussions.microsoft.com> wrote in message
news:DDCD95F2-FAD4-445B-9962-DAB0F797EF0E(a)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?


From: zip22 on 19 May 2010 13:52
[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(a)discussions.microsoft.com> wrote in message
> news:DDCD95F2-FAD4-445B-9962-DAB0F797EF0E(a)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?
>
>
> .
>
 |  Next  |  Last
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?