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
Hello,
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.
Aaron
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!
Aaron
Hi,
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,
Andrew
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?
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.
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.)
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
Hello,
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.
Aaron
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!
Aaron
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.
Thanks,
Andrew
Hi,
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.
Best Regards,
Yash
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,
Andrew
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?
Error -2147220504: Mismatched parentheses
Non-linear parser failed while processing cell vs_B54.
Solver: Bonmin
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.)