Originally posted by: nima_salehi
Hi,
I am trying to run the CPLEX engine for a model that I created in Excel VBA. It is a big model and I cannot assign one cell to every variable I have. So my question is: Is it possible for my to define variables in VBA for the parameters of my model and then choose some cell as the variables of my model and define the constraints and the objective in the VBA code instead of the Excel sheet?
Here is what I did so far (it is not working):
Dim BinVars As Range
Set BinVars = Range(Worksheets("Sheet2").Cells(3, 2), Worksheets("Sheet2").Cells(5, 2))
'CPXaddVariable Variable:=BinVars, Binary:=True
Dim CapVars As Range
Set CapVars = Range(Worksheets("Sheet2").Cells(3, 4), Worksheets("Sheet2").Cells(5, 14))
CPXaddVariable Variable:=CapVars, Binary:=True
Dim Flows() As Variant 'I have too many variables in this group and I cannot assign a cell for each of them
ReDim Flows(1 To n, 1 To m, 1 To l) As Variant
CPXaddVariable Variable:=Flows, Lb:=0
Dim ObjCoefs1() As Double
ReDim ObjCoefs1(1 To n, 1 To m) As Double
Dim ObjCoefs2() As Double
ReDim ObjCoefs2(1 To n, 1 To m, 1 To l) As Double
For i = 1 To n
For j = 1 To m
TotalCost1 = TotalCost1+ sc(i).Coef1(j) * CapVars(i, j)
For k = 1 To l
TotalCost2 = TotalCost2 + sc(i).Coef2(j) * Flows(i, j, k)
Next k
Next j
Next i
Total = TotalCost1 + TotalCost2
Worksheets("Sheet2").Cells(13, 19).Value = Tot
CPXsetObjective ObjCell:=Worksheets("Sheet2").Cells(13, 19), Sense:=2
' Solve the problem.
CPXsolve
#CPLEXOptimizers#DecisionOptimization