To Do

This page lists some of the improvements that could be made to OpenSolver.

Models could be built by parsing the spreadsheet formulae. See this OpenSolver page for a discussion of this.

Note that if you wish to change the OpenSolver ribbon buttons, you may wish to use this Custom UI Editor. This will let you open OpenSolver.xlam, then edit the XML, and save the changes. Make sure you don’t edit OpenSolver in Excel at the same time, as the Custom UI Editor will overwrite your changes.

To let the user select a range, we use either an InputBox or a RefEdit control. Both of these have bugs which make them challenging to use! For the input box, we note:

It may be tempting to use type 8 to indicate a range object, but there is an obscure glitch that causes it to fail when the worksheet contains conditional formatting conditions that use formulas in their definitions. Fortunately using Type 0 to specify a formula works just fine. (From here)

The RefEdit control has its own problem detailed here and here. These include:

RefEdits must be placed directly on the UserForm itself. If you put a RefEdit in a frame or on a multipage, strange things will happen, including bizarre Excel crashes.

RefEdits must not be used on modeless forms. RefEdits on modeless forms will result in bizarre Excel crashes.

RefEdit event procedures should be avoided. RefEdit events do not behave reliably, and they may result in VBA errors which are difficult to debug.

References to RefEdits must be removed. When a UserForm is added to a VB project, Excel adds a reference to the Microsoft Forms 2.0 Object Library. This reference is required for proper operation of the UserForms in your project. To see what references your project has, select the project in the Project Explorer, then select References from the Tools menu.

When a RefEdit is added to a UserForm, Excel sometimes adds a reference to the Ref Edit Control. This was fine in earlier versions of Excel, but there appear to be incompatibilities in recent versions of the RefEdit library. These problems often are associated with an Unspecified but Painfully Frustrating Error.

To reduce the risk of RefEdit-related issues, you should uncheck the box in front of Ref Edit Control in the References dialog. Otherwise, there may be problems loading the add-in on another user’s machine, including the removal of RefEdits from the dialogs and other bizarre behavior.

It is also helpful to process the workbook or add-in’s code through an earlier version of Excel. I use a VM with Office 2000 installed on it for this purpose. Open Excel 2000, open the workbook or add-in, run Rob Bovey’s free Code Cleaner application, then compile the code and save the file.

It may also be necessary to remove RefEdit.exd files from a user’s computer if problems occur and unchecking the reference doesn’t fix them. these exd files are temporary files that contain instructions Excel uses to build RefEdits, and the exd may conflict with the RefEdits in the workbook. If this doesn’t work, the next step is to run Detect and Repair on Excel, then reinstall Office, then sacrifice the computer at the next full moon.

Note also that, as detailed here, RefEdit does not fire the AfterUpdate and BeforeUpdate events, and setting the .Font property of cells (eg to colour selected cells blue) on the Enter or Exit events crashes Excel when the focus leaves the RefEdit control. Both the .Text and .Range properties of the RefEdit give the range selected as a string.

There are some useful RefEdit tips here including:

See also this knowledge base article for Excel 2002: XL2002: VBA Error on Macro Startup If You Open Multiple Excel Instances with RefEdit Control.

This page suggests that you trace the refedit’s exit event, and states that it MUST occur before you hide or close your userform. The following code is given to help ensure this:

Private Sub UserForm_QueryClose(Cancel As Integer, CloseMode As Integer)
If Me.ActiveControl Is Me.RefEdit1 Then
Me.Frame1.SetFocus
DoEvents
End If
Cancel = True
Me.Hide
End Sub

The source for a C#.net RefEdit replacement is available here.

Cheers,

Andrew

7 thoughts on “To Do”

  1. would be nice if the solver could support dynamic named ranges in excel created with the offset function so that variable/constraint ranges could be scaleable to the current data set

    1. The good news is that, thanks to Jack’s hard work. our pre-release 2.8.2 version does this now. You need to define a named range in the normal way that uses a formula, and then you can enter that name a the LHS of a constraint. You cannot type the formula directly into the OpenSolver Model dialog; that will not work. Hope this helps. Andrew

  2. Would like to have a utility like Solver Table inside the OpenSolver.
    I have tried to write a small routine for 1d solvertable as below.
    Request to please check and accordingly incorporate.

    Public Sub OpenSolver_SolverDataTable()
    ‘Puts each value in Selection into the cell named Input
    ‘Runs OpenSolver ‘Could be changed to running quicksolve only
    ‘After OpenSolver has run, takes the value in the cell named Output
    ‘and puts it next to the cell in the Selection column

    Dim cellItem As Range
    Dim tableInputs As Range
    Dim valueInputOrig As Double

    Set tableInputs = Selection

    If tableInputs.Columns.Count > 1 Then
    MsgBox “Please select a single column of input values.” & _
    ” Please run macro again.”
    Exit Sub
    End If
    If Application.WorksheetFunction.CountA(tableInputs.Offset(0, 1)) > 0 Then
    tableInputs.Offset(0, 1).Select
    MsgBox “This column should be empty. Please run macro again.”
    Exit Sub
    End If

    ‘Must activate Model sheet to run solver
    ActiveWorkbook.Worksheets(“Model”).Activate
    ‘Save original input value
    valueInputOrig = Range(“OpenSolverModelParameters”).value

    ‘Run solver for each value in the table of inputs
    For Each cellItem In tableInputs
    Range(“OpenSolverModelParameters”).value = cellItem.value
    ‘ SolverSolve userFinish:=True
    RunOpenSolver False, True
    ‘cellItem.Offset(0, 1).value = Range(“Output”).value
    i = 1
    For Each RC In Range(“Output”)
    cellItem.Offset(0, i).value = RC.value
    i = i + 1
    Next
    Next cellItem

    ‘Restore original input value
    Range(“OpenSolverModelParameters”).value = valueInputOrig

    ‘show data table
    tableInputs.Parent.Activate
    tableInputs.Resize(, 2).Select

    End Sub

    1. Thanks for your code; I’m sure others will find it useful. I have long-term plans for something like this. Cheers, Andrew

  3. Dear,

    I would like to know that OpenSolver can be implemented in VBA code. If answer is yes, please tell me how to do it.

    I am looking forward to hearing for your reply.

    Best Regards,

    1. OpenSolver is written in VBA, and so it can be used from VBA. You can view the code yourself to see examples of how OpenSolver is called. Hope this helps, Andrew

  4. We have had a request about viewing duals. CBC can write duals to a file; OpenSolver should read this file and prepare a duals report.

    To generate the duals, CBC needs to be sent the following commands:

    solve
    printingOptions rows
    solution solution.txt

    This will write solution.txt, which looks something like:

    Optimal – objective value 44.117647
    0 cons0 161.76471 0
    1 cons1 323.52941 0
    2 cons2 323.52941 0
    3 cons3 500 0.088235294
    0 C2 0 0.32352941
    1 D2 14.705882 0
    2 _E2 0 6.8235294

    The first 4 rows are the constraints showing (1) index, (2) name, (3) ???, and (4) the dual value.

Leave a Reply

Your email address will not be published. Required fields are marked *