Measure macro processing time Excel(Excel) Macro(VBA)

When trying to speed up a macro, we first need to be able to see how much time was saved before and after the improvement, and how much time was saved or not.

We need to be able to measure time for this purpose. We will show you how to do this in this issue.

This time we would like to try to display the time taken for processing in the "Immediate Window".

If there is no "Immediate Window" on the VBA screenHow to display the Immediate Windowpage.

1, Display the time taken for processing

Sub Execution()

    Dim starttime As Double : starttime = Timer' Start of time measurement
    
    '~Processing~.

    myspeed = Timer - starttime' End of time measurement
    Debug.Print "Processing time is " & myspeed & "seconds"

End Sub

By interspersing the process you wish to measure with the above code, you can display the time taken for this process.

Execution.

The Immediate window now looks like the one above.

In the example above, there is no processing content, so the time is displayed as 0 seconds.

2, Display the time of the process in progress.

Sometimes it is necessary to measure several steps in order to analyze where in the code the longest time is spent, rather than 1.

Here is an example of such a case.

Sub Execution()

    Dim starttime As Double: starttime = Timer ' Start of time measurement

    Call Processing 1

    Dim myspeed Double

    myspeed = Timer - starttime 'Time measurement lapse 1
    myspeed = WorksheetFunction.Round(myspeed, 3)

    Debug.Print "The time for process 1 is " & myspeed & "seconds"

    starttime = Timer 'reset the time

    Call Process 2

    myspeed = Timer - starttime 'Time measurement progress 2
    myspeed = WorksheetFunction.Round(myspeed, 3)

    Debug.Print "The time for process 2 is " & myspeed & "seconds"

End Sub

This will give you a clear picture of how many seconds it is taking to process 1 and how many seconds it is taking to process 2.

If you put a slightly larger formula in process 1 and a simple formula in process 2 and run it, you will see that

was displayed.

This revealed that process 1 took approximately 6.32 seconds and process 2 took approximately 0.06 seconds.

We found that process 1 took 100 times longer than process 2, so we knew that it was process 1 that we needed to work on.

Please try to use it in this way as it is convenient for you.