Using SolverStudio

We assume you already know something about optimization modelling. If that’s not the case, please visit the NEOS Guide. The SolverStudio language-specific help pages (see below) may also be helpful.

Example Files: We strongly recommend that new users start by opening one of the example files provided. You can find these under the Data tab, SolverStudio: Show Model, File, View SolverStudio Examples. After switching to a tab with a model on it, you can choose Data, SolverStudio : Edit Data to see how data items are defined to exchange data between the spreadsheet and the model.

Building your first Model
The key steps in building a SolverStudio model are as follows.

Data Setup using Edit Data
Set up the problem data for your problem on a spreadsheet. You can have 3 types of data:
1/ A list of items, such as a list of warehouses, or a list of bars, or a list of arcs each identified by a from and a to node. In PuLP, this will appear as a list of values, a list of values, or a list of (to,from) tuples respectively.
2/ A list of indexed items, such as the supply at each warehouse. This will be a dictionary in PuLP of values. Dictionaries can contain values or tuples, and can be indexed by values or tuples.
3/ A table, such as the distance from each warehouse to each bar. This table has a row index, eg a warehouse, and a column index, eg a bar.
Your data items can be created using SolverStudio’s Edit Data button.

Model Creation using Show Model
Once you have set up these items on the spreadsheet, you use SolverStudio’s Show Model button to show the model editor and associated menus. You need to use the Language menu to choose what language you wish to use (PuLP, AMPL, …), and then write your model using this language. All the data items created in the data setup step automatically become available within the model.

Solving using Solve Model
You can run and solve your model using SolverStudio’s Solve Model button. We suggest you first click the Show Model button so you can see any output written to the screen by the model.

Simply saving your spreadsheet will save your model and data items. You will need SolverStudio installed to see these when you next open the spreadsheet.

We strongly suggest you start by looking at the models included in the example workbooks in the SolverStudio download. Choosing File… View SolverStudio Examples will open the folder of example workbooks.

Language Resources
SolverStudio and PuLP
SolverStudio and Gurobi
SolverStudio and AMPL
SolverStudio and AMPL using NEOS

Debugging and Temporary Files
SolverStudio creates temporary files in its working directory. These are often useful to check for debugging. You can view these files by choosing SolverStudio’s File menu, selecting View Working Files, and opening a file of interest with a text editor. Some of these files include:
model.txt – the model as entered by the user
ampl.mod – the AMPL model as modified by SolverStudio ready to send to NEOS
SheetData.dat – the AMPL data file (also viewable directly from the AMPL menu)
Sheet – the results file created by running AMPL (or AMPL on NEOS)
NEOSJob.xml – the XML file sent to NEOS with the model and data

Note that these files are deleted when you quit Excel.

SolverStudio and Python
If you are building a model in Python (as opposed to using AMPL, for example), then you can use Python to directly access the standard Excel objects. These are available thru the SolverStudio.Application object (which is just called Application when using the “Pulp (IronPython)” language). Some examples are:
Application.ActiveSheet.Range(“A1″).Value = 10
r.Cells(1,1).Value = 10

SolverStudio and VBA
You can use the following VBA code to run SolverStudio; many thanks to ‘xpto’ for this code. Kyle also noted that: You need to include an accessibility module in order for the code to work. See this site for the code, and that got it working for me. I think that could definitely be clarified though on the usage page. Thanks, Kyle.

On Error Resume Next
Application.EnableEvents = True
Dim RibbonPropPage As IAccessible
Set RibbonPropPage = GetAccessible(CommandBars(“Ribbon”), _
37, _
RibbonPropPage.accDoDefaultAction CHILDID_SELF
Application.Wait DateAdd(“s”, 1, Now)
Set RibbonPropPage = GetAccessible(CommandBars(“Ribbon”), _
43, _
“Solve Model”)
RibbonPropPage.accDoDefaultAction CHILDID_SELF
Application.Calculation = xlCalculationAutomatic

If used in a loop, this code needs to be extended with a DoEvents call to actually make SolverStudio react to the Solve click. See “VBA Example.xlsm” in the SolverStudio examples folder (as of an approx Feb 2016 release).