Programming

We’ve learnt quite a bit about the intricacies of Excel; this page documents some of our more unusual findings. These comments refer to Excel 2007 unless we state otherwise.

Duplicate Cells in Ranges and Range Unions
Excel’s union operator can produce ranges with duplicate cells!? Chip Pearson describes the problem here; it is also discussed on Daily Dose of Excel (with some interesting code). We haven’t implemented Chip’s solution yet in OpenSolver as it may slow things down, but will do if it causes problems. I also realised that Excel allows ranges (such as for the decision variables) like “A1:A5,A2:A6″ that internally overlap; OpenSolver now internally fixes ranges like this when they occur using the following code:

Function MergeRangesCellByCell(ByVal r1 As Range, ByVal r2 As Range) As Range
    ' This merges range r2 into r1 cell by cell.
    ' This shoulsd be fastest if range r2 is smaller than r1
    Dim result As Range, cell As Range
    result = r1
    For Each cell In r2
        result = Union(result, cell)
    Next cell
    MergeRangesCellByCell = result
End Function

Function RemoveRangeOverlap(ByVal r As Range) As Range
    ' This creates a new range from r which does not contain any multiple repetitions of cells
    ' This works around the fact that Excel allows range like "A1:A2,A2:A3", which has a .count of 4 cells
    ' The Union function does NOT remove all overlaps; call this after the union to
    If r.Areas.count = 1 Then
        RemoveRangeOverlap = r
        Exit Function
    End If
    Dim s As Range, a As Range, i As Integer
    s = r.Areas(1)
    For i = 2 To r.Areas.count
        If Intersect(s, r.Areas(i)) Is Nothing Then
            ' Just take the standard union
            s = Union(s, r.Areas(i))
        Else
            ' Merge these two ranges cell by cell; this seems to remove the overlap in my tests, but also see http://www.cpearson.com/excel/BetterUnion.aspx
            ' Merge the smaller range into the larger
            If s.count < r.Areas(i).count Then
                s = MergeRangesCellByCell(r.Areas(i), s)
            Else
                s = MergeRangesCellByCell(s, r.Areas(i))
            End If
        End If
    Next i
    RemoveRangeOverlap = s
End Function

 

Named Ranges can contain cells from multiple sheets, but Ranges cannot
The built in Name Manager allows named ranges to be created that contain cells from multiple sheets. However, this “named range” cannot be turned into a range because a range must old only cells from a single sheet. As a consequence, all your decision cells must be on the same sheet (which does not have to be the active sheet).

Name Definitions not Localised
Solver stores its constraint LHS and RHS values as defined Names. Excel users have probably used Named Ranges, such as Test=$D$1. Names can store not only ranges, but also constants, such as Test=5.3, and formulae, such as Test=SQRT(2)*D4.

Solver stores its integer tolerance in a name solver_tol, with a value such as 0.05 meaning a 5% tolerance. Like all names, this is simply stored as a string that is NOT affected by regional settings, and so in VBA this must be interpreted using the Val() function which always uses the full stop, and not the comma, as the decimal point. We didn’t get this right in OpenSolver until version 1.3beta.

You can see this if you switch your regional settings to French, use Excel’s Name Manager to define a value such as Test = 0,25 (ie one quarter), and then in VBA’s immediate window type ? Range(“Test”). The output is 0.25.

Excel is consistent in this regard in that formulae are always stored using English settings. So, if if 1,23 is entered into A1 on a French system, ? Range(“A1”).value gives 1,23, but ? Range(“A1”).Formula gives 1.23.

VBA, however, is a bit more tricky. If you simply assign a string to a double in VBA, then that string is interpreted using the current locale, and so using this approach to get constants from names does not work. This means that this otherwise excellent code by Chip Pearson does not work on non-English systems. See OpenSolver’s code for an updated version.

Localisation and Evaluate
Excel has an evaluate function, allowing you to type the following into the immediate window:

? Evaluate(“1,3+1,6”)

Surprisingly, even if the system settings are set to French, this fails. However, typing

? Evaluate(“1.3+1.6”)

on a French system gives “2,9” (with a comma) as the output, showing again that formulae must always be internally in English. Fortunately, we use Evaluate to calculate values for formulae stored in defined names (which are always stored in US format), so this all “just works” for us.

Excellent Localisation Site
There is some excellent information on localisation issues available here. A copy of this in Word is also available, and the full book. Also look at: http://www.rondebruin.nl/international.htm.

RefEdit Control
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.

To let the user select cells on the spreadsheet, we now use the RefEdit control. This has caused us more grief than anything else!  We believe the fundamental issue is that a RefEdit control implements its own simplified event loop whenever it has the focus, and so you should not do anything complicated while this RefEdit event loop is running. For example, closing a form or doing Application.Calculate within this event loop can crash Excel. Note also that VBA breakpoints are all ignored within this loop.

To help avoid the RefEdit bugs, we suggest you look at the Peltier Tech Blog which notes  that

The RefEdit control has some strange and wonderful idiosyncrasies which must be considered when designing a dialog. There are (at least) four issues that are important to keep in mind.

  • 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. [We have not needed to do this.]
  • 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.
  • 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.

After a day of anguish, we can also add:

  • If you are enabling/disabling controls on a form, make sure that the last item you enable is not a RefEdit. Our code that was doing this would crash Excel on the second time around when it called Application.Calculate.
  • We can now be a bit more specific, thanks in part to this comment on tab-stops. If a refedit control has a .TabStop value, then when another control is disabled, the focus can move to the RefEdit (as the next item in the tab-stop sequence). This means the RefEdit grabs the focus, and in doing so, pauses the VBA execution. This can be avoided by removinbg the tab stops from the RefEdits, or managing the focus to stop RefEdits getting focus unexpectedly (by, for example, when disabling a control, first setting the focus to an item that is not being disabled).
In OpenSolver, Excel seems to crash most often when users press <return> instead of clicking on a button (eg on the Continue button), or when the user presses <escape> instead of clicking Cancel (which we now disable). To try to catch these, we have made sure that the form is not closed while a RefEdit control has the focus, and put in lots of DoEvents around anything involving a RefEdit control. These checks seem to work, mostly, but the RefEdit is still somewhat unreliable.
In version 1.3beta, we no longer associate pressing the return key with the next Continue button. When this was allowed (eg in version 1.2 beta), selecting a blank objective cell, and then pressing return caused an error dialog to appear (even tho’ there was an On Error top catch this error) and also caused a floating RefEdit dialog to show; this sometimes then resulted in crashes. Debugging this was difficult as breakpoints in this code were ignored, perhaps because RefEdit was confusing the debugger. (I suspect that RefEdit runs its own event loop to do its clever stuff, and this causes all these problems.) We do, however, still allow Escape to ‘press’ the Cancel button (via Cancel=true for this button); this code again runs without stopping at breakpoints, but does not seem to cause crashes.
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.

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

We have found that the VBA debugger is not good at tracking events associated with RefEdit’s. For example, RefEdit events may fire and run ok, but without triggering any breakpoints set in the event handler. We have seen errors get thrown (and a dialog appear on the screen), even though our event handler has an On Error handlers; On Error handlers (and errors in general) are perhaps best avoided. (Given the tricks the RefEdits do, it is likely that they run their own main event loop which allows them to process clicks in Excel windows. We suspect that differences between this main event loop standard VBA main event loop leads to things operating differently in subtle but dangerous ways!)

This page suggests that you trace the refedit’s exit event (by debug.print, not breakpoints), 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(ByVal Cancel As Integer, ByVal 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 on Gabhan Berry’s MSDN Excel Programming blog.

RefEdit Range Localisation
The RefEdit.Text returns, as might be expected, the text associated with a range. For ranges with multiple areas, Excel uses a “,” as the separator in English locales, and a “;” in German, for example. You can see this is you switch your “Current Format” to German, and type the following into VBA’s immediate window:? range(“A1,B4”).AddressLocal
$A$1;$B$4  <- Note Excel’s AddressLocal has replaced the , by a ;
This case can be detected by observing that Application.International(xlListSeparator) is “;” instead of “:”. The next version of OpenSolver will translate formulae (and ranges) from the current language to US by putting the item to translate into a cell, making use of .FormulaLocal and .Formula
Testing a String for an English Number – IsNumeric() fails miserably
OpenSolver needs to test if a defined name contains a formula, a constant or a range. It is surprisingly difficult  in VBA to test for a constant value expressed in English numerical style in a fashion that works in different locales (such as French with commas instead of full stops for decimal points). The obvious IsNumeric() has its problems, such as IsNumeric(“($1,23,,3.4,,,5,,E67$)”) giving True! This is the code we wrote for this; it will detect English numbers (as appear in defined names) even if the locale is non-English.
Function IsAmericanNumber(ByVal s As String, Optional ByVal i As Integer = 1) As Boolean
    ' Check this is a number like 3.45  or +1.23e-34
    ' This does NOT test for regional variations such as 12,34
    ' This code exists because
    '   val("12+3") gives 12 with no error
    '   Assigning a string to a double uses region-specific translation, so x="1,2" works in French
    '   IsNumeric("12,45") is true even on a US English system (and even worse, eg IsNumeric("($1,23,,3.4,,,5,,E67$)")=True! See http://www.eggheadcafe.com/software/aspnet/31496070/another-vba-bug.aspx)
    Dim MustBeInteger As Boolean, SeenDot As Boolean, SeenDigit As Boolean
    MustBeInteger = i > 1   ' We call this a second time after seeing the "E", when only an int is allowed
    IsAmericanNumber = False    ' Assume we fail
    If Len(s) = 0 Then Exit Function ' Not a number
    If Mid(s, i, 1) = "+" Or Mid(s, i, 1) = "-" Then i = i + 1 ' Skip leading sign
    For i = i To Len(s)
        Select Case Asc(Mid(s, i, 1))
            Case Asc("E"), Asc("e")
                If MustBeInteger Or Not SeenDigit Then Exit Function ' No exponent allowed (as must be a simple integer)
                IsAmericanNumber = IsAmericanNumber(s, i + 1)   ' Process an int after the E
                Exit Function
            Case Asc(".")
                If SeenDot Then Exit Function
                SeenDot = True
            Case Asc("0") To Asc("9")
                SeenDigit = True
            Case Else
                Exit Function   ' Not a valid char
        End Select
    Next i
    ' i As Integer, AllowDot As Boolean
    IsAmericanNumber = SeenDigit
End Function

 

Excel Programming and Advanced Use
Gaghan Berry has a good (but old, around 2008) blog on programming Excel, part of the Microsoft MSDN site. We are also a big fan of Chip Pearsons’s Excel site. There are some very interesting projects (including source code) showing XLL add-in examples, VBA COM code and C# add-ins at Codematic.com.
User Defined Functions We would like to make OpenSolver accessible as a function entered into a cell. Normally a formula needs to be exntered (using Shift-Ctrl-Enter) into multiple cells if they are all to be set as a result of the function call. However, this CodeProject article shows how the Calculate event can be used to allow data to be written to a number of cells outside the actual formula cell. This could be an easy way to write the decision variable cells to the sheet using a function such as =OpenSolver.Solver(righHandSideCells) that is triggered whenever a right hand side is changed.

Compiling CBC v2.7.6 – our experiences
To compile CBC.exe ready for use with OpenSolver, you need to download the latest zip CBC source code. Our experiences below come from compiling version 2.7.6. We tried compiling under the free version of Visual Studio 2010, using the “v10” folder in the CBC download. This compiled successfully, but did not appear to produce a CBC.exe file. Instead, we compiled using a full copy of Visual Studio v9 using the “v9” folder in the CBC download (Cbc-2.7.6Cbc-2.7.6CbcMSVisualStudiov9), working with the default project “cbcExamplesSample2”. This generated multiple errors, but did produce a CBC.exe file in the “Release” folder (after switching from Debug to Release). We then re-compiled after changing the properties for all the projects (ie with them all selected) to set the “C/C++ : Code Generation : Runtime Library” to “Multi-Threaded” (so we don’t need to distribute any DLL’s), and “General : Common Language Runtime support” to “No common language runtime support”. To check that there are no dependencies, you can use Dependency Walker (depends.exe) to open CBC.exe, and check that it only requires Kernel32.dll instead of all the library files you normally see.

AddConstraint – model building using Application.Run with OpenSolver API

(Applies to Excel 2016 – other versions may differ)

When calling OpenSolver API subs and functions with the Application.Run method, normal VBA conventions about the scopes of subs and functions do not always apply. According to an article from wellsr and a StackOverflow question, Application.Run is able to run both ‘Public’ and ‘Private’ subs. 

In OpenSolver.xlam, the sub AddConstraint is defined in two places – in the OpenSolverAPI module and the SolverFileNL module. Its definition in the API module is Public and in SolverFileNL it is Private.

However, if AddConstraint is called using Application.Run, it seems that these scopes are ignored and there are effectively conflicting or duplicate definitions for the sub – it is now ambiguous as to whether OpenSolverAPI.AddConstraint or SolverFileNL.AddConstraint should be run.

Thus, in order to build up a model using the OpenSolver API while using Application.Run, the following function call should be made e.g.:

Application.Run "OpenSolver.xlam!OpenSolverAPI.AddConstraint", LHSRange, rel, RHSRange

instead of calling:

Application.Run "OpenSolver.xlam!AddConstraint", LHSRange, rel, RHSRange

44 thoughts on “Programming”

  1. Hi,

    I am trying to build a solver model using VBA, so far I have been able to set the objectives, variables and constraints but I am having trouble with the sensitivity analysis. I would like to have a sensitivity analysis on another sheet and would greatly appreciate it if someone could help me out here.
    Here is what I have coded so far

    Sub Model_Creation()

    Dim TestSheet As Worksheet
    Set TestSheet = Sheets(“Sheet4”)

    OpenSolver.ResetModel Sheet:=TestSheet

    ‘Objective Definition
    OpenSolver.SetObjectiveFunctionCell TestSheet.Cells(39, 13), Sheet:=TestSheet
    OpenSolver.SetObjectiveSense MaximiseObjective, Sheet:=TestSheet

    ‘Variables Definition
    OpenSolver.SetDecisionVariables TestSheet.Range(Cells(4, 2), Cells(38, 10)), Sheet:=TestSheet

    ‘Constraints Definition
    OpenSolver.AddConstraint TestSheet.Range(Cells(4, 2), Cells(38, 10)), RelationLE, TestSheet.Range(Cells(42, 4), Cells(76, 12)), Sheet:=TestSheet
    OpenSolver.AddConstraint TestSheet.Range(Cells(4, 11), Cells(38, 11)), RelationLE, TestSheet.Range(Cells(4, 12), Cells(38, 12)), Sheet:=TestSheet

    End Sub

    Thank you in advance for the assistance.

    Reply
  2. Pingback: OpenSolver 2.8.5 (3 Nov 2016) – OpenSolver for Excel

  3. I’m re-purposing an old Classic 4th-Order Runge-Kutta (RK4) subroutine I wrote in 2002 to integrate a series of ordinary differential equations (ODEs). I’m trying to reverse model via Solver microbial kinetic constants within a series of ODEs (Ks, km, X, etc.) (by minimizing an objective sum of square residual between my model and measured data by changing kinetic constants with constraints applied). My RK4 subroutine seems to solve the the series of ODEs successfully. However, I need to be able to run (i.e., Call) my RK4 subroutine every time Solver changes my kinetic constants so that my model output is updated, thus updating my objective sum of square residual cell (or internal value), so that Solver can “see” a change in the Objective cell from changing the “Changing” variables. Is there a way to call my RK4 subroutine every time Solver updates my “changing” cells? Thanks so much! If interested I can send you my code and excel file.

    Reply

    1. If I’m understanding correctly, you need to run a macro after every change to the variable cells in order to update the objective/constraint cells, correct? This is something that is theoretically possible to do with the NOMAD solver in OpenSolver (but not the others). This feature is on our todo list but we haven’t added it yet because we haven’t settled on the best UI for such a feature.

      It only takes a very simple 2-line modification to the OpenSolver code to achieve what you want, so email me (email hidden; JavaScript is required) if you are interested in trying it out and I can walk you through the modification that you need to make.

      Reply

  4. Hi.

    I have an issue with opensolver: I set the objective cell, the variable cells and the constraints all via VBA, then I run it and get the results. After that I change all those parameters, still in the same loop on VBA.

    The second running gives me the error “no solver model with decision variables was found on the sheet”. If I check the model, however, it is there and correct. If I debug and stop the code right after the first running and manually start it again, it works too.

    The problem only happens if I let it run all at once. I have tried the same code with the native solver and it runs fine. Any ideas?

    Thanks for the attention.

    Reply

    1. This sounds odd – it should be working since we do a lot of our testing via VBA and solve hundreds of models in VBA loops. I’ll look into it if you send me a copy of the sheet along with your version information (copy everything in the About OpenSolver page) to email hidden; JavaScript is required

      Reply

  5. Hi there! I have developed a simple macro to loop OpenSolver.
    For some reason when I run the problem manually over the various steps it works fine, but when I loop it it almost always return the following message: “OpenSolver could not find an optimal solution, and reported: No Feasible Solution. No solution was able to load into the spreadsheet”.
    Some times the error comes at the 2nd iteration, sometimes at a later one.
    That is quite annoying indeed, as I cannot automatize the process. Any idea how to solve that?

    Thanks!

    Reply

    1. I would suggest you save all the .LP files created at each step/solve and look for differences. This may help track down the problem. If the files are the same on your manual and automatic runs then it is a Cbc issue (or an OpenSolver timing issue). If they are different then you can work back to find out why. Please let us know what you find so we can fix any OpenSolver errors. Thanks for reporting in the possible issue… we will definitely follow it up if it is our problem. Andrew.

      Reply

      1. Solved! That was an issue in my code.
        An interesting point regarding constraints on the outputs: if I say “the variable must be >=0” then very often the solver returns a solution that is of the type -0.0001e-16. Very small yet negative.
        Since I iterate the solver by re-initializing the start values given the results of the previous run, I get a “no solution available” whenever the initial value is set to a negative number, however small it is.
        So I have to manually go through the solution and correct for these small negative numbers.
        Perhaps interesting for a future OpenSolver release to force outputs to be 0 if they have to be >=0 ?

        Reply

  6. Hi

    I see OpenSolver models involve lots of named ranges. The model I’m building inserts a whole load of named ranges itself and then creates formulae which use these (so instead of the anonymous =a1*b2 I have something like =capacity_used*emissions_factor). As I develop the model and change its layout, I periodically want to delete all the named ranges and recreate them using VBA. But when I do this I find I’ve deleted the model definition too. I notice there are some with names like “solver_adj” and so I’ve avoided deleting anything with “solver” in the name (i.e. only delete name if InStr(1, “solver”, nm.Name) = 0), but are there any others to avoid?

    …My code follows Chip Pearson’s and only deletes those names which refer to ranges using this approach:

    Set r[ange] = nm.RefersToRange
    If Err.Number = 0… …then test name of range for deletion as above and delete

    Thanks for any help you can give

    Fernley

    Reply

    1. Fernley: Yes, as you have seen, OpenSolver and Solver store models using named ranges (and named expressions). The safest approach is not to delete any ‘hidden’ names – you can check for this using “Not name.Visible” in your VBA. The excellent Name Manager (http://www.jkp-ads.com/officemarketplacenm-en.asp) will let you see all the names in the spreadsheet, including the hidden ones. I don’t have a complete list of all the names we use, sorry, but they all start with OpenSolver_ or solver_. Hope this helps, Andrew

      Reply

    2. Many thanks for this: will add that check.

      Another problem, though…

      …Am trying to delete a constraint programmatically, do some model runs and then reinstate it. Using XL 2013 64 bit. Have Tools => references ticked for both Solver and OpenSolver. Code is this:

      If Range(“inc_c_price”).Value = “y” Then
      SolverDelete cellRef:=Range(“emissions_cap”), relation:=1, formulatext:=Range(“emissions”).Address
      ‘Following line doesn’t seem to work either!
      Application.Run “SolverDelete”, “$$L$187:$AV$187”, 1, “$L$186:$AV$186”
      End If

      VBA does follow the “IF” clause, and tries to execute the “THEN…” code so it’s not a problem with Range(“inc_c_price”).Value. However, if I stop the debugger after the “END IF”, the OpenSolver model definition is apparently unchanged — the constraint “emissions_cap” <= "emissions" is still listed. I'm using OpenSolver261. Any ideas?

      Cheers

      F

      Reply

      1. If you install Name Manager (google it), you will be able to see if the model is being changed by your calls. Hope this helps, Andrew

        Reply

        1. Thanks. I hacked it in the end by looking for a non-visible named range and deleting this if it referred to the target cells:

          For Each nm In Names
          If InStr(1, nm.Name, “model!solver_lhs”) > 0 And Not nm.Visible Then
          Set r = nm.RefersToRange
          If Err.Number = 0 And (nm.RefersTo = “=model!” & Range(“emissions_cap”).Address Or nm.RefersTo = “=model!” & Range(“emissions”).Address) Then
          nm.Delete
          End If
          End If
          Next nm

          This seems to clear the constraint as required… So it suggests there’s a problem with Solver. The opposite code, to add a constraint, works fine:

          If Range(“inc_c_price”).Value = “y” Then
          SolverAdd CellRef:=Range(“emissions”), Relation:=1, FormulaText:=Range(“emissions_cap”)
          End If

          Reply

          1. Pleased it worked. I don’t know what OpenSolver will do if some of the constraints are missing – it expects them all to be there in sequence. But I expect you got it working, so maybe we are gracefully skipping the missing data! Cheers, Andrew

            Reply

  7. Hi,Andrew
    When I programming in VBA check the model, VBA are not recognized commands
    SolverAdd cellRef: = Range (), _
    relation: = 4
    SolverOk SetCell: = Range (“”), _
    MaxMinVal: =, _
    ByChange: = Range (“”)
    What is the reason?

    Reply

  8. Hi,

    Is it possible in VBA to set the engine of OpenSolver? For example to set the engine in VBA to ‘Gurobi’ or to ‘COIN-OR’?

    Thanks!

    Reply

    1. You can use the following:
      OpenSolver.SetNameOnSheet "OpenSolver_ChosenSolver", "=" & Solver
      where `Solver` is set to one of the following: CBC, Gurobi, NeosCBC, Bonmin, Couenne, NOMAD, NeosBon, NeosCou

      Make sure you have added a reference to OpenSolver in the Tools>References menu, otherwise this won’t work

      Reply

  9. Hi I am working on a project using macros to run the open solver to solve a linear problem. I can successfully run the solver but I have a problem with clearing the previous model I built. I have to go to the UI to clear the model every time before I run my macro. I have tried using “SolverReset” but it does not really work. Could you please help me with this issue? What is the line to clear the model?

    Reply

    1. SolverReset should be working; I will ask out programmer to check out what’s happening. Thanks for alerting us to this. Andrew

      Reply

      1. We have tested this, and it seems to be working fine for us. To reproduce our test, please try opening the model dialog (using Solver or OpenSolver) after calling SolverReset, and verify that the model has been deleted. It is possible that there is a more subtle bug in the way Solver then adds constraints, but this is unlikely given that the Solver code is well polished by now. Andrew

        Reply

        1. Thanks for the prompt response, Andrew. I tried running my code in the debug mode and when I passed the SolverReset line, I stopped debugging it. The model was gone when the model window was opened. However, when I ran the code right after this and I checked the model, there were two sets of constraints. The first one was the one that should be cleared and the second one was the one appended by that run. I can compile some simple codes and send them to you for the test purpose. Again I really appreciate your help!

          Reply

          1. That is some good testing you have done. Can you please try your vba test (of building a model, resetting it, and then building it again) on a brand new workbook without using opensolver at all? We need to start by eliminating any Solver issues. Thanks for your help. Andrew

            Reply

            1. Hi Andrew,

              I have solved this issue. I did not switch to the right sheet to clear the solver so I actually cleaned up the solver in the wrong sheet. Sorry to bother you with this issue.

              However, there was another issue related to my solver. I tried to add an integrality constraint. I wrote the code like this in VBA:
              SolverAdd cellRef:=Range(“D3:O53”), _
              relation:=4

              But at the end when I check the model, all the decision variables are not integer. I opened another file and tried to run this:
              Sub Macro1()
              ‘
              ‘ Macro1 Macro
              ‘

              ‘
              Range(“C1”).Select
              ActiveCell.FormulaR1C1 = “=2*RC[-2]+5*RC[-1]”
              Range(“C2”).Select
              ActiveCell.FormulaR1C1 = “=4*R[-1]C[-2]+R[-1]C[-1]”
              Range(“C3”).Select
              ActiveCell.FormulaR1C1 = “=R[-2]C[-2]+R[-2]C[-1]”
              Range(“C4”).Select

              SolverReset
              SolverAdd cellRef:=Range(“C1”), _
              relation:=1, _
              formulaText:=Range(“D1”)
              SolverAdd cellRef:=Range(“C2”), _
              relation:=1, _
              formulaText:=Range(“D2”)

              SolverAdd cellRef:=Range(“A1:B1”), _
              relation:=4

              SolverOk SetCell:=Range(“C3”), _
              MaxMinVal:=1, _
              ByChange:=Range(“A1:B1”)

              RunOpenSolver False

              End Sub

              The result of this is the same. It did not append the integrality constraint in the model. Could you please help me with this?

              Thanks! I really appreciate your help!
              Haoxiang

              Reply

  10. How do I prevent opensolver to show the messagebox, when no feasible solution have been found?

    Thx.

    Reply

    1. Are you using the VBA code given under the Using OpenSolver web page? I have asked our programmer to look into this issue, and fix it for the next version. In the meantime, you may need to edit the OpenSolver code. Sorry this is not as easy as it should be. Andrew

      Reply

  11. Hi Andrew,

    I’m running solver on historic price data, upto how many variables would it be convenient to use Open Solver? This is an amazing tool since the Excel Solver is limited to just 200 variables.

    Thanks

    Reply

    1. How big a problem you can solve totally depends on your problem size and computer specs. The next OpenSolver version has experiental support for non linear models. Andrew

      Reply

      1. So I’m assuming there is no upper limit to the number of variables and that 30,000 would be possible to execute. Also, is there any version available for non linear regression? Would be of tremendous help.

        Reply

  12. My objetcive function and target values ate in different sheets. how can I run SOLVER ? It give me a massgae that they should be in the same sheet.

    Reply

    2. I have the same question. For me it does not message back anything however when I look at the solver form datatab>>Opensolver>>Model>>Model and look at the constraints and objective functions it just shows me the one that relates to the excel sheet being shown ( Active sheet)

      Reply

      1. Just to clarify, you can only have one model per sheet. OpenSolver (but not Solver) allows a model specified on one sheet to specify constraints that exist on another sheet; Solver does not allow this. Andrew

        Reply

  13. Hello!

    I’m running an excel VBA Macro in loops, but I need to always manually press the “Solve” button in excel interface to proceed my loop. How can I create a VBA Macro that “press” the “Solve” button for me?

    Thank you!

    Reply

  15. Hi,

    I am running loops for solving where constraints are sometimes not met and a message pops up. How can I code around in VBA so as to not display these messages( like a UserFinish:=False function in regular solver).

    Thank you in advance.

    Reply

Leave a Reply

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