Applying Table Objects

In INOSIM models, it can be very handy to organize the input for your recipes in Excel Tables, either to have a better overview over your input, or to make parameter changes accessible to people who do not know INOSIM. In order to use the information in Excel within INOSIM, they can be read into a Table object.

Introduction

Table objects have some advantages. First of all it is a lot faster to read your information into a Table object than accessing individual cells in Excel. Furthermore, Table objects in INOSIM are indexed tables. You can directly access a cell’s content by the row or column string index (i.e., its headline) without iterating through all rows or columns with a For-loop. The table shown below shows different operating parameters for three dryers, e.g., the drying duration.

To overwrite an operation’s duration in a parameter control with a value from the Table object t_process, the rows can directly accessed via the unit’s name if it matches the unit’s name in the INOSIM Database. The following code can be used:

Sub Drying_Duration(cop As OrderOperation, mop As RecipeOperation)
    cop.Duration = t_process(cop.Unit.Name, "Drying [h]") * 3600
End Sub

Read_Table function

To read this data from the Table object during the simulation, the data must first be written from Excel into the Table object. This is usually done in the Simulation_Init procedure using the Read_Table function, which will be introduced in this Tip & Trick. To access the Table object from any procedure (e.g., different parameter controls), it must be declared as a global variable, i.e., not as a local variable in the Simulation_Init.

The function expects as input parameters

XLS_Sheet (the string name of the Excel sheet you want to read from),
VBA_Table (the Table object defined before),
rs and cs (first row and first column of the area you want to read from),
re and ce (last row and last column, 0 for all),
ri and ci (activate the row and column index of the Table object). When the row index is activated, the first row is used as index (columns analogously).

For example, in the Simulation_Init procedure use the following code:

t_input = Read_Table("Input",1,1,0,0,1,1)

By this code, the complete Excel sheet is read, since the function re = 0 interprets this as “all available rows” (analog for ce). Of course, you may also, from row 2, only read columns 3 to 8 and only enable the row index with t_input = Read_Table(“Input”, t_input,2,3,0,8,1,0).
Keep in mind that as no duplicate row or column indices may be created, the row (or column) used as index may not contain duplicate entries. In order to use the Read_Table function in your project, copy the following code into your macro.

Function Read_Table(XLS_Sheet As String, rs As Integer, cs As Integer, re As Integer, ce As Integer, ri As Boolean, ci As Boolean) As Table
    'Support function for reading excel sheets into VBA table objects

    'XLS_Sheet = sheet name of excel sheet
    'rs and cs = first row(rs)/first column (cs) to be used in sheet
    're and ce = last row(re)/last column (ce) to be used in sheet. re = 0 or ce = 0 results in all rows/columns of sheet to be used
    'ri and ci = settings, whether first row/column should be used as an index in table object

    Dim VBA_Table As New Table
    Dim s As Sheet
    s = Parameters.Sheets(XLS_Sheet)
    Dim ro As Integer, co As Integer   'Offset für Zeilen-/Spaltenindex

    s.BufferEnabled = True
    Dim r,c As Integer
    If re = 0 Then
        re = s.UsedRows
    End If
    If ce = 0 Then
        ce = s.UsedColumns
    End If
    If ri Then
        'Switch row index on and set offset for first column = 0
        VBA_Table.RowIndex = True
        co = 0
    Else
        'Set offset for first column = 0
        co = 1
    End If
    If ci Then
        'Switch column index on and set offset for first row = 0
        VBA_Table.ColumnIndex = True
        ro = 0
    Else
        'Set offset for first row = 0
        ro = 1
    End If

    'pre dimension the table-object to reduce memory usage for large tables
    VBA_Table.ColumnCount = ce-cs
    VBA_Table.RowCount = re-rs

	'write data from sheet into Table
    For r = 0 To re-rs
        For c = 0 To ce-cs
            If r + ro = 0 Or c + co = 0 Then
                If CStr(s.CellYX(r+rs,c+cs)) <> "" Then
                    VBA_Table(r+ro,c+co) = CStr(s.CellYX(r+rs,c+cs))
                End If
            Else
                If IsDate(s.CellYX(r+rs,c+cs)) Then
                    VBA_Table(r+ro,c+co) = CStr(s.CellYX(r+rs,c+cs))
                Else
                    VBA_Table(r+ro,c+co) = s.CellYX(r+rs,c+cs)
                End If
            End If
        Next
    Next
    Read_Table = VBA_Table
End Function

Write_Table function

Table objects are also very useful to store and manage custom results during simulation. In the following example, the value of the Custom Attribute CIP Delay is aggregated per unit in a Table object.

t_results(cop.Unit.Name,1) += cop.OrderProcedure.CustomAttribute("CIP Delay")

To view the results stored in the Table object, they can be written to Excel at the end of the simulation run, usually within an End_Sim procedure. This could be done cell-wise, using the CellYX property. A current computer, though, would take approximately 1 s to write 1000 cells. With large amounts of data, long waiting times might result. Hence, in the second part of this tip, the Write_Table function is introduced, by which writing is performed approximately a hundred times faster than writing single cells.

This function expects as input parameters

VBA_Table (a Table object with results),
t_rs and t_cs (first row and first column of the Table object),
t_re and t_ce (last row and last column of the Table object, 0 for all),
XLS_Sheet (the string name of the Excel sheet),
s_rs and s_cs (first row and first column of the sheet to be written).

For example, in the End_Sim procedure use the code:

Write_Table(t_results,0,0,0,0,"Results",1,1)

By this code, the complete Table object is written into the sheet t_results. Please keep in mind that the index row or the index column has the number 0. For reading out the row index, 0 is the first row. With t_re = 0, all available rows are selected (analogously for ce). You can of course also write the Table object to Excel without indices and only the columns 3-8 without overwriting the columns 1-3 of the sheets with:

Write_Table(t_results,1,3,0,8,"Results",1,4)

To use the Write_Table function, the following code has to be copied into your macro:

Sub Write_Table(VBA_Table As Table, t_rs As Long, t_cs As Long, t_re As Long, t_ce As Long, XLS_Sheet As String, s_rs As Long, s_cs As Long) ', ri As Boolean, ci As Boolean)
    'Support function for writing VBA table objects into an Excel Sheet using RangeYX method

	'VBA_Table = VBA table object 
	't_rs and t_cs = first row(rs)/first column (cs) of table object. Use t_rs = 0 and t_cs = 0 to include the index
	't_re and t_ce = last row(re)/last column (ce) of table object. t_re = 0 or t_ce = 0 results in all rows/columns of sheet to be used 
	'XLS_Sheet = sheet name of excel sheet as string
	's_rs and t_cs = first row/first column of the excel sheet to be used

	'set re, ce
    Dim r As Long, c As Long
    If t_re = 0 Then
        t_re = VBA_Table.RowCount
    End If
    If t_ce = 0 Then
        t_ce = VBA_Table.ColumnCount
    End If

    'set array dimensions
	Dim a(,) As Object
	ReDim a(t_re-t_rs,t_ce-t_cs)

	'writing into array
    For r = t_rs To t_re
        For c = t_cs To t_ce
			If Not IsDBNull(VBA_Table(r,c)) Then
				a(r-t_rs,c-t_cs) = VBA_Table(r,c)
			End If
        Next
    Next

	'writing array to sheet
    Dim s As Sheet
    s = Parameters.Sheets(XLS_Sheet)

	'(first row, first column, first row + number of rows, first column + number of columns)
	s.RangeYX(s_rs, s_cs, s_rs+t_re-t_rs, s_cs+t_ce-t_cs) = a()

End Sub

Demo project

In the Downloads area, there is a demo project with two experiments: General Understanding and Process Example.

Experiment General Understanding

In this experiment, in the Simulation_Init procedure an Excel sheet with 10000 values by the Read_Table Function is transferred into the Table object t_input. During the simulation, the Table object t_results is created and, in the End_Sim procedure, by use of the Write_Table function it is written into the Excel sheet Results. For comparison, t_results is also written cell-wise into Excel sheet Results cell-wise, and the particularly needed calculation time is written to the console.

Experiment Process Example

In this experiment, you will find the three dryers from our first example in this Tip & Trick. The three dryers perform a simple recipe, which consist only of a drying and a cleaning operation. There are 3 dryers with different drying durations and different cleaning demand (after 2-5 batches).

The drying duration in the parameter control Drying_Duration is read out of the Table object t_process and is assigned to the operation (see example above).

In the link condition Dryer_Clean, the Custom Attribute BatchCounter is compared with the value CleanAfterXBatches from t_process to be able to decide if the dryer has to be cleaned. When cleaning is processed, the value in cell t_process([dryer name], CleanedXTimes is counted up by 1.

Sub Dryer_Clean(cond As OrderLinkCondition)
    cond.OrderOperationFrom.Unit.CustomAttributes("BatchCounter") = cond.OrderOperationFrom.Unit.CustomAttributes("BatchCounter") + 1

    If cond.OrderOperationFrom.Unit.CustomAttributes("BatchCounter") mod t_process(cond.OrderOperationFrom.Unit.Name, "CleanAfterXBatches") = 0 Then
        cond.Value = True
        t_process(cond.OrderOperationFrom.Unit.Name, "CleanedXTimes") = t_process(cond.OrderOperationFrom.Unit.Name, "CleanedXTimes") + 1
    End If
End Sub

In the End_Sim procedure, with the Write_Table function the column 3 (CleanedXTimes) is written back to the Excel sheet.

Write_Table(t_process,1,3,0,3,"Example Process Parameters",2,4)

The resulting Gantt chart shows different drying durations for the different dryers. One can also notice the different cleaning frequency (green bars) for the three dryers.

In the Downloads area, you can find the demo project and the Read_Table and Write_Table functions as text documents.

Downloads

(For registered INOSIM Users only)

Example Project
VBA Function Read_Table
VBA Function Write_Table

More Tips & Tricks

23. February 2021

Applying Table Objects

Introduction

Read_Table function

Write_Table function

Demo project

Experiment General Understanding

Experiment Process Example

More Questions?

More Tips & Tricks

INOSIM Remote Control via the COM-Interface

Applying Table Objects

Custom Curves and Properties Of Curves in INOSIM Gantt