High Res VBA Timer

[warhead92100]

Hi Guys,

I've trying to review how long does my code run, however I would like it to be precise as much as possible. While googling a work around, I stumbled upon these class module code:

Option Explicit
Private Type LARGE_INTEGER
    lowpart As Long
    highpart As Long
End Type
Private Declare Function QueryPerformanceCounter Lib "kernel32" (lpPerformanceCount As LARGE_INTEGER) As Long
Private Declare Function QueryPerformanceFrequency Lib "kernel32" (lpFrequency As LARGE_INTEGER) As Long
Private m_CounterStart As LARGE_INTEGER
Private m_CounterEnd As LARGE_INTEGER
Private m_crFrequency As Double
Private Const TWO_32 = 4294967296# ' = 256# * 256# * 256# * 256#
Private Function LI2Double(LI As LARGE_INTEGER) As Double
Dim Low As Double
    Low = LI.lowpart
    If Low < 0 Then
        Low = Low + TWO_32
    End If
    LI2Double = LI.highpart * TWO_32 + Low
End Function
Private Sub Class_Initialize()
Dim PerfFrequency As LARGE_INTEGER
    QueryPerformanceFrequency PerfFrequency
    m_crFrequency = LI2Double(PerfFrequency)
End Sub
Public Sub StartCounter()
    QueryPerformanceCounter m_CounterStart
End Sub
Property Get TimeElapsed() As Double
Dim crStart As Double
Dim crStop As Double
    QueryPerformanceCounter m_CounterEnd
    crStart = LI2Double(m_CounterStart)
    crStop = LI2Double(m_CounterEnd)
    TimeElapsed = 1000# * (crStop - crStart) / m_crFrequency
End Property

From: optimization - How do you test running time of VBA code? - Stack Overflow

However I quite don't get how this works, I'm trying to use it:

Public Sub setup()
    Dim t As Long
    
    With New CTimer
    .StartCounter

'codes....

.TimeElapsed
    t = .TimeElapsed
    
    End With
    
    MsgBox t
End Sub

But I'm getting an Invalid use of property error, sorry I'm still pretty new to VBA, can someone spell this out for me.

Thanks!

 

[RoryA]

Remove the

.TimeElapsed

line - you need to assign the result of the property to a variable (as you do in the next line).

 

[Kyle123]

Public Sub setup()
    Dim t As Long
    
    With New CTimer
    .StartCounter


'codes....


    '.TimeElapsed Remove this line
    t = .TimeElapsed
    
    End With
    
    MsgBox t
End Sub

Simply remove the line indicated, time elapsed returns a number, Excel doesn't know what to do with it

 

[warhead92100]

Thanks Rory and Kyle!

 

[joeu2004]

I've trying to review how long does my code run, however I would like it to be precise as much as possible. While googling a work around, I stumbled upon these class module code:
[....]
From: optimization - How do you test running time of VBA code? - Stack Overflow

That's a poor implementation, IMHO. I think the implementation below is cleaner, and it might be easier to understand.

Also note that in your usage, it is better to declare t As Double instead of Long. That retains the full precision of TimeElapsed, which is even better than microseconds. (But I think it is sufficient to format only to the microsecond, 3 decimal places.)

Option Explicit
Private Declare Function QueryPerformanceCounter Lib "kernel32" _
    (lpPerformanceCount As Currency) As Long
Private Declare Function QueryPerformanceFrequency Lib "kernel32" _
    (lpFrequency As Currency) As Long

Private m_CounterStart As Currency
Private m_CounterEnd As Currency
Private d_PerfFrequency As Double

Private Sub Class_Initialize()
    Dim m_PerfFrequency As Currency
    QueryPerformanceFrequency m_PerfFrequency
    d_PerfFrequency = m_PerfFrequency
End Sub

Public Sub StartCounter()
    QueryPerformanceCounter m_CounterStart
End Sub

Property Get TimeElapsed() As Double    ' milliseconds
    QueryPerformanceCounter m_CounterEnd
    TimeElapsed = 1000# * (m_CounterEnd - m_CounterStart) / d_PerfFrequency
End Property