none
Excel-OML: Using Decisions binding to empty sheets

    Question

  • Hello,

    It took me a long time before I was able to force the Excel-OML solver to write a Decision table onto a sheet.
    I had used the following binding:

    sold[period,product] <== results!$A:$C

    The range included headers that were mapped as needed.
    The whole sheet was empty, except for the header cells A1:C1.
    This produced the following error message in the modeling pane log tab:

    The column named [period] is missing from the data. Please check the Range setting on [sold].

    I tried another binding which produced the same error message:

    sold[period,product] <== results!$A$1:$C$52

    I tried several alternative, like not including the header, which resulted in other error messages, like this one:

    (period) is not a valid Rational.

    I could not make sense of these error messages, and was about to give up.

    By chance, I wrote something in a cell (A6) and running the solver gave a solution without error messages and the decisions were partly written in the expected range (up to line 6).
    I then wrote something again in cell A1000, and I got all the decisions writen to the sheet (there were 294).

    In a further test, I erased everything on the sheet except for the header line.
    I then wrote something in cell AB11.
    Running the solver, I then got the decisions written up to line 11.

    A last test finally convinced me that the Worksheet.UsedRange is the determining factor in this behaviour.
    A temporary turn-around is then obvious: make it such that the Worksheet.UsedRange is large enough!

    This looks very much like a small bug where the Worksheet.UsedRange plays an unexpected role. 
    The error messages were not very helpful.

    Thanks for providing us all these nice tools!

    Michel


    • Edited by Lalbatros Friday, October 28, 2011 7:54 AM
    Friday, October 28, 2011 7:52 AM