LINGO allows you to place a LINGO command script in a range in an Excel spreadsheet and then pass the script to LINGO by means of OLE Automation. This allows you to setup a client-server relationship between Excel and LINGO.

To illustrate this feature, we will once again make use of the staff scheduling model introduced in the Primitive Set Example – Staff Scheduling. This illustration assumes the reader is moderately familiar with the use of Excel Visual Basic macros. If needed, you can refer to the Excel documentation for more background.

Consider the following Excel spreadsheet:

Spreadsheet: STAFOLE1.XLS

We have placed the staffing requirements in the range C16:I16 and assigned the name REQUIREMENTS to this range. We have also assigned the name START to the range C18:I18. LINGO will be sending the solution to the START range. We have also included two graphs in the sheet to help visualize the solution. The graph on the left shows how many employees to start on each day of the week, while the graph on the right compares the number on duty to the number required for each day.

Note that our spreadsheet has a second tab at the bottom titled Model. Select this tab and you will find the following:

Spreadsheet: STAFOLE1.XLS

This page contains the command script we will use to solve the staffing model. For more information on command scripts, refer to LINGO Command Scripts. In line 1, we turn on terminal echoing, so LINGO will echo the command script to the command window as it is read. Lines 2 through 21 contain the text of the model, which should be familiar by now. Note, in the data section, we are using two @OLE functions—the first to import the data from the spreadsheet and the second to export the solution back to the spreadsheet. The data is read from the range named REQUIRED and the solution is written to the START range on the first tab of the sheet. In line 22, we use the GO command to solve the model. We have also assigned the range name MODEL to the range that contains this script (Model!A1:A23).

Given that we have our LINGO command script contained in our spreadsheet, the next question is how we pass it to LINGO to run it. This is where OLE Automation comes in. If you recall, the first tab of our sheet (the tab labeled Data) had a button titled Solve. We added this button to the sheet and attached the following Excel Visual Basic macro to it:

Sub LINGOSolve()

 Dim iErr As Integer

 iErr = LINGO.RunScriptRange("MODEL")

 If (iErr > 0) Then

   MsgBox ("Unable to solve model")

 End If

End Sub

We use OLE Automation to call the LINGO exported method RunScriptRange, passing it the range name MODEL. This, of course, is the name of the range that contains the command script. The RunScriptRange routine calls Excel to obtain the contents of the range and begins processing the commands contained therein. Processing continues until either a QUIT command is encountered or there are no further commands remaining in the range.

RunScriptRange will return a value of 0 if it was successfully able to que the script for processing. If RunScriptRange was not successful, it will return one of the following error codes:

We have also added the following Auto_Open macro to the sheet:

Dim LINGO As Object

Sub Auto_Open()

  Set LINGO = CreateObject("LINGO.Document.4")

End Sub

An Auto_Open macro is automatically executed each time a sheet is opened. We declare LINGO as an object and attach the LINGO object to the LINGO application with the CreateObject function

Now, go back to the first tab on the workbook and press the Solve button. After a brief pause, you should see the optimal solution installed, so the sheet resembles:

Spreadsheet: STAFOLE1.XLS

The optimal number of employees to start on each day of the week are now contained in the START range (C18:I18), and the graphs have been updated to reflect this solution.

Editing Models Stored in Excel

In the example above, we have placed the LINGO model on a tab in an Excel workbook. There will be times when you may wish to load this model back into LINGO for testing and development purposes. You could certainly do this by cutting-and-pasting the model text back and forth from LINGO to Excel, but LINGO has a two commands to simplify the process.

To temporarily load a model from Excel into LINGO, you can use the File|Excel|Read Model from Range command. This command will prompt you for the workbook name that you wish to retrieve the model from. It will then display a list of range names found in the model, as seen here:

In this example, the model is stored in the range titled MODEL. Selecting MODEL from the list and then clicking on the OK button, you will then be presented with a copy of the model in the LINGO GUI, ready for testing and editing:

Once you have the model ready to return Excel, you may use the File|Excel|Write Model to Range command to return the model to the original Excel range. Note that if the range is too small for the entire model, you will receive an error message. On the other hand, if the range is larger than required, LINGO will erase the content of the range from the end of the model text to the end of the workbook range, so that no spurious text remains after the end of the new model text. Given this, it's a good idea to always use ranges that are actually larger than required by the model text.