How to get a DateDiff-Value in milliseconds in VBA (Excel)?

vba excel datediff
Florian picture Florian · Jun 2, 2009 · Viewed 68.8k times · Source

I need to calculate the difference between two timestamps in milliseconds. Unfortunately, the DateDiff-function of VBA does not offer this precision. Are there any workarounds?

Answer

Adam Ralph picture Adam Ralph · Jun 2, 2009

You could use the method described here as follows:-

Create a new class module called StopWatch Put the following code in the StopWatch class module:

Private mlngStart As Long
Private Declare Function GetTickCount Lib "kernel32" () As Long

Public Sub StartTimer()
    mlngStart = GetTickCount
End Sub

Public Function EndTimer() As Long
    EndTimer = (GetTickCount - mlngStart)
End Function

You use the code as follows:

Dim sw as StopWatch
Set sw = New StopWatch
sw.StartTimer

' Do whatever you want to time here

Debug.Print "That took: " & sw.EndTimer & "milliseconds"

Other methods describe use of the VBA Timer function but this is only accurate to one hundredth of a second (centisecond).

Related questions

How to find the difference between dates in VBA

I am trying to find out the difference between the system date and the date stored in the worksheet. If the difference between them is > 30 days, the result is true, else the result is false Dim result as boolean …

Read More
Is there a way to crack the password on an Excel VBA Project?

I've been asked to update some Excel 2003 macros, but the VBA projects are password protected, and it seems there's a lack of documentation... no-one knows the passwords. Is there a way of removing or cracking the password on a VBA …

Read More
How do I declare a global variable in VBA?

I wrote the following code: Function find_results_idle() Public iRaw As Integer Public iColumn As Integer iRaw = 1 iColumn = 1 And I get the error message: "invalid attribute in Sub or Function" Do you know what I did wrong? I tried …

Read More