Originally posted by: SanW

Dear All

I have coded a column generation procedure with VBA and cplex (version 12.5) in Office Excel 2010 in an attempt to solve a vehicle routing problem, but I am unable to make VBA print the shadow prices (Would prefer to an array but printing to sheet is also acceptable). Our goal with using the cplex solver is to reoptimize a problem several times by using the Shadow Prices, but the only option we have found so far is by using the dialogbox at the end of the optimization add-in, and manually choose what and where to post the shadow prices. This approach is not really viable if we intend to include it in a VBA loop.

Any ideas on how to do this? Using c++ is not an option for this particular problem so any suggestions would be highly appreciated.

This is the VBA sub calling cplex:

Sub cplexsolverforIP()
Dim lastColumn As Integer
Dim nodialog As Variant
Dim Obj As Range
Dim Vars As Range
Dim ObjCoefs As Range
    Application.ScreenUpdating = False
    
'Find the last used column
    lastColumn = ActiveSheet.Cells(8, Columns.Count).End(xlToLeft).Column

' Clear the current values in cplex
    CPXclear
    
    ' Objective coefs. The idea is to redim the area to optimized to only go from column 4 to the last used column.
    Set Vars = Range(ActiveSheet.Cells(4, 11), ActiveSheet.Cells(4, lastColumn))
    
    ' Row 3 is a series of costs
    Set ObjCoefs = Range(ActiveSheet.Cells(3, 11), ActiveSheet.Cells(3, lastColumn))
    
' Set the objective value to minimize
    Set Obj = ActiveSheet.Cells(3, 8)
    Obj.Formula = "=SUMPRODUCT(" & Vars.Address & "," & ObjCoefs.Address & ")"
    CPXsetObjective ObjCell:=Obj, Sense:=2
    
    ' Variables
    CPXaddVariable Variable:=Range(ActiveSheet.Cells(4, 11), ActiveSheet.Cells(4, lastColumn)), Lb:=0
    
    ' Constraints
    CPXaddConstraint Constraint:=Range(ActiveSheet.Cells(5, 10), ActiveSheet.Cells(100, 10)), Lb:=30

    ' Solve
        CPXsetSpecial LinOrQuad:=True
        nodialog = True
    CPXsolve (nodialog)

    Application.ScreenUpdating = True
End Sub

Thank you in advance for your explanations and your solutions.

I'm afraid there is no way to get shadow prices using the VBA functions/macros that the CPLEX Excel connector provides. The only way to get them is via sensitivity analysis in the interactive dialog.

Would it be an option to use VB.NET or C#.NET? In those languages you can use the rich Concert API which provides easy access to this information.