Masters thesis: OpenSolver for scheduling physicians

Bernhard Aeschbacher (University of Zurich, Department of Business Administration) has just finished his Masters thesis on using OpenSolver for scheduling physicians. His thesis and Excel model are available here. It was great to see Bernard using OpenSolver’s on-sheet highlighting in his appendix to show how his model was built. Bernhard also gave me most useful feedback on a problem with non-English systems; OpenSolver 2.1 fixes this.

OpenSolver 2.1 (6 Sept 2012)

OpenSolver 2.1 is now released, and has a number of fixes and improvements. These include a newer version of the optimiser CBC, a bug fix for handling multiple cell blocks as decision cells on non-English systems, more robust non-linearity checking, and the ability to turn off this non-linearity checking if you know your model is linear. We now also display ≥ instead of > in our on-sheet constraints. All feedback is welcome.

Masters thesis using OpenSolver

Iain Dunning just sent me a link to Katherine Perry’s masters thesis on Call Center Scheduling Problem using Spreadsheet Optimization and VBA. (You will have to click past a broken certificate.) Katherine has been using OpenSolver to solve her call center problems. Her thesis includes an appendix giving step by step instructions on installing OpenSolver that might be useful for new users.

OpenSolver 1.9 (5 Dec 2011)

Fixed some display issues in the model dialog to give more compact displays, and force formulae RHS to be in absolute terms
Fixed a bug that saw models being wrongly built and then reported as non-linear (incorrectly) for some complex models if Excel calculation mode was set to manual.
Fixed passing of multiple parameters to CBC if the user defines a parameter table on the sheet; previously only the last parameter was passed.
Deleted the DLL’s downloaded in the .zip file which don’t seem to be needed now that we statically link everything in CBC.

OpenSolver 1.8 (5 Dec 2011)

Version 1.8 fixes a problem that prevented OpenSolver 1.7 loading on 64 bit systems, and improves handling of models that have decision variables and constraints on multiple sheets. Note that Excel does not allow ranges to contain arbitrary cells from many sheets, and so all the decision variables must be on the same sheet.


Mike Trick recently blogged about OpenSolver winning the COIN-OR 2011 Cup, and commented that OpenSolver was now available under the CPL. This was what we had on our web site as something we were doing, but I must confess the change from the GPL has not yet happened. At the moment, the GPL seems to meet everyone’s needs that we are aware of. However, to ensure this matter is clear, we hereby specifically give approval for OpenSolver to be distributed in its unaltered state as part of a larger piece of software, which is effectively saying that we do not consider calling OpenSolver routines from a third party add-in or VBA module in Excel to be linking in the sense of OpenSolver being a “subroutine library”. As long as OpenSolver continues to be a standalone Excel add-in, we are happy for it to be included and used in other applications.

Thanks Mike for your positive comments.

OpenSolver 1.7 (11 November 2011)

OpenSolver 1.7 has been released. (OpenSolver 1.6 was an internal release only.) Changes include:

Version 1.7 beta
Added controls in the About box to allow easy installation and uninstallation
Added code to interact nicely with the forthcoming OpenSolver Studio
Improved OpenSolver for use from VBA:
– Build and Solve operations now throw errors (instead of popping up dialogs), allowing dialog-free usage from VBA
– Return codes are better handled (and Solver compatible)
– A new optional parameter has been added to RunOpenSolver to avoid dialogs even if infeasible/unbounded solutions are generated

Version 1.6 beta
Fixed display and editting of an objective target value in the Model dialog.
Modified the Open Last Model in CBC functionality so that it passes any Solver options and any CBC solve parameters to CBC if they are available in any current worksheet
Fixed a minor issue in Model dialog where a RHS could be entered for a new constraint if the user had previously had a Bin or Int constraint selected
Fixed a redim bug in the quick non-linearity checker for models with no constraints (which can happen if there is only a target objective value)
Added “Show optimisation progress while solving”  (being Solver’s “Show Iteration Results”) to the OpenSolver options dialog
Improved operation of Options dialog, including proper sycnronisation of values when opened from the Model dialog
Better handling of the Excel 2010 “Simplex engine” option as used in parallel with “Assume linear model”
Fixed an error in the full non-linearity checker
Added output of dual prices onto the sheet; this is set using the Model dialog
Rearranged Model dialog to better fit new Duals option, and better use space around constraint listing
Better handling of the Excel solver options – OpenSolver now sets all these to sensible defaults
Better handling of users entering formulae in the Model dialog for a constraint RHS in terms of non-English localisation issues, but this still needs work
Fixed a size limitation in Quick Solve, and converted Quick Solve to sparse matrix handling for better memory usage.

OpenSolver 1.5 (9 August 2011)

We are pleased to announce the release of OpenSolver 1.5!

This is a bug-fix release:

  • Re-compiled CBC so that it does not have any external dependencies.
  • Fixed an error with cells formatted as currency or date.
  • Fixed some localisation-related issues with tolerance/ratio gap.

Thank you for submitting bug reports – they are very handy in solving problems. With so many different system configurations out there, it can be tricky to test even a fraction!

OpenSolver 1.4 (31 July 2011)

We are pleased to announce the release of OpenSolver 1.4!

For a full changelog, please see the document in the download.

  • Fixed 2003 menus – version 1.3 had some issues remaining.
  • Fixed some issues with and streamlined AutoModel
  • Fixed a ref-edit related focus bug in Model tool that was causing some strange behaviour.
  • Added partial locale support to Model. Entering number like 180,2 will work, but will display as 180.2
  • Added an Options window, available under Model in menu, and from a button on the Model form.
  • Added error catching in int/bin constraints on non-decision variable cells.
  • Updated CBC to version 2.7
  • Added custom icons for toolbar!

As always, please report any bugs via email or as comments.

OpenSolver 1.3 beta (7 July 2011)

We are pleased to announce the release of OpenSolver 1.3 beta!

Big features in this release are:

  • Excel 2003 menu support, thanks to Paul at Eclipse Engineering. Eclipse Engineering provide high-end CFD and FEA analysis along with a host of highly specialized CAE-based automation and integration solutions.
  • Support for 64-bit Excel 2010 has been added.
  • Removed last dependence on the built-in Solver: you can now build a model in the OpenSolver Model window!
A screenshot of the new Model tool
The new Model tool in OpenSolver 1.3

Bug reports are very welcome.

OpenSolver 1.2 is now the official stable version of OpenSolver.

Paul Jensen – Rest In Peace

I was saddened to read in the INFORMS news that Paul Jensen passed away last week. I met Paul at the 2010 Informs conference where he gave a great talk about his set of OR and MS teaching add-ins for Excel. (See also these supplements.) We also corresponded about OpenSolver. You can read about Paul’s contributions at his University of Textas site; his obituary is available here. Just as PuLP has been picked up and supported by Stuart Mitchell after its creator passed away, I hope that someone will pick up the maintenance of Paul’s Excel tools; they are a great online legacy which deserve a life beyond that of their creator.

OpenSolver speedups

We’ve worked hard in OpenSolver 1.1 to limit the number of reads from the spreadsheet as these are slow. Testing on a user-supplied airline problem with 70,000 variables and 76,000 constraints shows that we can build this model in 164 minutes on a 2.66GHz Core 2 Duo laptop; this used to take over 12 hours in the first version of our code! If we can parse the formulae, we can make it even faster…