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.
(for registered INOSIM users)
- Example model VBA Timer.ixml
- CTimer class text
- PDF printout of this Tip & Trick
More Questions?
Want to know more about this topic or have another question? Please contact us!
More Tips & Tricks
Using the new controls for Unit Pools in INOSIM 13
The control frameworks for the Unit Pool Allocation control and the Transfer to/from Unit Pool control in the new INOSIM version 13 have changed. The…
Transfer Analysis with Power BI
In this tip and trick, the Transfer Analysis dashboard is introduced. It is an extension of the Power BI standard dashboard that allows you to…
Preventing Runtime Errors and Custom Error Handling
This Tip & Trick describes a multitude of methods to prevent and handle runtime errors of your VBA code. You will be introduced to the…