Known Problems in Microsoft Excel Solver

[Dividing Line Image]

This page lists all of the bugs we know about in the standard Solver shipped with Microsoft Excel 5.0 and 7.0. Except for the international settings problems noted below, all of these problems are fixed in the Solver shipped with Excel 97. Of course, these problems are also fixed in the most recent versions of Frontline's enhanced solvers. Even with the standard Solver, you are unlikely to encounter any of these problems, and you can easily work around them.

Not listed here are cases where the Solver is not as robust as we would like on poorly formulated models. The most common problems of this type involve poorly scaled models (see the discussion of Assume Linear Model and Use Automatic Scaling) and premature stopping by the nonlinear GRG Solver on problems where the objective is changing slowly (see GRG Stopping Conditions).

If you are using Microsoft Excel under Windows 3.x, make sure that you have Excel Version 5.0c or later (select menu choice Help About Microsoft Excel...). A few bugs in the Excel 5.0 Solver were fixed as of Version 5.0c, and hence are not listed here. WE URGE YOU TO UPGRADE TO EXCEL 97 AS SOON AS POSSIBLE, since we won't support the Excel 5.0 Solver for much longer.

If you believe you have found an "actual bug" not covered here, please let us know by email to info@frontsys.com. If we verify that you have found a new bug, we'll send you a free copy of our $495 Premium Solver! Even if you haven't found a bug, but you have comments or complaints (or compliments!) about the way the Solver works, we'd like to hear from you.

Solution Values

Incorrect solution on linear problems if Precision is too large

If the Precision value in the Solver Options dialog is set to a very large value, such as 0.01 (versus the default value of 1E-6) and the Assume Linear Model box is checked, the Solver will sometimes return a suboptimal set of values for the decision variables. Avoid this by making Precision about 1E-4 (0.0001) or smaller.

Solver Reports

Problems with Slack values in the Answer Report

The values listed in the Slack column in the Answer Report are incorrect in two cases: (i) when a constraint right hand side is a cell reference whose value depends on the decision variables, and (ii) when the Solver problem involves integer constraints. In the first case, you can obtain correct slack values by simply changing the cell reference in the Constraint edit box to a formula: For example, change A1 to 0+A1. Slack values in the report for mixed-integer problems should be ignored.

Reduced Gradients in Sensitivity Report if Central Differencing

If the Derivatives option in the Solver Options dialog is set to Central and the nonlinear GRG Solver is used, the Reduced Gradient values shown in the Sensitivity report may be incorrect. To avoid this problem, set the Derivatives option to Forward instead of Central.

Solver Interactive Use

Constraints referencing deleted rows/columns cause problems

If you delete entire rows and/or columns containing cells referenced in your constraints, the symbol #REF! appears in lieu of the deleted references in the Constraint List Box. After this occurs, in certain Excel versions from 5.0 to 7.0, using either the Change, Delete or Reset All buttons may yield the message "Solver: An unexpected internal error occurred, or available memory was exhausted." If none of these buttons will remove the #REF! constraint, choose Insert Name Define..., enter solver_num for the name, 0 in the "Refers to:" box, and click OK; then try the Solver again.

Defined names which are deleted may remain in the Constraint List Box

If you define a name with Insert Name Define..., refer to the name on the right hand side of a constraint, and then delete the defined name, the name will still appear in the Constraint List Box, and solving will yield the error message "Solver encountered an error value in a target or constraint cell." You must change the constraint manually so that it references the cells themselves instead of the deleted name.

Save Model to a different sheet cannot be reloaded with Load Model

If you use the Save Model... button in the Solver Options dialog to save a Solver problem to an area on a different sheet, cell references will be saved as (for example) Sheet1!A1. Such references cannot be loaded back into the Solver through use of the Load Model... button. To avoid this problem, save to an area on the same sheet as the Solver model.

Solver Macro Interface

Callback argument of SolverSolve() not available

The second argument of SolverSolve(), which is a reference to a function to be called on each Solver iteration (if Show Iteration Results is enabled), cannot be used from VBA. It can be used with SOLVER.SOLVE() in the Excel macro language. If you need to use the callback function, you can write it in the macro language and write the rest of your application in VBA.

SolverGet() on a sheet with no options defined

On a worksheet where Solver options have never been defined (either through the SolverOptions() function or clicking OK in the Solver Options dialog), calling the SolverGet() function with the typenum argument in the range 9 to 18 will return #VALUE! instead of the default values of the relevant Solver options. To avoid this problem, call SolverOptions() first.

SolverLoad() on a sheet with no Solver model

Calling SolverLoad() as the first Solver function on a worksheet where no Solver model has been defined will yield the error message "Solver: An unexpected internal error occurred, or available memory was exhausted." To avoid this problem, call SolverOk() before calling SolverLoad().

International Versions

Solver doesn't record macros with international decimal separators

In countries where the decimal separator is not a period, if Tools Record Macro... is used with the Solver and constraints are defined with decimal separators in their right hand sides, the recorded macro will use a period for the separator. This will not re-define the constraint when the macro is played back. This problem also arises in the U.S. English version of Excel 97 when used with international settings. It is corrected in the international versions of Excel 97.

Multiple selections for Changing Cells aren't accepted by U.S. English Excel 97 with international settings

In countries where the argument separator is not a comma, the U.S. English version of Excel 97 won't accept multiple selections (containing either commas or the country-specific separator) in the Changing Cells edit box of the Solver Parameters dialog. This problem (which was due to last-minute changes in Excel 97 Visual Basic beyond our control) is corrected in the international versions of Excel 97. If you are affected by this problem, you can download an updated version of the Solver add-in on our Private Web for Solver Users (you can sign up for free).