I need to calculate the difference between two timestamps in milliseconds. Unfortunatley, the DateDiff-function of VBA does not offer this precision. Are there any workarounds?
Thanks in advance!
+6
A:
You can use the method described here
Other methods describe use of the VBA Timer function but this is only accurate to one hundredth of a second (centisecond).
AdamRalph
2009-06-02 12:35:20
+1. I was wracking my brain trying to remember what the Win32 API function was!
Mitch Wheat
2009-06-02 12:36:59
Someone has downvoted this answer. What is the reason?
AdamRalph
2009-06-02 19:03:55
Me. No shame in my game:) NOI, but you had him use an API call when he could have just used the built in timer function. It would be like hitting the Beep API instead of just using Beep. Or the MessageBox API instead of MsgBox etc. IMHO if you can do it simply without an API or a reference you should. Course that's just personal opinion... And you know what they say about opinions:)
Oorang
2009-06-03 05:54:49
@Oorang - the VBA Timer function returns a Single which counts the number of seconds elapsed since midnight, it only supplies 2 decimal places so therefore appears to be accurate to only one hundredth of a second.
AdamRalph
2009-06-03 07:45:59
Well crap. You're right:) You can only get down to centiseconds. I never noticed that it was only coming back with two decimals. If you do an edit I'll change my vote (it's too old for me to change my vote without an edit). (This is why I love this board... I *learned* something:))
Oorang
2009-06-04 02:18:33
have edited ;-)
AdamRalph
2009-06-04 10:43:05
Have changed from -1 to +1:)
Oorang
2009-06-04 15:05:39
Thanks! Works great :)
Florian
2009-07-06 12:38:56
A:
Besides the Method described by AdamRalph (GetTickCount()), you can do this:
- Using the
QueryPerformanceCounter()andQueryPerformanceFrequency()API Functions
http://stackoverflow.com/questions/198409/how-do-you-test-running-time-of-vba-code - or, for environments without access to the Win32 API (like VBScript), this:
http://ccrp.mvps.org/ (check the download section for the "High-Performance Timer" installable COM objects. They're free.)
Tomalak
2009-06-02 12:49:52
Using QueryPerformanceCounter() requires considerably more code. Maybe it's useful if you are already dealing with perf counters in your code. I just wanted to mention the alternative, I don't think the results are any different. I suspect it boils down to GetTickCount() internally anyway. :)
Tomalak
2009-06-06 08:50:48
A:
If you just need time elapsed in Centiseconds then you don't need the TickCount API. You can just use the VBA.Timer Method which is present in all Office products.
Public Sub TestHarness()
Dim fTimeStart As Single
Dim fTimeEnd As Single
fTimeStart = Timer
SomeProcedure
fTimeEnd = Timer
Debug.Print Format$((fTimeEnd - fTimeStart) * 100!, "0.00 "" Centiseconds Elapsed""")
End Sub
Public Sub SomeProcedure()
Dim i As Long, r As Double
For i = 0& To 10000000
r = Rnd
Next
End Sub
Oorang
2009-06-02 18:58:19
A:
GetTickCount and Performance Counter are required if you want to go for micro seconds.. For millisenconds you can just use some thing like this..
'at the bigining of the module
Private Type SYSTEMTIME
wYear As Integer
wMonth As Integer
wDayOfWeek As Integer
wDay As Integer
wHour As Integer
wMinute As Integer
wSecond As Integer
wMilliseconds As Integer
End Type
Private Declare Sub GetLocalTime Lib "kernel32" (lpSystemTime As SYSTEMTIME)
'In the Function where you need find diff
Dim sSysTime As SYSTEMTIME
Dim iStartSec As Long, iCurrentSec As Long
GetLocalTime sSysTime
iStartSec = CLng(sSysTime.wSecond) * 1000 + sSysTime.wMilliseconds
'do your stuff spending few milliseconds
iCurrentSec=CLng(sSysTime.wSecond) * 1000 + sSysTime.wMilliseconds
'Different between iStartSec and iCurrentSec will give you diff in MilliSecs
Adarsha
2009-06-04 01:56:15