Topic
• 5 replies
• Latest Post - ‏2015-12-30T06:01:11Z by nima_salehi
7929 Posts

# Pinned topic CPLX and VBA

‏2013-01-25T19:27:43Z |
Dear All,

I have created a model in CPLEX 12.5 and want to call and solve the model from VBA in Excel, any idea?
I tried the CPLEX add-in but didn't work so I just want to call the model from a vba application and solve it.

Regards,
Updated on 2013-02-06T18:07:51Z at 2013-02-06T18:07:51Z by SystemAdmin
7929 Posts

#### Re: CPLX and VBA

‏2013-01-28T18:58:52Z
There is not direct support for using CPLEX from VBA in case the Excel connector does not provide what you need.
But you can embed CPLEX like any other DLL into VBA. I see two ways to do that:
1. Call out to an external application that is implement in VB.NET or C#.NET and uses CPLEX. To this end you need to use the COM interface. See the Windows documentation of COM for further details.
2. Embed the CPLEX dll directly into VBA. This is the same for any dll and instructions can be found in the Windows documentation. Here is a small code snippet that loads a problem from a file, solves it and displays the optimal objective function value:

Declare Function CPXopenCPLEX Lib
"C:\path\to\dll\cplex125.dll" (ByRef status As Integer) As Long Declare Function CPXcreateprob Lib
"C:\path\to\dll\cplex125.dll" (ByVal env As Long, ByRef status As Integer, ByVal name As String) As Long Declare Function CPXreadcopyprob Lib
"C:\path\to\dll\cplex125.dll" (ByVal env As Long, ByVal lp As Long, ByVal file As String, ByVal filetype As String) As Integer Declare Function CPXlpopt Lib
"C:\path\to\dll\cplex125.dll" (ByVal env As Long, ByVal lp As Long) As Integer Declare Function CPXgetobjval Lib
"C:\path\to\dll\cplex125.dll" (ByVal env As Long, ByVal lp As Long, ByRef obj As Double) As Integer Declare Function CPXcloseCPLEX Lib
"C:\path\to\dll\cplex125.dll" (ByRef env As Long) As Integer   Sub Solve() Dim env As Long Dim lp As Long Dim status As Integer
' Create an environment. env = CPXopenCPLEX(status) If status  0 Then MsgBox
"CPXopenCPLEX failed: " & CStr(status) Return End If
' Create a problem. lp = CPXcreateprob(env, status,
"problem") If status  0 Then MsgBox
"CPXcreateprob failed: " & CStr(status) GoTo TERMINATE End If status = CPXreadcopyprob(env, lp,
"problem.lp",
"lp") If status  0 Then MsgBox
"CPXreadcopyprob failed: " & CStr(status) GoTo TERMINATE End If
' Optimize status = CPXlpopt(env, lp) If status  0 Then MsgBox
"CPXlpopt failed: " & CStr(status) GoTo TERMINATE End If
' Get objective value Dim obj As Double status = CPXgetobjval(env, lp, obj) If status  0 Then MsgBox
"CPXgetobjval failed: " & CStr(status) GoTo TERMINATE End If MsgBox
"Optimal objective: " & CStr(obj) TERMINATE: CPXcloseCPLEX (env) End Sub

7929 Posts

#### Re: CPLX and VBA

‏2013-01-31T23:12:12Z
There is not direct support for using CPLEX from VBA in case the Excel connector does not provide what you need.
But you can embed CPLEX like any other DLL into VBA. I see two ways to do that:
1. Call out to an external application that is implement in VB.NET or C#.NET and uses CPLEX. To this end you need to use the COM interface. See the Windows documentation of COM for further details.
2. Embed the CPLEX dll directly into VBA. This is the same for any dll and instructions can be found in the Windows documentation. Here is a small code snippet that loads a problem from a file, solves it and displays the optimal objective function value:
<pre class="jive-pre"> Declare Function CPXopenCPLEX Lib "C:\path\to\dll\cplex125.dll" (ByRef status As Integer) As Long Declare Function CPXcreateprob Lib "C:\path\to\dll\cplex125.dll" (ByVal env As Long, ByRef status As Integer, ByVal name As String) As Long Declare Function CPXreadcopyprob Lib "C:\path\to\dll\cplex125.dll" (ByVal env As Long, ByVal lp As Long, ByVal file As String, ByVal filetype As String) As Integer Declare Function CPXlpopt Lib "C:\path\to\dll\cplex125.dll" (ByVal env As Long, ByVal lp As Long) As Integer Declare Function CPXgetobjval Lib "C:\path\to\dll\cplex125.dll" (ByVal env As Long, ByVal lp As Long, ByRef obj As Double) As Integer Declare Function CPXcloseCPLEX Lib "C:\path\to\dll\cplex125.dll" (ByRef env As Long) As Integer Sub Solve() Dim env As Long Dim lp As Long Dim status As Integer ' Create an environment. env = CPXopenCPLEX(status) If status 0 Then MsgBox "CPXopenCPLEX failed: " & CStr(status) Return End If ' Create a problem. lp = CPXcreateprob(env, status, "problem") If status 0 Then MsgBox "CPXcreateprob failed: " & CStr(status) GoTo TERMINATE End If status = CPXreadcopyprob(env, lp, "problem.lp", "lp") If status 0 Then MsgBox "CPXreadcopyprob failed: " & CStr(status) GoTo TERMINATE End If ' Optimize status = CPXlpopt(env, lp) If status 0 Then MsgBox "CPXlpopt failed: " & CStr(status) GoTo TERMINATE End If ' Get objective value Dim obj As Double status = CPXgetobjval(env, lp, obj) If status 0 Then MsgBox "CPXgetobjval failed: " & CStr(status) GoTo TERMINATE End If MsgBox "Optimal objective: " & CStr(obj) TERMINATE: CPXcloseCPLEX (env) End Sub </pre>
Thank you Daniel. Where should I put the problem.lb file?

Also, when I implemented the code in VBA 7.0 (adding "PtrSafe" after the "Declare" statements), it gives an error saying "return without going to sub".

When I implement the code in Visual Basic 6.5, it says "dll file not found" even though it is there.
Thanks again for your help and support

Faisal
7929 Posts

#### Re: CPLX and VBA

‏2013-02-05T06:26:52Z
Thank you Daniel. Where should I put the problem.lb file?

Also, when I implemented the code in VBA 7.0 (adding "PtrSafe" after the "Declare" statements), it gives an error saying "return without going to sub".

When I implement the code in Visual Basic 6.5, it says "dll file not found" even though it is there.
Thanks again for your help and support

Faisal
I think we solved all your problems in a private chat. Here are the issues that we had to clear:
• In the code I posted there was a bug: Instead of "status = 0" it should read "status 0" since only a non-zero status indicates failure.
• The problem.lp file should be in the same directory in which Excel runs. Alternatively, use an absolute path to that file.
• If the .dll is not found then try using an absolute path to that .dll.
7929 Posts

#### Re: CPLX and VBA

‏2013-02-06T18:07:51Z
I think we solved all your problems in a private chat. Here are the issues that we had to clear:
• In the code I posted there was a bug: Instead of "status = 0" it should read "status 0" since only a non-zero status indicates failure.
• The problem.lp file should be in the same directory in which Excel runs. Alternatively, use an absolute path to that file.
• If the .dll is not found then try using an absolute path to that .dll.
Thank you so much Daniel. Yes, that works perfect.
• nima_salehi
2 Posts

#### Re: CPLX and VBA

‏2015-12-30T06:01:11Z

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))

Dim CapVars As Range
Set CapVars = Range(Worksheets("Sheet2").Cells(3, 4), Worksheets("Sheet2").Cells(5, 14))

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

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