Measuring the Execution Duration of VBA Controls

This Tip & Trick is about using a VBA Timer execution timer for VBA controls and measuring the execution duration of VBA controls. To check and improve the performance of a model it can be useful to have a timer that measures the execution time of your VBA code. The timer can for example be used to measure the execution time of certain controls.

Additional VBA class

The timer is based on a class defined in VBA which is called CTimer. The code for the class is originally from Stackoverflow (class CTimer) and has been modified for the use with WWB-COM and WWB.NET. The class can be found besides a demo model in the download section (see below). To use the class, the code can just be copied into a separate macro. In the class CTimer, the method CTimer.startCounter can be called to start the timer. With CTimer.TimeElapsed, the elapsed time can be read out in milliseconds.

Measure the execution time

In some examples, the influence of different syntax on the execution duration in VBA is shown. To store the measured times in a table, a subroutine called Store_Measured_Time is called. In this subroutine, the execution duration of all calls of the observed VBA code are added up. Furthermore, the maximum duration for one call of the control, the number of calls, and the mean duration of the call is written into a table. If there is no row for the measured control, a new row is created. The subroutine receives the name of the measured sub and the Timer variable of class CTimer as argument:

Sub Store_Measured_Time(measured_sub_name As String, ByRef sub_timer As CTimer) 'time_elapsed As Double) 
        
        Dim time_elapsed As Double
        time_elapsed = sub_timer.TimeElapsed 'read out the passed time
    
        Dim row As Long
        row = t_Timer.FindRow(measured_sub_name) 'look in the table if there is already a line for the sub
    
        If row < 0 Then 'if there is no line for the sub yet
            row = t_Timer.RowCount + 1
            t_Timer(row,0) = measured_sub_name
            t_Timer(row,"Total Time [ms]") = 0
            t_Timer(row,"Number") = 0
            t_Timer(row,"Mean [ms]") = 0
            t_Timer(row,"Max [ms]") = 0
        End If
    
        t_Timer(measured_sub_name,"Total Time [ms]") += time_elapsed 'add the elapsed time
        t_Timer(measured_sub_name,"Number") += 1 'count the calls of the sub
        t_Timer(measured_sub_name,"Mean [ms]") =  t_Timer(measured_sub_name,"Total Time [ms]")/t_Timer(measured_sub_name,"Number") 'calculate the mean duration of the sub
    
        If time_elapsed > t_Timer(measured_sub_name,"Max [ms]") Then
            t_Timer(measured_sub_name,"Max [ms]") = time_elapsed 'saves the maximum time
        End If

End Sub

This sub is just one example of how to evaluate the results of the timer. Feel free to use your own KPIs to evaluate the execution duration of your VBA controls.

When the Store_Measured_Time sub is used like that, the table t_Timer must be initialized, e.g., in the Simulation Init:

Dim t_Timer As Table
Private Sub Simulation_Init() Handles Simulation.Init
    t_Timer = New Table
    t_Timer = Read_Table("Timer Template",1,1,0,0,True,True)
End Sub

In the example model in the download area, the Parameters workbook contains the sheet Timer Template which is an empty table with the headings (Procedures, Total Time [ms], etc.).

Examples

Comparing two ways of using if-Statements

The first example compares two different syntaxes to use an If-Statement in a start control.

'Version 1:
Sub OP_Start1(cop As OrderOperation)
    Dim sub_timer As New CTimer
    sub_timer.StartCounter 'starts the counter

    If cop.Unit.Allocated = True Then
        'do nothing
    End If

    Store_Measured_Time("OP_Start1",sub_timer)
End Sub

'Version 2:
Sub OP_Start2(cop As OrderOperation)
    Dim sub_timer As New CTimer
    sub_timer.StartCounter 'starts the counter

    If cop.Unit.Allocated Then
        'do nothing
    End If

    Store_Measured_Time("OP_Start2",sub_timer)
End Sub

In the result table, one can see that the first version of the control is a little slower than the second version. The maximum duration, the mean value for the duration and the total duration for the same number of calls are larger.

Procedures	Total Time [ms]	Number	Mean [ms]	Max [ms]
OP_Start1	1,1016	100	0,011016	0,0569
OP_Start2	0,5958	100	0,005958	0,0102

Note: All values are specific for the used computer and current state of the computer.

Comparing Different Syntaxes for Mathematical Operations

In the second example, two different syntaxes for doubling the operation’s duration in a parameter control are compared.

'Version 1:
Sub OP_Parameter1(cop As OrderOperation, mop As RecipeOperation)

    Dim sub_timer As New CTimer
    sub_timer.StartCounter

    cop.Duration = cop.Duration * 2

    Store_Measured_Time("OP_Parameter1",sub_timer)
End Sub

'Version 2:
Sub OP_Parameter2(cop As OrderOperation, mop As RecipeOperation)

    Dim sub_timer As New CTimer
    sub_timer.StartCounter

    cop.Duration *= 2

    Store_Measured_Time("OP_Parameter2",sub_timer)
End Sub

The results show that the second version is almost twice as fast as the first version. The maximum value for the call of both versions is nearly the same, but the total time and the mean duration of the controls are very different.

Procedures	Total Time [ms]	Number	Mean [ms]	Max [ms]
OP_Parameter1	1,1806	100	0,011806	0,0282
OP_Parameter2	0,6375	100	0,006375	0,0278

Note: All values are specific for the used computer and current state of the computer.

Unit Pool Allocation Control V12 vs. V13

In another example, the behavior of a Unit Pool Allocation control is compared. In the Tip & Trick Using the new controls for unit pools in INOSIM 13, the two different versions of the control are described in detail. The first version uses the default behavior of INOSIM Version 12 of the control, where the control is called several times and a unit is proposed. The proposed unit can be declined or accepted. If it is declined, the control is called anew. Starting with INOSIM 13, it is possible to decide in the first call of the function which unit is allowed. In that case, only one call is needed. Is there a significant advantage in simulation speed?

Procedures	Total time [ms]	Number	Mean [ms]	Max [ms]
Product_Tank_V12	17124,2879	399329	0,04288266	219,431
Product_Tank_V13	660,2568	3901	0,16925322	0,9836

Note: All values are specific for the used computer and current state of the computer.

The mean duration for the V12 control is only one quarter of the mean duration of the V13 control. However, the control is called about 100 times more often than the V13 version, as it is called several times until a suitable unit is found. All in all, we can find a 25-fold advantage in simulation speed for the V13 version in this specific use case.

Side Note

Be aware that measuring the execution time itself does require additional computation time. Comment or delete the lines from your VBA code, responsible for starting the timer and storing the data, if you don’t need them anymore. Alternatively, add a switch to turn the VBA timer on or off globally.

Downloads

(for registered INOSIM users)

Example model VBA Timer.ixml
CTimer class text
PDF printout of this Tip & Trick

More Tips & Tricks

4. October 2019

Measuring the Execution Duration of VBA Controls

Additional VBA class

Measure the execution time

Examples

Comparing two ways of using if-Statements

Comparing Different Syntaxes for Mathematical Operations

Unit Pool Allocation Control V12 vs. V13

Side Note

More Questions?

More Tips & Tricks

Applying Table Objects

Two Ways Of Simulating Unit Failures

Tank Level Evaluation