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?
This question is related to
optimization
testing
vba
profiling
performance
The answer is
Seconds with 2 decimal spaces:
Dim startTime As Single 'start timer
MsgBox ("run time: " & Format((Timer - startTime) / 1000000, "#,##0.00") & " seconds") 'end timer
Milliseconds:
Dim startTime As Single 'start timer
MsgBox ("run time: " & Format((Timer - startTime), "#,##0.00") & " milliseconds") 'end timer
Milliseconds with comma seperator:
Dim startTime As Single 'start timer
MsgBox ("run time: " & Format((Timer - startTime) * 1000, "#,##0.00") & " 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.
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
Sub Macro1()
Dim StartTime As Double
StartTime = Timer
''''''''''''''''''''
'Your Code'
''''''''''''''''''''
MsgBox "RunTime : " & Format((Timer - StartTime) / 86400, "hh:mm:ss")
End Sub
Output:
RunTime : 00:00:02
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.
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
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
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
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.
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)
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.
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
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)
Seconds with 2 decimal spaces:
Dim startTime As Single 'start timer
MsgBox ("run time: " & Format((Timer - startTime) / 1000000, "#,##0.00") & " seconds") 'end timer
Milliseconds:
Dim startTime As Single 'start timer
MsgBox ("run time: " & Format((Timer - startTime), "#,##0.00") & " milliseconds") 'end timer
Milliseconds with comma seperator:
Dim startTime As Single 'start timer
MsgBox ("run time: " & Format((Timer - startTime) * 1000, "#,##0.00") & " 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.
Sub Macro1()
Dim StartTime As Double
StartTime = Timer
''''''''''''''''''''
'Your Code'
''''''''''''''''''''
MsgBox "RunTime : " & Format((Timer - StartTime) / 86400, "hh:mm:ss")
End Sub
Output:
RunTime : 00:00:02
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.
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
Source: Stackoverflow.com