Можно ли использовать эталонные ячейки Солвера в Excel на нескольких листах с помощью VBA?

Question

Я видел этот вопрос, размещенный на других форумах, но я не верю, что он возник здесь. Мне бы хотелось, чтобы кнопка, назначаемая макросу Солвера, находилась на другом листе, чем цель и переменные Солвера. Все цели и переменные расположены на одном листе, т. Е. Нажатие кнопки на листе 1 приводит к выполнению Солвера на листе 2 с использованием значений на листе 2.

Другие форумы часто утверждают, что Солвер не может ссылаться на ячейки в листах, отличных от активной, но я надеялся, что, углубившись в код VBA, это будет возможно. Я знаю, что, отражая значения Sheet2 в Sheet1, я мог бы использовать Solver очень хорошо. Но это не вариант для этой программы.

Я пытался заставить кнопку в Sheet1 вызывать макрос Solver, содержащийся в Sheet2, но макрос по-прежнему ссылался на ячейки в Sheet1, а не на Sheet2. (По сути, это делало кнопку на Листе1, чтобы нажать кнопку на Листе2)

Затем я попытался явно указать ячейки, как показано ниже:

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"

но я получаю: Ошибка времени выполнения '9' Подстрочный индекс вне диапазона.

Любая помощь или направление будет принята с благодарностью. Я не продвинутый пользователь, а изучаю VBA по мере необходимости для этой задачи. Спасибо за ваше время.

Answer 1

Один из обходных путей, который я обычно использую, - это активировать лист, на котором вы хотите решить, вызвать решатель, а затем повторно активировать лист с кнопкой. Что-то вроде этого:

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

Answer 2

За кб

SetCell Необязательный вариант. Относится к одной ячейке на активном листе.