Topic
  • 14 replies
  • Latest Post - ‏2014-01-02T08:12:20Z by AlexFleischer
cplex_user
cplex_user
1 Post

Pinned topic Integrating OPL in VBA project

‏2012-08-28T12:15:48Z |
Hello guys!

I am currently in the urge of having to design an Arena simulation [1] that uses its VBA interface (based on VB 6.0) to integrate the CPLEX solver. I have already succeeded in writing a wrapper with corresponding "Declare Function CPXabc 'cplex122.dll'" statements to use the callable library.

However, I now would like to go one step further and benefit from the OPL advantages as well. Is there any easy way to achieve this? So far, I only found examples mentioned the use of the .NET interface, but the Arena VB interface is too old for this (if I am wrong, feel free to correct me).

Any hints are highly appreciated!

Cheers,
Fabian

[1] http://www.arenasimulation.com/Arena_Home.aspx
Updated on 2013-01-27T19:29:46Z at 2013-01-27T19:29:46Z by SystemAdmin
  • SystemAdmin
    SystemAdmin
    1883 Posts

    Re: Integrating OPL in VBA project

    ‏2013-01-27T19:29:46Z  
    Hi Fabian,
    I am working on the same idea. Can you please let me know how you did the first step.

    Than you.
  • jaunfra
    jaunfra
    4 Posts

    Re: Integrating OPL in VBA project

    ‏2013-05-15T10:03:18Z  

    Hi Fabian
    im working on my master thesis and i need to integrate arena and cplex, using the callable library, but im having a lot of problems trying to do it, do you know how to do it or do have some code to check and then try to make my own code, i will really appreciate it!

    thanks

    juanfra

  • AlexFleischer
    AlexFleischer
    1248 Posts

    Re: Integrating OPL in VBA project

    ‏2013-05-16T10:42:26Z  

    Hi,

    have you tried regasm on oplall.dll ?

     

    and then you can write codes like

     

    Dim oplF As New OplFactory

        Dim errHandler As OplErrorHandler

        Dim modelSource As OplModelSource

        Dim Settings As OplSettings

        Dim cplex As cplex

        Dim def As OplModelDefinition

        Dim opl As OplModel

        Dim dataSource As OplDataSource

       

        'oplF.DebugMode = True

       

        Set errHandler = oplF.CreateOplErrorHandler()

     

    ?

     

    regards

  • m.dehghani86
    m.dehghani86
    7 Posts

    Re: Integrating OPL in VBA project

    ‏2013-12-25T04:20:51Z  

    Hi,

    have you tried regasm on oplall.dll ?

     

    and then you can write codes like

     

    Dim oplF As New OplFactory

        Dim errHandler As OplErrorHandler

        Dim modelSource As OplModelSource

        Dim Settings As OplSettings

        Dim cplex As cplex

        Dim def As OplModelDefinition

        Dim opl As OplModel

        Dim dataSource As OplDataSource

       

        'oplF.DebugMode = True

       

        Set errHandler = oplF.CreateOplErrorHandler()

     

    ?

     

    regards

    can you explain more details about this code?

    did you write this code in arena to call CPLEX?

    please help me how can I call Cplex in Arena, if you have any simple example please let me know.

  • AlexFleischer
    AlexFleischer
    1248 Posts

    Re: Integrating OPL in VBA project

    ‏2013-12-25T19:56:33Z  

    can you explain more details about this code?

    did you write this code in arena to call CPLEX?

    please help me how can I call Cplex in Arena, if you have any simple example please let me know.

    Hi,

    this is some VBA code to call CPLEX with the OPL API

    If you want to call CPLEX directly, what you may do is get some ideas from the examples *.bas in

    CPLEX_Studio126\cplex\examples\src\excel

    regards

  • m.dehghani86
    m.dehghani86
    7 Posts

    Re: Integrating OPL in VBA project

    ‏2013-12-25T23:32:03Z  

    Hi,

    this is some VBA code to call CPLEX with the OPL API

    If you want to call CPLEX directly, what you may do is get some ideas from the examples *.bas in

    CPLEX_Studio126\cplex\examples\src\excel

    regards

    Thank you for your reply

    I used those samples but unfortunately VBA shows me an error (I attached the error)

    I don't know how can I figure it out.

    I couldn't add CPLEX add-ins to my Excel because my widows is 64 bit. so,  I'm just wondering can I  call CPLEX in VBA without CPLEX add-ins or it's impossible?

    if you can please attach very simple excel or arena file. I really appreciate your help

     

    Mohammad

    Attachments

  • AlexFleischer
    AlexFleischer
    1248 Posts

    Re: Integrating OPL in VBA project

    ‏2013-12-26T06:19:58Z  

    Thank you for your reply

    I used those samples but unfortunately VBA shows me an error (I attached the error)

    I don't know how can I figure it out.

    I couldn't add CPLEX add-ins to my Excel because my widows is 64 bit. so,  I'm just wondering can I  call CPLEX in VBA without CPLEX add-ins or it's impossible?

    if you can please attach very simple excel or arena file. I really appreciate your help

     

    Mohammad

    HI,

    as you can see at

    http://pic.dhe.ibm.com/infocenter/cosinfoc/v12r3/index.jsp?topic=%2Filog.odms.studio.help%2FContent%2FOptimization%2FDocumentation%2FOptimization_Studio%2F_pubskel%2Fps_COS_Eclipse31.html

    the excel add-in works with 64 bits

    Regards

  • m.dehghani86
    m.dehghani86
    7 Posts

    Re: Integrating OPL in VBA project

    ‏2013-12-26T16:42:39Z  

    Thank you again Alex.

    my version is 12.5, I will try to get 12.3 somehow and use it.

    would you please attach a VBA example for me, I know CPLEX provided 3 examples but I cannot use them. they show me error 1004: 

    Run-time error "1004"

    cannot run the macro 'CPLEX.CLear'. The macro may not be avaiable in this workbook or all macros bay be disabled

    I just could add coplexvba.bas to VBA. is that enough for running a VBA model?

     

    Thanks

  • AlexFleischer
    AlexFleischer
    1248 Posts

    Re: Integrating OPL in VBA project

    ‏2013-12-27T08:54:40Z  

    Thank you again Alex.

    my version is 12.5, I will try to get 12.3 somehow and use it.

    would you please attach a VBA example for me, I know CPLEX provided 3 examples but I cannot use them. they show me error 1004: 

    Run-time error "1004"

    cannot run the macro 'CPLEX.CLear'. The macro may not be avaiable in this workbook or all macros bay be disabled

    I just could add coplexvba.bas to VBA. is that enough for running a VBA model?

     

    Thanks

    Hi,

    12.5 should be fine, why do you want to move back to 12.3 ?

    Plus do not forget to use regasm

    regards

  • m.dehghani86
    m.dehghani86
    7 Posts

    Re: Integrating OPL in VBA project

    ‏2013-12-27T22:59:38Z  

    Hi,

    12.5 should be fine, why do you want to move back to 12.3 ?

    Plus do not forget to use regasm

    regards

    Actually, I recently started to use CPLEX, I need use CPLEX to use in Arena Rockwell (simulation software), but I have no idea how can I start? I read CPLEX manual but I couldn't use CPLEX. I cannot even add CPLEX to Excel. I found CPLEX xll file but I can not add it to excel as add-in. I'm totally lost. is there any video or manual which describes combination of VBA and CPLEX step b step? 

    thanks

     

     

  • AlexFleischer
    AlexFleischer
    1248 Posts

    Re: Integrating OPL in VBA project

    ‏2013-12-28T14:52:23Z  

    Actually, I recently started to use CPLEX, I need use CPLEX to use in Arena Rockwell (simulation software), but I have no idea how can I start? I read CPLEX manual but I couldn't use CPLEX. I cannot even add CPLEX to Excel. I found CPLEX xll file but I can not add it to excel as add-in. I'm totally lost. is there any video or manual which describes combination of VBA and CPLEX step b step? 

    thanks

     

     

    Hi,

    have you had a look at

    http://pic.dhe.ibm.com/infocenter/cosinfoc/v12r2/index.jsp?topic=%2Filog.odms.cplex.help%2FContent%2FOptimization%2FDocumentation%2FCPLEX%2F_pubskel%2FCPLEX1246.html

    ?

    regards

  • m.dehghani86
    m.dehghani86
    7 Posts

    Re: Integrating OPL in VBA project

    ‏2013-12-28T18:00:21Z  

    Yes I did before... I folowed the steps but I got problem. now today I found the cause, in order to Resolving the problem, I need Uninstall Office 2010 64-bit from client device. Re-install Office 2010 32-bit instead.

     

    Actually I dont need to use CPLEX in Ecxel, can you tell me how can I call CPLEX in Arena VBA...this my main issue

    as I've said before, can you attach any file for me?

     

    thanks

  • m.dehghani86
    m.dehghani86
    7 Posts

    Re: Integrating OPL in VBA project

    ‏2014-01-01T05:24:18Z  

    Can any body explain me what is wrapper?  in first post Fabina said "

     I have already succeeded in writing a wrapper with corresponding "Declare Function CPXabc 'cplex122.dll'" statements to use the callable library.

    do I need to add cplexXXX.dd to excel VBA before using it?

    Thanks

  • AlexFleischer
    AlexFleischer
    1248 Posts

    Re: Integrating OPL in VBA project

    ‏2014-01-02T08:12:20Z  

    Yes I did before... I folowed the steps but I got problem. now today I found the cause, in order to Resolving the problem, I need Uninstall Office 2010 64-bit from client device. Re-install Office 2010 32-bit instead.

     

    Actually I dont need to use CPLEX in Ecxel, can you tell me how can I call CPLEX in Arena VBA...this my main issue

    as I've said before, can you attach any file for me?

     

    thanks

    Hi,

    what you can also do in your VBA code:

    1) Fill an excel sheet with instructions like

    Cells(1, 1).Value = 12345

    2) Then call an OPL model through the VBA instruction Shell

    NB:

    The OPL model will take the excel file you described in your VBA code a input like the example OilSheet. You will use SheetRead for that.

     

    regards