How do you test running time of VBA code

Question

Is there code in VBA I can wrap a function with that will let me know the time it took to run  so that I can compare the different running times of functions

User · Answer

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

Public Declare Function GetTickCount Lib "kernel32.dll" () As Long
Sub testTimer()
Dim t As Long
t = GetTickCount

For i = 1 To 1000000
a = a + 1
Next

MsgBox GetTickCount - t, , "Milliseconds"
End Sub

after http://www.pcreview.co.uk/forums/grab-time-milliseconds-included-vba-t994765.html (as timeGetTime in winmm.dll was not working for me and QueryPerformanceCounter was too complicated for the task needed)

User · Answer

The Timer function in VBA gives you the number of seconds elapsed since midnight  to 1 100 of a second   Dim t as single t   Timer  code MsgBox Timer - t

User · Answer

The Timer function in VBA gives you the number of seconds elapsed since midnight  to 1 100 of a second   Dim t as single t   Timer  code MsgBox Timer - t

User · Answer

We ve used a solution based on timeGetTime in winmm dll for millisecond accuracy for many years  See http   www aboutvb de kom artikel komstopwatch htm  The article is in German  but the code in the download  a VBA class wrapping the dll function call  is simple enough to use and understand without being able to read the article

User · Answer

We ve used a solution based on timeGetTime in winmm dll for millisecond accuracy for many years  See http   www aboutvb de kom artikel komstopwatch htm  The article is in German  but the code in the download  a VBA class wrapping the dll function call  is simple enough to use and understand without being able to read the article

User · Answer

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

Public Declare Function GetTickCount Lib "kernel32.dll" () As Long
Sub testTimer()
Dim t As Long
t = GetTickCount

For i = 1 To 1000000
a = a + 1
Next

MsgBox GetTickCount - t, , "Milliseconds"
End Sub

after http://www.pcreview.co.uk/forums/grab-time-milliseconds-included-vba-t994765.html (as timeGetTime in winmm.dll was not working for me and QueryPerformanceCounter was too complicated for the task needed)

User · Answer

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 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  lt  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

User · Answer

Sub Macro1       Dim StartTime As Double     StartTime   Timer                                            Your Code                                   MsgBox  RunTime      amp  Format  Timer - StartTime    86400   hh mm ss   End Sub   Output      RunTime   00 00 02

User · Answer

Sub Macro1       Dim StartTime As Double     StartTime   Timer                                            Your Code                                   MsgBox  RunTime      amp  Format  Timer - StartTime    86400   hh mm ss   End Sub   Output      RunTime   00 00 02

User · Answer

We ve used a solution based on timeGetTime in winmm dll for millisecond accuracy for many years  See http   www aboutvb de kom artikel komstopwatch htm  The article is in German  but the code in the download  a VBA class wrapping the dll function call  is simple enough to use and understand without being able to read the article

User · Answer

The Timer function in VBA gives you the number of seconds elapsed since midnight  to 1 100 of a second   Dim t as single t   Timer  code MsgBox Timer - t

User · Answer

Seconds with 2 decimal spaces   Dim startTime As Single  start timer MsgBox   run time     amp  Format  Timer - startTime    1000000       0 00    amp    seconds    end timer     Milliseconds   Dim startTime As Single  start timer MsgBox   run time     amp  Format  Timer - startTime        0 00    amp    milliseconds    end timer     Milliseconds with comma seperator   Dim startTime As Single  start timer MsgBox   run time     amp  Format  Timer - startTime    1000       0 00    amp    milliseconds    end timer     Just leaving this here for anyone that was looking for a simple timer formatted with seconds to 2 decimal spaces like I was  These are short and sweet little timers I like to use  They only take up one line of code at the beginning of the sub or function and one line of code again at the end  These aren t meant to be crazy accurate  I generally don t care about anything less then 1 100th of a second personally  but the milliseconds timer will give you the most accurate run time of these 3  I ve also read you can get the incorrect read out if it happens to run while crossing over midnight  a rare instance but just FYI

User · Answer

The Timer function in VBA gives you the number of seconds elapsed since midnight  to 1 100 of a second   Dim t as single t   Timer  code MsgBox Timer - t

User · Answer

For newbees  these links explains how to do an automatic profiling of all the subs that you want to time monitor    http   www nullskull com a 1602 profiling-and-optimizing-vba aspx  http   sites mcpher com share Home excelquirks optimizationlink see procProfiler zip in http   sites mcpher com share Home excelquirks downlable-items

User · Answer

For newbees  these links explains how to do an automatic profiling of all the subs that you want to time monitor    http   www nullskull com a 1602 profiling-and-optimizing-vba aspx  http   sites mcpher com share Home excelquirks optimizationlink see procProfiler zip in http   sites mcpher com share Home excelquirks downlable-items

User · Answer

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 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  lt  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

User · Answer

Seconds with 2 decimal spaces   Dim startTime As Single  start timer MsgBox   run time     amp  Format  Timer - startTime    1000000       0 00    amp    seconds    end timer     Milliseconds   Dim startTime As Single  start timer MsgBox   run time     amp  Format  Timer - startTime        0 00    amp    milliseconds    end timer     Milliseconds with comma seperator   Dim startTime As Single  start timer MsgBox   run time     amp  Format  Timer - startTime    1000       0 00    amp    milliseconds    end timer     Just leaving this here for anyone that was looking for a simple timer formatted with seconds to 2 decimal spaces like I was  These are short and sweet little timers I like to use  They only take up one line of code at the beginning of the sub or function and one line of code again at the end  These aren t meant to be crazy accurate  I generally don t care about anything less then 1 100th of a second personally  but the milliseconds timer will give you the most accurate run time of these 3  I ve also read you can get the incorrect read out if it happens to run while crossing over midnight  a rare instance but just FYI

User · Answer

We ve used a solution based on timeGetTime in winmm dll for millisecond accuracy for many years  See http   www aboutvb de kom artikel komstopwatch htm  The article is in German  but the code in the download  a VBA class wrapping the dll function call  is simple enough to use and understand without being able to read the article

User · Answer

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 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  lt  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

User · Answer

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 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  lt  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

[optimization] How do you test running time of VBA code?

Examples related to optimization

Examples related to testing

Examples related to vba

Examples related to profiling

Examples related to performance