Solver e VBA no Excel 2010

Solver e VBA no Excel 2010

Objetivo: Demonstrar com um exemplo simples como automatizar a aplicação do Solver utilizando VBA.

//

Lomadee, uma nova espécie na web. A maior plataforma de afiliados da América Latina.

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

http://www.linhadecodigo.com.br/artigo/803/Tudo-o-que-voc%C3%AA-precisa-saber-sobre-o-Solver-no-Excel.aspx

Abraço

Marcos Rieper

Leia também sobre:

Acompanhamento de vendas
Como Utilizar Filtro Avançado com Macro
Planilha de Controle de Hora Extra
Dica de Tabela Dinâmica
Planilha Matriz BCG no Excel