VBA efficiency test

Question

I found a VBA code to test the run time of a code in the thread How do you test running time of VBA code? . I implemented and it worked. But every time I run a simple code, as below,it returns me a different result.

I searched and tested many codes, but didn't found what I was expecting.

Is there a way of test the code and return something like the number of clocks that the code demands? Something that every time I run with the code below, returns me the same value?

Sub teste_tempo()

    Dim eficiencia As New Ctimer
    eficiencia.StartCounter

    For i = 0 To 10
        i = i + 1
    Next i
    MsgBox eficiencia.TimeElapsed & "[ms]"

End Sub

What reference do you use for `Ctimer`? Do you have a class module? — FaneDuru, Feb 03 '20 at 12:40
For such a small task, any method will return huge differences. Try `For i = 0 To 100000000`... There will also be differences because Windows processes load the CPU differently in different moments. I can supply a, so named `MicroTimer` I use to test different ways of doing the same task... — FaneDuru, Feb 03 '20 at 12:56
I posted an accurate way of time measuring but It will never return the same passed time. And it is not its fault... Just compare its returns with the one you use now. You did not specified what Ctimer means in your code... — FaneDuru, Feb 03 '20 at 13:20
Sorry, I was at work, and this is a "plus" activity .I think I wasn't enough clear. I'm trying to write different codes to a same task, to check which is more "efficient". In order to do so, in a more fair test conditions, I'm searching for a VBA to mesure the number of clocks (or other measure, but not time) something that it does not depends on the Windows load of work. Each of these VBA macros would be testes with this resource, showing me what they need to do the job. I tried use just until the `getTickCount cyTicks2` but it didn't work. — Carlos Matioli, Feb 03 '20 at 16:02
An example of what I was hoping is in this link [link](http://www.decisionmodels.com/FastExcelV4Profiler_VBA.htm). It has a Start Seq Counter and a End Seq Counter, but I just need to `debug.prin` the result. — Carlos Matioli, Feb 03 '20 at 16:26

FaneDuru · Accepted Answer · 2020-02-03T13:44:20.523

Firstly, I did not design this piece of code. I have it in my collection of nice pieces. All the credit must go to the person who created. I found it in many places... Try this and compare results, please:

Option Explicit

#If VBA7 Then
    Private Declare PtrSafe Function getFrequency Lib "kernel32" Alias _
            "QueryPerformanceFrequency" (cyFrequency As Currency) As Long
    Private Declare PtrSafe Function getTickCount Lib "kernel32" Alias _
            "QueryPerformanceCounter" (cyTickCount As Currency) As Long
#Else
    Private Declare Function getFrequency Lib "kernel32" Alias _
            "QueryPerformanceFrequency" (cyFrequency As Currency) As Long
    Private Declare Function getTickCount Lib "kernel32" Alias _
            "QueryPerformanceCounter" (cyTickCount As Currency) As Long
#End If

Public Function MicroTimer() As Double
    ' returns seconds from Windows API calls (high resolution timer)
    Dim cyTicks1 As Currency, cyTicks2 As Currency
    Static cyFrequency As Currency

    MicroTimer = 0

    If cyFrequency = 0 Then getFrequency cyFrequency

    ' get ticks
    getTickCount cyTicks1
    getTickCount cyTicks2

    ' calc seconds
    If cyFrequency Then MicroTimer = cyTicks2 / cyFrequency
End Function

And use it in the next way:

Sub teste_tempo()
 Dim i As Long, dTime As Double
    dTime = MicroTimer
    For i = 0 To 100000000
        i = i + 1
    Next i
    MsgBox (MicroTimer - dTime) * 1000 & " [ms]"
End Sub

But, it will never return exactly the same passed time!. The Window processed load your CPU and RAM in different percentages for different moments. The differences will be smaller and smaller inverse proportional with the iterations number.

Thank you @FaneDuru, but I think I wasn't enough clear. I'm trying to write different codes to a same task, to check which is more "efficient". In order to do so, in a more fair test conditions, I'm searching for a VBA to mesure the number of clocks (or other measure, but not time) that each of these VBA macros need to do the job. — Carlos Matioli, Feb 03 '20 at 15:49
@Carlos Matioli: Can you define what "number of clocks" mean? I test all my code solutions in order to see which variant is faster, using the above described way. It is necessary to write only one time the above part and use only `dTime = MicroTimer` at the beginning of each code and the message of the end, qualifying the code speed. What else except how fast it runs do you want to test? — FaneDuru, Feb 03 '20 at 17:04
I'm sorry if I'm being anoying with such demands (at least in my head I'm beying like that rsrs). By "Number of clocks", I mean the number of calculations the processor needs to fully execute a macro. Something like the CPU usage in the Windows Task Manager. For reference, see the last two columns in the table at this link [FastExcel V4 VBA Profiler](http://www.decisionmodels.com/FastExcelV4Profiler_VBA.htm). — Carlos Matioli, Feb 03 '20 at 17:53
Since I think there is no solution for what I was hoping for, I will use @FaneDuru's solution. Thank you. — Carlos Matioli, Feb 04 '20 at 19:53

VBA efficiency test

1 Answers1