Blog

Support for dynamic named ranges

One of the features that we have enjoyed in SolverStudio is support for dynamic ranges, i.e. named ranges that are created using formulae. OpenSolver 2.8 and later now supports this, and so you can use formulae to define ‘dynamic’ named ranges that are then used for the left hand side and right hand side of constraints, for the decision cells and for the objective. So for example, instead of having a constraint left hand side of A1:A4, you can defined a named range Constraint1LHS with a formula such as “=offset($A$1, 0, 0, COUNTA($A:$A),1)”. This range is dynamic in that it automatically expands as more values are entered into the A column. You can then set up a constraint in which you enter Constraint1LHS for the left hand side range. This means your model automatically works as the user changes the data on the sheet. For more information on dynamic range, see excel-easy.com or ozgrid.com. We hope you find this useful.

OpenSolver 2.8.2 Pre-release

We have just uploaded OpenSolver 2.8.2, available on our SourceForge site here.

This is a second attempt to work around the “Automation Error” bug that some users are seeing on some versions of Excel. (This appears to be caused by a VBA bug that is corrupting the cached code. We have now “cleaned” the code)

As always, please send us any feedback and let us know if you have any problems.

OpenSolver 2.8.1 Pre-release

We have just uploaded OpenSolver 2.8.1, available here.

This fixes two bugs with the 2.8.0 release. Some users may have encountered an “Automation Error” depending on their version of Excel, which we have now fixed. Additionally we have fixed an error that occurred when cancelling the “Set QuickSolve Parameters” dialog.

As always, please send us any feedback and let us know if you have any problems.

OpenSolver 2.8.0 Pre-release

We are happy today to announce the release of OpenSolver 2.8.0!

We will be releasing this first as a pre-release for wider testing before releasing it as stable in the near future. Anybody that has elected to receive update checks for experimental updates should get an update notification in the next day, otherwise the notification will come later once it is tested further (if you enabled the update checker).

You can download OpenSolver 2.8.0 Prerelease here.

This update contains a large number of bugfixes (the changelog lists over 30 major fixes!) as well as some new features. It fixes a lot of problems that users encountered in version 2.7.1. In particular, it fixes the compile error message “Method or data member not found” that some Mac users were encountering due to VBA becoming case-sensitive and treating Me.width and Me.Width differently. If you somehow still run into this message on the latest version, please let us know immediately so we can work with you to fix it.

There are a couple of changes to the way models are defined. First of all, it is now possible to define ranges in the objective, variables and constraint cells that are not on the same sheet as the model. Beforehand this was not consistently allowed, but now there should be no problems. NOTE: For the non-linear solvers Bonmin and Couenne that use our formula parser, the entire model needs to be on the same sheet due to limitations in the formula parser. This is something we want to fix as soon as possible.

The other big change to the way models are defined is that models can now be defined using named ranges. Now you can give names to ranges on the sheet and use these names when defining the model. For instance, if you have a named range “production” in A1:A4 and another named range “capacity” in B1:B4, instead of entering the constraint as “A1:A4 <= B1:B4”, you can now add it as “production <= capacity”. If the named range is changed to point to a different set of cells, the model will update and use the new set of cells when solving the problem. This change will allow for much more expressive power when creating models, and makes it easier to keep track of what each constraint means when defining the model.

We have also revamped the way we show the solver output when “Show optimisation progress while solving” is enabled. Previously we used a command prompt/terminal window to show the output from the solver, but now we do this inside Excel, along with an easy “Cancel” button to abort the solve. This is a much more stable way of doing things than before, and looks a lot nicer too!

The “About OpenSolver” form now indicates whether OpenSolver has been correctly installed, by which we mean all the files have been unzipped to the same place. If you are having trouble getting any solver to run, please make sure that this form says everything is correctly installed.

Finally, there are some minor changes to the API. We have removed the “GetObjectiveCellWithValidation” method, and have changed “GetObjectiveFunctionCell” to validate by default. A large number of the API methods have been altered to validate their inputs and outputs by default, which can be turned off. We have also added a host of new functions to go along with the ability to define the model using named ranges, which requires working with string arguments rather than ranges.

We appreciate any feedback you might have on the release, including the new features and any future features you might like. If you do encounter any problems, please either use the “Report Issue” button from inside OpenSolver, or leave a comment here so that we can address the problem.

INFORMS schedules conference volunteers using OpenSolver

http://meetings2.informs.org/wordpress/philadelphia/2015/11/04/optimal-student-volunteer-scheduling-for-the-2015-informs-annual-meeting/

“When we sat down to begin scheduling student volunteers for this year’s Annual Meeting, we recognized an opportunity to use OR methods to make the task more manageable. We realized that the problem consisted of scheduling 59 students across eight shifts (two shifts per day, four days), with each student required to work either one or two five-hour shifts (morning or afternoon). Since student volunteers are placed throughout the conference facilities, we decided to schedule students into the 74 “shift-location” combinations rather than simply assign a block of students into each shift.

A Doodle poll was set up to obtain student availability. Each student was asked to indicate five out of the eight shifts during which they were available. The availability data was translated into a cost matrix where the cost was zero if a student was available and a cost of 1,000,000 if a student was unavailable for a certain shift. The objective was to minimize the total cost of the assigned schedule, thereby creating a feasible schedule that did not assign any students to shift(s) for which they were unavailable.

The problem was set up and solved in MS Excel. The tool used was COIN-OR’s Open Solver add-in for MS Excel…”

New edition of Optimization Modeling with Spreadsheets by Kenneth Baker

Kenneth Baker is well known for his excellent introductory texts on optimisation modelling using spreadsheets. It is great to see that his latest edition of “Optimization Modeling with Spreadsheets” (ISBN: 978-1-118-93769-3, July 2015) now includes an online appendix on OpenSolver. I look forward to seeing a copy of this new edition when it arrives. Andrew

OpenSolver 2.7.1 (28 June 2015)

We have just released OpenSolver 2.7.1 which fixes a number of bugs in the 2.7.0 release. Please upgrade to this version if you are using 2.7.0 (or enable the update checker in OpenSolver to get automatically notified of these changes!).

Note: Unlike earlier versions of OpenSolver, we now correctly pass to the solvers (including CBC) the “Maximum number of iterations” value in the Options dialog. Be sure to set this (and the time limit) to a large value if want to find optimal solutions.

Great Maersk/OpenSolver presentation

I am on sabbatical at DTU in Denmark, and just enjoyed meeting Aleksandr from Maersk in Copenhagen who is doing some great work with OpenSolver (and perhaps SolverStudio in the future) to help make better decisions at one of the world’s largest shipping companies. Aleksandr presented some very sophisticated OpenSolver models, and is one of the few users I know of who uses the Quick Solve feature. (Any more of you out there?) Thanks, Aleksandr, for a great talk, and giving us some challenging models to think about as we plan for faster solve times in future releases. Andrew

OpenSolver 2.7.0 (16 June 2015)

Today we are happy to announce the release of OpenSolver 2.7.0, available for download here. The release fixes a large number of bugs and also introduces some great new features:

  • Update Checker: OpenSolver now includes an automatic update checker that will periodically check for updates. This is an opt-in feature that we hope will make it easier to keep up with the latest releases. As a side effect, we plan to move to more frequent releases so that new features and fixes are available sooner. More info on the Update Checker is available here.
  • VBA API: We have created a complete VBA interface to OpenSolver that allows full control of OpenSolver using VBA, much like Excel Solver’s API. We strongly recommend using these new functions to control OpenSolver instead of the Solver functions. The full list of API methods is here.
  • Error Reporting: We have revamped the error handling within OpenSolver to give us much more information when an error occurs. There is also an option to report issues from within Excel when an error occurs. This will email us directly with full information about the error, which will hopefully make it easier to resolve any issues you might encounter.
  • All solvers now support extra parameters being defined, and these parameters can now be set from the Options dialog. More information about using these parameters is here.
  • Highly experimental support has been added for the NOMAD solver on Mac. This feature is very new, so if you try this out please let us know how it goes.

We have also updated the included solvers to the following versions:

  • CBC 2.9.4
  • Bonmin 1.8.1
  • Couenne 0.5.3
  • NOMAD 3.7.2

As always, please let us know how you find the new version, and we appreciate any feedback you can offer.