Creando una Macro para Excel que ejecute Solver u OpenSolver

En esta entrada, crearemos una Macro en Microsoft Excel 2013® que nos permita resolver un Problema de Programación Lineal (o PPL), facilitando tanto la definición como el ingreso de los parámetros y variables que utilizaremos para, posteriormente, ejecutar complementos como Solver u OpenSolver para buscar una solución.

 

1.- Pasos Previos:

Comenzaremos por preparar nuestro Excel, corroborando que tengamos todo lo necesario para llevar a cabo nuestra tarea. En particular:

• Primero, debemos abrir un libro de Excel y habilitar el complemento "Solver" dirigiéndonos a:
  Archivo → Opciones → Complementos → Click en el botón "Ir" → Marcar "Solver" y click en "Aceptar".
  
  

  

  
  
• Segundo, descargamos OpenSolver y descomprimimos los archivos. Posteriormente nos dirigimos a la siguiente ruta:
  Archivo → Opciones → Complementos → Click en el botón "Ir" → Click en "Examinar" → Y arrastramos los archivos comprimidos a esa ventana y, una vez realizado, seleccionamos el archivo "OpenSolver" y ponemos "Aceptar". Luego volvemos a la ventana anterior, activamos OpenSolver y clickeamos en "Aceptar".
  
  

  

  
   
• Tercero, mostraremos la pestaña Desarrollador moviéndonos a:
  Archivo → Opciones → Personalizar cinta de opciones → Marcar "Desarrollador" y click en "Aceptar"
• Cuarto, guardaremos nuestro documento como un "Libro de Excel habilitado para macros" en:
  Archivo → Guardar Como → Escogemos el nombre, directorio y en Tipo seleccionaremos "Libro de Excel habilitado para macros" y damos click en "Aceptar"

2.- Problema de Programación Lineal:

Realizado lo anterior, debemos contar con un PPL. A modo de ejemplo, utilizaremos el problema:

"Usted quiere abastecerse de cervezas para el verano y cuenta con un presupuesto de $75.000. Para lo anterior, ha cotizado en una distribuidora mayorista los siguientes tipos de cervezas por botella: Cerveza Negra $800 c/u, Cerveza Rubia $700 c/u y Cerveza Artesanal $1200 c/u. Cabe recalcar que cada tipo de cerveza se vende por botellas ( o unidades enteras).

Asimismo, el vendedor le informa que, para acceder a los precios anteriores, usted debe comprar al menos 12 unidades de Cerveza Negra, 24 de Cerveza Rubia y 6 de Cerveza Artesanal."

Un ejemplo para el problema planteado puede ser el siguiente:

En donde:

• Las Celdas C3 a C5 contienen las celdas variables de nuestro problema (destacadas en color azul).
• La Celda A13 contiene la función objetivo (destacada en amarillo), que contiene la sumatoria de las multiplicaciones de cada precio unitario por las unidades respectivas. 
• Las Celdas C9 a C11 contienen las restricciones de unidades mínimas a consumir.
• Las Celdas B15 a B17 nos recuerdan que debemos imponer que las celdas variables sean enteros.
  

 

3.- Trabajando con Nuestra Macro:

Una vez definido nuestro PPL a resolver, podemos comenzar a programar nuestra Macro. En primera instancia, supondremos que deseamos que el usuario final no tenga la posibilidad de editar los parámetros y variables de nuestro problema, por lo que el usuario utilizará la Macro para obtener solamente el resultado. 

Comenzaremos por crear nuestra Macro clickeando en el botón "Macro" dentro de la pestaña Desarrollador. En la ventana emergente, indicamos que llamaremos a nuestra Macro "ResolverOculto" (sin comillas) y apretamos el botón "Crear" para, posteriormente, clickear en "Modificar". Luego, se nos abrirá la ventana de "Microsoft Visual Basic para Aplicaciones", en donde podremos programar nuestra Macro.

En ésta última ventana, deberemos activar las referencias a Solver y OpenSolver (si es que usaremos los dos) dirigiéndonos hacia:
Herramientas → Referencias → Marcamos "Solver" y "OpenSolver" y damos click en "Aceptar".

 

A continuación, debemos programar la macro con las instrucciones necesarias para que se ejecute el Solver o el OpenSolver correctamente. Se muestra una imagen a continuación que posee comentarios con la explicación de los comandos e instrucciones utilizadas para construir nuestra Macro:

Nota 1: Para correr el OpenSolver se debe quitar el apostrofe ( ' ) que se encuentra antes de la expresión "RunOpenSolver=True".

Nota 2: Cabe mencionar que para controlar Errores se incluyeron la expresiones: "On Error GoTo Tratar_Errores" (al inicio de la Macro), "Exit Sub", "Tratar_Errores:" y un Mensaje que señala que ha ocurrido un error y el programa ha finalizado (al final de la Macro).

Otro situación a la que nos podemos enfrentar consiste en que queramos que el usuario ingrese los parámetros y restricciones para nuestro modelo. Para lograr lo anterior, creamos una nueva Macro llamada ResolverInteractivo (siguiendo el mismo procedimiento empleado para crear ResolverOculto). A continuación, explicaremos por partes el código programado:

En la imagen anterior se aprecia lo siguiente:

1. Primero, se genera la expresión para el control de errores. 
2. Segundo, se definen las variables "temp" que utilizaremos. 
3. Tercero, se restablecen los parámetros del Solver mediante el comando SolverReset. 
4. Cuarto, se solicita que el individuo indique si quiere Maximizar (1), Minimizar (2) o Alcanzar un Valor Objetivo (3) y se controlan errores de ingreso mediante un comando "Do While". 
5. Quinto, se solicita que el usuario seleccione la celda objetivo.
6. Sexto, se requiere que la persona ingrese las celdas variables.
7. Séptimo, se generan distintos escenarios según el problema que va a resolver el individuo. Lo anterior se hace con el fin de definir el modelo de manera correcta. Cabe señalar que se utiliza la función SolverAceptar señalando la Función Objetivo (definirCelda), el tipo de problema a resolver (valorMáxMín), el valor deseado (valorDe, que es "0" para los problemas 1 y 2) y las celdas variables (celdasCambiantes).  

En la segunda imagen, notamos que:

1. Primero, se pregunta el número de restricciones a ingresar. 
2. Segundo, se definen las variables "aux" que utilizaremos. 
3. Tercero, se crea un ciclo para ingresar el número de restricciones, pidiendo que ingrese el lado izquierdo restricción. Luego, se solicita que señale la relación ingresando un 1 para menor o igual, 2 para igual, 3 para mayor o igual, 4 para enteros y 5 para binarios. 
4. Cuarto, se generan distintos escenarios según el tipo de relación, separando los casos 4 y 5 del resto. La idea es utilizar la función SolverAgregar que requiere el lado izquierdo de la restricción (referenciaCelda), la relación (relación) y el lado derecho de la restricción (Formula, que no corre para los casos 4 y 5).
5. Quinto, se restablecen las variables "aux" y se va a la siguiente iteración.

En la tercera imagen, se aprecia que:

1. Primero, se vuelven a ingresar los parámetros con el SolverAceptar. Lo anterior es requerido por el Solver para poder operar.
2. Segundo, se definen las opciones adicionales del Solver (las dejamos fijas para el caso analizado).
3. Tercero, se ejecuta el Solver sin mostrar el cuadro de diálogo al finalizar. Además, se señala que mantendremos el resultado final encontrado por la aplicación. 
4. Finalmente, aparecen las líneas relacionadas con el tratamiento de errores.

Con todo lo anterior, cualquiera de las dos macros mostradas en la presente entrada debiese arrojar un resultado como el siguiente:

Finalmente, es importante volver a recalcar que debemos tener habilitados los complementos (Solver y OpenSolver). Además, dichos complementos deben ser referenciados en nuestra macro tal y como se mencionó anteriormente.

En el siguiente Link, podrán descargar el Excel utilizado en la siguiente entrada (con sus respectivas Macros incorporadas). Adicionalmente, se entregan los archivos de texto de las Macros programadas:
ResuelveOculto (Texto)

ResuelveInteractivo (Texto)


Referencias:

• Microsoft (Solver en Macros): http://support.microsoft.com/kb/843304/es
• Microsoft (Funciones VBA de Solver): http://msdn.microsoft.com/en-us/library/office/ff196600.aspx
• Microsoft: (Función SolverReset): http://msdn.microsoft.com/en-us/library/office/ff821349.aspx
• Excel-Easy: http://www.excel-easy.com/vba/range-object.html
• Microsoft (Inputbox): http://msdn.microsoft.com/en-us/library/office/ff839468.aspx
• ExcelTip (Referenciar): http://www.exceltip.com/custom-functions/how-to-use-your-excel-add-in-functions-in-vba.html
• OpenSolver: http://opensolver.org/installing-opensolver/

Entrada publicada el 22 de enero de 2013 por Pierre Mariani R.