Solver e VBA no Excel 2010
Objetivo: Demonstrar com um exemplo simples como automatizar a aplicação do Solver utilizando VBA.
O Solver é uma poderosa ferramenta de análise, utilizada para a otimização e simulação de modelos de negócios e engenharia.Pode ser ainda mais poderosa, se usado em conjunto com VBA, para automatizar a solução de vários modelos que utilizam parâmetros de entrada diferentes e restrições.
Este suplemento do Excel serve para a solução de problemas lineares, simples não-lineares e complexos.
No exemplo que faremos neste artigo nós resolveremos o seguinte problema:
Em uma lista de valores com casas decimais precisamos encontrar quais que somados chegam a um determinado valor.
Para isso:
- Ativar o suplemento Solver, clique em Arquivo->Opções->Suplementos e selecione o Suplemento Solver na tela que segue.
- Crie uma lista na coluna A com valores aleatórios utilizando a fórmula: =ARRED(ALEATÓRIOENTRE(1000;100000)+ALEATÓRIO();2), esta lista não deve ter tantos números, mais ou menos uns 15.
- Copie e cole somente valores estes números nesta coluna, desta forma teremos uma lista de valores fixos para trabalharmos.
- Some alguns destes valores aleatoriamente e salve na célula E1, este será nosso objetivo.
- Na célula B1 digite o valor 1, e na célula C1 digite a fórmula =A1*B1.
- Na célula E2 digite =SOMA(C1:C38).
- Teremos a seguinte planilha:
- Agora já temos a planilha preparada para aplicarmos o Solver e conseguirmos o nosso objetivo.
Automatizando o Solver
- Clique na guia Desenvolvedor, clique em Gravar Macro e configure a tela conforme abaixo:
- Clique na guia Dados e no botão Solver, ele ficará no canto direito superior da tela.
- No campo Set Objective selecione a célula $E$2, esta fórmula é a que irá ser alterada conforme os parâmetros passados.
- No campo Value Of digite o valor da célula E1, você não conseguirá digitar.
- No campo By Changing Variable Cells selecione o intervalo: $B$1:$B$19.
- Clique no botão Add e na tela que segue você deve selecionar a Cell Reference a célula $B$1 no campo que segue selecione Bin que no caso significa que este campo apenas aceitará valores binários (1 ou 0).
- Clique no botão Solve e ao mostrar a mensagem clique em OK.
- Selecione a guia Desenvolvedor e clique em Parar Gravação.
- Clique no botão Visual Basic e selecione o módulo 1 que foi criado pela gravação da macro.
- Aqui você já terá a macro gravada para gerar a mesma solução automaticamente, mas vamos imaginar que a lista de valores muda, desta forma você terá que alterar a macro com algumas adaptações.
- O suplemento Solver precisa ser ativado também para o VBA, então a primeira coisa que você deve fazer é ativar esta referência.
- Para isso selecione no VBA o menu Ferramentas->Referências e na lista selecione Solver.
-
-
SolverOk SetCell:=”$E$2″, MaxMinVal:=3, ValueOf:=Range(“E1″).Value, ByChange:=”$B$1:$B$” & iTotalLinhas _
-
Agora já fiz as devidas alterações no código para que tudo que fizemos e gravamos na Gravação de Macros seja adaptado para qualquer quantidade de números que se tenha na lista de valores.
Sub lsAutoSolver()
Dim i As Long
Dim iTotalLinhas As Long
SolverReset
iTotalLinhas = Cells(Rows.Count, 1).End(xlUp).Row
Range("B1").Select
Selection.Copy
Range("B2:B" & iTotalLinhas).Select
ActiveSheet.Paste
Range("C1").Select
Application.CutCopyMode = False
Selection.Copy
Range("C2:C" & iTotalLinhas).Select
ActiveSheet.Paste
Range("E2").Select
SolverOk SetCell:="$E$2", MaxMinVal:=3, ValueOf:=Range("E1").Value, ByChange:="$B$1:$B$" & iTotalLinhas _
, Engine:=1, EngineDesc:="GRG Nonlinear"
While i <= iTotalLinhas
SolverAdd CellRef:="$B$" & i, Relation:=5, FormulaText:="binary"
i = i + 1
Wend
SolverSolve
End Sub
Sobre os comandos do Solver:
SolverReset: Limpa todas as configurações do Solver
SolverOk: Cria um novo cálculo do Solver, passando as configurações como os campos de fórmula, o tipo de cálculo e a lista de células variáveis.
SolverAdd: Adiciona restrições para o cálculo do Solver.
SolverSolve: Efetua o cálculo do Solver. Variação SolverSolve True, desta forma não mostrará a tela, apenas serão alteradas as variáveis.
Você pode fazer o download da planilha com o código fonte e o exemplo completo do Solver no botão abaixo.
Os cálculos para este exemplo podem demorar bastante, dado a complexidade do cálculo, sugiro até você tentar encontrar sozinho os números que somados chegam ao valor.
Para o cálculo feito pelo Solver o tempo irá variar conforme a capacidade do processador do seu computador.
Também pode ver mais sobre o solver em: http://guiadoexcel.com.br/como-utilizar-o-solver-3
