Topic
  • 4 replies
  • Latest Post - ‏2013-02-06T18:07:51Z by SystemAdmin
SystemAdmin
SystemAdmin
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
  • SystemAdmin
    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
    
  • SystemAdmin
    SystemAdmin
    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
  • SystemAdmin
    SystemAdmin
    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.
  • SystemAdmin
    SystemAdmin
    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.