OpenSolver for Excel
The Open Source Optimization Solver for Excel
We welcome your comments on OpenSolver.
i am not able to use non-linear solver engine in MS office 2021 excel, kindly share a fix
There are a few reasons it may not be working. Could you share any error messages you are getting/error log/solve log. Please test the non-linear solver with the model found under OpenSolver > OpenSolver Examples > Non-Linear Example.
I have a rather large spreadsheet that is optimized with no problem when using Opensolver. However, after running a normal “Solve” and then following with a “Quick Solve” to reduce processing time, sometimes “Quick Solve” provides a good solution, and sometimes it provides an error that there is no feasible solution. I know there is a feasible solution, as if I immediately process a normal “Solve”, once again with no parameter change, a feasible solution is returned. So, it appears that sometimes “Quick Solve” works and sometimes it doesn’t work. Do you have any ideas why this may be the case. Thank you.
I have used OpenSolver for a long time, it has always worked great for my needs. This year, when migrating to a new computer, I have encountered a problem with OpenSolver not loading when Excel starts (until now this feature worked seamlessly). I have placed the .xlam file (as well as the others) in this folder:
Most of the times, OpenSolver does not load when I open Excel. I have to load OpenSolver manually, (once it is loaded it works well, no problems here).
In the ribbon, I have also tried going to OpenSolver > About OpenSolver and checking the box “Load OpenSolver when Excel starts”. When I check the box, I get a dialog window saying that this will configure Excel to load automatically OpenSolver when it starts. But when pressing OK on the dialog window, the box is unchecked.
What I described above happens both in my laptop and in my desktop (both Windows 11). It did not happen until now.
Am I missing something? Is there a way to fix this? Thanks in advance!
It is strange that the box is left unchecked.
Here are a few things you can check:
– Check that “C:\Users\[my username]\AppData\Roaming\Microsoft\AddIns” is a trusted location (see below).
– Try adding the file to “C:\Program Files\Microsoft Office\root\Office16\Library”. (This seems to be the trusted Excel AddIns folder) If this path does not exist for you, view your trusted
locations (see below) and look for the location with Description: “Excel default locations: Add-ins”. Try adding the file to that path instead.
– In the ribbon, go to “Developer > Excel AddIns” and search for the OpenSolver box. If the box is there, check it.
Viewing/adding trusted locations in Excel:
Open Excel and go to “File > Options > Trust Center > Trust Center Settings > Trusted Locations”. If you are adding a trusted location you need to go into “Add new Location” and paste the absolute path to the location you want to trust. If the file you want to trust is not directly in the trusted folder, select the option “Subfolders of this location are also trusted”.
Once you have done each of the above things, you can try selecting the auto-load box in “About OpenSolver” again.
Let us know if this works or if you still have the issue. Cheers, John
Hi John, many thanks for your response!
I have tried several of your suggestions and now it works. I write here what I did in case it may be useful for others. (My files had been previously unblocked).
I first added the .xlam file to “C:\Program Files\Microsoft Office\root\Office16\Library”. This did not solve the problem.
Then I went to the ribbon “Developer tab > Excel AddIns”. The OpenSolver box was there, and it was checked. I unchecked it and then checked it again. When checking it, I received the message that the OpenSolver file was no longer in a certain path and asked me whether I wanted to remove the OpenSolver box. (That path was the Dropbox folder where I used to keep the OpenSolver files in my old computer; when setting up the new desktop, I kept OpenSolver in the same Dropbox folder). I clicked Yes and removed the OpenSolver box. Then I added it again by going to the “Developer tab > Excel AddIns > Browse”, browsed to
and chose the .xlam file (this path is where I now keep the OpenSolver files). The OpenSolver box appeared again among the AddIns, I checked it, and clicked OK. Then I went to the auto-load box in “About OpenSolver”, and this time I could check it. Now OpenSolver auto-loads when opening Excel.
The problem was that the initial reference to the OpenSolver Addin was different than the path that you guys recommend (the one above, which works well).
Thanks again for all your suggestions! Rafael
PS-I’m interested in your findings regarding the OneDrive folder, as other people I work with use these folders.
A few more quick things:
– We have been investigating issues with having OpenSolver under a OneDrive folder, although we note this is not the issue in your case.
– Check that you followed the Unblock step before unzipping. Can be found here: https://opensolver.org/installing-opensolver/
– Make sure your username only contains ASCII characters, no Unicode.
Hi, I am using Opensolver 2.9.3 to solve a problem with ~ 2700 variables and 6 constraints. The setup (parsing) time is extremely slow (< 1 iteration per second), yet once setup is finished, the model solves instantaneously. I am using Windows 10, EXCEL 2016. I have plenty of memory and a fast processor. Any ideas on why OpenSolver is taking so long to set this up?
OpenSolver has to re-calculate the spreadsheet 2700 times. Make sure you don’t have graphs etc in the workbook; keep it as simple as possible. If it is still too slow, try SolverStudio. Hope this helps, Andrew
I ran a simple test to make sure OpenSolver is working correctly. 1 + [blank] = 5. The answer is 4. When running excel solver the answer is correct but when I run OpenSolver it says there is no feasible answer or it’ll return the incorrect number (3) . Why would this happen and how do you fix this?
Hi Alex, this bug will be fixed in the next version. If you want to solve it yourself, go to the SolverFileLP module, line 26 and change it to: Print #1, ” = “; StrEx(s.ObjectiveTargetValue). This is an issue for the Neos solvers too. To fix it for them go to SolverFileAMPL, line 16 and change it to: Print #1, “;”. Cheers, John
I am working on a MILP using OpenSolver, where I have assigned dynamic ranges within the Names section in Microsoft Excel. The syntax is correct and everything since it recognizes de dynamic range name on the Model window of OpenSolver. However, every time the data is changed for more or less variables and constraints, I get the following error:
“method ‘Range’ of object ‘_Global’ failed”
Any idea of how to troubleshoot this issue? Thanks
Sorry it is giving you errors. Can you explain precisely when that error message occurs, i.e. what you are doing to make it happen? Is it repeatable? We try to handle dynamic ranges, but there may be edge cases we have missed. Andrew
As soon as I add or delete data, whenever I click on Model to show the window with all the decision variables and constraints, solver engine, etc, that is when I see an error box instead. It seems like the VBA code crashes for some reason.
I am currently working with a model that has over 100 set of constraints, not sure if that makes a difference since I have previously tried dynamic ranges with another model that has about 6 set of constraints that is working perfectly though.
Hope it helps! Thank you for your prompt response.
It happens every time the data is modified (either increased or decreased) and I click on the Model button to change parameters, constraints or decision variables. Hope it helps!
Sorry to hear OpenSolver is giving errors with your dynamic ranges.
Can you please confirm what you mean by dynamic ranges; are you referring to the new Office 365 functions detailed at, eg, https://www.ablebits.com/office-addins-blog/excel-dynamic-arrays-functions-formulas/ , that expand the number of cells used for their answer? We have not tested OpenSolver on these, so it may well give errors. Or are you talking about dynamic named ranges, as detailed at https://www.ablebits.com/office-addins-blog/excel-dynamic-named-range/ ? We should handle these ok.
I’ve used OpenSolver Excel plug-in (2.9.3 Linear for Windows) to solve large linear optimization models in excel successfully in the past. It has been working perfectly fine for me till the last time I used it, which was around 2-3 months back. However, when I came back to it yesterday, it appears to have stopped working for some reason. I can see the OpenSolver add-in in my “Data” tab, but it has essentially become non-responsive. Clicking on buttons is not giving any response – The OpenSolver dialog box/editor that’s supposed to open doesn’t open at all.
I was relying on this to run some optimization models, but I’m handicapped now. I tried uninstalling and reinstalling it, also tried downgrading my Office 365 – but nothing seems to work.
Can someone please help? Would really appreciate any help I can get.
Sorry it is not working. We have not changed anything. Options are Microsoft has done an Excel update that broke OpenSolver, or, more likely, your Excel installation has “broken” somehow. I suggest you try repairing your Excel installation. Also make sure that OpenSolver (and the correct version) is showing as you expect in the File…. Options…. Add-ins dialog. So, either it is not listed (in which case you load OpenSolver when you need it by double clicking the OpenSolver.xlam file – this is what I do), or it is listed as an “Active Application Add-in”. Sorry I cannot be more specific – it is super frustrating when things inexplicably break. Andrew
PS: I don’t use Office 365, so others may have more advice for this?
Thanks Andrew for a quick response. I also suspected that maybe a recent Office update broke something, so that’s why I tried downgrading my Excel version, however, that didn’t work.
Based on your suggestion, I also tried to “repair” my installation of entire Office 365 suite of apps. Unfortunately, that didn’t fix the problem either.
Like you said, I also always open my excel file and then open OpenSolver.xlam file which loads up the add-in to my “Data” tab. And then it also correctly appears in the “Active Application Add-in” list. So there are no issue with that.
Anyway, I’m still looking for a solution – so Andrew, if you may have any other suggestions or anyone else who’s a user who might have faced this problem and have been able to figure out a solution – I would love to hear from you.
Have you tried the latest 2.9.4 beta version? It has some fixes (but probably not for your issue). If you go back to earlier OpenSolver versions (eg 2.9.0), you will find one that is has the code “signed”. You could try a signed version to see if that helps. (The latest versions are not signed; it got too hard.)
Please confirm you get no messages at all; OpenSolver just appears in the menus but does not work?
You might want to check that the drive you are opening OpenSolver from is “trusted” by Excel… maybe Microsoft have hardened something? Also double check you “unblocked” the .zip download BEFORE extracting the files.
Please let me know how you get on,
After 1 year using your solver, I’m still amazed to see how it works well and fast. So, thanks again for this.
I have a question related to the VBA API. I’m using the following statements to set the objective cell sense to maximization:
Application.Run “OpenSolver.xlam!OpenSolverAPI.ResetModel”, sh
Application.Run “OpenSolver.xlam!OpenSolverAPI.SetObjectiveSense”, 0, sh
If I run the solver via the API, it works as expected. But if I open the model using the ribbon GUI, I see the objective cell still set to “minimize” (the default value). Same problem if I open the model with the frontline solver, which is strange, as both are supposed to share the same way to store models.
Is it only a graphical bug, or is there a deeper problem?
Hi Alexandre, apologies for the delayed reply. For others who have a similar issue, it seems in the code that the Enum ObjectiveSenseType has set the following values: 0 is UnknownObjectiveSense (will set to minimize by default), 1 is MaximiseObjective, 2 is MinimiseObjective, 3 is TargetObjective. Let us know if this does not solve it.
Error -2147220504: Mismatched parentheses
Non-linear parser failed while processing cell vs_B54.
B54 is the cell with the if function and it’s never wrong.
If I remove the if function, bonmin works fine.
Bonmin can’t handle if function?
Can you tell me what could be causing this problem?
If() functions and optimisation do not go well together, sorry. Remove the if() if you can, or solve twice – once assuming the if() is TRUE, and once with it FALSE(). You can re-create the if() using a binary variable to do this automatically. Cheers, Andrew
When trying to download Open Solver I get the following message:
Microsoft recommends you don’t continue to this site. It has been reported to Microsoft for containing harmful programs that may try to steal personal or financial information.
The message is about the website: sourceforge.net not OpenSolver per-se.
Any comments about this?
We don’t know why Microsoft are saying this now. Note that Chrome and Firefox give no such warnings. The file has not changed since Dec 2020. We have reported this false alert to Microsoft.
You can download the file directly from https://sourceforge.net/projects/opensolver/files/ if you prefer. (By the way, we recommend using version 2.9.4; this fixes some bugs.)
Your email address will not be published. Required fields are marked *