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.