Solucionador de VBA

Solucionador de Excel VBA

¿Cómo resuelves problemas complicados? Si no está seguro de cómo solucionar estos problemas, no hay nada de qué preocuparse, tenemos un solucionador en nuestro Excel. En nuestro artículo anterior "Excel Solver" hemos aprendido cómo resolver ecuaciones en Excel. Si no sabe que "SOLVER" también está disponible con VBA. En este artículo, le mostraremos cómo utilizar "Solver" en VBA.

Habilitar Solver en la hoja de trabajo

Un solucionador es una herramienta oculta disponible en la pestaña de datos en Excel (si ya está habilitada).

Para usar SOLVER en Excel primero, necesitamos habilitar esta opción. Siga los pasos siguientes.

Paso 1: Vaya a la pestaña ARCHIVO. En la pestaña ARCHIVO elija "Opciones".

Paso 2: En la ventana Opciones de Excel, elija "Complementos".

Paso 3: En la parte inferior elige "Complementos de Excel" y haz clic en "Ir".

Paso 4: Ahora marque la casilla "Complemento Solver" y haga clic en Aceptar.

Ahora debe ver "Solver" en la pestaña de datos.

Habilitar Solver en VBA

También en VBA, Solver es una herramienta externa, necesitamos habilitarlo para usarlo. Siga los pasos a continuación para habilitarlo.

Paso 1: Vaya a Herramientas >>> Referencia en la ventana del Editor de Visual Basic.

Paso 2: De la lista de referencias, elija "Solver" y haga clic en Aceptar para usarlo.

Ahora también podemos usar Solver en VBA.

Funciones de solucionador en VBA

Para escribir un código VBA, necesitamos usar tres "Funciones de Solver" en VBA y esas funciones son "SolverOk, SolverAdd y SolverSolve".

SolverOk

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

SetCell: Esta será la referencia de celda que debe cambiarse, es decir, celda de beneficio.

MaxMinVal: este es un parámetro opcional, a continuación se muestran números y especificadores.

  • 1 = Maximizar
  • 2 = Minimizar
  • 3 = Coincide con un valor específico

ValueOf: este parámetro debe proporcionarse si el argumento MaxMinVal es 3.

ByChange: Al cambiar las celdas, esta ecuación debe resolverse.

SolverAdd

Ahora veamos los parámetros de SolverAdd

CellRef: Para establecer los criterios para resolver el problema, ¿cuál es la celda que debe cambiarse?

Relación: En esto, si se satisfacen los valores lógicos, podemos usar los números siguientes.

  • 1 es menor que (<=)
  • 2 es igual a (=)
  • 3 es mayor que (> =)
  • 4 debe tener valores finales que sean enteros.
  • 5 debe tener valores entre 0 o 1.
  • 6 debe tener valores finales que sean todos diferentes y enteros.

Ejemplo de Solver en Excel VBA

Puede descargar esta plantilla de Excel de VBA Solver aquí - Plantilla de Excel de VBA Solver

Para ver un ejemplo, mire el siguiente escenario.

Usando esta tabla necesitamos identificar la cantidad de "Beneficio" que debe ser un mínimo de 10000. Para llegar a este número tenemos ciertas condiciones.

  • Las unidades para vender deben ser un valor entero.
  • El precio / unidad debe estar entre 7 y 15.

Con base en estas condiciones, necesitamos identificar cuántas unidades vender a qué precio para obtener el valor de ganancia de 10000.

Ok, resolvamos esta ecuación ahora.

Paso 1: Inicie el subprocedimiento de VBA.

Código:

 Sub Solver_Example () End Sub

Paso 2: Primero necesitamos establecer la referencia de la celda Objetivo usando la función SolverOk .

Paso 3: El primer argumento de esta función es “SetCell”, en este ejemplo necesitamos cambiar el valor de la celda de Beneficio, es decir, la celda B8.

Código:

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

Paso 4: Ahora necesitamos establecer este valor de celda en 10000, por lo que para MaxMinVal use 3 como valor de argumento.

Código:

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

Paso 5: El siguiente valor del argumento ValueOf debe ser 10000.

Código:

 Sub Solver_Example () SolverOk SetCell: = Rango ("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: 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.