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:
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
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
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
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
Thanks for your code; I’m sure others will find it useful. I have long-term plans for something like this. Cheers, Andrew
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,
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
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.