There is also a base GAMS model underlying this application which is listed below
SETS Supply Locations of supply points
Demand Location of Demand markets;
$Call 'Gdxxrw Excelincharge.XLS skipempty=0 trace=2 index=inputs!g10'
$gdxin excelincharge.gdx
$Load supply demand
set tranparm parameters of transport rate function /fixed, permile/
PARAMETERS Available(supply) Supply available in cases
Needed(demand) demand requirement in cases
Distance(supply,demand) distance in thousands of miles
tranrate(tranparm) transport rate data;
$Load available needed distance tranrate
$gdxin
PARAMETER Cost(supply,demand) transport cost in thousands of dollars per case ;
Cost(supply,demand) = tranrate("Fixed")
+ tranrate("permile") * Distance(supply,demand) / 1000 ;
positive VARIABLES ship(supply,demand) shipment quantities in cases
variable Z total transportation costs in thousands of dollars ;
EQUATIONS COSTacct define objective function
SUPPLYbal(supply) observe supply limits at sources
DEMANDbal(demand) satisfy demand requirements at markets ;
COSTacct .. Z =E= SUM((supply,demand), Cost(supply,demand)*ship(supply,demand)) ;
SUPPLYbal(supply) .. SUM(demand, ship(supply,demand)) =L= Available(supply) ;
DEMANDbal(demand) .. SUM(supply, ship(supply,demand)) =G= needed(demand) ;
MODEL TRANSPORT /ALL/ ;
SOLVE TRANSPORT USING LP MINIMIZING Z ;
parameter misc(*);
misc("cost")=z.l;
misc("modelstat")=transport.modelstat;
Execute_Unload 'excelincharge.gdx',ship, supplybal, demandbal, misc;
Execute 'Gdxxrw Excelincharge.gdx skipempty=0 zeroout=0 trace=2 index=results!e1'
This code goes through several notable stages in terms of the Excel in charge application.
| • | When the code starts up Gdxxrw is called to transfer data for a number of items the just saved version of the excelincharge.xlsspreadsheet into a GDX file. These include the element definitions for the supply and demand sets, along with the data for the available, needed, distance, and tranrate parameters. |
| • | This transfer is controlled by the index field in the spreadsheet starting in g10 as in the blue box below. |
| • | Subsequently I load that data into sets and parameters that have been declared in GAMS. |
| • | In these first two phases I use $Call and $Load rather than Execute and Execute_Load so that I may do compile time domain checking and since the data items are fully defined when the GAMS job starts. (See the discussion of the $Call and Execute choices in the chapter Links to Other Programs Including Spreadsheets and the discussion of $Load and Execute_Load in the Using GAMS Data Exchange or GDX Files chapter). |
| • | Later I prepare to pass data back to the spreadsheet using a parameter array called Misc to pass assorted information including the objective function value and the model solution status indicator. That solution indicator is used in the Excel macros to see if the model solution is optimal in the Visual Basic component optstatus below that is part of the rungams macro. This code searches the results sheet first column for the word Modelstat which is the GAMS model solution status (this item is discussed in the Model Attributes chapter). |
Function optstatus() As String
Dim oResults As Range, oX As Range, nj As Integer, stat As Integer
Set oResults = Worksheets("Results").Range("A1").CurrentRegion
' for each production center, update the results
stat = 0
For nj = 2 To oResults.Rows.Count
If Trim(UCase(oResults.Cells(nj, 1))) = "MODELSTAT" Then
stat = oResults.Cells(nj, 2)
End If
Next
optstatus = "Unknown I cant find model stat"
If stat > 0 Then
Select Case stat
Case 1
optstatus = "Optimal"
Case 2
optstatus = "Optimal"
Case 3
optstatus = "Unbounded"
Case 4
optstatus = "Infeasible"
Case Else
optstatus = "Bad Result from GAMS"
End Select
End If
End Function
| • | Next we unload data to a GDX file using Execute_Unload saving the ship variables, the demandbal and supplybal variables and the Misc parameter. |
| • | Finally Gdxxrw is used to carry the information from the GDX file and place it into the excelincharge.xls spreadsheet. Note that spreadsheet needs to be shared at the time of this write. |
| • | The information sent to the results worksheet is the optimal solution levels for the ship variable and the marginals from the supplybal and demandbal equations as well as the contents of the Misc parameter as controlled by the index command and the spreadsheet range in the blue box below that starts at e1. The clear option is used in these commands to remove the old content and to assure the item order is the same as expected by the Excel workbook. |
| • | In these last two phases I use Execute and Execute_Unload rather than $Call and $Unload so that I may do send the latest results from any solves and calculations. (See the discussion of the $Call and Execute choices in the chapter Links to Other Programs Including Spreadsheets and the discussion of $Unload and Execute_Unload in the Using GAMS Data Exchange or GDX Files chapter. |