Topic
4 replies Latest Post - ‏2013-12-13T17:30:59Z by AlexFleischer
AndrewBullock
AndrewBullock
12 Posts
ACCEPTED ANSWER

Pinned topic Providing optimisation status to user with VBA -> COM -> .NET -> OPL

‏2013-11-29T08:04:18Z |

I have a simple piece of VBA that is triggering a large OPL optimisation through VBA -> COM wrapper -> .NET -> OPL and I want to provide the user with more granular status information than that provided by checkpointing the stages of New OplFactory, Factory.CreateOplProject, Project.MakeRunConfiguration, RunConfiguration.getOplModel(), Model.Generate, Model.Cplex.solve() and Model.PostProcess. The optimisation takes upwards of 10 minutes and so some reassurance to the user that all is well is needed, particularly during Cplex.solve. I'm trying to get the engine log and other logs passed back to the user in real time but I'm struggling to make this happen. Obviously all of this information is available through the IDE, but they need to run it from the VBA.

My approaches and their issues have been:

  1. Use .setOut and create a TextWriter event handler in VBA to writethe log to a textbox
    • Lack of easy multithreading in VBA makes this near impossible, I think
  2. Write the log to a text file and then use "Get-Content <logfile> -Wait" in PowerShell to perform a "tail -f" on the text file
    • I know this is somewhat crude, but it's probably good enough
    • I can't see how to write the log to a text file as .setOut won't directly write to a file and my attempts to use Scripting.FileSystemObject and Scripting.TextStream with a Cplex.setOut in the VBA didn't work

I've read other posts on this subject in this forum, but none have helped resolve this issue.

I have a feeling I'm missing someting obvious here so any suggestions as to how to make either of the above approaches work, or else suggesting a completely different approach would be much appreciated.

Thanks

Andrew

  • AndrewBullock
    AndrewBullock
    12 Posts
    ACCEPTED ANSWER

    Re: Providing optimisation status to user with VBA -> COM -> .NET -> OPL

    ‏2013-11-29T10:41:06Z  in response to AndrewBullock

    To get a log file, Alex suggested setting the run_Enginelog setting. I've tried this both in the .ops file and also as this script in the .mod:

    execute{

    thisOplModel.settings.run_engineLog = "C:\\Temp\\Ilog.log";

    }

    Neither seems to work when it is invoked from .NET (but both work when it is invoked from the IDE).

    Can this be made to work with .NET?

     

    • AlexFleischer
      AlexFleischer
      453 Posts
      ACCEPTED ANSWER

      Re: Providing optimisation status to user with VBA -> COM -> .NET -> OPL

      ‏2013-12-01T06:33:09Z  in response to AndrewBullock

      Hi,

       

      my latest suggestion:

      in VBA

      Dim fso As New FileSystemObject

      ' Declare a TextStream.
      Dim stream As TextStream

      ' Create a TextStream.
      Set stream = fso.OpenTextFile("C:\log.txt", 2, True)

       

      • AndrewBullock
        AndrewBullock
        12 Posts
        ACCEPTED ANSWER

        Re: Providing optimisation status to user with VBA -> COM -> .NET -> OPL

        ‏2013-12-02T08:39:54Z  in response to AlexFleischer

        Alex - thanks for the suggestion. I tried this but in VBA this gives a Type Mismatch error.

        I set up the reference in Excel VBA to "Microsoft Scripting Runtime" and used the following code:

            Dim fso As FileSystemObject

            Dim stream As TextStream

            Set fso = New FileSystemObject

            Set stream = fso.OpenTextFile("C:\Temp\Ilog4.log", 2, True)

            Call Model.Cplex.setOut(stream)

        but it gave the Type Mismatch error. This might be because the .setOut is being given a TextStream rather than a TextWriter

        Not sure where to go with this next.

         

        • AlexFleischer
          AlexFleischer
          453 Posts
          ACCEPTED ANSWER

          Re: Providing optimisation status to user with VBA -> COM -> .NET -> OPL

          ‏2013-12-13T17:30:59Z  in response to AndrewBullock

          Hi,

           

          let me share the model we used offline to give status during the solve of the LP problem

          execute
           {
            cplex.lpmethod=1;
           }   
          int Fixed        = 1000;
          int NbWarehouses = 500;
          int NbStores     = 2000;

          assert( NbStores > NbWarehouses );

          range Warehouses = 1..NbWarehouses;
          range Stores     = 1..NbStores;
          int Capacity[w in Warehouses] =
            NbStores div NbWarehouses +
            w % ( NbStores div NbWarehouses );
          int SupplyCost[s in Stores][w in Warehouses] =
            1 + ( ( s + 10 * w ) % 100 );
          dvar float Open[Warehouses] in 0..1;
          dvar float Supply[Stores][Warehouses] in 0..1;
          dexpr float TotalFixedCost = sum( w in Warehouses ) Fixed * Open[w];
          dexpr float TotalSupplyCost = sum( w in Warehouses, s in Stores )  SupplyCost[s][w] * Supply[s][w];
          minimize TotalFixedCost + TotalSupplyCost;

          subject to {
            forall( s in Stores )
              ctStoreHasOneWarehouse:
                sum( w in Warehouses )
                  Supply[s][w] == 1;
            forall( w in Warehouses )
              ctOpen:
                sum( s in Stores )
                  Supply[s][w] <= Open[w] * Capacity[w];
          }

          execute
          {
            var d=new Date();
            writeln("at date ",d," obj=",TotalFixedCost + TotalSupplyCost);
            writeln("-->",cplex.getNnodes ());
          }  


          main
          {
           thisOplModel.generate();
           cplex.tilim=1;
           for(var i=1;i<=100;i++)
           {
             
           
           
            writeln("---> ",i," ",cplex.getNiterations()," ",cplex.getObjValue()," ",new Date(),"obj ",cplex.getObjValue());  
          }   
          }

           

          which gives

           

          ---> 1 0 44.4643 12/13/2013 18:30:01 520obj 44.4643
          ---> 2 25324 338500 12/13/2013 18:30:02 692obj 338500
          ---> 3 0 338500 12/13/2013 18:30:02 767obj 338500
          ---> 4 0 338500 12/13/2013 18:30:02 854obj 338500
          ---> 5 0 338500 12/13/2013 18:30:02 930obj 338500
          ---> 6 0 338500 12/13/2013 18:30:03 004obj 338500

           

          Regards