We have been shortlisted! We are delighted to be nominated for 5 Tech for Good awards for our work with South African charity Shout It Now. Read more here.
VBA and Simul8 COM
This document details instructions on driving Simul8 through VBA. This assumes no previous knowledge of COM or VBA but does assume some understanding of the use of Excel. The example used here is included with the software and can be found in the Simul8 C Drive folder in the Example>API>COM>Standard section as example1.xls but we recommend you build it yourself because there are key steps to learn in checking boxes in VBA.
In this example we use a standard S8 file, and we assume you have installed Simul8 in the default directory. However, XML files can be used, with the advantage that their content can be changed under program control (by reading and writing to the file. E.g. to change the value of a variable).
Initial Set up
- Open Excel with a blank spreadsheet.
- Ensure that the Development Tab is added to your Ribbon. You can do this through File> Options> Customise Ribbon and check “Developer” on the Main Tabs Section.
- Ensure that macros are enabled. You can do this through File> Options> Trust Center> Trust Center Settings> Macro Settings and select “Enable all Macros”
- From the Insert button drop-down, in the Controls section, select an ActiveX Controls Button and drag a rectangle on the spreadsheet to create a Button. Don't cover column A with your button because we will use it later.
- Double click the new button and you will see VBA open and place some empty code on the screen.
- In the menu on the VBA window click Tools>References.
- Find and check Simul8 Library.
- Click OK. Excel's VBA can now help and prompt you to get the syntax right when using Simul8's COM/ActiveX interface.
Example commands in VBA
In the code window move the cursor up above the Private Sub line and type:
Private WithEvents MYSimul8 As Simul8.S8Simulation
Let’s add some code to our button to open up Simul8.
- Inside the code for the button click type:
Set MYSimul8 = GetObject("", "Simul8.S8Simulation")
Note that the Set keyword is NOT optional here (in most VBA it is optional - but NOT here!)
- ALT-TAB to the main part of EXCEL.
- Save the Excel file as an .xlsm.
- Close the Excel file
- Click the button and Simul8 will start. DO NOT attempt to close Simul8 from the Simul8 Screen (Because it is not in control, Excel is)
- ALT-TAB back to Excel
- Use Tools>Macros>Visual Basic Editor to redisplay the VBA screen and click the RESET button. This stops your macros and closes Simul8 (we will next create a button to do this neatly.)
- Navigate back to the Developer tab in Excel, and use the Insert button again to put a second button on the screen, double click it and enter this code:
Private Sub CommandButton2_Click() MYSimul8.Open "c:\program files\simul8\examples\others\demo2.s8" MYSimul8.RunSim 2400 End Sub
- Put a 3rd button on the screen with this code, to close down Simul8:
Private Sub CommandButton3_Click() Set MYSimul8 = Nothing End Sub
Again, the SET keyword must be used.
- Switch out of Design Mode so you can use the buttons
- Click each button in turn to open Simul8, open and run a simulation, and finally close Simul8.
- Next we will add an 'Event' to your VBA so you know when the simulation run has finished and you can display some results
- Go back to the VBA screen and select MYSimul8 in the left had drop down box.
- In the right drop down box select S8SimulationEndRun
- This code will appear:
Private Sub MYSimul8_S8SimulationEndRun()
- Add these 3 extra lines between Private Sub MYSimul8_S8SimulationEndRun() and End Sub
Private Sub MYSimul8_S8SimulationEndRun() For n = 1 To MYSimul8.ResultsCount Worksheets("Sheet1").Cells(n, 1).Value = MYSimul8.Results(n).Value Next n End Sub
At the end of the run Simul8 will tell Excel the run has finished. Excel them loops through all the results on the KPI Summary and adds the results into the cells in column 1 on sheet 1.
- Go back to Excel, click button 1, then 2, then wait for Simul8 to finish the run. The results will appear once the run is complete.
- Now click button 3 to close Simul8.
- Try out the other methods and properties listed in the Simul8 COM Object simulation reference over the page.
Finally, one helpful hint
If you don't want to see Simul8 running put the line: MYSimul8.Visible = False after the Set MYSimul8 = GetObject(””, “Simul8.S8Simulation”) line