In this session, you will learn how to enter a simple Visual Basic program in Excel that will read in and solve an existing MPL model and then displays the result in a message box.
After starting Microsoft Excel, make sure you have an open empty spreadsheet. To create one press the New button on the Excel Standard toolbar.
Press the Command Button on the Control Toolbox. This will cause you to enter design mode and a small box Edit Design Mode tool bar to appear. Place the cross-hairs curser on the spreadsheet and drag a box to create a Command Button. When you release the mouse the Button will be drawn and automatically receive the name CommandButton1. While in the Design Mode you can edit the properties of the Command Button by right mouse clicking on the Button and choosing properties.
Right-mouse click on the Command Button you created and choose Properties from the pull-down
menu. This will cause the Properties window to pop up so that you can edit various properties for the
Command Button. Change the (Name) property to cmdSolve to give the button a more descriptive name than
CommandButton1. It is customary to start command buttons names with the prefix cmd.
You can also change the caption for the button by changing the Caption property to
Solve Planning Model to reflect that pressing the button will solve a model. Close the properties window.
Right-mouse click on the Solve Planning Model Button you created and choose View Code from the pull-down menu. This will start the Microsoft Visual Basic editor where you can enter your code. You will see a window named Book1 - Sheet1 (Code) with the following code fragment already entered:
Private Sub cmdSolve_Click()
End Sub
This is the Visual Basic subroutine that will be automatically called each time you press cmdSolve (previously named CommandButton1).
From the Tools menu choose References. This will display a dialog box called References - VBAProject which contains a list of all available object library references on the machine. You should find the reference for OptiMax under the name MPL OptiMax 1.0 Object Library. Select it by clicking on it and press the OK button to close the dialog. This will allow your Visual Basic project to refer to all the objects, methods, and properties that are defined in OptiMax.
Enter the following code inside the subroutine:
Private Sub cmdSolve_Click()
Dim MPL As OptiMax
Dim planModel As Model
Dim result As Integer
Set MPL = New OptiMax
MPL.WorkingDirectory = "c:\mplwin4"
MPL.Solvers.Add "cplex"
Set planModel = MPL.Models.Add("Planning")
result = planModel.ReadModel("planning.mpl")
If result > 0 Then
MsgBox planModel.ErrorMessage
Else
planModel.Solve
MsgBox planModel.Solution.ResultString & ": " & _
planModel.Solution.ObjectValue
End If
End Sub
As you type in the code you will see that the Visual Basic editor automatically shows valid entries for OptiMax objects. This is the result of having added a reference to the MPL OptiMax Object Library in Step 4.
Switch back to Microsoft Excel by pressing the View Microsoft Excel button in the toolbar. Press the Exit Design Mode button on the Control Toolbox toolbar in Excel to exit the design mode so you can run the program.
Press the Solve Planning Model Button to run the Visual Basic program you entered. If everything works correctly, you should see a dialog pop up with the message:
Optimal Solution Found: 19422800
which is the correct solution for the planning.mpl model.
If you get any Visual Basic errors, first check the program to see if it was entered correctly. If you can't see what the problem is, refer to the Troubleshooting section later in this chapter for suggestions.
We are going to use this workbook as a starting point for the next session so save it now to the hard disk. You can name the file OptimaxTutor1.xls, as this is the first session of the OptiMax tutorial.
We will now describe each part of the program you entered in detail so you get a good understanding of how to write programs using the MPL OptiMax component library.
Private Sub cmdSolve_Click()
The name of the subroutine is cmdSolve_Click. This routine is automatically called by Visual Basic every time the user presses the cmdSolve button.
Dim MPL As OptiMax
This line declares the main MPL OptiMax object variable. This variable is often named MPL, although you can call it anything else you prefer. The OptiMax class name is recognized by Visual Basic since we added a reference to the MPL OptiMax Type Library in Step 4. After the MPL object variable has been defined, it can be used to access all other objects of the OptiMax component library.
Dim planModel As Model
This declares the object variable planModel to be of the type Model, which is another class name that is part of the MPL OptiMax object model
Dim result As Integer
This declares the result variable to be of the type integer. This variable will be used to store the results of reading the planning.mpl model file.
Set MPL = New OptiMax
After an object variable has been declared, it needs to be set or assigned before it is used in the program. This line allocates a new object instance of the OptiMax class and assigns it to the MPL object variable.
MPL.WorkingDirectory = "c:\mplwin4"
When running models with MPL OptiMax it is necessary, in most cases, to specify where the model and solver files are stored. You can specify a default working directory by setting the WorkingDirectory property, which is normally a good idea.
MPL.Solvers.Add "cplex"
When OptiMax starts, it does not know which solvers you want to use to solve models in your program. You can add supported solvers to the Solvers collection by using the Add() method. The argument can either be the filename for the solver or the name of the solver, in which case the default filename will be used. If OptiMax has difficulty locating or loading the solver, check first if the solver can be successfully used by the MPL for Windows standalone application. You can also specify the full path for the solver in the argument to make sure the correct solver is being loaded.
Set planModel = MPL.Models.Add("Planning")
In order to be able to read and solve models in OptiMax, you need to create a model object. You do this by using the Add() method of the Models collection. The argument specifies the name of the model object that is created. We place the resulting object into the planModel object variable.
result = planModel.ReadModel("planning.mpl")
The most common way to read in a model into OptiMax is using the ReadModel() method of the planModel object. This method will read in a standard MPL or MPS model file with the filename given as argument from the current working directory.
If result > 0 Then
MsgBox planModel.ErrorMessage
If there were any problems reading in the model file, the result variable will have a value greater than zero and the ErrorMessage property of the planModel object will contain a description of the problem. A message box will pop up containing the error message if this is the case.
Else
planModel.Solve
If there were no problems, we can go right ahead and solve the model using the Solve() method. OptiMax will automatically pick the solver we added above (using the MPL.Solvers.Add method) since it was the only solver loaded. If you are using more than one solver in your program, you can specify which one you want to use as an argument to the Solve() method.
MsgBox planModel.Solution.ResultString & ": " & _
planModel.Solution.ObjectValue
The results of the optimization will be placed in the Solution object of the planModel object. We retrieve the ResultString and ObjectValue properties of the Solution object and display the results in a message dialog box for the end-user.
If this is the first time you are creating a Visual Basic program in Excel you may not be familiar with the error messages you can receive when there is a mistake in your program. Here are few of the possible error messages with suggestions on how to fix them.
As you debug your program please note that you can switch from Excel to Visual Basic by pressing the Visual Basic Editor button in the Visual Basic toolbar. In similar fashion you can switch from Visual Basic back to Excel by pressing the View Microsoft Excel button in the toolbar.
Please note also that before you can make some of the edits or run the program again you need to stop the current run by pressing the Reset button in Visual Basic. This is because the program is still running in Debug mode even after the error message has popped up.
User-defined type not defined
If you receive this error message with the "Dim MPL As OptiMax" line highlighted, this means that the reference for MPL OptiMax has not been added as described in Step 4. Please stop the current program run, by pressing the Reset button, and then make sure the MPL OptiMax 1.0 Object Library entry is selected in the Tools | References dialog.
Syntax Error
This error message means that the program line does not contain a valid Visual Basic code. Please check the entry for errors and, if necessary, consult the Visual Basic documentation for the correct syntax.
Run-time error '91': Object variable or With block not set
This error message normally means one of two things:
1) That you have forgotten to use the Set keyword before assigning an object variable. For example, in the code you entered, both MPL and planModel are object variables that can only be assigned using the Set keyword. To fix this, press the Debug button and then verify that the assignment in the line starts with the Set keyword.
2) That you are referring to an object variable that was correctly assigned before. Please check to see if the variable was not correctly declared with a Dim statement and then assigned with a Set statement.
Run-time error '438': Object doesn't support this property or method
This error means you are trying to refer to a property or a method for an object that is not available. Normally there is either a misspelling, or the property (or method) is defined for a different object. Check the object model documentation to see what the correct name and usage is.