Is it possible to have Excel's Solver reference cells in multiple sheets using VBA?

Question

I have seen this question posted on other forums, but I don't believe it has come up here. I would like the button I assign to a Solver macro to be located in a different sheet than Solver's target and variables. All targets and variables are located in the same sheet i.e. pressing a button in Sheet1 causes Solver to execute in Sheet2 using values in Sheet2.

Other forums often claim that Solver is unable to reference cells in sheets other than the active one, but I was hoping by digging into the VBA code this would be possible. I am aware that by mirroring Sheet2's values in Sheet1, I could use Solver just fine. But, this is not an option for this program.

I tried to make the button in Sheet1 call the Solver macro contained in Sheet2, but the macro still referenced cells in Sheet1, not Sheet2. (This was essentially making a button in Sheet1 to press a button in Sheet2)

I then tried to explicitly state the cells, as below:

SolverOk SetCell:=ThisWorkbook.Sheets("Sheet2").Cells(12, 2), MaxMinVal:=2, ValueOf:=0, ByChange:=ThisWorkbook.Sheets("Sheet2").Range("$B$9:$B$10"), _
        Engine:=1, EngineDesc:="GRG Nonlinear"

but I get: Run-time Error '9' Subscript out of range.

Any help or direction would be greatly appreciated. I am not a power-user, rather learning VBA as needed for this task. Thank you for your time.

Solution

One workaround I usually use is to activate the worksheet where you want to solve, call the solver, then reactivate the sheet that has the button. Something that goes like this :

Sub solveForValues()

    ' Prevent Excel from updating the screen while switching sheets
    ' This is purely cosmetic
    Application.ScreenUpdating = False

    Dim wkb As Workbook
    Set wkb = ThisWorkbook

    ' Let's call "solver_sheet" your solver data sheet
    wkb.Worksheets("solver_sheet").Activate

    SolverReset
    SolverAdd CellRef:="$X$83", Relation:=2, FormulaText:="100000"
    SolverOk SetCell:="$X$83", MaxMinVal:=1, ValueOf:=0, ByChange:="$AC$1"
    SolverSolve True

    ' The sheet "button_sheet" contains the button 
    ' and references to the result cells from "solver_sheet" sheet
    wkb.Worksheets("button_sheet").Activate

    ' Turning screen updating back ON
    Application.ScreenUpdating = True

    ' Visual confirmation for the button
    MsgBox ("Done!")

End Sub

Comments

[Marten Thompson]

Thank you Naucle for the thorough response! I especially like the Application.ScreenUpdating = False