From: Mike H on
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(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
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(a)discussions.microsoft.com> wrote in message
news:C86781FC-5C44-411A-9A76-8A1266F65319(a)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(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: Dave Peterson on
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(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?
> >
> >
> > .
> >

--

Dave Peterson
From: Dave Peterson on
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(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?
> > >
> > >
> > > .
> > >
>
> --
>
> Dave Peterson

--

Dave Peterson
From: zip22 on
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(a)discussions.microsoft.com> wrote in message
> news:C86781FC-5C44-411A-9A76-8A1266F65319(a)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(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?
> >>
> >>
> >> .
> >>
>
>
> .
>