Split-second timer (Excel 97 VBA)

davidtrickett

New Member
Joined
Apr 22, 2002
Messages
37
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.
 

Excel Facts

Get help while writing formula
Click the italics "fx" icon to the left of the formula bar to open the Functions Arguments dialog. Help is displayed for each argument.
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,<font size=+2><font color="red"> Nate<font color="blue">O</font></font></font>
This message was edited by NateO on 2002-04-30 13:41
 
Upvote 0
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.
 
Upvote 0
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.
 
Upvote 0
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
 
Upvote 0

Forum statistics

Threads
1,213,510
Messages
6,114,037
Members
448,543
Latest member
MartinLarkin

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top