Split-second timer (Excel 97 VBA)

davidtrickett · Apr 30th, 2002, 01:27 PM

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.

NateO · Apr 30th, 2002, 02:40 PM

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 ]

davidtrickett · May 1st, 2002, 03:26 AM

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.

NateO · May 1st, 2002, 07:41 AM

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.

pbornemeier · Feb 4th, 2010, 12:59 PM

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

Apr 30th, 2002, 02:40 PM	#2
NateO Legend Join Date: Feb 2002 Location: Minneapolis, Mn, USA Posts: 9,704	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 ]

May 1st, 2002, 07:41 AM	#4
NateO Legend Join Date: Feb 2002 Location: Minneapolis, Mn, USA Posts: 9,704	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. __________________ Regards, Nate Oliver Microsoft Excel MVP Nate's Excel Blog

Feb 4th, 2010, 12:59 PM	#5
pbornemeier Board Regular Join Date: May 2005 Location: Virginia Beach, VA USA Posts: 1,562	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 Please use code tags [code]<your code here>[/code] to preserve indents when you post your code. I wish I had known before my first post! Display worksheets using Excel Jeanie or HTML Maker Test and validate results for all code on a copy of your worksheet!! How do you use the code you just found?

Thread Tools
Show Printable Version Email this Page
Display Modes
Linear Mode Switch to Hybrid Mode Switch to Threaded Mode

Apr 30th, 2002, 01:27 PM	#1
davidtrickett New Member Join Date: Apr 2002 Posts: 33	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.

May 1st, 2002, 03:26 AM	#3
davidtrickett New Member Join Date: Apr 2002 Posts: 33	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.