Feedback

We welcome your comments on OpenSolver.

677 thoughts on “Feedback”

  1. Hello everyone,
    I have a question, Can I use constraints wrote in different sheets? I mean i have one sheet to my variables and objective cell but every constraint is in a different sheet.
    Thank you so much.

  2. The Nomad solver normally allows to optimize categorical variables (cfr. Sec 7.1 of https://www.gerad.ca/nomad/Downloads/user_guide.pdf), which can be particularly convenient in Excel by the way.
    I tried adding one such categorical cell as variable with OpenSolver, but as you’d expect it does not work as such. One way to make it work would be to add in the constraints field the option to define the type of a variable not only as ‘int’, ‘bin’, ‘alldif’, but also as ‘cat’ (BB_INPUT_TYPE = ‘C’, cfr. p76). Is this something you could look into implementing? Given how advanced you are with the integration of NOMAD, it hopefully should not take you much time to do that.
    In theory I know I could replace my categories by a int, however I have no way a priori to order the categories (which is why they created that specific type of variable in NOMAD).
    Thanks a lot for your time and help!

    1. As a simpler idea that would work with the current options, couldn’t you just define a binary variable for each category and set it to 1 if the variable takes that category, along with a constraint that these binaries sum to 1?

      1. This would likely remove the error and be accepted by NOMAD, but the equality constraint introduces the risk to hide the structure of the problem to the solver and then result in either very slow or no progress. I am not sure how NOMAD would handle that approach.

        1. Yes this is true. However it is probably the best option for now, as building the model construction interface and other elements to support this extra NOMAD-only variable type is not high on our priority list.

          1. Ok no problem! Thanks for the previous suggestion, i’ll give it a go anyway I think. I know it is NOMAD only but optimizing categories (something that very few solvers even consider by the way, I’m not sure to know other solvers doing that) is probably going to be of interest for others, in particular amongst Excel users. But yes that surely can wait some later stage as you see best for OpenSolver. Cheers, Emile

  3. Hi,

    First of all, thanks for a great solver.

    I am experiencing an issue after i updated the solver to ver. 2.8.4, I now have an issue that some constraints on decisions variables are not being respected.

    Do you have any tips for addressing this?

    Regards,

    Jesper

    1. On top of this, after being updated to Excel 2013, I am also experiencing that Excel is chrashing whenever i try to delete any decisions variables and click “Save Model”.

      1. Jesper, sorry you are having problems. Can you send through (email hidden; JavaScript is required and email hidden; JavaScript is required) a copy of a sheet that has the problems along with a description of the exact actions you are taking and we will look into it.

  4. I am running excel 2010 with OS 2.7.1 which I recently downloaded and when I run a sensitivity report on a seperate sheet the header of the report is moved 1 cell to the left & I have used examples of models done exactly cell for cell , with exact same data and the sensitivity report throws up 1e+100 when the example on you tube does not. All this is new to me , I am a student recently introduced 2 weeks ago to solver. Can you assist?

    Regards
    Daryle

    1. Thanks for your feedback. From memory, there is an issue of alignment on Excel for Mac, but this should not change the results. (This alignment issue will be fixed in the next release; sorry it is affecting you.) Regarding the actual numbers, some problems have multiple soluiions, and so the output can look different if you get a different optimal solution. Also, 1e100 means, roughly, infinity.
      As an aside, can I ask if you are using OpenSolver as part of the MIT MOOC that started a few weeks ago?

      1. Minor correction, the alignment issue affected all versions of 2.7.x but was fixed in the 2.8.x releases

  5. Hi,

    I have used OpenSolver with great success on a number of projects. Thank you for making it available.

    Is there a way I can automatically record the date Open Solver was last executed in my Excel Worksheet ? I would like a quick means for several users to identify when the results were last generated.

    Damian.

  6. What is the current status regarding the Uncertainty Explorer, referenced here: http://opensolver.org/uncertaintyexplorer/
    The Uncertainty Explorer would allow for modelling on the basis of Monte Carlo simulations, i.e. where a distribution is underlying the variable rather than a fixed number. To be able to do this in the framework of opensolver would be really powerful.
    Is there any (beta) code available already? I would be more than happy to contribute.

    1. We have not made much progress on this. Do you have VBA experience? If so your input would be most appreciated. Please email me at email hidden; JavaScript is required Thanks, Andrew

  7. Hi OpenSolver team!
    I’m trying to have this solver run a forecast macro that does not work on my Excel 2007 with the Excel Solver (although it does work on Excel 2010 with the Excel Solver° In the company, we only have Excel 2007.
    Therefore I thought that using OpenSolver would clear the issue.
    When launching the macro, I get a compile error on “MaxTimeNoImp” which seems not to known (Compile error: Named Argument cannot be found)

    I know that the Solver in Excel 2010 was improved to include those arguments, as compared to Excel 2007 Solver.

    Is OpenSolver including these arguments? If yes, what am I doing wrong by using OpenSolver?
    OpenSolver is unblocked, installed, and I can see its entries in the ribbon

    Thanks for your help

    1. We don’t use that constant; you can check this by searching our source code. So, it is not an OpenSolver error. Hope this helps you track down the bug. Andrew

  8. When I try using OpenSolver in Excel 2016 running under Windows 8.1, I get a compile error. It says that refobj is not defined.

    1. Sorry it is not working. Please look in the VBA window under References to see if something is missing, and let us know what you find. I also suggest you repair Excel; that often fixes these glitches. Andrew

  9. I’m currently using OpenSolver 2.7.1, Mac OSX 10.11.6, Microsoft Excel 2011 and encountered the following error:

    “OpenSolver 2.7.1 encountered an error: Out of memory”

    The error log shows:

    : Line 0
    : Line 0
    : Line 0
    : Line 0

    Error 7: Out of memory
    Solver: CBC

    I would like to know how can I fix this problem. I’m solving a really small (2 equations) program.

    1. Most strange. Can you please try the pre-release 2.8.2 version. If that still fails, can you please send a copy of your spreadsheet to email hidden; JavaScript is required? And also a copy of your About OpenSolver window’s text. Thanks, Andrew

  10. I try to run a problem using open solver, however nothing happens when i click on solve. In fact i am not able to see the constraints of ‘model’ tab of open solver.
    – I have already ‘unblocked’ the downloaded zipped solver and it still is not working.
    Please assist if you’ve faced this problem.
    Thanks!

    1. Did you unblock before you unzipped the files? Does the About OpenSolver work? Are you using the 2.8.2 pre-release version? Is it a PC? If the answer is yes to all of these, then I suspect a faulty Excel installation breaking some of the controls on the form; please repeair your Excel installation. Andrew

  11. Hi Andrew,
    I’m trying to solve a problem with about 60000 variables and that really takes too long. So I wonder how effectively can I use quick solve. Is there a limit to the number of quick solve parameters? Will that number elongate the problem set up process?

    1. Quick Solve handles repeated solves with different right hand sides for the constraints. It does not save the model, and so it needs to be built each time you load the model, but can then solve more quickly after that. Let us know if it works for you. Andrew. PS: The only limits anywhere in OpenSolver are memory-based limits.

  12. Hey guys,

    I cannot for the life of me get the OpenSolver.xlam to open when I open Excel. The checkbox in the About OpenSolver is checked, I’ve added it to the Library file and I’ve even tried renaming the OpenSolver.xlam to solver.xlam in the solver file to try and get it to launch haha.

    It’s a great Add-in however I use it a lot and it’d be great to have it automatically launch so I don’t have to add it in every time Excel closes…

    Cheers,

    Joe

    1. Microsoft have changed Excel to silently not load “blocked” files; the lack of any error message is very frustrating (not just for us, but all add-in developers)! To fix this, right click the .zip, choose Properties, and then “Un-block” the .zip archive before extracting it. Then it will work. Cheers, Andrew

      1. I am having a problem with the ribbon group disappearing. There is no unblock feature on my Properties list for any file. I can’t tell you if that means the July 2016 updates were not installed but in any case I can’t get the ribbon group to remain after I close the program (I can also see as Excel 2013 loads that OpenSolver is not loading). When I look at Add-Ins, Opensolver is checked but ribbon group is not there. I uncheck Opensolver in Add-ins, then recheck and ribbon group appears. The box in About OpenSolver for launching Opensolver each time is checked and there is the statement that the installation is successful.

        Is there anything else I can try?

          1. Unfortunately, I can not create a Trusted Location (via administrator rules) and even if I did, the box that say Disable all Trusted Locations is checked and grayed out. Mu admin won’t let me touch that.

            1. In case this helps, I found that if I go through the uncheck/check procedure in Add-Ins, and keep a ribbon group enabled workbook opens, all workbooks I subsequently create/open have the ribbon group in the. This is probably because EXCEL had already loaded the OpenSolver add-in in the original unclick/click spreadsheet. However, I am working on a project with a colleague and he sent me a workbook where the ribbon group persists even though no other workbook is open. I have no idea why the ribbon group persists in that one workbook he created because he is having the same issue with new workbooks that I am having.

              I guess I am asking why that one workbook maintains a ribbon group while all others don’t. If it were a trusted location issue, that workbook should have the same problem. The only difference is that workbook had a lot of VBA code referencing OpenSolver while the other ones I have opened do not.

              1. What I have learned so far.

                Here’s the deal (I think). If you open a blank Excel sheet, it won’t see
                OpenSolver. However, open the VBA developer environment for that sheet,
                insert a VBA module into it, create a subroutine with any legal name, and
                just insert one OpenSolver command into that subroutine (e.g. Call
                SetChosenSolver(“CBC”)). If you place the cursor within that subroutine
                body and click the “green arrow” to run it, it will not recognize the
                command. Load the OpenSolver.xlam by using Tools->References, etc. Rerun
                the subroutine and it won’t crash. Further, if you now save the sheet as an
                .xlsm sheet, you can close it, and when you reopen it, OpenSolver commands
                will still work, and be visible under the data tab. If you send that sheet
                to anybody else all of the OpenSolver stuff will
                be visible and work for the person that you send it to, as long as they have
                the OpenSolver files in the AddIns system directory. If you send the sheet
                to somebody who does not have the OpenSolver files prepositioned, then if
                they enable macros when they open the sheet, it will cause a VBA crash.

                This may be a problem with our organization’s security setting but since there are times the AddIn is read, I find it hard to believe this is a security issue. Might be a Microsoft issue though.

                1. Sorry you are having such trouble with it. I do know that the behaviour with the VBA reference is to be expected. Any time a reference is added to a project, both the name and location of the reference are embedded in the file. When you load a VBA project with this reference, it first checks if you have any reference by that same name. If not, it tries to load the reference from the filepath that was saved when the reference was added. If this doesn’t exist, the reference will be missing and VBA will not run. The other part to this is that the references in a project are implicitly trusted by whatever rules Excel uses, and so opening any file with the reference set will indeed cause OpenSolver to load and not get blocked.

                  To summarize, if you have a workbook with a reference to OpenSolver, OpenSolver will load without being blocked whenever that workbook is opened.

                  The core issue is still that the OpenSolver file is being blocked in the first place. To confirm this, can you close Excel, open it again and create a blank workbook, and then double click OpenSolver.xlam. If there is no popup asking you to enable macros, that is the sign that Excel is blocking OpenSolver. I wonder if the lack of ‘Unblock’ in the properties window is the result of the admin policies on your computer or something else. Is the .xlam on a network drive or networked home folder? If so, maybe try running it from the local disk and see if unblock shows up there, Excel might simply distrust network drives or something…

                  You could also try this to unblock the files from command line with powershell, not sure if it would work though: https://stackoverflow.com/questions/28142826/

                  Please let us know how you get on

                  1. .xlam is not on a networked drive. When I performed your experiment, I did not get message asking me to enable macros so I guess the file is being blocked.

                    For the spreadsheets where the ribbon group persists, I was always getting a warning about whether I trusted the Macro. I got those to disappear when I added Univ of Auckland as a Trusted Author. Apparently, my admin lets me trust authors but not locations on my own PC.

                    I will look at the powershell unblock thing this afternoon.

                    I appreciate the help. We have found how to work around the problem (by adding VBA code) but clearly I would prefer everything to work as intended.

                    1. Using Powershell and the link you gave me, I got no response that anything worked until ….

                      I opened a blank workbook and the ribbon group APPEARED. You were right all along – the file must have been blocked and I had to use Powershell to unblock. Don’t know why the unblock doesn’t appear on properties but I am happy now so I won’t tempt fate by trying to learn how Microsoft and my System Admins think 🙂

                      Thanks you for all your help

                    2. Thanks for letting us know, glad it’s working now! It is very odd that the unblock option didn’t appear in the properties window, but good to know that the powershell approach still works in that case.

  13. Open Solver used to fly through my table but within the last two weeks it seems like something has changed. The solver now takes 5 minutes or more when it used to take under a minute. I use the add on with google sheets so I’m not sure if that is the issue or not. Every row it goes through the calculating formula bar now loads when it never used to…has something changed with the programming?

    1. There haven’t been any changes on our end. Has your sheet changed in the meantime, perhaps adding a graph or similar? If the calculating formula bar is showing up, something on the sheet is slowing down the Google Sheets recalculation, which will in turn slow down our solving.

  14. Hi Andrew,
    Congrats!!! This is an excellent tool. I am working with a problem that has 12,000 variables and about 10,000 rows. The parsing process takes about one hour. Is there any way to bypass the parsing process since I am not changing the structure of the problem so the run could be faster?
    Thanks,
    Miguel

    1. If you just change the right hand sides, then Quick Solve will be useful. Otherwise, at the moment, you have to parse it each time. SolverStudio does this much more efficiently and quickly. Andrew

    2. Hi Andrew and Miguel,
      I had the same issue but i’ve found the cause. When Excel loads model it is heavily affected by formula updates and especially chart updates. To significantly speed up the loading process do this:
      1. Close all excel file except one with your model
      2. Make sure in excel file with your model there are no charts
      3. Don’t use lookups and random number generation in excel with your model (just in case, i’m not sure, but when i have second file with lookup formulas it tremendously decrease speed).
      My model with 12000 variables is loaded in under 3 minutes.
      And thanks for the great product!

      Anton

      1. Anton: Thanks for that feedback. Yes, your model will solve much faster (at least with the “iterative” solvers like CBC where we re-calculate the sheet many times to extract the model) if the workbook is simple in that there are no unnecessary cells (or plots) that depend on the values of the decision variables. Andrew

Leave a Reply

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