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.
This topic has been locked.
4 replies Latest Post - 2013-02-06T18:07:51Z by SystemAdmin
Pinned topic CPLX and VBA
Answered question This question has been answered.
Unanswered question This question has not been answered yet.
Updated on 2013-02-06T18:07:51Z at 2013-02-06T18:07:51Z by SystemAdmin
Re: CPLX and VBA2013-01-28T18:58:52Z in response to SystemAdminThere 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
Re: CPLX and VBA2013-01-31T23:12:12Z in response to SystemAdminThank 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
Re: CPLX and VBA2013-02-05T06:26:52Z in response to SystemAdminI 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.