Back Issues Learning Zone Cafe Templates Components Training Register
	- 2003 Training Schedule - Onsite Training - One to One Training

Often when we build simulations for clients we create an Excel interface. On the interface we include parameters we're likely to want to change, for example staff availability or cycle times and key results such as staff utilization. We build these interfaces for many reasons:

- To make it easy to experiment with the simulation
- To allow the client to experiment without having to learn SIMUL8
- To document the simulation

Of course the same interface could be built in SIMUL8's internal spreadsheets. They have most of the power of Excel. However Excel is a familiar environment to most people so they feel more comfortable with it. Also it means the Excel spreadsheet can be passed to others who don't own SIMUL8. They can populate the Excel file with their data and the SIMUL8 license holder can run their scenario.

How to Create an Excel Interface

Creating an Excel Interface requires Visual Logic. The code is pretty simple though as the example below will show. Click here to download the simulation discussed in this example.

Step 1: Getting the information from Excel

We want to input the staff availability pattern below from Excel into SIMUL8. Most people think this will be the hard part, its actually the easiest. We only need one command to do this, 'Get From Excel'.

The first parameter of the command holds the SIMUL8 variable the information is to be written to in this case SS Staff Feature Support. As our variable is a spreadsheet the reference [1,1] tells SIMUL8 which cell to start writing the information in.

The second parameter holds the names of the Excel file, Data.XLS, and the sheet where the information is located, Inputs. The Excel spreadsheet must be saved in the same location as the simulation file.

The remaining parameters tell SIMUL8 which Excel cell to start copying from and how many rows and columns of data to copy.

We try never to read information in from Excel during a run, this would really slow down the simulation because Excel is slow. Generally we read in all the information we need in 'On Reset' logic as this way the information will be updated just before a run.

Step 2: Using the information from Excel

We want to change the staff level each hour so we place our visual logic code in the Time Check section. Time Check logic is repeated on a fix timed interval. We look at our spreadsheet where we stored our data from Excel to set the maximum number of resources available. We use variables to represent the row and column numbers. We then increment these so that the next time the visual logic is run the next cell down is read.

Step 3: Sending information Back to Excel

Often you might want to report the results of your simulation back to Excel. This is done in two steps. We first store the result we wish to report in a variable using the Get Result command. We then use the Set in Excel command to send the information to Excel.

The first parameter of the Set in Excel command tells SIMUL8 what information is to be written to Excel, in this case var temp 1. The second parameter holds the names of the Excel file, Data.XLS, and the sheet where the information is located, Outputs. The remaining parameters tell SIMUL8 where in Excel to write this information to and how many rows and columns of data to copy. (You only need the last 2 if you're copying information from a spreadsheet otherwise just enter 1 for both.)

Tips

- If a lot of data has to be included in the Excel Interface, have a summary sheet that lists the main inputs and outputs clients are likely to want to examine

- If the structure of the sheets changes as the simulation evolves rather than change all your visual logic references you can create another sheet which is nicely formatted and has direct links to the old sheets, which you can hide.

- Lock cells so people don't think they can move them about!

- Save different scenarios in different Excel files

- Your Excel spreadsheet must be opened to use it with the simulation. If you've forgotten to open it you'll get the error message "EXCEL is not responding to a conversation request".

Download the Simulation

Download this example simulation to see this in action. The example also shows other types of information you could read in from Excel and how to use it in SIMUL8.

If you have any questions generated from, or comments about this newsletter then email