VBA Solver - Ejemplo paso a paso para usar Solver en Excel VBA

Tabla de contenido

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 lo sabe, "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 qué celdas, esta ecuación debe resolverse.

SolverAdd

Ahora veamos los parámetros de SolverAdd

CellRef: Para establecer los criterios para resolver el problema, es necesario cambiar cuál es la celda.

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

Para ver un ejemplo, observe 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.

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, ejecute el código presionando la tecla F5 para obtener el resultado.

Cuando ejecute el código, verá la siguiente ventana.

Presione Ok y obtendrá el resultado en una hoja de Excel.

Entonces, para obtener una ganancia de 10000, necesitamos vender 5000 unidades a 7 por precio donde el precio de costo es 5.

Cosas para recordar

  • Para trabajar con Solver en Excel y VBA, primero habilítelo para la hoja de trabajo y luego habilítelo para la referencia de VBA.
  • Una vez que está habilitado en ambas hojas de trabajo y VBA, solo nosotros podemos acceder a todas las funciones de Solver.

Articulos interesantes...