How do you check the running time of VBA code?

Question

Is there any code in VBA, I can wrap a function that allows me to find out the time it takes to start, so that I can compare different times of the functions?

Answer 1

If your functions are not very slow, you will need a timer with a very high resolution. The most accurate I know is QueryPerformanceCounter . Google for more information. Try clicking the following in the class, name it CTimer say, then you can make the 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 < 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 

Answer 2

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

 Dim t as single t = Timer 'code MsgBox Timer - t 

If you need more resolution, I will just run the function 1000 times and divide the total time by 1000.

Answer 3

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

 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 (since timeGetTime in winmm.dll did not work for me, and QueryPerformanceCounter was too complicated for the required task)

Answer 4

For beginners, these links explain how to automatically profile all the sub-sites that you want to track over time:

http://www.nullskull.com/a/1602/profiling-and-optimizing-vba.aspx

http://sites.mcpher.com/share/Home/excelquirks/optimizationlink see procProfiler.zip at http://sites.mcpher.com/share/Home/excelquirks/downlable-items

Answer 5

We have used timeGetTime-based solution 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 (VBA class that completes the call to the dll function) is simple enough to use and understand, without being able to read the article.