VBA Solver - Exemplo passo a passo para usar o Solver no Excel VBA

Excel VBA Solver

Como você resolve problemas complicados? Se você não tem certeza de como resolver esses problemas, não se preocupe, temos um solucionador em nosso excel. Em nosso artigo anterior “Excel Solver”, aprendemos como resolver equações no Excel. Se você não sabe, “SOLVER” também está disponível com VBA. Neste artigo, mostraremos como usar o “Solver” no VBA.

Habilitar Solver na planilha

Um solucionador é uma ferramenta oculta disponível na guia de dados do Excel (se já estiver ativada).

Para usar o SOLVER no excel primeiro, precisamos habilitar esta opção. Siga as etapas abaixo.

Etapa 1: Vá para a guia ARQUIVO. Na guia ARQUIVO, escolha “Opções”.

Etapa 2: Na janela Opções do Excel, escolha “Add-Ins”.

Passo 3: Na parte inferior escolha “Excel Add-Ins” e clique em “Go”.

Passo 4: Agora marque a caixa “Solver Add-in” e clique em OK.

Agora você deve ver “Solver” na guia de dados.

Habilitar Solver em VBA

Também no VBA, o Solver é uma ferramenta externa; precisamos habilitá-lo para usá-lo. Siga as etapas abaixo para habilitá-lo.

Etapa 1: Vá para Ferramentas >>> Referência na janela do Editor do Visual Basic.

Passo 2: Na lista de referências, escolha “Solver” e clique em Ok para usar.

Agora podemos usar o Solver no VBA também.

Funções do Solver em VBA

Para escrever um código VBA, precisamos usar três “Funções do Solver” no VBA e essas funções são “SolverOk, SolverAdd e SolverSolve”.

SolverOk

SolverOk (SetCell, MaxMinVal, ValueOf, ByChange, Engine, EngineDesc)

SetCell: Esta será a referência de célula que precisa ser alterada, ou seja, célula de lucro.

MaxMinVal: Este é um parâmetro opcional, abaixo estão números e especificadores.

  • 1 = Maximize
  • 2 = Minimizar
  • 3 = corresponde a um valor específico

ValueOf: Este parâmetro precisa ser fornecido se o argumento MaxMinVal for 3.

ByChange: Alterando quais células, essa equação precisa ser resolvida.

SolverAdd

Agora vamos ver os parâmetros do SolverAdd

CellRef: Para definir os critérios para resolver o problema, o que é a célula precisa ser alterado.

Relação: Neste caso, se os valores lógicos forem satisfeitos, podemos usar os números abaixo.

  • 1 é menor que (<=)
  • 2 é igual a (=)
  • 3 é maior que (> =)
  • 4 deve ter valores finais inteiros.
  • 5 deve ter valores entre 0 ou 1.
  • 6 deve ter valores finais diferentes e inteiros.

Exemplo de Solver no Excel VBA

Para ver um exemplo, veja o cenário abaixo.

Usando esta tabela, precisamos identificar o valor do “Lucro”, que deve ser no mínimo 10.000. Para chegar a este número, temos certas condições.

  • As unidades a serem vendidas devem ser um valor inteiro.
  • O preço / unidade deve ser entre 7 e 15.

Com base nessas condições, precisamos identificar quantas unidades vender a que preço para obter o valor de lucro de 10.000.

Ok, vamos resolver essa equação agora.

Step 1: Start the VBA subprocedure.

Code:

Sub Solver_Example() End Sub

Step 2: First we need to set the Objective cell reference by using the SolverOk function.

Step 3: First argument of this function is “SetCell”, in this example we need to change the value of Profit cell i.e. B8 cell.

Code:

Sub Solver_Example() SolverOk SetCell:=Range("B8") End Sub

Step 4: Now we need to set this cell value to 10000, so for MaxMinVal use 3 as the argument value.

Code:

Sub Solver_Example() SolverOk SetCell:=Range("B8"), MaxMinVal:=3 End Sub

Step 5: The next argument ValueOf value should be 10000.

Code:

Sub Solver_Example() SolverOk SetCell:=Range("B8"), MaxMinVal:=3, ValueOf:=10000 End Sub

The next argument is ByChange i.e. by changing which cells this equation needs to be solved. In this case by changing Units to Sell (B1) and Price Per Unit (B2) cell needs to be changed.

Code:

Sub Solver_Example() SolverOk SetCell:=Range("B8"), MaxMinVal:=3, ValueOf:=10000, ByChange:=Range("B1:B2") End Sub

Note: remaining arguments are not required here.

Step 6: Once the objective cell is set, now we need to construct other criteria’s. For this open “SolverAdd” function.

Step 7: First Cell Ref we need to change is Price Per Unit cell i.e. B2 cell.

Code:

Sub Solver_Example() SolverOk SetCell:=Range("B8"), MaxMinVal:=3, ValueOf:=10000, ByChange:=Range("B1:B2") SolverAdd CellRef:=Range("B2") End Sub

Step 8: This cell needs to be>= 7, so the Relation argument will be 3.

Code:

Sub Solver_Example() SolverOk SetCell:=Range("B8"), MaxMinVal:=3, ValueOf:=10000, ByChange:=Range("B1:B2") SolverAdd CellRef:=Range("B2"), Relation:=3 End Sub

Step 9: This cell value should be>=7 i.e. Formula Text = 7.

Code:

Sub Solver_Example() SolverOk SetCell:=Range("B8"), MaxMinVal:=3, ValueOf:=10000, ByChange:=Range("B1:B2") SolverAdd CellRef:=Range("B2"), Relation:=3, FormulaText:=7 End Sub

Step 10: Similarly the same cell needs to be less than 15, so for this relation is <= i.e. 1 as the argument value.

Code:

Sub Solver_Example() SolverOk SetCell:=Range("B8"), MaxMinVal:=3, ValueOf:=10000, ByChange:=Range("B1:B2") SolverAdd CellRef:=Range("B2"), Relation:=3, FormulaText:=7 SolverAdd CellRef:=Range("B2"), Relation:=1, FormulaText:=15 End Sub

Step 11: First cell i.e. Units to Sell must be an Integer value for this also set up the criteria as below.

Code:

Sub Solver_Example() SolverOk SetCell:=Range("B8"), MaxMinVal:=3, ValueOf:=10000, ByChange:=Range("B1:B2") SolverAdd CellRef:=Range("B2"), Relation:=3, FormulaText:=7 SolverAdd CellRef:=Range("B2"), Relation:=1, FormulaText:=15 SolverAdd CellRef:=Range("B1"), Relation:=4, FormulaText:="Integer" End Sub

Step 12: In one final step, we need to add the SolverSolve function.

Code:

Sub Solver_Example() SolverOk SetCell:=Range("B8"), MaxMinVal:=3, ValueOf:=10000, ByChange:=Range("B1:B2") SolverAdd CellRef:=Range("B2"), Relation:=3, FormulaText:=7 SolverAdd CellRef:=Range("B2"), Relation:=1, FormulaText:=15 SolverAdd CellRef:=Range("B1"), Relation:=4, FormulaText:="Integer" SolverSolve End Sub

Ok, execute o código pressionando a tecla F5 para obter o resultado.

Ao executar o código, você verá a seguinte janela.

Pressione Ok e você obterá o resultado em uma planilha do Excel.

Portanto, para obter um lucro de 10.000, precisamos vender 5.000 unidades a 7 por preço, onde o preço de custo é 5.

Coisas para lembrar

  • Para trabalhar com o Solver no Excel e VBA, primeiro, habilite-o para a planilha e, em seguida, habilite-o para referência do VBA.
  • Depois de habilitado nas planilhas e no VBA, só podemos acessar todas as funções do Solver.

Artigos interessantes...