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:
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
Cancel = True
The source for a C#.net RefEdit replacement is available here.