How do you test running time of VBA code? How do you test running time of VBA code? vba vba

How do you test running time of VBA code?

Unless your functions are very slow, you're going to need a very high-resolution timer. The most accurate one I know is QueryPerformanceCounter. Google it for more info. Try pushing the following into a class, call it CTimer say, then you can make an instance somewhere global and just call .StartCounter and .TimeElapsed

Option ExplicitPrivate Type LARGE_INTEGER    lowpart As Long    highpart As LongEnd TypePrivate Declare Function QueryPerformanceCounter Lib "kernel32" (lpPerformanceCount As LARGE_INTEGER) As LongPrivate Declare Function QueryPerformanceFrequency Lib "kernel32" (lpFrequency As LARGE_INTEGER) As LongPrivate m_CounterStart As LARGE_INTEGERPrivate m_CounterEnd As LARGE_INTEGERPrivate m_crFrequency As DoublePrivate Const TWO_32 = 4294967296# ' = 256# * 256# * 256# * 256#Private Function LI2Double(LI As LARGE_INTEGER) As DoubleDim Low As Double    Low = LI.lowpart    If Low < 0 Then        Low = Low + TWO_32    End If    LI2Double = LI.highpart * TWO_32 + LowEnd FunctionPrivate Sub Class_Initialize()Dim PerfFrequency As LARGE_INTEGER    QueryPerformanceFrequency PerfFrequency    m_crFrequency = LI2Double(PerfFrequency)End SubPublic Sub StartCounter()    QueryPerformanceCounter m_CounterStartEnd SubProperty Get TimeElapsed() As DoubleDim crStart As DoubleDim crStop As Double    QueryPerformanceCounter m_CounterEnd    crStart = LI2Double(m_CounterStart)    crStop = LI2Double(m_CounterEnd)    TimeElapsed = 1000# * (crStop - crStart) / m_crFrequencyEnd Property

The Timer function in VBA gives you the number of seconds elapsed since midnight, to 1/100 of a second.

Dim t as singlet = Timer'codeMsgBox Timer - t

If you are trying to return the time like a stopwatch you could use thefollowing API which returns the time in milliseconds since system startup:

Public Declare Function GetTickCount Lib "kernel32.dll" () As LongSub testTimer()Dim t As Longt = GetTickCountFor i = 1 To 1000000a = a + 1NextMsgBox GetTickCount - t, , "Milliseconds"End Sub

after (as timeGetTime in winmm.dll was not working for me and QueryPerformanceCounter was too complicated for the task needed)