É possível ter células de referência do Solver do Excel em várias planilhas usando o VBA?

Question

Eu já vi essa pergunta postada em outros fóruns, mas não acredito que tenha aparecido aqui. Gostaria que o botão que eu atribuísse a uma macro do Solver fosse localizado em uma planilha diferente do alvo e das variáveis do Solver. Todos os destinos e variáveis estão localizados na mesma planilha, ou seja, pressionar um botão na Planilha1 faz com que o Solver seja executado na Planilha2 usando valores na Planilha2.

Outros fóruns costumam afirmar que o Solver é incapaz de fazer referência a células em outras planilhas além da ativa, mas eu esperava cavar o código do VBA e isso seria possível. Estou ciente de que, espelhando os valores da Sheet2 na Planilha1, eu poderia usar o Solver muito bem. Mas esta não é uma opção para este programa.

Eu tentei fazer o botão na Chamada1 chamar a macro Solver contida na Planilha2, mas a macro ainda referencia as células na Planilha1, não na Planilha2. (Essencialmente, isso criava um botão na Planilha1 para pressionar um botão na Planilha2)

Eu tentei declarar explicitamente as células, como abaixo:

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"

mas recebo: Erro em tempo de execução '9' Subscrito fora do intervalo.

Qualquer ajuda ou direção seria muito apreciada. Eu não sou um usuário avançado, e sim aprendendo o VBA conforme necessário para essa tarefa. Obrigado pelo seu tempo.

    

Answer 1

Uma solução normalmente usada é ativar a planilha onde você quer resolver, chamar o solucionador e reativar a planilha que contém o botão. Algo que é assim:

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

Por KB

SetCell Optional Variant. Refers to a single cell on the active worksheet.