We welcome your comments on OpenSolver.
|
||||
FeedbackWe welcome your comments on OpenSolver. 78 comments to FeedbackLeave a Reply |
||||
|
Copyright © 2013 OpenSolver for Excel - All Rights Reserved Powered by WordPress & Atahualpa |
||||
OpenSolver is great, thank you! I’m trying to call SolverDelete to make adjustments to the model in VBA, however I just get a “Function Not Defined” error. I can call RunOpenSolver just fine, but I can’t seem to call the other functions. I feel like it’s something simple…Any advice?
Many thanks,
-JC
OpenSolver does not have any such functions. But the model is stored using the usual defined names, so you can still use the Solver functions to manipulate the model. Cheers, Andrew
Thanks for OpenSolver – it’s really great. But after some time of using I have a problem sometimes. It looks like:
OpenSolver: Calculation Error Occured…
Warning: The initial worksheet calculation did not complete, and so the model not be generated correctly. Would you like to try again?
Some other guys haven’t this problem on the same data – may it be because of my 2007 Office instead their 2010? Or the reason is the different?
We think this is an Excel issue. We first noticed on a 70,000 variable model where Excel would report the calculation had not completed. OpenSolver checks for this, and re-tries several times. If it still fails, you get the error message you report. Do you have a big model? If so, I’d suggest using our free companion product SolverStudio (www.solverstudio.org). Hope this helps, Andrew
Hello everybody,
is there a limit of decision variables that can be handled in the Open Solver?
Do you think a problem with 25’000 decision variables could be solved in an “appropriate” time?
Regards,
Robert
The only limit is memory and time; it depends on your problem as to how hard it will be to solve. However, for a problem of this size, I’d recommend http://SolverStudio.org Cheers, Andrew
Do I need to install official solver add-in to use OpenSolver?
I’m asking because one of my developed macro which use OpenSolver occur a problem of missing solver reference and can not be executed.
Thank you.
Thank you for this amazing tool.
However, there’s a problem occur while using excel 2007 but it works perfectly in excel 2010, please refer to the hyperlink below
http://ppt.cc/jGx1
Greatly appreciate your awesome work!
Regards,
Julian Chu
I see you are getting an error “bad file name or number (at line 102320) (at line 19540)”. Can you please tell me the full path of the directory which contains OpenSolver? Thanks, Andrew
Spreadsheet has been sent, thanks.
Julian: Thanks for investigating this “bad file name or number” error, and finding that it was fixed by ensuring OpenSolver is in a directory with an English name (ie no Chinese characters in the full path to the OpenSolver file). I’m sure others will appreciate knowing this. Andrew
I have a compatibility issue between OpenSolver 2.1 and Excel 2007 which doesn’t exist with Excel 2010.
When I run my model using Excel 2010, I have no issues (the solution is created in Excel 2010), however when I run the same model using the same file using Excel 2007 on the same PC, I get the following message and a far from optimal solution).
As our organisation uses Excel 2007, I’d like to find a solution other than upgrading Excel.
Warning/Error Message:
”
OpenSolver could not find an optimal solution, and reported:
No Feasible solution
The solution generated has been loaded into the spreadsheet.
CBC Solver reported: Infeasible – object value -462.19378370
“/b>
Is this because your model is perhaps non-linear? The results in such cases will be rather random! Andrew
The model isn’t non-linear but does involve one discrete variable. By removing this from the solver, the solver works.
OpenSolver should be fine with integer and binary variables, as long as there are no vlookup(), if() or similar functions in your model that depend on the decision variables. Please feel free to email me your spreadsheet if you’d like me to look at it: a.mason at auckland.ac.nz. Cheers, Andrew
The tool is already great and I thank the developer to have it available to everyone for free, but if I pick one major feature to see in the tool (if I am not missing anything): It will be great if we can terminate cbc solver prematurely through an excel dialog box. For example, the solve button brings up a dialog box that allows the interruption (and potentially restart if cbc allows it).
Is it possible to model a semi-continuous variable with OpenSolver?
I’m trying to do something like x = {y OR 0}, where x and y are both variables.
My first attempt was introducing a binary flag variable like this:
x = y * flag
but that doesn’t work because then we’ve got a non-linear constraint (two vars multiplied together).
Any suggestions? thanks!
I suggest you check out an integer programming textbook. The form you will need will depend on how this interacts with the objective function. There are some good examples at this INFORMS Transactions on Education article. Hope this helps, Andrew
Is there a way to localize (translate) OpenSolver?
OpenSolver will need to be improved to allow localisation. We would welcome contributions to this Andrew
First, thank you for a very nice tool!
Second, I thought you’d like to know that on the ‘Using OpenSolver’ page (cannot enter Comment there), the link to the online textbook by Robert Vanderbei now shows the following:
> Linear Programming: Foundations and Extensions
> At the publisher’s request, the online version is currently unavailable.
Finally, I’m running OpenSolver for Excel in the Excel 2013 Preview version with absolutely no issues so far … so it looks like the ‘forward compatibility’ is working!
Thanks for the link info; the other information on thatr page is still useful, so I will leave it up. I was pleased to read your comments that OpenSolver works in Excel 2013. Much appreciated. Cheers, Andrew
Hello!
I love OpenSolver. Thank y’all so much
My team at work are now using OpenSolver to optimize staffing and production. The runtime for our model (build + write to disk) is now at about 45min with 7700ish variables. We’d like to use the Quick Solve to cut that down but haven’t been able to find any documentation for how to set it up for our model. Is there some that we’ve overlooked? Thank you!
Sorry, there’s no documentation apart from what’s on the web site. For big models, SolverStudio (http://solverstudio.org) will dramatically reduce your run times as it allows you to build the model in a proper modelling language such as AMPL or GMPL or PULP. Your 45 minutes will then reduce to 1 or 2 minutes. Hope this helps, Andrew
I’m getting “model cannot be solved as it has not yet been built. What am I missing?
Dear OpenSolver Developers:
Thank you for the good work to develop OpenSolver and make it free for users to download. Opensolver has been very helpful and I just would like to let you know that at least one student from UC Berkeley has benefitted from your development.
Thank you!
I’m pleased that it has been useful. If you have a chance, we’d love to hear more about what you were doing with OpenSolver. Cheers, Andrew
Andrew,
I would like to report an error when running OpenSolver in Excel (Visual Basic).
In my application, OpenSolver is called in a loop that has to be executed a number of times. When running the application, after 3 or 4 loops depending upon the case, a message appears:
Model Solve Failed: Unable to delete file C:\Documents and Settings\Daniel\Local files\Temp\model.lp.
After clicking the error message, the calculation of the current step is skipped and the program continues with the calculation of the next 3 or 4 loops and then the error message appears again.
The error appreared in the earlier version 1.4 and still appears now with the latest version 1.9.
Kind regards,
Daniel.
Daniel. Thanks for the error report. I am away for Christmas, but will have a look when I get back in the new year. Thanks for trying this with the different versions. Did you try anything before version 1.4, and if so, did it also cause an error? Please note that there is better error handling in the latest version in that errors are thrown right back to the caller, so you can trap this error yourself in your VBA code and try again. See the page on this site under Using OpenSolver for more details. Merry Christmas, Andrew
Daniel: If you search the code, you will find a “Close 1″ which should probably be “Close #1″. However, there is another “Close #1″ that also should close this file, so I’m not sure what’s going on. Maybe you could email me a spreadsheet I could look at to: a dot mason at auckland dot ac dot nz. Thanks. Andrew
Andrew,I did not try earlier versions than the 1.4.
The error occurs in Excel 2003 and in Excel 2007.
Without the proposed correction and with “ScreenUpdating false” it occurs every 3 to 4 loops; with the correction (indeed in de code there was one statement “Close 1″ instead of
“Close #1″) the error occurs every 8 to 9 loops. With “ScreenUpdating True” it occurs every second loop.
I am using another (commercial) tool in parallel; the Simplex method does not work but more sophisticated methods do the job. If a calculation is executed by OpenSolver, then the result is correct. Don’t let your holidays be spoiled by this; Happy New Year!
Daniel.
Daniel: I have now tried to reproduce the problem; it doesn’t fail for me even after trying 1000 solves in succession. Could you perhaps send me your example? a dot mason at auckland dot ac dot nz. Thanks, Andrew
Hallo,
I have encountered error in OpenSolver version 1.7 , when I was trying to pass more parameters to CBC through OpenSolver_CBCParameters range. The error was caused by lines
ParamName = CBCParametersRange.Cells(i, 1).Trim
and
ParamName = CBCParametersRange.Cells(i, 1).Trim
This modification works for me:
ParamName = Trim(CBCParametersRange.Cells(i, 1))
ParamValue = Trim(CBCParametersRange.Cells(i, 2))
I’m using Czech version of MS Excel 2007
Best regards,
Karel Charvát
Karel: Thanks for your bug report and fix; bugs like are what happens if you program in C#, Python and VBA! Getting a software fix is great – and what I love about OpenSource! I will make sure this gets incorporated into the next release. By the way, what setting are you changing? You are probably the first person to use this – I have never needed it. Andrew
Andrew: I’ was only doing some experiments with parameters settings.
It was curiosity more than real necessity. As I know, CBC usually does well with its default settings. Only parameter, I sometimes change, when I work with CBC, is integer optimality tolerance, and in OpenSolver case, it is possible to change this parameter through the settings form.
I’m also sometimes using CBC with PuLP, and I think, It’s good open source solver. It was great Idea to use it with VBA add-in and you have done really good work
I will probably do some experiments with your OpenSolver studio, where you putting together OpenSolver and PuLP
Btw: I’m Ph.d students of program Econometrics and Operations research at University of Economics, Prague. As Ph.D students, I’m teaching practical lectures of Software for Mathematical Modelling.
In these lectures, students are using Excel Solver, LINGO and MPL with CPLEX. I’m trying to find some good tools, which may help to students, when they graduate and start work as analysts.
In most cases their company wouldn’t like to spend 10000$ on professional modeling tools with professional solvers like CPLEX or GUROBI.
I think, OpenSolver is great tool for models with not very complicated structure, if the number of variables and number of constraints exceed limits of standard excel solver, or if the computation time in Excel solver is too long.
Karel Charvát
I installed OpenSolver in Excel 2010. But when I click on the Model button, I get an error “Run-time error ’459′: Object or class does not support the set of events”. Clicking on the “Debug” button then takes me to
Sub OpenSolver_ModelClick(Optional Control)
‘frmAutoModel.Show
‘frmAutoModel2.Show
If Not CheckWorkSheetAvailable Then Exit Sub
=> frmModel.Show ‘ Where the error occurred)
DoEvents
End Sub
Any idea what I might be missing? The addin is installed in “\Profiles\[user]\Application Data\Microsoft\AddIns\OpenSolver17\”
Many thanks.
Thanks for the detailed error message. I have just tried this in Excel 2010. (I normally test in 2007.) Clicking the Model button works for me (at least on an empty spreadsheet, and then after creating a model, closing, and clicking Model again). Could you please try this in your version? If you still have a problem, I will send you a special debugging version to help track the problem. Thanks, Andrew. PS: It could possibly be a problem with the RefEdit control… please try the Excel Repair link given at http://opensolver.org/help
Andrew,
Thank you for responding. I tried the following:
* Clicking the Model button in a new empty spreadsheet produced the same error
* Clicking on “Set Quick Solve Parameters” brings up an input box (“Please select the ‘parameter’ cells …)
* Clicking on the “Option…” under Model dropdown successfully brings up the “Open Solver – Solve Options” dialog.
Since “Set Quick Solve Parameters” dialog works, do you still think I should use the Temp File Deleter? Many thanks.
I went ahead and ran the Temp File Deleter. But that did not help either.
I would guess that your ListBox control is corrupted or missing, so please try to repair your Excel, and then, launch the Visual Basic editor (Alt-F11), and check Tools… References… for any ‘missing’ items. Good luck, Andrew
Can you also please try “Set Quick Solve Parameters” under the OpenSolver menu; this will test the RefEdit on a simple form. Thanks, Andrew
Thanks for letting me know that repairing Excel fixed your problem, as described at http://www.sobolsoft.com/question/#Run-time_error_459:_Object_or_class_does_not_support_the_set_of_events. Cheers,
Andrew
Hi, I noticed that it is required to ‘turn on both the “Assume Linear Model” and “Assume Non Negative” Solver options’. But I tried to run a model with the value of a variable that could be negative without turning on the non-negative option, and it worked well. Just want to know if this is also a right way. Thanks.
Sorry, that is a mistake in our documentation. OpenSolver no longer needs “assume non negative” to be turned on; we have updated the web pages. Thanks for alerting us to this. Andrew
What method exactly is opensolver using? I noticed I get different results in R/MATLAB using their solve functions than I do with opensolver working with an overdetermined system. I believe R/MATLAB will use a least squares fit if appropriate for over-determined systems but what does opensolver do? The results are very much different.
It seems this uses Newton’s Method/steepest descent?
Greg: OpenSolver only solves linear problems, for which it (and Solver) use the Simplex method with Branch and Bound. Newton’s method is for non-linear problems. Hope this helps, Andrew
Hello,
Thanks for providing OpenSolver. It is a boon for students like me who like the UI of excel. I have a question. Is there a limit on the number of characters that can be entered in the “variable cells” dialog? It ceases to accept variable after reaching 266 characters. Is there any way to get around this?
Thanks,
Sashi
Sashi: I think the RefEdit control we are using is setting that limit; we don’t impose any restrictions. Solver also seems to have a 256 character restriction. You can (possibly) get around this by defining your decision variables using a local Named Range with a name such as “Sheet1!solver_adj”. Create this using the Excel Name Manager, making sure the “scope” you choose is the name of the sheet, not the workbook. Don’t be surprised if other things then break, though; I have not tried this! Another approach is to create a contiguous block of new decision variables (which then won’t need many characters to define the range), and then put formulae in the old decision variables that reference the new decision variables. Note that OpenSolver does not require the decision variables and the constraints to be on the same sheet, but Solver does I believe. I look forward to hearing how you get on. Andrew
Thanks Andrew. Named ranges was the way I handled it and it works like a charm. Thanks for the suggestion.
Sashi
Hi Andrew,
I am using OpenSolver to solve some IP problems that are generated for student assessment. The VBA code is now as follows. Whenever I click a button, a macro will run and generate a random problem. I tried to use RunOpenSolver False and it returns error saying that the sub or routine is not found. I now use openSolver.RunOpenSolver False and it seems to work. However, an error returns (I have error catcher but do not know what it is) even though some solutions has been found. It turns out that that feasible solution is not optimal because I am able to click the OpenSolver button or solve through Solver and both give me the correct optimal solution, not the feasible solution I got from running the VBA code. Do you know why this is the case? Thanks,
Xuan, I’m pleased to hear you are using OpenSolver. I cannot think of any reason why you would be getting an error. Perhaps you’d like to email the file to me. I can have a look at it to see where the problem lies. My email is a dot mason at auckland dot ac dot nz. Cheers, Andrew
Hi Andrew,
I sent you an example with the issue I described above. Thanks for your help,
Vinh
Xuan, If you want to use OpenSolver from VBA, you need to add a reference to “openSolver” in your project. I’ve added info on doing this at http://opensolver.org/using-opensolver. I hope this helps. Andrew
Hi Andrew,
Congratulations on the 2011 COIN-OR INFORMS Cup!
All the best,
Vinh
Dear OpenSolver team – great work!! I realize the next question is a leap but is there any plan to allow this wonderful tool to solve MINL or at least MIQP ? I realize there might be liminations to the CO-IN framework but I thought I’d ask…
Cheers,
Georgi
Georgi: We have thought about an extension into the non-linear world, and there are some good COIN-OR tools for these problems, but it is not on our current to-do list, sorry. Andrew
Hey,
I just wonder that is there similar VBA functions in opensolver like those in SOLVER.
e.g. SolverReset, SolverOptions, SolverOk, SolverAdd, SolverSolve.
I would like to run linear programming repeatly through VBA.
Thank you.
Jadeson
Jadeson, Because OpenSolver works with standard Solver models, you can use all the standard Solver VBA functions such as SolverAdd to construct and modify the problem. Then, when you want to solve it, you should call OpenSolver using SolveModel(SolveRelaxation As Boolean). This does not currently return any result to tell you what happened. However, Mauricio suggested the function below for this. (Thanks, Mauricio, for sharing your code with us.) We are working on making OpenSolver more VBA friendly. Cheers, Andrew
Public Function statusReport(SolutionFilePathName As String) As Integer
‘ Read in the solution, status first
If Dir(SolutionFilePathName) = “” Then
MsgBox “The CBC solver did not create a solution file. No new solution is available.”, , “OpenSolver Error”
statusReport = -1
Else
Open SolutionFilePathName For Input As 1 ‘ supply path with filename
Dim cbcStatus As String
Line Input #1, cbcStatus ‘ Optimal – objective value 22
‘ Line Input #1, junk ‘ get rest of line
If cbcStatus Like “Optimal*” Then
statusReport = 0
ElseIf cbcStatus Like “Infeasible*” Then
statusReport = 1
ElseIf cbcStatus Like “Integer infeasible*” Then
statusReport = 2
ElseIf cbcStatus Like “Unbounded*” Then
statusReport = 3
ElseIf cbcStatus Like “Stopped*” Then ‘ Stopped on iterations or time
statusReport = 4
Else
MsgBox “The response from the solver is not recognised. The response was: ” & cbcStatus, , “OpenSolver Error”
statusReport = -1
End If
Close #1
End If
End Function
Thanks a lot. It is a really good tool.
Is there any way to know the status of the solver after the method “RunOpenSolver” was executed on VBA?
Thanks,
Mauricio, Unfortunately not at the moment. But you can change the code in “Function SolveModel(SolveRelaxation As Boolean)” so that OpenSolver returns an error message or result code instead of showing a dialog. This is on our to-do list, but if you do it first, we’d love to receive the changes. Andrew
I have a model that runs fine with Solver but when I try to run it with Open solver, it gives me an Error 1: Object variable or with variable not set.Am I doing something incorrectly?
You may have found a bug; sorry about that. Are you able to send us your spreadsheet? My email is a dot mason at auckland dot ac dot nz. Thanks, Andrew
Hallo,
When I click solve button, I always get Type mismatch error. I’m using Czech version of office 2007. I think it has something to do with Czech national environment, because the error occurs on lines of source code, where OpenSolver is reading values of convergence, tolerance and precision from excel solver.
If I change the source code and set the values of these attributes manually instead of using mid() function, The OpenSolver works and finds optimal solution.
Best Regards,
Karel Charvat
Karel, Thanks for your detailed error report. We have fixed this in version 1.3 which I will email you for testing. Thanks, Andrew
Hi All… Trying to solve a IP which shows infeasible solution when integer values are greater than 10 … Any comments??
Vyanjana, It seems like you have problems with your model, not with OpenSolver…. is that correct? (In other words, does it work with Solver, but not with OpenSolver?) It is not surprising that changing the model can make it infeasible. I would suggest that you use OpenSolver’s Show Model and then very carefully check your constraints. Hope this helps, Andrew
Is it available for OpenOffice.org for use on Linux and/or Mac OS X? If this is a repeat question, my apologies (and please post to a link to the relevant info). Thanks.
Sid.
Have never tried OpenSolver on a Mac. The VB code should mostly work, but it has some Windows specific code to run CBC that will need changing. You will also need a version of CBC compiled for the Mac. The CBC site http://www.coin-or.org/projects/Cbc.xml suggests a Mac version is available. Let me know how you get on; I’d love to make a Mac version available if you succeed. Have not tried OpenSolver on Linux, but the same comments apply. Andrew
The call Solver v. 0.982 (2010-08-17) leads to occurrence error 13. In the initial table of errors is not present. In the first variant for nonintegral numbers it was used «.», in the second variant «,». In both cases there is a message «Type mismatch Source = OpenSolver». What to do? Thanks, Alex.
If you like to send us your spreadsheet, we can add it to our suite of test problems to check our forthcoming release (now only days away). We expect this new version to fix all the problems we have found, and to be much faster for large problems. Andrew
I recently worked on project of mine,where I had to use Open Solver through VBA .I am very sure that you guys would be knowing the procedure.But as a user of this open source software,I found it pretty difficult to know the VBA code for running Open Solver.It would be really helpful to the users if you could post this code on your site. This is the code to run it:
Set NewRange = Range(“DecisionVariables”)
OpenSolver.InitializeQuickSolve
OpenSolver.RunQuickSolve
Even though it might be just 3 lines of code,I waste close to a week to find this out.I would really appreciate if you guys could post this info on your site.
Thank You.
Arjun Srinivasan
Arjun: Pleased to hear that you are using OpenSolver. However, your code for calling OpenSolver does not look right. I suggest you look at the code in OpenSolverMain, eg see OpenSolver_SolveClickHandler. You only need one line of VBA to solve a model: RunOpenSolver false. (Set the argument to be true to solve while ignoring integrality requirements.) The RunOpenSolver, InitializeQuickSolve and RunQuickSolve subroutines provide more detailed examples of using OpenSolver. Hope this helps, Andrew
PS: What are you solving with OpenSolver? We love to hear how this code is being used…
The software is working great. But only use integers. If decimals are used, the results are unreliable.
Maurice: Thanks for the feedback, and your subsequent help in tracking down the internationalisation issue causing this problem. The next version shoudl fix any “,” vs “.” issues that arise in non-English systems. Andrew
Thanks, Andrew! I’m using OpenSolver on a project I’m doing for a Major League Baseball team, and it’s exactly what I needed. It lets the team use their preferred platform, creates and solves their LPs very quickly, and doesn’t constrain them with any variable limits. Thanks again!
The OpenSolver.zip file has now been uploaded; our first public beta release! Andrew