• Overview
• Examples
• Manuals
• References

 

AMPL-COM Example

How to create your first AMPL-COM Program in Excel Visual Basic.

This simple example walks you through the steps involved in creating a Visual Basic program in Excel that will read in and solve an existing AMPL model and then displays the result in a message box.

Step 1: Open a New Empty Spreadsheet:

After starting Microsoft Excel, make sure you have an open empty spreadsheet. To create one, press the ‘New’ button on the Excel standard toolbar.

Step 2: Create a Command Button:

Press the ‘Command Button’ on the Control toolbox. This will cause you to enter design mode and a small box ‘Edit Design Mode’ toolbar 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’. Meanwhile, in the Design Mode you can edit the properties of the Command Button by right mouse clicking on the Button and choosing properties.

Step 3: Change the Properties for the Command Button:

Right-mouse click on the Command Button you have 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. Close the properties window.

Step 4: Enter the Visual Basic code editor:

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).

Step 5: Add a Reference for AMPL-COM Object Library:

From the ‘Tools’ menu choose ‘References’. This will display a dialog box called References – VBA Project which contains a list of all available object library references on the machine. You should find the reference for AMPL under the name-AMPLCom 1.0 Type 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 AMPL-COM.

Step 6: Enter the Visual Basic code to call AMPL-COM:

Enter the following code inside the subroutine:

            Private Sub cmdSolve_Click()

                        Dim myAmpl As AMPLCOMLib.Ampl
                        Dim myModelCollection As AMPLCOMLib.ModelCollection
                        Dim myModel1ObjectiveCollection As AMPLCOMLib.AMPLObjectivesCollection
                        Dim IterObjective As AMPLCOMLib.objective
                        Dim myModel1 As AMPLCOMLib.Model
                        Dim sol As AMPLCOMLib.Solution

                        Set myAmpl = New AMPLCOMLib.Ampl
                        Set myModelCollection = myAmpl.AMPLModelCollection

                        myModelCollection.Add ("MyModel1")

                        Set myModel1 = myModelCollection.Item("MyModel1")

                        myModel1.WorkingDirectory = “c:\MyModel\”

                        myModel1.ReadModel ("Steel.mod")
                        myModel1.ReadData ("Steel.dat")
                        myModel1.Option ("solver afortmp;")

                        myModel1.Solve

                        Set sol = myModel1.Solution

                        Set myModel1ObjectiveCollection = myModel1.Objectives

                        For Each IterObjective In myModel1ObjectiveCollection
                                    If (IterObjective.Name = "total_profit") Then
                                                ObjectiveValue = IterObjective.CurrentValue
                                    End If
                        Next

                        MsgBox ObjectiveValue
            End Sub

As you type in the code you will see that the Visual Basic editor automatically shows valid entries for AMPL-COM objects. This is the result of having added a reference to the AMPL Object Library in Step 4.

Step 7: Switch back from Visual Basic to Excel:

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.

Step 8: Run the Steel Model from Excel:

Press the ‘Solve Steel 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: 192,000

which is the correct solution for the Steel.mod model.

Step 9: Troubleshooting Visual Basic Errors:

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, then refer to the ‘Troubleshooting’ section later in this chapter for suggestions.

Step 10: Save the Excel Workbook:

We are going to use this workbook as a starting point for the other session so save it now to the hard disk. You can name the file AMPLCOMStepByStep1.xls, as this is the first session of the AMPL-COM tutorial.