Error Checking in Excel
Thanks Thanks:  0
Likes Likes:  0
Results 1 to 5 of 5

Thread: Split-second timer (Excel 97 VBA)

  1. #1
    New Member
    Join Date
    Apr 2002
    Posts
    37
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default

    I need to find out how long various sections of a routine are taking and need a timer (fairly) accurate to milliseconds - even microseconds!

    The "Time" function is of course useless for this - any ideas?

    I suspect it needs an API call, but I'm not very well up on these. Whatever I use must work on W98 & NT4.

    As a bonus if any responders could point me at a good - and understandable - web resource on API calls I would be grateful.

    Thank you.


  2. #2
    Legend NateO's Avatar
    Join Date
    Feb 2002
    Location
    Minneapolis, Mn, USA
    Posts
    9,700
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default

    Well here's something for millisecs, I was surprised that if you tell Excel to wait for 2 seconds it only waits for 1500 milliseconds, maybe XL is this choppy:

    Code:
    Private Declare Function GetTickCount Lib "kernel32" () As Long
    Dim str, fin
    Sub TimTst()
    Call StrTim
    Call tstproc
    Call StpTim
    N = fin - str
    MsgBox (N)
    fin = ""
    str = ""
    End Sub
    Private Sub StpTim()
    fin = GetTickCount()
    End Sub
    Private Sub StrTim()
    str = GetTickCount()
    fin = ""
    End Sub
    Sub tstproc()
    Application.Wait (Now + TimeValue("00:00:02")) 'Your Test Procedure
    End Sub
    The other thing is that this really ought to vary on any given machine, as with micros (RAM, etc)...

    Don't necessarily know of a good one stop shop for API's. Good luck.

    _________________
    Cheers, NateO

    [ This Message was edited by: NateO on 2002-04-30 13:41 ]

  3. #3
    New Member
    Join Date
    Apr 2002
    Posts
    37
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default

    NateO

    Many thanks for your help - just what I needed.

    Excel certainly has an eccentric idea of timing - running the macro as a loop typical results are:
    1181(sic)
    2031
    1979
    1975
    2035
    1975
    2035
    1975
    1980
    2031
    Invariably the first result is considerably shorter than the rest.


  4. #4
    Legend NateO's Avatar
    Join Date
    Feb 2002
    Location
    Minneapolis, Mn, USA
    Posts
    9,700
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default

    David you're welcome, I was always suprised MilliS's aren't built in. I almost wonder if Xl isn't cheating a bit on this one, calling the stp procedure milli seconds before it's fully completed the test procedure. Could set up a user form that runs after your procedure where you manually stop the "stop-watch" if you will. Food for thought. Have a good one.

  5. #5
    Board Regular pbornemeier's Avatar
    Join Date
    May 2005
    Location
    Virginia Beach, VA USA
    Posts
    3,506
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    1 Thread(s)

    Default Re: Split-second timer (Excel 97 VBA)

    When I ran TimTst multiple times i got a range of values, always less than 2000
    1703
    1437
    1672
    1750
    1718
    1765
    1703
    1828
    1937
    1234
    1625
    1656
    1640
    1687
    1656
    1703
    1718
    1890
    1078

    When I called it inside a For..Next loop (and used debug.print instead of displaying a msgbox), the first value was less than 2000, but all of the remaining values were exactly 2000
    Phil

    - Use CODE tags to keep your code formatted. See: BB Tags
    - How to attach Screenshots
    - Try searching for your answer first, see how
    - Test and validate results for all code on a copy of your worksheet!! How do you use the code you just found?
    - Posting guidelines, forum rules and terms of use
    - Read the FAQs

Some videos you may like

User Tag List

Like this thread? Share it with others

Like this thread? Share it with others

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •