Within Arena , programming support comes in two forms: laying down flow chart modules and computer language integration (e.g. VBA, C, etc.). This section presents some common programming issues that are helpful to understand when trying to get the most out of your models. First, we will examine how Arena stores the output from a simulation run. Then, the discussion involving input and output started in Chapter 4 will be continued. Finally, the use of Visual Basic for Applications (VBA) within the environment will be introduced.
Each time the simulation experiment is executed writes the statistical information collected during the run to a Microsoft Access database that has the same name as the model file. This database is what is used by the Crystal Reports report generator. You can also access this database and extract the statistical information yourself. This is useful if you need to post process the statistical information in a statistical package.
This section demonstrates how to extract information from this database. The intention is to make you aware of this database and to give you enough information so that you can use the database for some simple data extraction. There is detailed information concerning the Reports Database within the Help system. You should search under The Report Database. We will be reusing the LOTR example model from Chapter 4 within this section to illustrate these concepts.
When you create and run a model, e.g. yourmodel.doe, running the model will create a corresponding Microsoft Access file called yourmodel.mdb. Each time the model is run, the statistical information from the simulation is written to the database. If you change the input parameters to your model (without doing anything else), and then re-run the model, that model’s results are written to the database. Any previous data will be over written. There are two basic ways to save information from multiple simulation runs. The first is to simply rename the database file to a different name before rerunning the simulation. In this case, a new database file for the model will be created when it is run. This approach creates a new database for each simulation execution.
Figure D.17: Run Setup statistical collection options
The second approach is to use the Projects Parameters panel within the Run \(>\) Setup menu dialog as illustrated in Figure D.17. The Project Title field identifies a particular project. This field is used by the database to identify a given project’s results. If this name is not changed when the simulation is rerun then the results are overwritten for the named project. Thus, by changing this name before each experiment, the database will contain the information for each experiment accessible by this project name. Now let’s take a look at the type of information that is stored within the database.
The Arena Reports database consists of a number of tables and queries. A table is a set of records with each row in the table having a unique identifier called its primary key and a set of fields (columns) that hold information about the current record. A query is a database instruction for extracting particular information from a table or a set of related tables.
The help system describes in detail the structure of the database. For accessing statistical information there are a few key tables:
Holds the name of the statistical item from within the model, its type (e.g. DSTAT, TALLY, FREQ, etc.) and information about its collection and reporting.
The summary within replication results for a given statistic on a given replication
The ending value for a RECORD module designated as Count for a given replication.
The ending values for tabulated frequency statistics by replication
The value of a defined OUTPUT statistic for each replication.
The Definition table with the Statistic table can be used to access the replication information. Figure D.18 illustrates the fields within the Definition table. The important fields in the Definition table are ID, Name, and DefinitionTypeID. The ID is the primary key of the table, Name represents the name to appear on the report that was assigned within the model for the statistic, DefinitionTypeID indicates the type of statistical element. For the purposes of this section, the statistic types of interest are DSTAT (time-based), TALLY (observation-based), COUNTER (RECORD with count option), OUTPUT (captured at end of replication), and FREQUENCY (tabulates percentage of time spent in defined categories).
Figure D.18: Definition table field design
The Statistic table holds the statistical values for the within replication statistics as shown in Figure D.19. Notice that the Statistic table has a field called DefintitionID . This field is a database foreign key to the Definition table. With this field you can relate the two tables together and get the name and type of statistic.
Figure D.19: Statistic table field
Figure D.20: Accessing statistics information
To explore these tables, you will work with the database called DB-Stat-Example.mdb. This file was produced by running the LOTRExample.doe with 56 replications and renaming the created database file. If you have Microsoft Access then open the database and then open the table called Definition. Select the desired row, e.g. row 8, and use Insert \(>\) Subdatasheet to get the Insert Subdatasheet dialog as shown in Figure D.20. Then, select the Statistic table and press the OK button as shown in the figure.
Figure D.21: Replication statistics for make and inspect time
Figure D.21 shows the result of inserting the linked data sheet and selecting the “+" sign for the statistic named”Record Make and Inspect Time". You should right-click on the ReplicationID field and sort the subdatasheet by increasing replication number. From this table, you can readily assess the replication statistics. For example, the AvgObs column refers to the ending average across all the observations recorded during each replication. From this you can easily cut and paste any required information into a spreadsheet or a statistical package such as R. For those familiar with writing queries, this same information can be extracted by writing the query as shown in Figure D.22.
Figure D.22: Query to access replication statistics
To summarize the statistics across the replications, you can write a group by query as shown in Figure D.23.
Figure D.23: Query to summarize replication statistics
In the original LOTR Ring Maker, Inc. model, an OUTPUT statistic was defined to collect the time that the simulation ended. The OUTPUT statistic values collected at the end of each replication are saved in the Output table as shown in Figure D.24.
Figure D.24: Output table field design
To see the collected values for a specific defined statistic, you can again use the Definition table. Open up the Definition table and scroll down to the row 48 which is the “TimeToMakeOrderStat” statistic. Select the row and using the Insert \(>\) Subdatasheet dialog, insert the Output statistic table as the subsheet. You should see the subsheet as shown in Figure D.25. These same basic techniques can be used to examine information on the COUNTERS and FREQUENCY statistics that have been defined in the model. If you name each run with a different Project Name, then you can do queries across projects. Thus, after you have completed your simulation analysis you do not need to rerun your model, you can simply access the statistics that were collected from within the Reports database. If you are an experienced Microsoft Access user you can then form custom queries, reports, charts, etc. for your simulation results.
Figure D.25: Expanded data sheet view for OUTPUT statistics
As discussed in Chapter 2, Arena allows the user to directly read from or write to files within a model during a simulation run by using the READWRITE Module located on the Advanced Process panel. Using this module, the user can read from the keyboard, read from a file, write to the screen, or write to a file. When reading from or writing to a file, the user must define an File Name and an optionally a file format. The File Name is the internal name (within the model) for the external file defined by the operating system. The internal file name is defined with the FILE data module. Using the FILE data module the user can specify the name and path to the external file. The file format can be specified either in the FILE data module or in the READWRITE module to override the specification given in the FILE data module. The format can be free format, a valid C or FORTRAN format, WKS for Lotus spreadsheets, Microsoft Excel, Microsoft Access, and ActiveX Data Objects Access types. In order for the READWRITE module to operate, an entity must pass through the module. Thus, as demonstrated in Chapter 2, it is typical to create a logic entity at the appropriate time of the simulation to read from or write to a file. This section presents examples of the use of the READWRITE module. Then, the pharmacy model is extended to read parameters from a database and write statistical output to a database.
In this example, the SMART file, Smarts162.doe, is used to show how to read from a text file. Open up the file named Smarts162Revised.doe. Figure D.26 provides an overview of the model.
Figure D.26: Smarts 162 Model
In this model, entities arrive according to a Poisson process, the type of entity and thus the resulting path through the processing is determined via the values within the simdat.txt file. The first number in the file is the type (1 or 2). Then, the following two numbers are the station 1 and station 2 processing times, as shown in Figure D.27. In Figure D.28, the READWRITE module reads directly from the SIMDAT file using a free format. Each time a read occurs, the attributes ( myType , myStation1PT , myStation2PT ) are read in. The myType attribute is tested in the DECIDE module and the attributes myStation1PT and myStation2PT are used in the PROCESS modules.
Figure D.27: Sample processing times is simdat.txt
In Figure D.29, the end of file action specifies what to do with the entity when the end of the file is reached. The Error option can be used if an unexpected EOF condition occurs. The Dispose option may be used to dispose of the entity, close the file or ADO recordset and stop reading from the file. The Rewind option may be used so that every time you reach an EOF, you start reading the file or recordset again from Record 1. Finally, the Ignore option can be used if you expect an EOF and want to determine your own action (such as reading another file). With the Ignore option when the EOF is reached, all variables read within the READWRITE module will be set to 0. The READWRITE module can be followed with a DECIDE module to ensure that if all values are 0, the desired action is taken. The comment character is useful to embed lines within the file that are skipped. These lines can add columns headers, comments, etc. to the file for easier readability of the file. Suppose the comment character was a “;” then
; This would be a comment followed by a header comment ; Num Servers\ \ Arrival Rate 1 10The Initialize option indicates what should do at the beginning of a replication. The file can stay at the current position (Hold), be rewound to the beginning of the file (Rewind), or the file can be closed (Close).
The rest of the model is straightforward. In this case, the values from the file are read into the attributes of an entity. The values of an array can also be read in using this technique.
Figure D.28: READWRITE module for simdat.txt
Figure D.29: FILE module for Smarts162Revised.doe
Smarts file, Smarts164.doe, Figure D.30, shows how to read into a two-dimensional array. The CREATE module creates a single entity and the ASSIGN module initializes the array index. Then, iterative looping is performed using a DECIDE module as previously discussed in Chapter 4.
Figure D.30: Smarts164.doe reading in a 2-D array
In this particular example, the entity delays for 10 minutes before looping to read in the next values for the array. The assignments for the READWRITE module are show in Figure D.31. You can easily see how the use of two WHILE-ENDWHILE loops could allow for reading in the size of the array. You would first read in the number of rows and columns and then loop through each row/column combination.
Figure D.31: READWRITE assignments module using arrays
So far the examples have focused on text files and for simple models these will often suffice. For more user friendly input of the data to files, you can read from Excel files. Smarts file 185, Figure D.32, shows how to read in values from an Excel named range.
Figure D.32: Smarts185.doe reading from an Excel named range
In order to read or write to an Excel named range, the named range must already be defined within the spreadsheet. Open up the Excel file Smarts185.xls. Select cells C5:E6 and look in the upper left hand corner of the Excel input bar. You will see the named range. By selecting any cells within Excel you can name them by typing the name into the named range box as indicated in Figure D.33. You can organize your spreadsheet in any way that facilitates the understanding of your data input requirements and then name the cells required for input. The named ranges are then accessible through the FILE module.
Figure D.33: Checking the named range in Excel
In this example, the processing times are distributed according to a triangular distribution. The named spreadsheet cells hold the parameters of the triangular distribution (min, mode, max) for each of the two PROCESS modules. An EXPRESSION module was used to define expressions with the variables to be read in indicating the parameter values. The order quantities are how much the customer has ordered. The lower CREATE module creates 50 arriving customers, where the order quantity is read and then used in the processing times. In the use of named ranges, essentially the execution of each READWRITE module causes a new row to be read. Thus, as indicated in Figure D.32 there are two back to back READWRITE modules in the top level create logic to read in each of the rows associated with the processing times. In the bottom create logic, each new entity reads in a new row from the named range.
Figure D.34: Data sheet view of FILE module
After setting up the spreadsheet and defining the named ranges within Excel, you must then a file with the named range. This is accomplished through the use of the FILE module. Select the FILE module within Smarts185.doe. The FILE module, Figure D.34, is already defined for you, but the steps will be indicated here. In the spreadsheet data view, double-click to add a new row and fill in AccessType, Operating System File Name, End of File Action, and Initialize Option the same as in the previous row. Then, click on the define Recordsets row button.
Figure D.35: Defining the recordset for the FILE module
You will see a dialog box similar to Figure D.35. is smart enough to connect to Excel and to populate the Named Range text box with the named ranges that you defined for your spreadsheet. You need only select your desired named ranges and click on Add/Update. This will add the named range as a record set in the Recordsets in file area. You should add both named ranges as shown in Figure D.36. Once you select a named range you can also view the data in the range by selecting the View button. If you try this with the ProcessingTime named range, you will see a view of the data similar to that shown in Figure D.36. As you can see, it is very simple to define a named range and connect to it.
Figure D.36: Viewing the ProcessingTime named range
Now, you have to indicate how to use the named range within the READWRITE module. Open up the READWRITE module, see Figure D.37, for reading the processing time 1 parameters. After selecting read from file and the associated file name, will automatically recognize the file as a named range based Excel file. You can then choose the recordset that you want to read from and then the module works essentially as it did in our text file examples. You can also use these procedures to write to Excel named ranges and to Access using active data objects (ADO). See for example SMARTS files 189 and 190.
Figure D.37: READWRITE module using named range
Making the simulation file driven takes special planning on how to organize the model to take advantage of the data input. Using sets and arrays appropriately is often necessary when making a model file driven. Here are some ideas for using files:
This example uses the pharmacy model and augments it to allow the parameters of the simulation to be read in from a database. In addition, the simulation will write out statistical data at the end of each run. This example involves the use of Microsoft Access, if you do not have Access then just follow along with the text. In addition, the creation of the database show in Figure D.38 will not be discussed.
Figure D.38: PharmacyDB Access Database
The database has two tables: one to hold the inputs and one to hold the output across simulation replications. Each row in the table InputTable has three fields. The first field indicates the current replication, the second field indicates the mean time between arrivals for that experiment and the last field indicates the mean service time for the experiment. This example will only have a total of 3 replications; however, each replication is not going to be the same. At the beginning of each replication, the parameters for the replication will be read in and then the replication will be executed. At the end of the replication, some of the statistics related to the simulation will be written out to the table called OuputTable. For simplicity, this table also only has three columns. The first column is the replication number, the second column will hold the average waiting time in the queue and the third column will hold the half-width reported from .
Figure D.39: Read and write logic for Access example
Open up the PharmacyModelRWv1.doe file and examine the VARIABLE module. Notice that variables have been defined to hold the mean time between arrivals and the mean service time. Also, you should look at the CREATE and PROCESS modules to see how the variables are being used. The logic to read in the parameters at the beginning of each replication and to write the statistics at the end of the replication must now be implemented. The logic will be quite simple as indicated in Figure D.39. An entity should be created at time zero, read the parameters from the database, delay for the length of the simulation, and then write out the data.
You should lay down the modules indicated in Figure D.39. Now, the FILE module must be defined. The process for using Access as a data source is very similar to the way Excel named ranges operate. Figure D.40 shows the basic setup in the data sheet view. Opening up the recordset rows allows you to define the tables as recordsets. You should define the recordsets as indicated in the figure.
Figure D.40: Read and write logic for Access example
The two READWRITE modules are quite simple. In the first READWRITE module (Figure D.41), the variables RepNum , MTBA , and MST are assigned values from the file. This will occur at time zero and thereby set the parameters for the run. The second READWRITE module (Figure D.42) writes out the value of the RepNum and uses the functions TAVG() and THALF() to get the values of the statistics associated with the waiting time in queue.
Figure D.41: READWRITE assignments in first READWRITE module
Figure D.42: READWRITE assignments in second READWRITE module
Now, the setup of the replications must be considered. In this model, at the beginning of each replication the parameters will be read in. Since there are 3 rows in the database input table, the number of replications will be set to 3 so that all rows are read in. In addition, the run length is set to 1000 hours and the base time unit to minutes, as shown in Figure D.43.
Figure D.43: Run setup parameters for the database example
Only one final module is left to edit. Open up the DELAY module. See Figure D.44. The entity entering this module should delay for the length of the simulation. has a special purpose variable called TFIN which holds the length of the simulation in base time units. Thus, the entity should delay for TFIN units. Make sure that the units match the base time units specified in the Run Setup dialog.
Figure D.44: Delaying for the length of the replication
After the entity delays for TFIN time units, it enters the READWRITE module where it writes out the values of the statistics at that time. After running the model, you can open up the Microsoft Access database and view the OutputTable table. You should see the results for each of the three replications.
Figure D.45: Output within the Access database
Suppose now you wanted to replicate each run involving the parameter settings 3 times. All you would need to do would be to set up your Access input table as shown in Figure D.45 and change the number of replications to 9 in the Run Setup dialog.
Figure D.46: Making repeated replications
This same approach can be easily repeated for larger models. This allows you to specify a set of experiments, say according to an experimental design plan, execute the experiments and easily capture the output responses to a database. Then, you can use any of your favorite statistical analysis tools to analyze your experiments. I have found that for very large experiments where I want fine grained control over the inputs and outputs that the approach outlined here works quite nicely.
This section discusses the relationship between and Visual Basic for Applications (VBA). VBA is Microsoft’s macro language for applications built on top of the Windows operating system. As its name implies, VBA is based on the visual basic (VB) language. VBA is a full featured language that has all the aspects of modern computer languages include the ability to create objects with properties and methods. A full discussion of VBA is beyond the scope of this text. For an introduction to VBA for people interested in modeling, you might refer to (Albright 2001) .
The section assumes that the reader has some familiarity with VBA or is, at the very least, relatively computer language literate. This topic is relatively advanced and typical usage of often does not require the user to delve into VBA. The interaction between and VBA will be illustrated through a discussion of the VBA block and the use of Arena’s user defined function. Through VBA, also has the ability to create models (e.g. lay down modules, fill dialogs, etc) via Arena’s VBA automation model. This advanced topic is not discussed in this text, but extensive help is available on the topic via the on-line help system.
To understand the connection between and VBA, you must understand the user event oriented nature of VBA. Do not confuse the user interaction events discussed in this section with discrete events that occur within a simulation. Visual basic was developed as an augmentation of the BASIC computer language to facilitate the development of visual user interfaces. User interface interaction is inherently event driven. That is, the user causes various events to occur (e.g. move the mouse, clicks a button, etc.) that the user interface must handle. To build a program that interacts significantly with the user involves writing code to react to specific events. This paradigm is quite useful, and as VB became more accepted, the event-driven model of computing was expanded beyond that of directly interacting with the user. Additional non-user interaction events can be defined and can be called at specific places within the execution of the program. The programmer can then place code to be called at those specific times in order to affect the actions of the program. is integrated with VBA through a VBA event model.
There are a number of VBA events that are predefined within Arena’s VBA interaction model. The following key VBA events will be called automatically if an event routine is defined for these events in VBA within .
These events are called when the model is opened or saved. The SIMAN object is not available, but the object can be used. The SIMAN object will be discussed within some examples.
This event is called prior to the model being checked. When the model is checked, the underlying SIMAN code is compiled and translated to executable machine code. You can place code within the RunBegin event to add or change modules with VBA automation so that the changes get complied into the model. The SIMAN object is not available, but the object can be used. This event occurs automatically when the user invokes a simulation run. The object will not be discussed in this text, but plenty of help is available within the help system.
This event is called prior to starting the simulation (i.e. before the first replication). This is a perfect location to set data that will be used by the model during all replications. The SIMAN object is active when this event is fired and remains active until after RunEndSimulation fires. Because the simulation is executing, changes using the object via automation are not permissible.
This event is called prior to the start of each replication.
This event is called if the clear statistics option has been selected in run setup. This event occurs prior to each replication and at the designated warm up time if a warm up period has been supplied.
This event is called at the end of each replication. It represents a perfect location for capturing replication statistical data. It is only called if the replication completes naturally with no interruption from errors or the user.
This event is called after all replications are completed (i.e. after the last replication). It represents a perfect place to close files and get across replication statistical information.
RunPause, RunRestart, RunResume, RunStep, RunFastForward, RunBreak
These events occur when the user interacts with Arena’s run control (e.g. pauses the simulation via the pause button on the VCR control). These events are useful for prompting the user for input.
This event is called after RunEndSimulation and after the run has been ended. The SIMAN object is no longer active in this event.
OnKeystroke, OnFileRead, OnFileWrite, OnFileClose
These events are fired by the SIMAN runtime engine if the named event occurs.
The UserFunction event is fired when the UF function is used within the model. The UserRule event is fired when the UR function is called within the model.
This event is called if SIMAN encounters an error or warning. The modeler can use this event to trap SIMAN related errors.
A VBA event is defined, if a subroutine is written that corresponds to the VBA event naming convention within the ThisDocument module accessed through the VBA editor. In VBA, a file that holds code is called a module. This should not be confused with an module.
Let us take a look at how to write a VBA event for responding to the RunBegin event. The file, VBAEvents.doe that is available with this chapter, should be opened. The model is a simple CREATE, PROCESS, DISPOSE combination to create entities and have a model to illustrate VBA. The specifics of the model are not critical to the discussion here. In order to write a subroutine to handle the RunBegin event, you must use the VBA Editor. Within the environment use the Tools \(>\) Macro \(>\) Show Visual Basic Editor menu option (as shown in Figure D.47).
Figure D.47: Showing the Visual Basic editor
This will open the VBA Editor as shown in Figure D.48. If you double click on the ThisDocument item in the VBA projects tree as illustrated in Figure D.48, you will open up a VBA module that is specifically associated with the current model.
Figure D.48: Showing the Visual Basic editor
Figure D.49: Showing the VBA Events for Arena
A number of VBA events (Figure D.49) have already been defined for the model. Let’s insert an event routine to handle the RunBegin event. Place your cursor on a line within the ThisDocument module and go to the event drop down box called (General). Within this drop down box, select Model Logic. Now go to the adjacent drop down box that lists the available VBA events. Clicking on this drop down list will reveal all the possible VBA events that are available, as shown in Figure D.49. The event routines that have already been written are in bold. As can be seen the OnClearStatistics event is indicating that it has been written and this can be confirmed by looking at the code associated with the ThisDocument module. At the end of the list is the RunBegin event. Select the RunBegin event and an event routine will automatically be placed within the ThisDocument module at your current cursor location.
The event procedure has a very special naming convention so that it can be properly called when the VBA integration mechanism needs to call it during the execution of the model. The code will be very simple, it will just open up a message box that indicates that the RunBegin VBA event has occurred. The code to open up a message box when the event procedure fires is as follows:
Private Sub ModelLogic_RunBegin() MsgBox "RunBegin" End SubThe use of the MsgBox function in this example is just to illustrate that the subroutine is called at the proper time. You will quite naturally want to put more useful code within your VBA event subroutines.
Figure D.50: Message Box for RunBegin example
A number of similar VBA event subroutines have been defined with similar message boxes. Go back to the Environment and press the run button on the run control toolbar. As the model executes a series of message boxes will open up. After each message box appears, press okay and continue through the run. As you can see, the code in the VBA event routines is executed when fires the corresponding event. The use of the message box here is a bit annoying, but clearly indicates where in the sequence of the run that the event occurs.
The Smart files are a good source of examples related to VBA. The following models are located in the Smarts folder within your main folder (e.g., Program Files \(>\) Rockwell Software \(>\) Smarts).
The Smarts files 001, 024, 090, 099, and 109 are especially illuminating. In the next section, the UserFunction event and the VBA block are illustrated.
This section presents how to 1) use a user form to set the value of a variable, 2) call a user defined function which uses the value of an attribute to compute a quantity, and 3) use a VBA block to display information at a particular point in the model. Since the purpose of this example is to illustrate VBA, the model is a simple single server queuing system as illustrated in Figure D.51. The model consists of CREATE, ASSIGN, PROCESS, VBA, and DISPOSE modules.
Figure D.51: Simple VBA example model
The VBA block is found on the Blocks panel. To attach the Blocks panel, you can right-click in the Basic Process Panel area and select Attach, and then find the Blocks.tpo file. Now you are ready to lay down the modules as shown in Figure D.51. The information for each module is given as follows:
No editing is necessary for the VBA block and the DISPOSE module. If you have any difficulty completing these steps you can look at the module dialogues in the file called VBAExample.doe.
Now you are ready to enter the world of VBA. Using Alt-F11, open the VBA Editor. Double-click on the ThisDocument object and enter the code as shown in the following code. If you don’t want to retype this code, then you can access it in the file VBAExample.doe.
Option Explicit '' Declare global object variables to refer '' to the Model and SIMAN objects '' Public allows them to be accessed anywhere in this module '' or any other vba module Public gModelObj As Model Public gSIMANObj As SIMAN '' Variables can be accessed via their uniquely assigned '' symbol number. An integer variable is needed to hold the index '' It is declared public here so it can be used throughout this module '' and other vba modules Public vPTFactorIndex As Integer '' Index for the myPT attribute '' It is declared private here so it can be used throughout this module Private myPTIndex As IntegerLet’s walk through what this code means. The two public variables gModelObj and gSIMANObj are object reference variables of type Model and SIMAN respectively. These variables allow access to the properties and methods of these two objects once the object references have been set. The Model object is part of Arena’s VBA Object model and allows access to the features of the Model as a whole. The details of Arena’s Object model can be found within the help system by searching on Automation Programmer’s Reference. The SIMAN object is created after the simulation has been complied and essentially gives access to the underlying simulation engine. The variables vPTFactorIndex and myPTIndex will be used to index into SIMAN to access the variable vPTFactor and the attribute myPT.
This example will use VBA forms to get some input from the user and to also display some information. Thus, the forms to be used in the example need to be developed. Use Insert \(>\) UserForm to create two forms called Interact and UserForm1 as shown in Figure D.52.
Figure D.52: Building the Interact form
Use the show toolbox button to show the VBA controls toolbox. Then, you can select the desired control and place your cursor on the form at the desired location to complete the action. Build the forms as shown in the Figure D.52 and Figure D.53. To place the labels used in the forms, use the label control (right next to the textbox control on the Toolbox). The name of a form can be changed in the Misc \(>\) (Name) property as shown in Figure D.54. Now that the forms have been built, the controls on the forms can be referenced within other VBA modules.
Figure D.53: VBA UserForm1
Figure D.54: Properties Window
Before looking at the code for this situation, we need to understand how to interchange data between the model and the VBA code. This will be accomplished using the SIMAN object within the VBA environment. When a model is complied each element of the model is numbered. This is called the symbol number. The method, SymbolNumber(element name) , on the SIMAN object will return the index symbol number that can be used as an identifier for the named element. To find information on the SIMAN object search the help system on SIMAN Object (Automation). There are many properties and methods associated with the SIMAN Object. The documentation states the following concerning the SymbolNumber method.
SymbolNumber Method
Syntax SymbolNumber (symbolString As String, index1 As Long, index2 As Long) As Long
Description All defined simulation elements have a unique number. For those constructs that have names, this function may be used to return the number corresponding to the construct name. For example, many of the methods in the SIMAN Object require an argument like resourceNumber or variableNumber, etc. to identify the specific element. Since it is more common to know the name rather than the number of the item, SymbolNumber(“MyElementName”) is often used for the elementNumber type argument.
As indicated in the documentation, an important part of utilizing the SIMAN object’s other methods is to have access to an element’s unique symbol number. The symbol number is an integer and is then used as an index into other method calls to identify the element of interest. According to the help system the VariableArrayValue property can be used either to get or to set the value associated with the variable identified by the index number.
VariableArrayValue Read/Write Property
Syntax VariableArrayValue (variableNumber As Long) As Double
Description Gets/Sets the value of the variable, where variableNumber is the instance number of the variable as listed in the VARIABLES Element. For more information on working with variables in automation, please see making variable assignments.
Now we are ready to look at the code. The VBA code shows the RunBeginSimulation event. When the user executes the simulation, the RunBeginSimulation event is fired. The first two lines of the routine set the object references to the Model object and to the SIMAN object in order to store the values in the global variables that were previously defined for the VB module. The SIMAN object can then be accessed through the variable gSIMANObj to get the indexes of the attribute and variable myPT and vPTFactor. In the exhibit, after the indexes to the myPT and vPTFactor are found, the SIMAN object is used to access the VariableArrayValue property. In this code, the value of the variable is accessed and then assigned to the value of the TextBox1 control on the Interact form. Then, the Interact form is told to show itself and the focus is set to the text box, TextBox1, on the form for user entry.
Private Sub ModelLogic_RunBeginSimulation() '' set object references Set gModelObj = ThisDocument.Model Set gSIMANObj = ThisDocument.Model.SIMAN '' Get the index to the symbol number '' if the symbol does not exist there will be an error, '' There is no error handling in this example '' The SymbolNumber method of the SIMAN object returns '' the index associated with the named symbol '' get the index for the myPT attribute myPTIndex = gSIMANObj.SymbolNumber("myPT") '' get the index for the vPTFactor variable vPTFactorIndex = gSIMANObj.SymbolNumber("vPTFactor") '' set the current value of the textbox to the '' current value of the vPT variable in Arena '' The VariableArrayValue method of the SIMAN object '' returns the value of the variable associated with the index Interact.TextBox1.value = gSIMANObj.VariableArrayValue(vPTFactorIndex) '' Display the user form Interact.Show '' Set the focus to the textbox Interact.TextBox1.SetFocus '' The code for setting the variable's value is '' in the OK button user form code End SubWhen the Interact form is displayed, the text box will show the current value of the variable. The user can then enter a new value in the text box and press the OK button. Since the user interacted with the OK button, the button press event within the VBA form code can be used to change the value of the variable within to what was entered by the user.
To enter the code to react to the button press, go to the Interact form and select the OK button on the form. Right-click on the button and from the context menu, select View Code. This will open up the form’s module and create a VBA event to react to the button click.
The code provided next shows the use of the VariableArrayValue property to assign the value entered into the textbox to the vPTFactor variable. To access a public variable within the ThisDocument module, you precede the variable with ThisDocument . (e.g. ThisDocument.gSIMANObj ). Once the value of the text box has been assigned to the variable, the form is hidden and input focus is given back to the model via the ThisDocument.gModelObj.Activate method.
Private Sub CommandButton2_Click() '' when the user clicks the ok button '' Set the current value of vPT to the value '' currently in the textbox '' uses the global variable vPTFactorIndex '' defined in module ThisDocument '' uses the global variable for the SIMAN object '' defined in ThisDocument ThisDocument.gSIMANObj.VariableArrayValue(ThisDocument.vPTFactorIndex) = TextBox1.value '' hide the form Interact.hide '' Tell the model to resume running ThisDocument.gModelObj.Activate End SubSince the setting of the variable vPTFactor occurs as a result of the code initiated by the RunBeginSimulation event the value supplied by the user will be used for the entire run (unless changed again within the model or within VBA).
The model will now begin to create entities and execute as a normal model. Each entity that is created will go through the ASSIGN module and have its myPT attribute set to a randomly drawn U(10,20) number. Then the entity proceeds to the PROCESS module where it invokes the SEIZE, DELAY, and RELEASE logic. In this module, the processing time is specified by a user defined function via the UF(fid) function. The UF(fid) function will call associated VBA code from directly within an model.
You should think of the UF(fid) function as a mechanism by which you can easily call VBA functions. The fid argument is an integer that will be passed into VBA. This argument can be used to select from other user written functions as necessary. The following code exhibit shows the code for the UF function for this example.
'' This Function allows you to pass a user '' defined value back to the '' module which called upon the UF(functionID) function in Arena. '' Use the functionID to select the function that you want via '' the case statement, add additional functions as necessary '' The functions can obviously be named something '' more useful than UserFunctionX() '' The functions must return a double Private Function ModelLogic_UserFunction(ByVal entityID As Long, ByVal functionID As Long) As Double '' entityID is the active entity '' functionID is supplied when the user calls UF(functionID) Select Case functionID Case 1 ModelLogic_UserFunction = UserFunction1() Case 2 ModelLogic_UserFunction = UserFunction2() End Select End FunctionWhen you use the UF function, you must write your own code. To write the UF function, use the drop down box that lists the available VBA events on the ThisDocument module and select the UserFunction event. The UserFunction event routine that is created has two arguments. The first argument is an identifier that represents the current active entity and the second argument is what was supplied by the call to UF(fid) .
When you create the function it will not have any code. You should organize your code in a similar fashion. As can be seen in the exhibit, the supplied function identifier is used within a VBA select-case statement to select from other available user written functions.
By using this select-case construct, you can easily define a variety of your own functions and call whichever function you need from within the model by supplying the appropriate function identifier.
In order to implement the called functions, we need to understand how to access attributes associated with the active entity. This can be accomplished using two methods associated with the SIMAN object: ActiveEntity and AttributeValue. The help system describes the use of these methods as follows.
ActiveEntity Method
Syntax ActiveEntity () As Long
Description Returns the record location (the entity pointer) of the currently active entity, or 0 if there is not one. This is particularly useful in a VBA block Fire event to access attributes of the entity that entered the VBA block in the model.
AttributeValue Method
Syntax AttributeValue (entityLocation As Long, attributeNumber As Long, index1 As Long, index2 As Long) As Double
Description Returns the value of general-purpose attribute attributeNumber* with associated indices index1 and index2. The number of indices specified must match the number defined for the attribute.*
Let’s see how to put these methods into action within the user defined functions. The following code exhibit shows how to access the value of an attribute associated with the active entity.
Private Function UserFunction1() As Double '' each entity has a unique id Dim activeEntityID As Long '' get the number of the active entity '' this could have been passed from ModelLogic_UserFunction activeEntityID = gSIMANObj.ActiveEntity Dim PTvalue As Double '' get the value of the myPT attribute PTvalue = gSIMANObj.AttributeValue(activeEntityID, myPTIndex, 0, 0) Dim factor As Double factor = gSIMANObj.VariableArrayValue(vPTFactorIndex) '' this could be complicated function of the attribute/variables '' here it is very simple (and could have been done within Arena itself '' rather than VBA UserFunction1 = PTvalue * factor End FunctionFirst, the ActiveEntity property of the SIMAN object is used to get an identifier for the current active entity (i.e. the entity that entered the VBA block). Then, the AttributeValue method of the SIMAN object is used to get the value of the attribute.
Within Arena, attributes can be defined as multi-dimensional via the ATTRIBUTES module. Multi-dimensional attributes are not discussed in this text, but the AttributeValue method allows for this possibility. The two indices that can be supplied indicate to the SIMAN objecti how to access the attribute. In the example, these two values are zero, which indicates that this is not a multi-dimensional attribute. Once the values of the myPT attribute and the vPTFactor variable are retrieved from the SIMAN object, they are used to compute the value that is returned by the UF function. The variables factor and PTvalue are used to calculate the product of their values. Admittedly, this calculation can be more readily accomplished directly in without VBA, but the point is you can implement any complicated calculation using this architecture.
With the UF function, you can easily invoke VBA code from essentially any place within your model. The UF function is especially useful for returning a value back to the model. Using the VBA block, you can also invoke specific VBA code when the entity passes through the block. In this example, after the entity exits the PROCESS module, the entity enters a VBA block.
When you use a VBA block within your model, each VBA block is given a unique number. Then, within the ThisDocument module, an individual event routine can be created that is associated with each VBA block. In the example, the VBA block is used to open up a form and display some information about the entity when it reaches the VBA block. This might be useful to do when running an model in order to stop the model execution each time the entity goes through the VBA block to allow the user to interact with the form. However, most of the time the VBA block is used to execute complicated code that depends on the state of the system when the entity passes through the VBA block. The following exhibit shows the code for the VBA block event.
Private Sub VBA_Block_1_Fire() '' set the values of the textboxes UserForm1.TextBox1.value = gSIMANObj.VariableArrayValue(vPTFactorIndex) UserForm1.TextBox2.value = gSIMANObj.AttributeValue(gSIMANObj.ActiveEntity, myPTIndex, 0, 0) UserForm1.TextBox3.value = UserFunction1() '' Display the user form UserForm1.Show End SubBy now, this code should start to look familiar. The SIMAN object is used to access the values of the attribute and the variable that are of interest and then set them equal to the values for the textboxes that are being used on the form. Then, the form is shown. This brings up the form so that the user can see the values. In UserForm1, a command button was defined to close the form. The logic for hiding the form is shown in the next exhibit.
Private Sub CommandButton1_Click() '' hide the form UserForm1.hide '' Tell the model to resume running ThisDocument.gModelObj.Activate End SubThe show and hide functionality of VBA forms are used within this example so that new instances of the forms did not have to be created each time they are used. This keeps the form in memory so that the controls on the form can be readily accessed. This is not necessarily the best practice for managing the forms, but allows the discussion to be simplified. As long as you don’t have a large number of forms, this approach is reasonable. In addition, within the examples, there is no error catching logic. VBA has a very useful error catching mechanism and professional code should check for and catch errors.
The final example involves how to explicitly model dependence (correlation) within input distributions. In the fitting of input models, it was assumed and tested that the sample observations did not have correlation. But, what do you do if the data does have correlation? For example, let \(X_i\) be the service time of the \(i^\) customer. What if the \(X_i\) have significant correlation? That is, the service times are correlated. Arrival processes might also correlated. That is, the time between arrivals might be correlated. Research has shown, see (Patuwo, Disney, and Mcnickle 1993) and (Livny, Melamed, and Tsiolis 1993) , that ignoring the correlation when it is in fact present can lead to gross underestimation of the actual performance estimates for the system.
The discussion here is based on the Normal-to-Anything Transformation as discussed in Banks et al. (2005) , (Cario and Nelson 1998) , (Cario and Nelson 1996) , and (Biller and Nelson 2003) . Suppose you have a \(N(0,1)\) random variable, \(Z_i\) , and a way to compute the CDF, \(\Phi(z)\) , of the normal distribution. Then, based on the inverse transform technique, the random variable, \(\Phi(Z_i)\) will have a \(U(0,1)\) distribution. Suppose that you wanted to generate a random variable \(X_i\) with CDF \(F(x)\) , then you can use \(\Phi(Z_i)\) as the source of uniform random numbers in an inverse transform technique.
This transform is called the normal-to-anything (NORTA) transformation. It can be shown that even if the \(Z_i\) are correlated (and thus so are the \(\Phi(Z_i)\) ) then the \(X_i\) will have the correct CDF and will also be correlated. Unfortunately, the correlation is not directly preserved in the transformation so that if the \(Z_i\) have correlation \(\rho_z\) then the \(X_i\) will have \(\rho_x\) (not necessarily the same). Thus, in order to induce correlation in the \(X_i\) you must have a method to induce correlation in the \(Z_i\) .
One method to induce correlation in the \(Z_i\) is to generate the \(Z_i\) from an autoregressive time-series model of order 1, i.e. AR(1). An AR(1) model with \(N(0,1)\) marginal distributions has the following form:
\[Z_i = \phi Z_ + \varepsilon_i\]
where \(Z_1 \sim N(0,1)\) , with independent and identically normally distributed errors, \(\varepsilon_i \sim N(0,1-\phi^2)\) with \(-1<\phi<1\) for . \(i = 2,3,\ldots\) .
It is relatively straightforward to generate this process by first generating \(Z_1\) , then generating \(\varepsilon_i \sim N(0,1-\phi^2)\) and using \(Z_i\) = \(\phi Z_ + \varepsilon_i\) to compute the next \(Z_i\) . It can be shown that this AR(1) process will have lag-1 correlation:
Therefore, you can generate a random variable \(Z_i\) that has a desired correlation and through the NORTA transformation produce \(X_i\) that are correlated with the correlation being functionally related to \(\rho_z\) . By changing \(\phi\) , one can get the correlation for the \(X_i\) that is desired. Procedures for accomplishing this are given in the previously mentioned references. The spreadsheet NORTAExample.xls can also be used to perform this search process.
The implementation of this technique cannot be readily achieved in a general way within through the use of standard modules. In order to implement this method, you can make use of VBA. The model, NORTA-VBA.doe, shows how to implement the NORTA algorithm with a user defined function. Just as illustrated in the last section, a user defined function can be written as shown in the following code exhibit.
Private Function ModelLogic_UserFunction(ByVal entityID As Long, ByVal functionID As Long) As Double Select Case functionID Case 1 ModelLogic_UserFunction = CorrelatedUniform() Case 2 Dim u As Double u = CorrelatedUniform() Dim x As Double x = expoInvCDF(u, 2) ModelLogic_UserFunction = x End Select End Function When you uses UF(1) , a correlated U(0,1) random variable will be returned. If the user uses UF(2) , then a correlated exponential distribution with a mean of 2 will be returned. Notice that in generating the correlated exponential random variable, a correlated uniform is first generated and then used in the inverse transform method to transform the uniform to the proper distribution. Thus, provided that you have functions that implement the inverse transform for the desired distributions, you can generate correlated random variables.
The following code exhibit illustrates how the NORTA technique is used to generate the correlated uniform random variables. The variable, phi, is used to control the resulting correlation in the AR(1) process. The function, SampleNormal, associated with the SIMAN object is used to generate a \(N(0,1)\) random variable that represents the error in the AR(1) process.
Private Function CorrelatedUniform() As Double Dim phi As Double Dim e As Double Dim u As Double '' change phi inside this function to get a different correlation phi = 0.8 '' generate error e = gSimanObj.SampleNormal(0, 1 - (phi * phi), 1) '' compute next AR(1) Z mZ = phi * mZ + e '' use normal cdf to get uniform u = NORMDIST(mZ) CorrelatedUniform = u End Function Private Function expoInvCDF(u As Double, mean As Double) As Double expoInvCDF = -mean * Log(1 - u) End FunctionThe variable, mZ, has been defined at the VBA module level, and thus it retains its value between invocations of the CorrelatedUniform function. The variable, mZ, represents the AR(1) process. The current value of mZ is multiplied with phi and the error is added to obtain the next value of mZ. The initial value of mZ was determined by implementing the RunBeginReplication event within the ThisDocument module. Because of the NORTA transformation, mZ, is \(N(0,1)\) , and the function NORMDIST is used to compute the probability associated with the supplied z-value. The NORMDIST function (not shown here) is an implementation of the CDF for the standard normal distribution.
With minor changes, the code supplied in NORTA-VBA.doe can be easily adapted to generate other correlated random variables. In addition, the use of the UF function can be expanded to generate other distributions that does not have built in (e.g. binomial).
Arena is a very capable language, but occasionally you will need to access the full capabilities of a standard programming language. This section illustrated how to utilize VBA within the environment by either using the predefined automation events or by defining your own functions or events via the UF function and the VBA block. With these constructs you can make into a powerful tool for end users. There is one caveat with respect to VBA integration that must be mentioned. Since VBA is an interpreted language, its execution speed can be slower than compiled code. The use of VBA within as indicated within this section can increase the execution time of your models. If execution time is a key factor for your application, then you should consider using C/C++ rather than VBA for your advanced integration needs. allows access to the SIMAN runtime engine via C language functions. Essentially, you write your C functions and bundle them into a dynamic linked library which can be linked to . For more information on this topic, you should search the help system under Introduction to C Support.
Albright, S. C. 2001. VBA for Modelers, Developing Decision Support Systems with Microsoft Excel. Duxbury Thomson Learning.
Banks, J., J. Carson, B. Nelson, and D. Nicol. 2005. Discrete-Event System Simulation. 4th ed. Prentice Hall.
Biller, B., and B. L. Nelson. 2003. “Modeling and Generating Multivarate Time- Series Input Processes Using a Vector Autogressive Technique.” Assoc. Comput. Mach. Trans. Modeling and Comput. Simul. 13: 211–37.
Cario, M. C., and B. L. Nelson. 1996. “Autoregressive to Anything: Time Series Input Processes for Simulation.” Operations Research Letters 19: 51–58.
———. 1998. “Numerical Methods for Fitting and Simulating Autoregressive-to-Anything Processes.” INFORMS Journal of Computing 10: 72–81.
Livny, M., B. Melamed, and A. K. Tsiolis. 1993. “The Impact of Autocorrelation on Queueing Systems.” Management Science 39 (3): 322–39.
Patuwo, B. E., R. L. Disney, and D. C. Mcnickle. 1993. “The Effect of Correlated Arrivals on Queues.” IIE Transactions 25 (3): 105–10.
