Building a VBA-Powered Excel Solver Automation Engine: Batch Optimize Multiple Scenarios, Capture Results, and Generate Sensitivity Reports Programmatically

Introduction

You've just been handed a capital allocation model with 47 product lines, three competing optimization objectives, and a request from the CFO to "run it a few different ways and show me the tradeoffs." Without automation, this means manually reconfiguring Solver, clicking Solve, recording the results, resetting the model, and starting over — dozens of times. By the time you finish, the assumptions have changed and you need to run it all again.

This is the moment most analysts either give up and build a crude sensitivity table, or discover that Excel Solver has a full programmatic interface through VBA. The Solver Add-in exposes a library of functions — SolverOk, SolverAdd, SolverSolve, and others — that allow you to construct, run, and interrogate optimization problems entirely through code. Pair that with a well-designed automation engine and you can batch-process 50 scenarios overnight, capture every result in a structured output sheet, and generate a sensitivity analysis report that would take a junior analyst three days to build manually.

By the end of this lesson, you'll have built a complete Solver automation engine from scratch. You'll understand how to control Solver programmatically, design a scenario-driven batch architecture, capture and log optimization outcomes, and generate meaningful sensitivity reports — all without touching the Solver dialog box once.

What you'll learn:

• How to reference and configure the Solver VBA library in your project
• How to programmatically build, run, and reset Solver problems across multiple parameter sets
• How to design a robust scenario input table that drives batch optimization
• How to capture Solver result codes, objective values, and decision variable states in a structured log
• How to generate a sensitivity (ranging) report programmatically and parse its output for executive-ready summaries

---

Prerequisites

Before diving in, you should be comfortable with:

• Writing VBA macros beyond the recorder level — if you need a refresher, start with Getting Started with VBA Macros in Excel
• Loops and conditional logic in VBA — the batch engine relies heavily on these (Automating Repetitive Tasks with VBA Loops and Conditions)
• Basic use of the Solver Add-in interactively, including objective cells, variable cells, and constraint setup — the conceptual foundation is covered in Advanced What-If Analysis: Scenario Manager, Goal Seek, and Solver in Excel
• Error handling patterns in VBA (Error Handling and Debugging VBA Code Like a Pro)

You'll also need the Solver Add-in installed (it ships with Excel) and enabled in your VBA project references — we'll handle that in the first section.

---

Understanding the Solver VBA Interface

Before writing a single line of automation code, you need to understand what you're actually calling. The Solver Add-in isn't a native Excel object like a worksheet or a chart. It's a COM add-in with its own function library, and interacting with it from VBA requires you to either reference it explicitly or call its functions through Application.Run.

Two Ways to Call Solver from VBA

Method 1: Direct reference (recommended for this lesson)

Open the VBA editor (Alt+F11), go to Tools > References, scroll down to find "Solver" in the list, and check the box. Once referenced, you can call Solver functions directly:

SolverOk SetCell:="$C$2", MaxMinVal:=1, ByChange:="$B$5:$B$20"

Method 2: Application.Run (reference-free, more portable)

If you're distributing the workbook and can't guarantee the reference is set on the recipient's machine, use:

Application.Run "Solver.xlam!SolverOk", "$C$2", 1, , "$B$5:$B$20"

Warning: The positional argument order in Application.Run differs from named parameters in direct calls. A mismatch here causes silent failures — your Solver setup appears to work but optimizes the wrong thing. Always use named parameters when calling directly, and test Application.Run calls on a clean machine before deployment.

Core Solver Functions You'll Use

Here's a precise map of the functions in the Solver library that matter for automation:

Function	Purpose
SolverOk	Define the objective cell, direction, and changing cells
SolverAdd	Add a constraint
SolverDelete	Remove a specific constraint
SolverReset	Clear all current Solver settings
SolverOptions	Set solving method, tolerance, iterations
SolverSolve	Execute the solve; returns a result code
SolverFinish	Accept or reject a solution after solving
SolverSensitivity	Generate the sensitivity (ranging) report

The SolverSolve return codes are critical — they tell you what actually happened:

• 0: Solver found a solution; all constraints and optimality conditions satisfied
• 1: Solver converged to a solution, but optimality is not guaranteed
• 2: Solver cannot improve the current solution
• 3: Stop chosen when maximum iteration limit was reached
• 4: Objective function value did not converge
• 5: Solver could not find a feasible solution

Key insight: A return code of 0 doesn't mean your answer is globally optimal — it means the local search terminated successfully. For non-linear or integer problems, always consider multiple starting points. Your automation engine should log the return code alongside every result.

The UserFinish parameter in SolverSolve is also worth understanding. Setting it to True suppresses the "Solver Results" dialog that normally appears after each run — essential when you're running 50 scenarios unattended.

---

Designing the Workbook Architecture

Before writing automation code, design the workbook structure deliberately. A poorly organized workbook makes batch automation brittle; a well-structured one makes it extensible.

The Four-Sheet Pattern

We'll use four sheets:

Model — The actual optimization model. This is where Solver runs. It contains the objective formula, decision variable cells, and constraint helper formulas. The automation engine reads scenarios from the Scenarios sheet and writes parameters here before each run.

Scenarios — A table-driven input sheet. Each row defines one optimization scenario: parameter values, constraint bounds, solver options, and a scenario name. This is the "what to run" specification.

Results — An append-only log. Every time a scenario runs, the engine appends a row with the scenario name, Solver result code, objective value, all decision variable values, and a timestamp. Never overwrite — append.

SensitivityReport — A structured interpretation of Solver's sensitivity output. The engine generates this after each solve and extracts the key ranging data into a clean table.

The Model Sheet: A Capital Allocation Example

Let's use a realistic example throughout: allocating a $10 million marketing budget across eight product lines to maximize total margin, subject to constraints on individual product budgets, regulatory spend limits, and channel capacity.

Set up the Model sheet like this:

• B2: Total Budget (input parameter — the automation engine writes here)
• B5:B12: Decision variables — spend allocated to each of 8 products (these are what Solver changes)
• C5:C12: Margin rates per dollar spent for each product (inputs — vary by scenario)
• D5:D12: =B5*C5 — contribution margin for each product
• D2: =SUM(D5:D12) — total margin (this is the objective cell)
• E5:E12: Maximum budget per product (inputs — vary by scenario)
• F2: Minimum regulatory spend total (input — varies by scenario)
• F5:F12: Product-level regulatory spend coefficient (inputs)
• G2: =SUMPRODUCT(B5:B12,F5:F12) — total regulatory spend (for constraint checking)

Name these ranges for cleaner VBA code. Named ranges make your automation code readable and resilient to row/column shifts. If you want to build this habit systematically, the principles in Mastering Excel's Name Manager: Define, Organize, and Use Named Ranges for Cleaner Formulas and VBA apply directly here.

The Scenarios Sheet

Create a table (convert it to an Excel Table for easier VBA access) with these columns:

ScenarioName | TotalBudget | MinRegSpend | MktProd1Max | ... | MktProd8Max | 
MarginRate1 | ... | MarginRate8 | SolverMethod | MaxTime | Tolerance

Each row is one scenario. A few sample rows:

ScenarioName	TotalBudget	MinRegSpend	MktProd1Max	...	Tolerance
BaseCase	10000000	500000	2000000	...	0.0001
Aggressive	12000000	400000	3000000	...	0.0001
Conservative	8000000	800000	1500000	...	0.0005
HighRegulatory	10000000	1200000	2000000	...	0.0001

Tip: Use Excel's data validation on the SolverMethod column (values: "GRG Nonlinear", "Simplex LP", "Evolutionary") to prevent typos that would silently configure the wrong algorithm. Dropdown validation for critical inputs is covered in Excel Data Validation Techniques: Drop-Down Lists, Custom Rules, and Input Controls for Reliable Data Entry.

---

Building the Core Automation Engine

Now we build the engine. We'll develop it in modular procedures so you can test and debug each piece independently.

Module 1: Solver Setup and Reset

Option Explicit

' Reference required: Tools > References > Solver

Private Const MODEL_SHEET As String = "Model"
Private Const RESULTS_SHEET As String = "Results"
Private Const SCENARIOS_SHEET As String = "Scenarios"
Private Const SENSITIVITY_SHEET As String = "SensitivityReport"

Sub ResetAndConfigureSolver(wsModel As Worksheet, _
                             solverMethod As String, _
                             maxTimeSec As Long, _
                             tolerance As Double)
    
    ' Clear all existing Solver settings for this sheet
    SolverReset
    
    ' Configure the objective: maximize total margin in D2
    SolverOk SetCell:=wsModel.Range("D2"), _
             MaxMinVal:=1, _    ' 1=Max, 2=Min, 3=Value of
             ByChange:=wsModel.Range("B5:B12")
    
    ' Translate method name to Solver engine integer
    Dim engineNum As Integer
    Select Case solverMethod
        Case "GRG Nonlinear": engineNum = 2
        Case "Simplex LP":    engineNum = 1
        Case "Evolutionary":  engineNum = 3
        Case Else:            engineNum = 2  ' default to GRG
    End Select
    
    ' Set solver options
    SolverOptions _
        Engine:=engineNum, _
        MaxTime:=maxTimeSec, _
        Precision:=tolerance, _
        Iterations:=1000, _
        AssumeNonNeg:=True  ' All decision variables must be >= 0
    
End Sub

Note: SolverReset clears constraints, objective, and changing cells but does NOT reset the values in the changing cell range. If you want each scenario to start from the same initial point (important for non-linear problems), you should also write a starting-value reset routine that populates your decision variables with initial guesses before calling SolverSolve.

Module 2: Constraint Builder

Constraints are the most scenario-specific part of the problem. This procedure rebuilds them from scratch for each scenario:

Sub BuildConstraints(wsModel As Worksheet, _
                     totalBudget As Double, _
                     minRegSpend As Double, _
                     productMaxima As Variant)
    
    ' Constraint 1: Total spend <= Total Budget
    ' SUM(B5:B12) <= TotalBudget
    SolverAdd CellRef:=wsModel.Range("B2"), _
              Relation:=1, _   ' 1=<=, 2=>=, 3==, 4=integer, 5=binary, 6=alldiff
              FormulaText:=totalBudget
    
    ' Wait — B2 holds our budget parameter, not the sum of spend.
    ' Let's use a helper cell. We'll reference the SUM directly.
    ' Assume B14 = SUM(B5:B12) in the model sheet.
    
    ' Clear that first add and redo it properly:
    SolverDelete CellRef:=wsModel.Range("B2"), _
                 Relation:=1, _
                 FormulaText:=totalBudget
    
    SolverAdd CellRef:=wsModel.Range("B14"), _  ' B14 = SUM(B5:B12)
              Relation:=1, _
              FormulaText:=totalBudget
    
    ' Constraint 2: Regulatory spend >= MinRegSpend
    ' G2 = SUMPRODUCT(B5:B12, F5:F12)
    SolverAdd CellRef:=wsModel.Range("G2"), _
              Relation:=2, _   ' >=
              FormulaText:=minRegSpend
    
    ' Constraint 3: Individual product maximum budgets
    Dim i As Integer
    For i = 1 To 8
        SolverAdd CellRef:=wsModel.Cells(4 + i, 2), _  ' B5 through B12
                  Relation:=1, _                          ' <=
                  FormulaText:=productMaxima(i - 1)
    Next i
    
End Sub

This approach — deleting and rebuilding constraints entirely per scenario — is more reliable than trying to modify existing constraints. The SolverDelete function is finicky; a small mismatch between the stored constraint and what you're trying to delete causes it to silently fail.

Warning: Always pair SolverReset with a full constraint rebuild rather than trying to surgically modify individual constraints between scenarios. The internal constraint indexing shifts when you delete midway through a list, and the resulting constraint set is often wrong in ways that don't surface until the solution looks suspiciously good.

Module 3: Writing Scenario Parameters to the Model

Sub WriteScenarioToModel(wsModel As Worksheet, scenarioRow As ListRow)
    
    ' Write scalar parameters
    wsModel.Range("B2").Value = scenarioRow.Range.Columns(2).Value  ' TotalBudget
    wsModel.Range("F2").Value = scenarioRow.Range.Columns(3).Value  ' MinRegSpend
    
    ' Write margin rates (columns 4-11 in scenario table = C5:C12 in model)
    Dim i As Integer
    For i = 1 To 8
        wsModel.Cells(4 + i, 3).Value = scenarioRow.Range.Columns(3 + i).Value
    Next i
    
    ' Reset decision variables to a neutral starting point
    ' This matters for non-linear problems: starting point affects local optimum found
    Dim totalBudget As Double
    totalBudget = wsModel.Range("B2").Value
    Dim equalShare As Double
    equalShare = totalBudget / 8
    
    For i = 5 To 12
        wsModel.Cells(i, 2).Value = equalShare  ' Start each product at equal share
    Next i
    
    ' Force Excel to recalculate before Solver reads formulas
    Application.Calculate
    
End Sub

The Application.Calculate call before handing off to Solver is not optional. If you've just written new parameter values, Excel may not have recalculated dependent formula cells yet. Solver reads the current cell values to evaluate feasibility — stale values produce misleading starting-point evaluations.

---

The Batch Execution Loop

This is the heart of the engine — the procedure that reads every scenario row, sets up Solver, runs it, and captures the outcome.

Sub RunBatchOptimization()
    
    Dim wsModel As Worksheet
    Dim wsResults As Worksheet
    Dim wsScenarios As Worksheet
    Dim tblScenarios As ListObject
    Dim tblResults As ListObject
    Dim scenRow As ListRow
    Dim solverResult As Integer
    Dim startTime As Double
    Dim elapsedTime As Double
    
    ' --- Setup ---
    Set wsModel = ThisWorkbook.Worksheets(MODEL_SHEET)
    Set wsResults = ThisWorkbook.Worksheets(RESULTS_SHEET)
    Set wsScenarios = ThisWorkbook.Worksheets(SCENARIOS_SHEET)
    
    Set tblScenarios = wsScenarios.ListObjects("tblScenarios")
    Set tblResults = wsResults.ListObjects("tblResults")
    
    ' Turn off screen updating and automatic calculation for speed
    Application.ScreenUpdating = False
    Application.Calculation = xlCalculationManual
    Application.DisplayAlerts = False
    
    ' Activate the model sheet (Solver requires the target sheet to be active)
    wsModel.Activate
    
    Dim productMaxima(7) As Double
    Dim i As Integer
    
    ' --- Main Loop ---
    For Each scenRow In tblScenarios.ListRows
        
        On Error GoTo SolverError
        
        startTime = Timer
        
        ' 1. Extract scenario parameters
        Dim scenName As String
        Dim totalBudget As Double
        Dim minRegSpend As Double
        Dim solverMethod As String
        Dim maxTime As Long
        Dim tolerance As Double
        
        scenName    = scenRow.Range.Columns(1).Value
        totalBudget = scenRow.Range.Columns(2).Value
        minRegSpend = scenRow.Range.Columns(3).Value
        
        For i = 0 To 7
            productMaxima(i) = scenRow.Range.Columns(4 + i).Value
        Next i
        
        ' Margin rates are columns 12-19 in the scenario table
        ' (assumes 8 product max columns then 8 margin rate columns)
        solverMethod = scenRow.Range.Columns(20).Value
        maxTime      = CLng(scenRow.Range.Columns(21).Value)
        tolerance    = CDbl(scenRow.Range.Columns(22).Value)
        
        ' 2. Write parameters to model
        WriteScenarioToModel wsModel, scenRow
        
        ' 3. Reset Solver and rebuild
        ResetAndConfigureSolver wsModel, solverMethod, maxTime, tolerance
        BuildConstraints wsModel, totalBudget, minRegSpend, productMaxima
        
        ' 4. Run Solver — suppress dialog with UserFinish:=True
        Application.Calculation = xlCalculationAutomatic
        solverResult = SolverSolve(UserFinish:=True)
        Application.Calculation = xlCalculationManual
        
        ' 5. Accept the solution (even if not perfect — we'll flag the result code)
        SolverFinish KeepFinal:=1  ' 1 = keep solution values, 2 = restore originals
        
        elapsedTime = Timer - startTime
        
        ' 6. Capture results
        CaptureResults tblResults, wsModel, scenName, solverResult, elapsedTime
        
        ' 7. Generate sensitivity report for feasible solutions
        If solverResult = 0 Or solverResult = 1 Then
            GenerateSensitivityData wsModel, scenName
        End If
        
        GoTo NextScenario
        
SolverError:
        ' Log the error but continue with remaining scenarios
        Dim errMsg As String
        errMsg = "VBA Error " & Err.Number & ": " & Err.Description
        AppendErrorToResults tblResults, scenName, errMsg
        Resume NextScenario
        
NextScenario:
        On Error GoTo 0
        
    Next scenRow
    
    ' --- Cleanup ---
    Application.Calculation = xlCalculationAutomatic
    Application.ScreenUpdating = True
    Application.DisplayAlerts = True
    Application.Calculate
    
    MsgBox "Batch optimization complete. " & tblScenarios.ListRows.Count & _
           " scenarios processed.", vbInformation, "Solver Automation Engine"
    
End Sub

There are several architectural decisions embedded in this code worth explaining:

Why toggle calculation mode around SolverSolve? Solver needs automatic calculation enabled while it iterates — it must be able to evaluate the objective and constraint formulas as it moves the decision variables. But we want manual mode during the setup phases to prevent unnecessary recalculation. The pattern of switching to auto just for the solve call is intentional.

Why SolverFinish with KeepFinal:=1 even for imperfect results? Because we want to capture the best solution Solver found, even if it didn't converge cleanly. The result code in the log tells us what happened. Restoring the original values (KeepFinal:=2) would leave the model in its starting-point state, which is useless for the sensitivity analysis that follows.

Why the On Error GoTo inside the loop rather than wrapping the whole loop? Because a VBA runtime error from Solver on scenario 3 shouldn't abort scenarios 4 through 47. Error handling that allows the loop to continue requires the GoTo pattern shown. For a deeper treatment of this, Error Handling and Debugging VBA Code Like a Pro covers the Resume statement and loop-safe error handling in detail.

---

Capturing Results in a Structured Log

The CaptureResults procedure appends one row to the Results table for each scenario:

Sub CaptureResults(tblResults As ListObject, _
                   wsModel As Worksheet, _
                   scenName As String, _
                   solverResult As Integer, _
                   elapsedSec As Double)
    
    ' Add a new row to the results table
    Dim newRow As ListRow
    Set newRow = tblResults.ListRows.Add
    
    ' Map result codes to human-readable status
    Dim statusText As String
    Select Case solverResult
        Case 0: statusText = "Optimal"
        Case 1: statusText = "Converged (non-optimal)"
        Case 2: statusText = "No improvement"
        Case 3: statusText = "Iteration limit"
        Case 4: statusText = "Did not converge"
        Case 5: statusText = "Infeasible"
        Case Else: statusText = "Unknown (" & solverResult & ")"
    End Select
    
    ' Write core result fields
    With newRow.Range
        .Columns(1).Value = scenName
        .Columns(2).Value = Now()                              ' Timestamp
        .Columns(3).Value = solverResult                       ' Numeric code
        .Columns(4).Value = statusText                         ' Human label
        .Columns(5).Value = wsModel.Range("D2").Value          ' Objective: Total Margin
        .Columns(6).Value = wsModel.Range("B14").Value         ' Total spend used
        .Columns(7).Value = wsModel.Range("G2").Value          ' Regulatory spend
        .Columns(8).Value = elapsedSec                         ' Solve time
        
        ' Decision variables: B5:B12 -> columns 9 through 16
        Dim i As Integer
        For i = 1 To 8
            .Columns(8 + i).Value = wsModel.Cells(4 + i, 2).Value
        Next i
    End With
    
    ' Apply conditional formatting color to status column
    Dim statusCell As Range
    Set statusCell = newRow.Range.Columns(4)
    
    Select Case solverResult
        Case 0: statusCell.Interior.Color = RGB(198, 239, 206)  ' Green
        Case 1: statusCell.Interior.Color = RGB(255, 235, 156)  ' Yellow
        Case 5: statusCell.Interior.Color = RGB(255, 199, 206)  ' Red
        Case Else: statusCell.Interior.Color = RGB(221, 221, 221) ' Gray
    End Select
    
End Sub

Sub AppendErrorToResults(tblResults As ListObject, _
                          scenName As String, _
                          errorMessage As String)
    
    Dim newRow As ListRow
    Set newRow = tblResults.ListRows.Add
    
    With newRow.Range
        .Columns(1).Value = scenName
        .Columns(2).Value = Now()
        .Columns(3).Value = -1
        .Columns(4).Value = "VBA Error"
        .Columns(5).Value = errorMessage
    End With
    
    newRow.Range.Columns(4).Interior.Color = RGB(200, 0, 0)
    newRow.Range.Columns(4).Font.Color = RGB(255, 255, 255)
    
End Sub

Tip: The Results table is append-only by design. Never add code that clears it before a batch run. If you rerun scenarios after tweaking the model, you want the historical record of previous runs. Add a "BatchID" column populated with a GUID or timestamp at the start of each batch run so you can filter to just the most recent execution.

---

Generating Sensitivity Reports Programmatically

The sensitivity report is where most analysts stop when using Solver manually — they generate it once, read a few numbers, and move on. With automation, we can extract sensitivity data systematically for every scenario and build a comparative analysis that would be impossible to produce by hand.

When you call SolverSensitivity, Solver creates a new worksheet named "Sensitivity Report 1" (or 2, 3, etc.). The automation challenge is that this sheet has an inconsistent structure — header rows, merged cells, and labeled sections — so you can't directly address cells by fixed row/column numbers.

Calling SolverSensitivity

Sub GenerateSensitivityData(wsModel As Worksheet, scenName As String)
    
    Dim wsSensRaw As Worksheet
    Dim wsSensOutput As Worksheet
    
    ' Generate the sensitivity report — Solver creates a new sheet
    SolverSensitivity
    
    ' Find the newly created sensitivity sheet
    ' It will be the last sheet, named "Sensitivity Report N"
    Set wsSensRaw = Nothing
    Dim ws As Worksheet
    For Each ws In ThisWorkbook.Worksheets
        If InStr(ws.Name, "Sensitivity Report") > 0 Then
            Set wsSensRaw = ws
        End If
    Next ws
    
    If wsSensRaw Is Nothing Then
        Debug.Print "Sensitivity report not generated for: " & scenName
        Exit Sub
    End If
    
    ' Parse and extract the report
    Set wsSensOutput = ThisWorkbook.Worksheets(SENSITIVITY_SHEET)
    ParseAndLogSensitivity wsSensRaw, wsSensOutput, scenName
    
    ' Delete the raw Solver-generated sheet (we've captured what we need)
    Application.DisplayAlerts = False
    wsSensRaw.Delete
    Application.DisplayAlerts = True
    
End Sub

Parsing the Sensitivity Report

The raw sensitivity report has two sections: Adjustable Cells (the ranging for decision variables) and Constraints (the ranging for constraint right-hand sides). Parsing it requires finding these section headers by searching for their label text.

Sub ParseAndLogSensitivity(wsSensRaw As Worksheet, _
                            wsSensOutput As Worksheet, _
                            scenName As String)
    
    Dim lastRow As Long
    Dim outputRow As Long
    Dim searchCell As Range
    Dim dataStart As Long
    Dim i As Long
    
    ' Find the next empty row in the sensitivity output sheet
    outputRow = wsSensOutput.Cells(wsSensOutput.Rows.Count, 1).End(xlUp).Row + 1
    If outputRow = 2 And wsSensOutput.Cells(1, 1).Value = "" Then outputRow = 2
    
    ' --- Section 1: Adjustable Cells (Decision Variables) ---
    Set searchCell = wsSensRaw.Cells.Find( _
        What:="Adjustable Cells", _
        LookIn:=xlValues, _
        LookAt:=xlPart, _
        SearchOrder:=xlByRows)
    
    If Not searchCell Is Nothing Then
        ' The data header row is 2 rows below the section label
        ' Columns in adjustable cells section:
        ' Cell | Name | Final Value | Reduced Cost | Objective Coeff | 
        ' Allowable Increase | Allowable Decrease
        
        dataStart = searchCell.Row + 2  ' Skip label and column headers
        
        ' Read until we hit a blank cell in column A (or the Constraints section)
        i = dataStart
        Do While wsSensRaw.Cells(i, 1).Value <> "" And _
                 InStr(wsSensRaw.Cells(i, 1).Value, "Constraints") = 0
            
            wsSensOutput.Cells(outputRow, 1).Value = scenName
            wsSensOutput.Cells(outputRow, 2).Value = "Variable"
            wsSensOutput.Cells(outputRow, 3).Value = wsSensRaw.Cells(i, 2).Value  ' Name
            wsSensOutput.Cells(outputRow, 4).Value = wsSensRaw.Cells(i, 3).Value  ' Final Value
            wsSensOutput.Cells(outputRow, 5).Value = wsSensRaw.Cells(i, 4).Value  ' Reduced Cost
            wsSensOutput.Cells(outputRow, 6).Value = wsSensRaw.Cells(i, 5).Value  ' Obj Coeff
            wsSensOutput.Cells(outputRow, 7).Value = wsSensRaw.Cells(i, 6).Value  ' Allow. Increase
            wsSensOutput.Cells(outputRow, 8).Value = wsSensRaw.Cells(i, 7).Value  ' Allow. Decrease
            
            outputRow = outputRow + 1
            i = i + 1
        Loop
    End If
    
    ' --- Section 2: Constraints ---
    Set searchCell = wsSensRaw.Cells.Find( _
        What:="Constraints", _
        LookIn:=xlValues, _
        LookAt:=xlPart, _
        SearchOrder:=xlByRows)
    
    If Not searchCell Is Nothing Then
        ' Columns in constraints section:
        ' Cell | Name | Final Value | Shadow Price | Constraint RHS | 
        ' Allowable Increase | Allowable Decrease
        
        dataStart = searchCell.Row + 2
        
        i = dataStart
        Do While wsSensRaw.Cells(i, 1).Value <> ""
            
            wsSensOutput.Cells(outputRow, 1).Value = scenName
            wsSensOutput.Cells(outputRow, 2).Value = "Constraint"
            wsSensOutput.Cells(outputRow, 3).Value = wsSensRaw.Cells(i, 2).Value  ' Name
            wsSensOutput.Cells(outputRow, 4).Value = wsSensRaw.Cells(i, 3).Value  ' Final Value
            wsSensOutput.Cells(outputRow, 5).Value = wsSensRaw.Cells(i, 4).Value  ' Shadow Price
            wsSensOutput.Cells(outputRow, 6).Value = wsSensRaw.Cells(i, 5).Value  ' Constraint RHS
            wsSensOutput.Cells(outputRow, 7).Value = wsSensRaw.Cells(i, 6).Value  ' Allow. Increase
            wsSensOutput.Cells(outputRow, 8).Value = wsSensRaw.Cells(i, 7).Value  ' Allow. Decrease
            
            outputRow = outputRow + 1
            i = i + 1
        Loop
    End If
    
End Sub

Key insight: Shadow prices from the sensitivity report are the most analytically valuable output of this whole exercise. A shadow price on the total budget constraint tells you exactly how much the objective (total margin) would improve if you relaxed that constraint by one unit. When you have 40 scenarios, comparing shadow prices across them reveals which constraints are truly binding your performance — information that's invisible when you run Solver once manually.

---

Building the Executive Summary Report

The batch results are only as useful as the summary you build from them. Let's add a procedure that generates a clean summary report from the Results table:

Sub GenerateExecutiveSummary()
    
    Dim wsResults As Worksheet
    Dim wsSummary As Worksheet
    Dim tblResults As ListObject
    Dim resultRow As ListRow
    
    Set wsResults = ThisWorkbook.Worksheets(RESULTS_SHEET)
    Set tblResults = wsResults.ListObjects("tblResults")
    
    ' Create or clear the summary sheet
    On Error Resume Next
    Set wsSummary = ThisWorkbook.Worksheets("ExecutiveSummary")
    On Error GoTo 0
    
    If wsSummary Is Nothing Then
        Set wsSummary = ThisWorkbook.Worksheets.Add(After:=ThisWorkbook.Sheets(ThisWorkbook.Sheets.Count))
        wsSummary.Name = "ExecutiveSummary"
    Else
        wsSummary.Cells.Clear
    End If
    
    ' Write summary headers
    With wsSummary
        .Range("A1").Value = "Optimization Batch Summary"
        .Range("A1").Font.Size = 16
        .Range("A1").Font.Bold = True
        .Range("A3").Value = "Generated: " & Now()
        
        ' Column headers for comparison table
        Dim headers As Variant
        headers = Array("Scenario", "Status", "Total Margin ($)", "Budget Used ($)", _
                        "Reg Spend ($)", "Margin Efficiency (%)", "Solve Time (s)")
        
        Dim col As Integer
        For col = 0 To UBound(headers)
            .Cells(5, col + 1).Value = headers(col)
            .Cells(5, col + 1).Font.Bold = True
            .Cells(5, col + 1).Interior.Color = RGB(31, 73, 125)
            .Cells(5, col + 1).Font.Color = RGB(255, 255, 255)
        Next col
        
        Dim outputRow As Integer
        outputRow = 6
        
        Dim bestMargin As Double
        Dim bestScenario As String
        bestMargin = 0
        
        For Each resultRow In tblResults.ListRows
            
            Dim margin As Double
            Dim budgetUsed As Double
            Dim regSpend As Double
            Dim solveTime As Double
            Dim status As String
            Dim sName As String
            
            sName     = resultRow.Range.Columns(1).Value
            status    = resultRow.Range.Columns(4).Value
            margin    = IIf(IsNumeric(resultRow.Range.Columns(5).Value), _
                            resultRow.Range.Columns(5).Value, 0)
            budgetUsed = IIf(IsNumeric(resultRow.Range.Columns(6).Value), _
                             resultRow.Range.Columns(6).Value, 0)
            regSpend  = IIf(IsNumeric(resultRow.Range.Columns(7).Value), _
                            resultRow.Range.Columns(7).Value, 0)
            solveTime = IIf(IsNumeric(resultRow.Range.Columns(8).Value), _
                            resultRow.Range.Columns(8).Value, 0)
            
            Dim efficiency As Double
            efficiency = IIf(budgetUsed > 0, margin / budgetUsed * 100, 0)
            
            .Cells(outputRow, 1).Value = sName
            .Cells(outputRow, 2).Value = status
            .Cells(outputRow, 3).Value = margin
            .Cells(outputRow, 3).NumberFormat = "$#,##0"
            .Cells(outputRow, 4).Value = budgetUsed
            .Cells(outputRow, 4).NumberFormat = "$#,##0"
            .Cells(outputRow, 5).Value = regSpend
            .Cells(outputRow, 5).NumberFormat = "$#,##0"
            .Cells(outputRow, 6).Value = efficiency
            .Cells(outputRow, 6).NumberFormat = "0.0%"
            .Cells(outputRow, 7).Value = solveTime
            .Cells(outputRow, 7).NumberFormat = "0.00"
            
            If margin > bestMargin And status = "Optimal" Then
                bestMargin = margin
                bestScenario = sName
            End If
            
            outputRow = outputRow + 1
        Next resultRow
        
        ' Highlight the best performing scenario
        If bestScenario <> "" Then
            Dim findCell As Range
            Set findCell = .Columns(1).Find(bestScenario, LookIn:=xlValues)
            If Not findCell Is Nothing Then
                .Rows(findCell.Row).Interior.Color = RGB(198, 239, 206)
                .Rows(findCell.Row).Font.Bold = True
            End If
        End If
        
        ' Add best-scenario callout
        .Cells(outputRow + 2, 1).Value = "Best Scenario: " & bestScenario
        .Cells(outputRow + 2, 1).Font.Bold = True
        .Cells(outputRow + 3, 1).Value = "Peak Margin: " & Format(bestMargin, "$#,##0")
        
        ' Autofit columns
        .UsedRange.Columns.AutoFit
        
    End With
    
    wsSummary.Activate
    MsgBox "Executive summary generated on the 'ExecutiveSummary' sheet.", _
           vbInformation, "Summary Complete"
    
End Sub

For teams that need these results distributed automatically, you can extend this to email the summary sheet as a PDF attachment. The patterns for that are covered in Automating Email and File Operations with VBA.

---

Performance Optimization and Scaling

Once you go beyond 20 scenarios, performance becomes a real concern. A single complex Solver run can take 5-30 seconds, so 50 scenarios might run for 25 minutes — fine if you kick it off before a meeting, not fine if your CFO is waiting.

Reducing Per-Run Overhead

The biggest performance wins come from minimizing Excel's work outside the actual Solver call:

Sub OptimizeApplicationState()
    Application.ScreenUpdating = False
    Application.DisplayAlerts = False
    Application.EnableEvents = False
    Application.Calculation = xlCalculationManual
    ' Re-enable ONLY calculation during SolverSolve call
End Sub

Application.EnableEvents = False is particularly important if you have worksheet event handlers that fire on cell changes — writing scenario parameters to the model sheet would trigger those events 8+ times per scenario without this setting.

Model Complexity vs. Solver Method Selection

The choice of solver engine has a dramatic effect on runtime:

• Simplex LP: Fastest, but requires a strictly linear model (objective and all constraints are linear combinations of decision variables). For our margin model where D5 = B5 * C5 with a fixed C5, this is linear and Simplex is appropriate.
• GRG Nonlinear: Handles nonlinear models but is sensitive to starting points. Use multiple starting points for confidence.
• Evolutionary: Slowest but most robust for discontinuous or highly nonlinear problems. Avoid in batch runs unless necessary.

Build your scenario table so each scenario can specify its method — a scenario with tight regulatory constraints might need Evolutionary while simpler configurations use Simplex.

Tip: For large batch runs, consider adding a "dry run" check that validates each scenario's parameter data before starting the actual optimization. A malformed constraint value discovered on scenario 34 of 50 after 20 minutes of computation is genuinely painful. Validate budget > 0, product maxima are positive, margin rates are reasonable — whatever your domain logic requires.

Progress Feedback Without Screen Flicker

Since ScreenUpdating is off, you need another way to show progress:

' In the main batch loop, after each scenario completes:
Application.StatusBar = "Optimizing: " & scenName & _
                        " (" & currentIndex & " of " & totalScenarios & ")" & _
                        " | Elapsed: " & Format(Timer - batchStartTime, "0") & "s"

The status bar updates even with ScreenUpdating = False — it's a separate UI element. Clean it up when the batch completes:

Application.StatusBar = False  ' Restores the default status bar text

---

Hands-On Exercise

Build this engine for a portfolio optimization problem:

The scenario: You're allocating a $5M discretionary investment budget across six business units. Each unit has a projected ROI rate and a maximum allocation cap. You must spend at least $200K on compliance-related units (Units 5 and 6). Your objective is to maximize total expected return.

1. Set up the Model sheet with:

   • B2: Total Budget (input)
   • B5:B10: Allocation per unit (decision variables)
   • C5:C10: ROI rates (input, varies by scenario)
   • D5:D10: =B5*C5 (return per unit)
   • D2: =SUM(D5:D10) (total return — objective)
   • B11: =SUM(B5:B10) (total allocated — for budget constraint)
   • G2: =B9+B10 (compliance units total — for regulatory constraint)

2. Create a Scenarios table with 6 rows:

   • "Baseline": $5M budget, market consensus ROI rates
   • "Bull Market": $6M budget, all ROI rates +20%
   • "Bear Market": $4M budget, all ROI rates -15%
   • "Compliance Focused": $5M budget, higher compliance ROI rates
   • "Concentrated": $5M budget with tighter caps on units 1-4
   • "Diversified": $5M budget with equal caps across all units

3. Implement the full batch engine as described, adapting the constraint structure to match this simpler 6-variable problem.

4. After the batch run, answer these questions using your Results and SensitivityReport sheets:

   • Which scenario produces the highest total return?
   • In the Baseline scenario, which constraint has the highest shadow price? What does that tell you about where to lobby for budget increases?
   • Which product units consistently receive zero allocation (are at reduced cost bounds) across multiple scenarios?
   • How does solve time vary across scenarios and methods?

---

Common Mistakes and Troubleshooting

"Solver could not find a feasible solution" on scenarios that should work

This almost always means a constraint contradiction — the scenario's constraint values collectively make the feasible region empty. Common culprits: product maximum allocations that sum to less than the minimum regulatory spend, or a total budget cap smaller than the sum of minimum required allocations. Add a pre-flight validation function that checks for obvious infeasibilities before calling Solver.

Sensitivity report not generated / "Sensitivity Report" sheet appears blank

Solver only generates a sensitivity report for LP and GRG Nonlinear solutions that converge cleanly (result code 0). Evolutionary solutions never produce sensitivity reports — they're heuristic. Also, the report is only available when the Engine parameter matches the actual solve method. If you switched engines mid-scenario without resetting, Solver may refuse to generate ranging.

Solver settings bleed between scenarios

If you ever see scenario 5 behaving like scenario 3, SolverReset didn't fully clear the previous configuration. This can happen when you call SolverReset on the wrong active sheet. Solver is always scoped to the active worksheet — always call wsModel.Activate before any Solver function call, not just before SolverSolve.

VBA type mismatch errors on constraint values

When reading from the scenario table, variant-typed cells from ListRows can return unexpected types. A cell containing "500000" (text) versus 500000 (numeric) makes a significant difference to SolverAdd's FormulaText parameter. Always explicitly cast: CDbl(scenRow.Range.Columns(2).Value).

"Method 'SolverSolve' of object failed" at runtime

This usually means Solver isn't properly referenced or the add-in isn't loaded. Check: Tools > References > Solver is checked. If you're distributing this workbook, add this to your initialization routine:

' Ensure Solver add-in is loaded
If Not AddIns("Solver Add-in").Installed Then
    AddIns("Solver Add-in").Installed = True
End If

Warning: Enabling the Solver add-in programmatically requires a restart of the Excel session to take effect in some configurations. The safest approach for distributed workbooks is to check for Solver availability at workbook open and prompt the user if it's not loaded, rather than silently trying to enable it. The event-driven patterns for workbook-open checks are covered in Building a Custom VBA Event-Driven Framework: Respond to Workbook, Worksheet, and Application Events for Real-Time Automation.

Solver runs but always returns the starting point unchanged

This is the symptom of calling SolverSolve while Application.Calculation = xlCalculationManual without calling Application.Calculate first. Solver needs formulas to be live. Alternatively, if all constraints are already satisfied at the starting point and you're maximizing a cell that doesn't depend on the decision variables (broken formula reference), Solver correctly reports "no improvement needed" and returns the starting values as optimal.

---

Summary and Next Steps

You've built a production-grade Solver automation engine that can batch-process dozens of optimization scenarios without human intervention. The key architectural patterns you've implemented:

• Stateless scenario execution: Each scenario fully resets and rebuilds the Solver configuration, eliminating contamination between runs
• Structured result capture: An append-only log with full parameter reconstruction, result codes, and timing data
• Programmatic sensitivity parsing: Extracting shadow prices and ranging data from Solver's raw report output and transforming it into a comparative analysis table
• Fault-tolerant batch looping: Error handling that continues processing remaining scenarios when individual runs fail
• Performance-aware execution: Strategic use of ScreenUpdating, Calculation, and EnableEvents to minimize overhead

The techniques here integrate naturally with broader Excel automation architecture. If you're building this as a standalone tool your team will reuse, consider packaging it as a proper add-in — Building Excel Add-Ins with VBA: Package and Deploy Custom Tools Across Your Organization walks through that path. For organizations with dynamic data feeds, you might also want to explore connecting the scenario parameters directly to a database source rather than maintaining them in a static table — the approach in Connecting Excel to External Databases with VBA: SQL Queries, ADO, and Database Automation gives you the pattern for that.

The next frontier is adding a feedback loop: use the sensitivity report data to automatically generate candidate new scenarios by perturbing binding constraints, then feed those back into the batch engine. That turns this from a batch reporting tool into a true optimization exploration engine — a system that helps analysts find the best possible solution space, not just evaluate scenarios they already thought to specify.