En este artículo encontraremos, cómo se puede hacer uso de Solver con VBA, para ello utilizaremos un ejemplo de Solver previamente resuelto: Ejemplo de Solver - Bolsos y Mochilas
Para poder emplear Solver, primero se debe añadir un complemento llamado "Solver", si no conoce como hacerlo, vea los siguientes pasos: Utilizar complemento de Excel
Comandos en Solver
Algunas de los comandos disponibles son: Solver Add, SolverChange, SolverDelete, SolverFinish, SolverFinishDialog, SolverLoad, SolverOk, SolverOkDialog, SolverReset, SolverSave y SolverSolve Function. Todas estas pueden permitir hacer modificaciones en las opciones de la celda objetivo o crear unas nuevas, también puede hacer cambios en las restricciones, verificar si se logró obtener una solución óptima acorde al problema, elaborar informes en donde resuman lo realizado por la herramienta y mostrar o hacer que se oculte los cuadros de diálogo.
Se procederá a explicar cada función de Solver y su sintaxis.
SolverAdd Function
Esta función tiene como sintaxis la siguiente: SolverAdd (CellRef, Relation, FormulaText) la cual sirve para registrar restricciones.
CellRef hace referencia a la parte izquierda de una restricción, mientras que Relation significa la relación que tiene una parte de la restricción con la otra. En ésta se tienen opciones como 1, 2, 3, 4, 5 y 6.
• 1 significa menor o igual (<=).
• 2 significa igual (=).
• 3 significa mayor o igual (>=)
• 4 significa que la CellRef se desea que sean valores enteros.
• 5 significa que la CellRef se desea que sean valores binarios.
• 6 significa que la CellRef se desea que tengan valores diferentes y enteros.
Por último, FormulaText hace referencia al lado derecho de la restricción. Cabe recalcar que, si se elige en Relation las opciones 4, 5 o 6 no será necesario especificar algún valor o celda en FormulaText.
Ejemplo de SolverAdd:
SolverAdd CellRef: = Range(“A4:A7”), Relation:=1, FormulaText:= “100”
En este caso se tiene que el rango de celdas “A4:A7” tiene que ser menor o igual (<=) a 100.
SolverChange Function
Esta función tiene como sintaxis la siguiente: SolverChange (CellRef, Relation, FormulaText) la cual sirve para cambiar una restricción que ya existe, así como el botón de Cambiar en el recuadro de Parámetros de Solver.
En este caso, esta función es bastante parecida a la anterior solo que en CellRef se seleccionará la celda por la que se desea cambiar. Asimismo, Relation y Formula Text siguen significando lo mismo, así como se describió en SolverAdd Function. Cabe recalcar que, si se elige en Relation las opciones 4, 5 o 6 no será necesario especificar algún valor o celda en FormulaText.
Ejemplo de SolverChange:
SolverAdd CellRef: = Range (“C4:C9”), Relation: =1, FormulaText: = “100”
En este caso, ahora se tiene un diferente rango de celdas “C4:C9” por la que se desea cambiar y su valor tiene que ser menor o igual (<=) a 100.
SolverDelete Function
Esta función tiene como sintaxis la siguiente: SolverDelete (CellRef, Relation, FormulaText) la cual sirve para eliminar una restricción que ya existe, así como el botón de Eliminar en el recuadro de Parámetros de Solver.
En este caso, esta función es parecida a las anteriores que han sido explicadas, solo que en esta función en CellRef se seleccionará la celda que se desea eliminar. Cabe recalcar que, si se elige en Relation las opciones 4, 5 o 6 no será necesario especificar algún valor o celda en FormulaText.
Ejemplo de SolverDelete:
SolverDelete CellRef: =Range (“C4:E6”), Relation: =3, FormulaText: = “200”
En este caso, se tiene una restricción completa que se desea eliminar.
SolverFinish Function
Esta función tiene como sintaxis la siguiente: SolverFinish (KeepFinal, ReportArray, OutlineReports) la cual sirve para indicar qué realizar con los resultados que se han obtenido y posteriormente indicar qué estilo de informe se debe construir una vez que haya culminado el proceso de Solver.
KeepFinal tiene 2 opciones que son 1 o 2, si se ingresa el número 1 es para que los valores óptimos de la solución del problema se posicionen en las celdas seleccionadas de modo que reemplaza los valores que se encontraban ahí. Por otro lado, el número 2 hace que esos valores de la solución final se descarten y se recuperen los valores que se encontraban anteriormente.
ReportArray hace referencia al tipo de informe que se desee que la herramienta Solver cree una vez finalizado todo el proceso. Este depende del método de resolución que se escoja el cual puede ser Simplex LP, GRG Nonlinear o Evolutionary. Si se escoge el método Simplex LP o GRG Nonlinear se puede elaborar un informe de respuesta con la opción 1, un informe de sensibilidad con la opción 2 y un informe de límite con la opción 3. Por otro lado, si el método es Evolutionary, se puede elaborar un informe de respuesta con la opción 1 y un informe de población con la opción 2.
OutlineReports puede ser True o False. Si es True entonces se van a generar los informes con grupos descritos correspondientes a las celdas que se ingresaron. Cabe mencionar que se puede omitir, así como también se puede poner False y ambas opciones harán que se generen los informes en un formato normal.
Ejemplo de SolverFinish:
SolverFinish KeepFinal: =1, ReportArray:=Array(1)
En este ejemplo, se desea que los valores que se han encontrado como solución del problema se posicionen en las celdas seleccionadas y que ya sea el método de resolución que se escogió al poner la opción 1 se desea que se elabore un informe de respuesta, así como también éste sea en un formato normal, ya que no se estableció nada en el OutlineReports.
SolverFinishDialog Function
Esta función tiene como sintaxis la siguiente: SolverFinishDialog (KeepFinal, ReportArray, OutlineReports) la cual tiene una similar función al SolverFinish Function solo que esta función lo que hará es que, al finalizar la solución de un problema se mostrará el cuadro de Resultados de Solver. La codificación para su elaboración es la misma como la anterior función, por lo que el ejemplo sería el mismo solo que con SolverFinishDialog.
SolverLoad Function
Esta función tiene como sintaxis la siguiente: SolverLoad (LoadArea, Merge) la cual tiene como función que los parámetros existentes en la hoja de trabajo se guarden en el modelo al usar Solver.
LoadArea hace referencia a una celda o rango de celdas en las que se desea registrar algo específico del problema.
Merge puede ser igual a True o False. Si es True es porque se desea que las celdas de las variables y sus restricciones que han sido seleccionadas se fusionen con las definidas. Si es False todo lo registrado se borra lo cual tendría igual función como SolverReset Function que sirve para volver a registrar nuevos parámetros.
Ejemplo de SolverLoad:
Worksheets(“Sheet4”). Activate
SolverLoad LoadArea: =Range (“D23:D40”)
En este ejemplo lo que se quiere es que se abra el modelo que se ha calculado en tal específica hoja de trabajo en donde se encuentra almacenado.
SolverOk Function
Esta función tiene como sintaxis la siguiente: SolverOk (SetCell, MaxMinVal, ValueOf, ByChange, Engine) la cual tiene la misma función que al presionar el botón de Solver en el recuadro de Parámetros de Solver.
SetCell hace referencia a una celda en la hoja en la que se encuentra actualmente activa, la cual es el cuadro en el que se selecciona la celda objetivo en el recuadro de Parámetros de Solver.
MaxMinVal hace referencia a la opción que se elija en el recuadro si se pone 1 es porque se desea Maximizar, si es 2 es minimizar y si es 3 es porque se espera que coincida con un valor determinado el cual tiene que digitarse.
ValueOf es el recuadro en el que se específica el valor si se desea que la celda objetivo coincida con un valor determinado.
ByChange es el rango de celdas que se quiere cambiar para poder obtener valores óptimos para la solución del problema, son las celdas de las variables.
Engine hace referencia al método de resolución a escoger que sería la opción 1 si se desea que sea el método SimplexLP, la opción 2 si se desea el método GRG Nonlinear y 3 para el método Evolutionary.
Ejemplo de SolverOk
SolverOk SetCell:=Range(“A1:A3”), MaxMinVal:=1, ByChange:=(“B1:C3”), Engine:=1.
En este ejemplo se tiene que la celda objetivo es A1:A3, se desea maximizar y que las celdas de las variables sean B1:C3 con el método de resolución SimplexLP.
SolverOkDialog Function
Esta función tiene como sintaxis la siguiente: SolverOkDialog (SetCell, MaxMinVal, ValueOf, ByChange, Engine) la cual tiene la misma función SolverOk solo que muestra el recuadro de Parámetros de Solver. En este caso, cada parte que forma la función significa lo mismo que en la función SolverOk y se podría usar el mismo ejemplo solo que con SolverOkDialog.
SolverReset Function
Esta función tiene como sintaxis la siguiente: SolverReset() la cual tiene como función restablecer lo que se ha indicado y establecido en el recuadro de Parámetros de Solver para volver a seleccionar desde un inicio. Esta función es como presionar el botón de Restablecer todo en el recuadro de Parámetros de Solver.
Ejemplo de SolverReset:
Worksheets(“Sheet4”). Activate
SolverReset
En este ejemplo lo que se quiere es restablecer el Solver ante de ejecutar un nuevo problema.
SolverSave Function
Esta función tiene como sintaxis la siguiente: SolverSave (SaveArea) la cual tiene como función guardar el modelo establecido para la solución del problema de Solver.
SaveArea hace referencia a una celda o rango establecido de celdas en las que se desea guardar el modelo realizado por Solver, dichas celdas pueden estar ubicadas en la hoja activa de ese momento o en cualquier hoja de trabajo; sin embargo, si se desea que se guarde en otra hoja de trabajo diferente a la actual se tiene que especificar mencionándola.
Ejemplo de SolverSave:
SolverSave (“Sheet4! B1:B3”)
En este ejemplo se desea guardar el modelo en la hoja de trabajo 4.
SolverSolve Function
Esta función tiene como sintaxis la siguiente: SolverSolver (UserFinish
, ShowRef) la cual tiene como función comenzar a ejecutar la solución de la herramienta Solver, así como el botón de Resolver que se muestra en el recuadro Parámetros de Solver.
UserFinish puede ser True o False. Si es True es porque se desea que se devuelvan los resultados sin que se tenga que mostrar el recuadro de Resultados de Solver, mientras que False sería lo mismo solo que haría que al final se muestre el recuadro mencionado.
ShowRef hace referencia a una función ya codificada en macros la cual contiene valores enteros del 1 al 5 según 5 situaciones.
1. Porque se presionó la tecla ESC para que se interrumpa la resolución en Solver.
2. Porque el tiempo máximo se superó respecto a los parámetros establecidos.
3. Porque las iteraciones se superaron respecto a los parámetros establecidos.
4. Porque se llegó a superar el límite máximo de subproblemas.
5. Porque se sobrepasó el límite máximo de las soluciones factibles.
Ejemplo de SolverSolve:
SolverSolve UserFinish;=False, ShowRef:= “Show”
Function Show (Razón As Integer)
MsgBox Razón
Show = 0
End Function
Este ejemplo significa que se ejecutará la resolución de Solver y si en caso haya algún problema relacionado a las situaciones que se han descrito, se llamará a la función Show que es la que mostrará un mensaje con un valor de uno a cinco que sea entero.
Utilizando Excel para identificar los comandos en VBA
Después de haber explicado todas las funciones de Solver, se procederá a resolver: Ejemplo de Solver - Bolsos y Mochilas, solo que ahora la resolución será utilizando VBA.
Descargar archivo: Utilizando Excel Solver
Para obtener un código similar (para el caso en que se encuentre) puede encender el grabador de macros, y realizar toda la configuración de la ventana "Parámetros de Solver", ejecutar la resolución del problema, la macro quedará grabada con los comandos usados previamente, y podrá modificarlos según lo necesite.
Cabe recalcar que para que la macro funcione, Solver debe estar habilitado en Referencias para VBA como se explicó anteriormente.
Por otro lado, cada línea de codificación se ha elaborado un comentario mencionando su significado con el fin de lograr una mejor comprensión. Asimismo, anteriormente ya se había explicado la sintaxis y la función de cada tipo de Solver Function que existe para elaborar una macro con el fin de facilitar la comprensión en la resolución de problemas de este tipo.
Elaborado por: Ana Flavia Urbano Lobos