Excel Solver VBA: Set Up and Run Optimization Models

VBA & macros · Updated November 21, 2026

Yes—Excel Solver can be controlled with VBA to set an objective, choose changing cells, add constraints, and run an optimization automatically. In Microsoft 365 for Windows, enable the Solver add-in first, then call its Solver procedures from a macro; the example below builds and solves a small linear product-mix model.

What Solver does and when VBA helps

Solver is an Excel add-in for finding values that optimize a formula subject to limits. You specify an objective cell to maximize, minimize, or set to a target value; one or more variable cells Solver may change; and constraints those cells or formulas must obey. A common use is deciding how much to make of each product when profit, labor, materials, or capacity are limited.

VBA is useful when a model must be solved repeatedly, when inputs change on a schedule, or when a workbook needs a one-click workflow. A macro can reset the previous Solver setup, apply the current model’s objective and constraints, run Solver, and respond to the outcome. VBA does not replace the model: the worksheet still needs correct formulas, meaningful input data, and a suitable Solver method.

Solver is not a general-purpose way to make any spreadsheet formula produce a desired answer. It searches the values of selected changing cells according to an algorithm, and it may fail to find a feasible or optimal result if the model is badly structured, exceeds Solver’s limits, or uses an unsuitable solving method.

Enable the Solver add-in

On Excel for Microsoft 365 for Windows, Solver is usually available as an Excel add-in but may not yet be loaded. Use this path:

  1. Select File > Options > Add-ins.
  2. At the bottom, set Manage to Excel Add-ins, then select Go.
  3. Check Solver Add-in and select OK. If prompted to install it, approve the installation.
  4. Open the Data tab and confirm Solver appears, usually at the right end of the ribbon.

To use the Solver VBA procedures, the add-in must be installed and available in the Excel session. A macro using the method in this article calls the add-in by name, so it can fail if Solver is disabled or Excel cannot locate its add-in file. The alternative is to add a reference to Solver in the VBA editor using Tools > References, then call the Solver procedures directly. A direct reference can become marked “MISSING” if a workbook is opened on a computer where the add-in is not installed or its location differs.

Excel for the web can open a workbook containing macros, but it cannot run VBA macros. Solver and the VBA workflow described here are for desktop Excel. Excel for Mac has Solver support in desktop Excel, but ribbon placement and add-in setup can differ; the VBA example’s Windows-oriented add-in reference may need adjustment on Mac. Verify the add-in name and availability in the Mac installation before relying on an automated macro there.

Build a worksheet model first

Before writing VBA, make the model work through the regular Solver interface. The example below has two products. Put decision quantities in B2:B3, unit profits in C2:C3, units of a first resource per product in D2:D3, and units of a second resource in E2:E3. Use row 1 for headings. Put total profit in B5, first-resource use in B7, and second-resource use in B8. Assume the available amounts are 100 units of the first resource and 80 of the second.

With quantities in B2:B3 and unit profits in C2:C3, enter this objective formula in B5:

=SUMPRODUCT(B2:B3,C2:C3)

For an illustrative check, if the quantities are 15 and 17.5 and unit profits are 30 and 50, respectively, B5 returns 1,325 because 15 × 30 plus 17.5 × 50 equals 1,325.

With quantities in B2:B3 and first-resource requirements in D2:D3, enter this usage formula in B7:

=SUMPRODUCT(B2:B3,D2:D3)

If the resource requirements are 2 and 4, the same illustrative quantities use 100 units: 15 × 2 plus 17.5 × 4 equals 100. With quantities in B2:B3 and second-resource requirements in E2:E3, enter this formula in B8:

=SUMPRODUCT(B2:B3,E2:E3)

If those requirements are 3 and 2, B8 returns 80. These values meet both capacity limits exactly. They are a useful arithmetic check, not a promise that a Solver run will choose those quantities for every set of coefficients.

In Solver, set B5 as the objective and choose Max. Set B2:B3 as the changing variable cells. Add B7 less than or equal to 100 and B8 less than or equal to 80; also require B2:B3 to be nonnegative. Because the objective and constraints are linear sums, select Simplex LP. If products must be made only in whole units, add an integer constraint for B2:B3; that changes the model and can change the best solution.

Automate the setup and solve with VBA

The following macro assumes the worksheet is named Model and uses the cell layout just described. It resets any prior Solver model on that sheet, maximizes B5 by changing B2:B3, adds resource and nonnegative constraints, and runs Simplex LP. Save a copy of the workbook before running a macro: VBA changes cannot be undone with Ctrl+Z.

Sub SolveProductMix()
    Dim ws As Worksheet
    Dim solveStatus As Variant

    ' Use the worksheet containing the model.
    Set ws = ThisWorkbook.Worksheets("Model")
    ws.Activate

    ' Clear the previous Solver model on this sheet.
    Application.Run "Solver.xlam!SolverReset"

    ' Maximize total profit by changing the two product quantities.
    Application.Run "Solver.xlam!SolverOk", _
        ws.Range("$B$5").Address, 1, 0, _
        ws.Range("$B$2:$B$3").Address, 2, "Simplex LP"

    ' Keep resource use within available capacity.
    Application.Run "Solver.xlam!SolverAdd", _
        ws.Range("$B$7").Address, 1, "100"
    Application.Run "Solver.xlam!SolverAdd", _
        ws.Range("$B$8").Address, 1, "80"

    ' Require nonnegative production quantities.
    Application.Run "Solver.xlam!SolverAdd", _
        ws.Range("$B$2:$B$3").Address, 3, "0"

    ' Run without opening the Solver Results dialog.
    solveStatus = Application.Run("Solver.xlam!SolverSolve", True)

    If solveStatus = 0 Then
        ' Keep the optimal solution in the worksheet.
        Application.Run "Solver.xlam!SolverFinish", 1
        MsgBox "Solver found an optimal solution."
    Else
        MsgBox "Solver returned status " & CStr(solveStatus) & _
            ". Review the model and Solver result."
    End If
End Sub

The Solver procedure arguments are positional in these Application.Run calls. For SolverOk, 1 means maximize and engine 2 selects Simplex LP; for SolverAdd, relation 1 means less than or equal to and relation 3 means greater than or equal to. The “100,” “80,” and “0” arguments are the right-hand-side values for the constraints. If the add-in is registered under a different filename or Excel cannot resolve Solver.xlam, the calls will need the correct registered add-in name, or the workbook can use a Solver reference instead.

This example treats the decision variables as continuous, so fractional quantities are permitted. To require whole units, add an integer constraint with relation 4 on B2:B3. For binary decisions, Solver uses relation 5. These restrictions may make the search slower and can make the result differ from the continuous model’s answer. Add only restrictions that reflect the real problem.

Understand Solver results and methods

Solver’s result is conditional on the model and solving method. Simplex LP is appropriate when the objective and constraints are linear. GRG Nonlinear is intended for smooth nonlinear models, while Evolutionary is designed for models with discontinuities or other difficult relationships. Choosing a method simply because a model “looks complicated” is not enough: a wrong method or inaccurate worksheet formula can produce unhelpful results.

The macro checks for status 0, which indicates an optimal solution, before explicitly keeping the solution through SolverFinish. Other return statuses should be treated as signals to inspect the result and model, rather than as proof that the workbook contains a usable optimum. Solver can report that it could not find a feasible solution, encountered a limit, or needs a different model or method. If automating a business-critical process, log the status and important input values rather than displaying only a success message.

For a model that must produce an exact target rather than a maximum or minimum, SolverOk uses a different objective setting: choose “Value Of” and supply the target value. In VBA, the MaxMinVal argument is 3 for a target, and ValueOf supplies that target. Confirm the relevant Solver procedure signature for the installed add-in if adapting the example, especially if using a direct VBA reference instead of Application.Run.

Common VBA problems and fixes

  • “Cannot run the macro” or a missing procedure: Confirm Solver Add-in is enabled, and check that the macro’s Solver.xlam name matches the installed add-in. A missing VBA reference is another possible cause when using direct Solver calls.
  • Solver runs on the wrong sheet: Solver operates on the active worksheet. The example activates Model before resetting or configuring Solver. Ensure the sheet name exists and is spelled exactly as in the workbook.
  • The model has no feasible solution: Check the formula cells and constraints. For example, a resource limit below the minimum required to make any permitted product quantity feasible can make the constraints incompatible.
  • The answer looks unreasonable: Verify units, signs, objective direction, formula ranges, and whether quantities are allowed to be fractional. A maximization model with a missing capacity constraint may push values to unrealistic levels.
  • The macro stops or hangs: Large nonlinear or integer models can take time. Check Solver’s limits and model size, use the appropriate engine, and test on a small copy before automating a production workbook.
  • The macro is blocked: Excel’s macro security settings may prevent code from running. Use a trusted workbook and follow organizational security rules; do not enable macros from an unknown source just to make Solver work.

For easier maintenance, avoid hard-coding many cell addresses if users may rearrange the model. Named ranges can make the objective and decision cells clearer, and VBA can refer to those names. Still, validate that the names exist and point to the expected cells before solving. A macro should also restore calculation, events, or screen updating settings if it changes them; the short example does not change those application-wide settings.

VBA automation versus manual Solver

Manual Solver is often preferable for a one-off analysis, a model still being developed, or a workbook used by people who need to inspect each setup. The Solver Parameters dialog makes the objective, variable cells, constraints, and method visible. It is also easier to diagnose a setup interactively before turning it into code.

VBA is better suited to repeatable runs, batch processing, and reducing the chance that someone forgets to update a constraint. It adds code to maintain, however, and the add-in dependency and macro security requirements make distribution more involved. A practical approach is to build and verify the model manually first, then automate only the stable steps. For very large optimization applications, specialized optimization software or a service may offer more control than Excel Solver, but it will require a separate evaluation of cost, compatibility, and model migration.

Frequently Asked Questions

Can I run Excel Solver from a VBA macro?
Yes. Enable the Solver add-in, then use its VBA procedures to reset the model, define the objective and constraints, and solve. The example uses Application.Run to invoke Solver.xlam procedures without requiring a compile-time VBA reference.

Do I need to install Solver before running the macro?
Yes. Solver must be installed and available in desktop Excel. The macro cannot call an add-in that is disabled or absent, and Excel for the web cannot execute VBA.

What does SolverSolve return?
It returns a status value describing the outcome of the solve. A status of 0 indicates an optimal solution in this example. Other statuses require review; do not treat every nonzero result as a successful optimum.

Can Solver VBA handle integer or binary decisions?
Yes. Add an integer constraint (relation 4) or binary constraint (relation 5) to the relevant changing cells. These restrictions can increase solve time and may change the solution compared with a continuous model.

Why does Solver.xlam not work on my computer?
The add-in may be unchecked, unavailable, or registered under a different name or path. Enable Solver in Excel Add-ins and verify the add-in registration. If using a direct VBA reference, check Tools > References for a missing Solver entry.

Can Excel for Mac or the web run this exact macro?
Excel for the web does not run VBA. Excel for Mac has desktop VBA and Solver support, but add-in setup, naming, and ribbon locations can differ, so verify the Solver add-in call on the Mac version before distributing a macro-dependent workbook.

Related

Try it: the Column Letter to Number: aA is 27, XFD is 16,384. Converts both ways.

More in VBA & macros

Sources: Function behaviour and menu paths: Microsoft Support documentation for Excel. Method & sources

Spotted an error in this guide? Let us know.