How can I measure time more precisely in VBA?-CodePudding

I have an Excel document with some VBA scripts. Measuring times with 'Time' is not very precise. Are there other options?

CodePudding user response：

At the top of the script you can add this:

Private Declare PtrSafe Function get_frequency Lib "kernel32" _
      Alias "QueryPerformanceFrequency" ( _
      ByRef Frequency As Currency) _
      As Long
 
Private Declare PtrSafe Function get_time Lib "kernel32" _
      Alias "QueryPerformanceCounter" ( _
      ByRef Counter As Currency) _
      As Long

Then in your code you can get high precision time measurements like this...

' run this once
Dim per_second As Currency: get_frequency per_second

' ...

' get start time
Dim t0 As Currency: get_time t0

' HERE perform the operations you want to time 
' ...

' get end time
Dim t1 As Currency: get_time t1

' calculate high precision duration
Dim elapsed_millis As Double: elapsed_millis = (t1 - t0) / per_second * 1000#

CodePudding user response：

Add this at the start of your subroutine:

Dim StartTime As Double 'Declaration of timer to identify length of runtime
StartTime = Timer

At this at the end of your subroutine:

Debug.Print "RunTime for Program is: " & Format((Timer - StartTime) / 86400, 
"hh:mm:ss.ms") 'Print out runtime for subroutine