VBA Solver

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 esta 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

Você pode baixar este modelo VBA Solver Excel aqui - VBA Solver Excel Template

Para obter 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.

Etapa 1: iniciar o subprocedimento VBA.

Código:

 Sub Solver_Example () End Sub 

Etapa 2: Primeiro, precisamos definir a referência da célula Objetivo usando a função SolverOk .

Passo 3: O primeiro argumento desta função é “SetCell”, neste exemplo, precisamos alterar o valor da célula Lucro, ou seja, célula B8.

Código:

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

Etapa 4: agora precisamos definir o valor da célula como 10000, portanto, para MaxMinVal, use 3 como o valor do argumento.

Código:

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

Etapa 5: o próximo valor do argumento ValueOf deve ser 10000.

Código:

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

O próximo argumento é ByChange, ou seja, alterando quais células essa equação precisa ser resolvida. Neste caso, alterando as unidades para vender (B1) e o preço por unidade (B2), a célula precisa ser alterada.

Código:

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

Nota: os argumentos restantes não são necessários aqui.

Etapa 6: Uma vez que a célula objetivo é definida, agora precisamos construir outros critérios. Para esta função aberta “SolverAdd”.

Etapa 7: A referência da primeira célula que precisamos alterar é a célula de preço por unidade, ou seja, célula B2.

Código:

 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: 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, run the code by pressing the F5 key to get the result.

When you run the code you will see the following window.

Press Ok and you will get the result in an excel sheet.

So in order to earn a profit of 10000, we need to sell 5000 units at 7 per price where the cost price is 5.

Things to Remember

  • To work with Solver in excel & VBA, first, enable it for worksheet, then enable for VBA reference.
  • Once it is enabled on both worksheets and VBA then only we can access all the Solver functions.