I recently installed Gurobi on my Windows 7 laptop and obtained the academic license. However when I attempt to use it to solve a LP I receive the message shown below which translates to saying that something went past the end of the file.
Suggestions??
02 ago 16 11:41:23 [OpenSolver.xlam] CSolverGurobi.ReadResults: Line 0
02 ago 16 11:41:23 [OpenSolver.xlam] SolverCommon.SolveModel: Line 0
02 ago 16 11:41:23 [OpenSolver.xlam] OpenSolverAPI.RunOpenSolver: Line 0
Error 62: Se sobrepasó el final del archivo
Solver: Gurobi
Version 2.7.1 (2015.06.28) running on 64-bit Windows 6.1 with VBA7 in 32-bit Excel 14.0
CBC v2.9.4 (64-bit) at “C:\Windows\addins\Solvers\win64\cbc.exe”
Gurobi v6.5.2 (64-bit) at “C:\gurobi652\win64\bin\gurobi_cl.exe”
Also when I attempt to use the CBC solver, I receive the following messages:
02 ago 16 11:50:24 [OpenSolver.xlam] CSolverCBC.ReadResults: Line 0
02 ago 16 11:50:24 [OpenSolver.xlam] SolverCommon.SolveModel: Line 0
02 ago 16 11:50:24 [OpenSolver.xlam] OpenSolverAPI.RunOpenSolver: Line 0
Error 91: Variable de objeto o bloque With no establecido
Solver: CBC
Version 2.7.1 (2015.06.28) running on 64-bit Windows 6.1 with VBA7 in 32-bit Excel 14.0
CBC v2.9.4 (64-bit) at “C:\Windows\addins\Solvers\win64\cbc.exe”
Gurobi v6.5.2 (64-bit) at “C:\gurobi652\win64\bin\gurobi_cl.exe”
Sorry it is not working. Can you please start by trying the latest 2.8.2 OpenSolver pre-release version. It may fix this issue. Gurobi is working for me in this version; I have gurobi604x32. Andrew
I have great interest in working with OpenSolver, but the thing is, I can’t. I have tried both 2.7 and the latest beta release and both give me de same error on start:
“Object library invalid or contains references to object definitions that could not be found.”
I have deleted the “RefEdit.exd” file on my C:\Users\$USERNAME$\AppData\Roaming\Microsoft\Forms folder and tried again, to no avail.
I have also checked the references on macro editor, but all seems ok.
I have used the OpenSolver earlier during my graduation project, but now I would like to use it again on a different laptop (both laptops operate on Windows 10). Somehow the Add-in does not want to appear on the Data tab in Excel. I have checked the available Add-ins and I am also not seeing the Add-in after opening the OpenSolver file.
No, I am not finding it anywhere under the Add-ins. But, Excel is also not asking me to enable any macro when I open the OpenSolver.xlam. I am not sure how to check what causes this?
I’ve just set up Office 365 on my machine to try and fix this, and I am seeing the same problem. Can you try the following to see if it fixes the problem (it is working for me on two different 365 installations).
Go to:
File > Options > Trust Center > Trust Center Settings... > Trusted Locations > Add new location...
and add in the OpenSolver folder (the one that contains OpenSolver.xlam).
Hello Jack, thank you for your idea. I managed to find a slightly different solution in the meantime. I opened the Add-ins tab under Options and selected to add a Excel add-in under “Manage”. Then, I browsed the computer for the OpenSolver file.
This solved it for me. I now see the OpenSolver again under the Data tab!
I’m using v2.7.1 to solve an optimization problem that has a quadratic objective function, but some of the inputs to the function are logistic functions (parameters for the logistic functions are part of the change cells). I’m getting a “circular reference” error, after running for 200,000 or more iterations. I’ve carefully checked all of the cells in the spreadsheet and find no circular references.
Is this just a quirky result from having a complex model? Is there a way to identify the supposed circular reference?
How strange; I’ve never heard of that before. What solver are you using? Nomad perhaps? You could try our pre-release 2.8.2 version, but I doubt it will fix this. Could you perhaps send us a screenshot of the error message to email hidden; JavaScript is required? Thanks, Andrew
For what it’s worth. I can restart the solver and it runs fine. This seems to just be a glitch, when some of my parameters hit their constraint values.
Thank you for your answer but I have many tables as decision variable cells should I count every single cell myself or can I see the total number anywhere?
If you look at the solver output (using View Last Solve Log), it may show you the size of the problem (but this is solver specific). You can also see that for linear models, the OpenSolver status shows the number of variables while it is building (which can be very quick). Hope this helps, Andrew
I wish to try this solver, but I would like to know its limits first. How many variables and constraints can this solver solve?
I have a system of non-linear equations with 500 variables and constraints.
Thanks and Regards,
Ameya Thakurdesai
The Open Solver is a great tool. However when i am optimizing 700 load to get minimum cost, my objective function is not fully optimized. but when i am optimizing only 15 load which is independent, it is giving perfect solution. what could be the issue, is there any specific setting i need to select.
addition:
The objective is to minimize the transportation cost by reducing empty run cost. for e.g. i have 2 cities each having demand of 4. My one van capacity is fixed i.e. 8, so i am combining these 2 cities demand & utilizing the full capacity. But the space of 4 is empty from city 1 to city 2 which is the empty or additional cost. The objective is to reduce this empty cost. so when 700 demand for 150 cities is optimised, it does not give 100% results, there are cases where empty cost can be reduced by 3 to 8%. how to get 100% optimization.
regards
I am having trouble using the ‘alldiff’ constraint. When I am specifying a list of decision variables (all integers) to be different it throws me an error “Unrecognized relationship for constraint” (Error number =-2147220504). Can you please help me here ?
I define my models using range names. However, in the OpenSolver dialog box the objective cell and variable cells are still displayed in form of cell addresses. (in the standard excel solver you will see the range names instead). The constraints are displayed als follows: $A$1:$A$4(production)<=capacity (that is to say range names AND cell addresses appear on the LHS).
Yes, this is our design. We should perhaps show named ranges for the objective and decision variables. We deliberately show both in constraints, as we think that is most useful. All feedback is welcome for alternative designs. Andrew
To add to Andrew’s reply, as of version 2.8, if you enter the name of the range directly into the textboxes (or use the `RefersTo` methods in the API), it will use this name as the reference and not the range. This means if you later change what the name points to, the model will automatically pull in the new reference range.
This is different to how Solver works (and how we used to work prior to 2.8). If you change the range that your name points to after building the model, the Solver model will not update and will instead point to the old range that used to be defined for that name. Effectively what Solver does (and we used to do) is save the range addresses only, and if a name is defined for any range, they show that. So although it is showing the range names in the model form, it isn’t actually ever storing the names internally and won’t update the ranges if the name changes.
In 2.8, we changed our behaviour to allow for the option to work with names as the saved element in the model, which we think is a powerful option to have. Ranges defined this way will appear in the model with their names instead of addresses (for the objective and variables), and as names only for the constraints rather than “address (name)” like you are seeing at the moment.
Jack, Andrew. Thanks a lot for your quick and detailed answers! The way the standard solver treats changes of ranges depends on how you make these changes. If you later extend a range by inserting a new column or row within the existing range, solver will automatically refer to the new range. However, if you extend a range by using the name manager, solver will refer to the old range. (well, this is only my personal experience).
If I understood you correctly OpenSolver will always refer to the new range, regardless of how you later make the change of the range. I find this is a very useful feature of OpenSolver!
I work a lot with range names. Therefore, I personally would prefer a dialog box that shows the entire model in terms of range names (that is to say also objective cell and variable cells without having to type them into the textboxes; for convenience, just having to mark the cells in the worksheet would be better). A nice feature would then be, if the checkbox “show named ranges” referred to the entire model.
Thanks for the feedback. Just to be clear, Solver (and old versions of OpenSolver) store the cell range, eg A1:B7, not the name of the range, and then checks (for display purposes) if there is any named range that matches the cell range it has stored. Inserting a column into the middle of an existing range changes both the cell range stored by Solver, and the definition of the named range, and hence they match again when the model is next displayed. OpenSolver can either store the cell range, or the name of the named range. We would need some way to distinguish these if we used “show named range” to show variables and the objective as named ranges. We will need to think about this. Andrew
Understood, and I agree that choosing the ranges by mouse then having them store the names instead would be great. The real complication is that we may then need to have the option to not convert ranges to names if that’s not desired.
Personally, I would prefer it the way you suggest, for increased modeling power, but it’s also dangerous if someone isn’t aware and chooses by mouse a named range that is later changed and breaks their model. As Andrew said, we probably need to come up with a good way of making this an opt-in power user option.
Andrew, Jack. Thanks again for your quick replies. This makes it very clear.
I like working with range names as they make large models a lot more expressive and easier to maintain. On the other hand, I also made the experience that they might cause headache if changed later 😉
Therefore, I very much appreciate that you explicitly address this issue in OpenSolver. Michael
Playing about with opensolver, at the moment on google sheets to see if I can then convince IT to let me install it at work.
Does the excel version have some kind of invalidated constraint checker?
As the google sheets one just says that my problem is infeasible. It would be useful to know which constraints are in conflict at least at the point of the error.
Tessa. OpenSolver does not need to be installed in Excel… you just need to open the opensolver.xlam file. We don’t provide tools to find the in feasibility but you can always change your model by adding extra variables so it is feasible and so that the new variables tell you where the infeasibility is. Hope this helps. Andrew
Thanks for an awesome product. I have a quick question on using VBA to control OpenSolver
I can run OpenSolver within my model using Application.Run (I do not want to set a reference as the model will be shared) but I cant seem to see how to get to the value of OpenSolverResult so I can tell if the optimization is good or not. If I set the reference it is simple but that is going to make it difficult to share the model. Any suggestions?
Assuming you downloaded an official copy of OpenSolver from SourceGorge, then we can be 99.99% sure that this is a false alert. My anti-virus reports no problems. Different anti virus packages use different approaches to detect malicious software, and they sometimes get confused. Andrew
I have to turn off F-Secure DeepGuard application exploits option to get OpenSolver to run without it being shutdown. It is an official copy and virus checked.
If you are worried, you can see the OpenSolver virus scan results across all major anti-virus packages at virustotal. (Thanks, Jack, for checking this.)
Thanks for reporting your Excel 2016 issues. Please try the 2.8.2 pre-release and email us with more information via email hidden; JavaScript is required if that doesn’t work. Thanks, Andrew
Open solver is a great tool for optimization in excel. However, I ran into some problems. For my master thesis I have made an ILP with 30604 variables. When I want to solve the problem with the standard linear solver, it takes a (too) long time to set up the problem. When setting up problem, it takes around 3 seconds for 200 variables to be processed.
– I am using the latest version of the opensolver.
I really hope that I can get some good advice or help on how to speed up this proces. Many thanks in advance!
You have a big problem; there is no easy way to make it work faster. I suggest you (1) remove any un-needed calculations or plots from your workbook, (2) try solving with a non-linear solver (which analyses the formulae in your workbook, which can be faster), (3) try http://solverstudio.org (which means learning a modelling language), or (4) be very patient! Good luck, Andrew
Thanks for your fast response! I cannot simplify the modelling, so I will test the other solver engines or consider using solver studio. Have a nice day!
Hi Onno,
I’m having the exact same problem (too many variables and very slow calculations). Did you manage with one of the other solvers? Which would you recommend?
Thanks
I find that the setup time of large optimization problems takes a long time and there is not much you can do if you use OpenSolver.
However, solving the optimization does not need to take very long though. I am using the Gurobi engine to solve my optimization, because it utilizes multi-CPU and is able to use 100% of my CPU capacity. The other engines run on a single core and take much longer.
I recently installed Gurobi on my Windows 7 laptop and obtained the academic license. However when I attempt to use it to solve a LP I receive the message shown below which translates to saying that something went past the end of the file.
Suggestions??
02 ago 16 11:41:23 [OpenSolver.xlam] CSolverGurobi.ReadResults: Line 0
02 ago 16 11:41:23 [OpenSolver.xlam] SolverCommon.SolveModel: Line 0
02 ago 16 11:41:23 [OpenSolver.xlam] OpenSolverAPI.RunOpenSolver: Line 0
Error 62: Se sobrepasó el final del archivo
Solver: Gurobi
Version 2.7.1 (2015.06.28) running on 64-bit Windows 6.1 with VBA7 in 32-bit Excel 14.0
CBC v2.9.4 (64-bit) at “C:\Windows\addins\Solvers\win64\cbc.exe”
Gurobi v6.5.2 (64-bit) at “C:\gurobi652\win64\bin\gurobi_cl.exe”
Also when I attempt to use the CBC solver, I receive the following messages:
02 ago 16 11:50:24 [OpenSolver.xlam] CSolverCBC.ReadResults: Line 0
02 ago 16 11:50:24 [OpenSolver.xlam] SolverCommon.SolveModel: Line 0
02 ago 16 11:50:24 [OpenSolver.xlam] OpenSolverAPI.RunOpenSolver: Line 0
Error 91: Variable de objeto o bloque With no establecido
Solver: CBC
Version 2.7.1 (2015.06.28) running on 64-bit Windows 6.1 with VBA7 in 32-bit Excel 14.0
CBC v2.9.4 (64-bit) at “C:\Windows\addins\Solvers\win64\cbc.exe”
Gurobi v6.5.2 (64-bit) at “C:\gurobi652\win64\bin\gurobi_cl.exe”
Sorry it is not working. Can you please start by trying the latest 2.8.2 OpenSolver pre-release version. It may fix this issue. Gurobi is working for me in this version; I have gurobi604x32. Andrew
Hi there.
I have great interest in working with OpenSolver, but the thing is, I can’t. I have tried both 2.7 and the latest beta release and both give me de same error on start:
“Object library invalid or contains references to object definitions that could not be found.”
I have deleted the “RefEdit.exd” file on my C:\Users\$USERNAME$\AppData\Roaming\Microsoft\Forms folder and tried again, to no avail.
I have also checked the references on macro editor, but all seems ok.
Any help plz?
You could try repairing your Excel installation. Are you on Windows or Mac? Macs and Excel 2016 are not OpenSolver friendly.
I have used the OpenSolver earlier during my graduation project, but now I would like to use it again on a different laptop (both laptops operate on Windows 10). Somehow the Add-in does not want to appear on the Data tab in Excel. I have checked the available Add-ins and I am also not seeing the Add-in after opening the OpenSolver file.
What am I doing wrong here?
Is it marked as disabled under the Excel addins list under the File menu under Excel Options?
No, I am not finding it anywhere under the Add-ins. But, Excel is also not asking me to enable any macro when I open the OpenSolver.xlam. I am not sure how to check what causes this?
I’ve just set up Office 365 on my machine to try and fix this, and I am seeing the same problem. Can you try the following to see if it fixes the problem (it is working for me on two different 365 installations).Go to:
File > Options > Trust Center > Trust Center Settings... > Trusted Locations > Add new location...and add in the OpenSolver folder (the one that contains OpenSolver.xlam).See Andrew’s solution here which is a better fix
Hello Jack, thank you for your idea. I managed to find a slightly different solution in the meantime. I opened the Add-ins tab under Options and selected to add a Excel add-in under “Manage”. Then, I browsed the computer for the OpenSolver file.
This solved it for me. I now see the OpenSolver again under the Data tab!
This is a terrific product.
I’m using v2.7.1 to solve an optimization problem that has a quadratic objective function, but some of the inputs to the function are logistic functions (parameters for the logistic functions are part of the change cells). I’m getting a “circular reference” error, after running for 200,000 or more iterations. I’ve carefully checked all of the cells in the spreadsheet and find no circular references.
Is this just a quirky result from having a complex model? Is there a way to identify the supposed circular reference?
Thanks in advance
How strange; I’ve never heard of that before. What solver are you using? Nomad perhaps? You could try our pre-release 2.8.2 version, but I doubt it will fix this. Could you perhaps send us a screenshot of the error message to email hidden; JavaScript is required? Thanks, Andrew
Yes. NOMAD.
For what it’s worth. I can restart the solver and it runs fine. This seems to just be a glitch, when some of my parameters hit their constraint values.
Hi,
Where can I see, how many decision variables and constraints I have? Thanks.
You can see the full model in the Model file (accessible from the OpenSolver menu).
Thank you for your answer but I have many tables as decision variable cells should I count every single cell myself or can I see the total number anywhere?
If you look at the solver output (using View Last Solve Log), it may show you the size of the problem (but this is solver specific). You can also see that for linear models, the OpenSolver status shows the number of variables while it is building (which can be very quick). Hope this helps, Andrew
Hi,
When analyzing the continuation of my model I am trying to read the progress. What does the number on tree mean?
Thanks in advance for your response,
Kind regards,
You need to understand a bit about integer programming for the solver output to make sense. Try Googling MIP solvers. Andrew
Hello Team OpenSolver,
I wish to try this solver, but I would like to know its limits first. How many variables and constraints can this solver solve?
I have a system of non-linear equations with 500 variables and constraints.
Thanks and Regards,
Ameya Thakurdesai
The limits are the problem complexity and excel formulae… we impose no restrctions. Why don’t you just try it? Andrew
Hello,
The Open Solver is a great tool. However when i am optimizing 700 load to get minimum cost, my objective function is not fully optimized. but when i am optimizing only 15 load which is independent, it is giving perfect solution. what could be the issue, is there any specific setting i need to select.
Ashish
addition:
The objective is to minimize the transportation cost by reducing empty run cost. for e.g. i have 2 cities each having demand of 4. My one van capacity is fixed i.e. 8, so i am combining these 2 cities demand & utilizing the full capacity. But the space of 4 is empty from city 1 to city 2 which is the empty or additional cost. The objective is to reduce this empty cost. so when 700 demand for 150 cities is optimised, it does not give 100% results, there are cases where empty cost can be reduced by 3 to 8%. how to get 100% optimization.
regards
Is your branch and bound tolerance 0% in the Options window? Andrew
I am having trouble using the ‘alldiff’ constraint. When I am specifying a list of decision variables (all integers) to be different it throws me an error “Unrecognized relationship for constraint” (Error number =-2147220504). Can you please help me here ?
OpenSolver does not support alldiff, sorry.
Hello OpenSolver Team
Thanks again for this great product.
I define my models using range names. However, in the OpenSolver dialog box the objective cell and variable cells are still displayed in form of cell addresses. (in the standard excel solver you will see the range names instead). The constraints are displayed als follows: $A$1:$A$4(production)<=capacity (that is to say range names AND cell addresses appear on the LHS).
Is it supposed to be like that?
I have already tried out version 2.8.2.
Thank you very much in advance.
Kind regards
Michael
Yes, this is our design. We should perhaps show named ranges for the objective and decision variables. We deliberately show both in constraints, as we think that is most useful. All feedback is welcome for alternative designs. Andrew
To add to Andrew’s reply, as of version 2.8, if you enter the name of the range directly into the textboxes (or use the `RefersTo` methods in the API), it will use this name as the reference and not the range. This means if you later change what the name points to, the model will automatically pull in the new reference range.
This is different to how Solver works (and how we used to work prior to 2.8). If you change the range that your name points to after building the model, the Solver model will not update and will instead point to the old range that used to be defined for that name. Effectively what Solver does (and we used to do) is save the range addresses only, and if a name is defined for any range, they show that. So although it is showing the range names in the model form, it isn’t actually ever storing the names internally and won’t update the ranges if the name changes.
In 2.8, we changed our behaviour to allow for the option to work with names as the saved element in the model, which we think is a powerful option to have. Ranges defined this way will appear in the model with their names instead of addresses (for the objective and variables), and as names only for the constraints rather than “address (name)” like you are seeing at the moment.
Jack, Andrew. Thanks a lot for your quick and detailed answers! The way the standard solver treats changes of ranges depends on how you make these changes. If you later extend a range by inserting a new column or row within the existing range, solver will automatically refer to the new range. However, if you extend a range by using the name manager, solver will refer to the old range. (well, this is only my personal experience).
If I understood you correctly OpenSolver will always refer to the new range, regardless of how you later make the change of the range. I find this is a very useful feature of OpenSolver!
I work a lot with range names. Therefore, I personally would prefer a dialog box that shows the entire model in terms of range names (that is to say also objective cell and variable cells without having to type them into the textboxes; for convenience, just having to mark the cells in the worksheet would be better). A nice feature would then be, if the checkbox “show named ranges” referred to the entire model.
Thanks again
Michael
Thanks for the feedback. Just to be clear, Solver (and old versions of OpenSolver) store the cell range, eg A1:B7, not the name of the range, and then checks (for display purposes) if there is any named range that matches the cell range it has stored. Inserting a column into the middle of an existing range changes both the cell range stored by Solver, and the definition of the named range, and hence they match again when the model is next displayed. OpenSolver can either store the cell range, or the name of the named range. We would need some way to distinguish these if we used “show named range” to show variables and the objective as named ranges. We will need to think about this. Andrew
Understood, and I agree that choosing the ranges by mouse then having them store the names instead would be great. The real complication is that we may then need to have the option to not convert ranges to names if that’s not desired.
Personally, I would prefer it the way you suggest, for increased modeling power, but it’s also dangerous if someone isn’t aware and chooses by mouse a named range that is later changed and breaks their model. As Andrew said, we probably need to come up with a good way of making this an opt-in power user option.
Andrew, Jack. Thanks again for your quick replies. This makes it very clear.
I like working with range names as they make large models a lot more expressive and easier to maintain. On the other hand, I also made the experience that they might cause headache if changed later 😉
Therefore, I very much appreciate that you explicitly address this issue in OpenSolver. Michael
Playing about with opensolver, at the moment on google sheets to see if I can then convince IT to let me install it at work.
Does the excel version have some kind of invalidated constraint checker?
As the google sheets one just says that my problem is infeasible. It would be useful to know which constraints are in conflict at least at the point of the error.
Thanks,
Tessa
Tessa. OpenSolver does not need to be installed in Excel… you just need to open the opensolver.xlam file. We don’t provide tools to find the in feasibility but you can always change your model by adding extra variables so it is feasible and so that the new variables tell you where the infeasibility is. Hope this helps. Andrew
Hello OpenSolver
Thanks for an awesome product. I have a quick question on using VBA to control OpenSolver
I can run OpenSolver within my model using Application.Run (I do not want to set a reference as the model will be shared) but I cant seem to see how to get to the value of OpenSolverResult so I can tell if the optimization is good or not. If I set the reference it is simple but that is going to make it difficult to share the model. Any suggestions?
Thanks
Peter
I have updated the “Using OpenSolver” page to show an example of this. Hope it is useful. Andrew
When i tried to use opensolver F-Secure shut down Excel with the message Possible exploitation detected, Expoit:W32/OfficeExploitPayload.A!DeepGuard
Assuming you downloaded an official copy of OpenSolver from SourceGorge, then we can be 99.99% sure that this is a false alert. My anti-virus reports no problems. Different anti virus packages use different approaches to detect malicious software, and they sometimes get confused. Andrew
I have to turn off F-Secure DeepGuard application exploits option to get OpenSolver to run without it being shutdown. It is an official copy and virus checked.
Sorry about this. I suggest you contact F-Secure, and point out the mistake their software is making. Good luck, Andrew
If you are worried, you can see the OpenSolver virus scan results across all major anti-virus packages at virustotal. (Thanks, Jack, for checking this.)
I am using excel 2016. The sensitivity report does not come out correctly
Thanks for reporting your Excel 2016 issues. Please try the 2.8.2 pre-release and email us with more information via email hidden; JavaScript is required if that doesn’t work. Thanks, Andrew
Open solver is a great tool for optimization in excel. However, I ran into some problems. For my master thesis I have made an ILP with 30604 variables. When I want to solve the problem with the standard linear solver, it takes a (too) long time to set up the problem. When setting up problem, it takes around 3 seconds for 200 variables to be processed.
– I am using the latest version of the opensolver.
I really hope that I can get some good advice or help on how to speed up this proces. Many thanks in advance!
You have a big problem; there is no easy way to make it work faster. I suggest you (1) remove any un-needed calculations or plots from your workbook, (2) try solving with a non-linear solver (which analyses the formulae in your workbook, which can be faster), (3) try http://solverstudio.org (which means learning a modelling language), or (4) be very patient! Good luck, Andrew
Thanks for your fast response! I cannot simplify the modelling, so I will test the other solver engines or consider using solver studio. Have a nice day!
Hi Onno,
I’m having the exact same problem (too many variables and very slow calculations). Did you manage with one of the other solvers? Which would you recommend?
Thanks
I find that the setup time of large optimization problems takes a long time and there is not much you can do if you use OpenSolver.
However, solving the optimization does not need to take very long though. I am using the Gurobi engine to solve my optimization, because it utilizes multi-CPU and is able to use 100% of my CPU capacity. The other engines run on a single core and take much longer.
Your observation is why we created SolverStudio which builds models much more quickly but has a longer learning curve. Andrew