# Help & Feedback

## Building  Linear Programming models

Writing optimisation models that only use linear mathematical equations and inequalities is not easy. However, most of the time you want to build these “linear programming” models (and avoid non-linear models) because these are easier and more reliable to solve using packages such as OpenSolver. (OpenSolver uses CBC by default to solve these linear programmes.) Check out this Wiki page on linear programming.

The AIMMS folks have a great online chapter describing how to take non-linear requirements (local copy of pdf) and model them using linear programming. (Thanks to Stu Mitchell of PuLP fame for pointing out this resource.) The NEOS guide is also a great resource. If you still have a modelling question (e.g., how do I express this requirement using the rules for a linear program), please ask it on https://www.or-exchange.org/.

## Software Problems

SolverStudio is a VSTO .Net 4 add-in for Office 2007 and later. If you have trouble installing it, you can view the error logs. This page on debugging Office probjects might also be useful, or you may wish to read how to resolve common VSTO installation error or this helpful blog.

Like many modern applications, SolverStudio uses .Net v4. Whenever a .Net application launches, Windows may try to access the internet to check that the certificate used to sign the code has not been revoked via a CRL (certificate revocation list). This Microsoft article has an overview, or you can view the technical details (but also see this very detailed article on changes made in .Net 4 to make all of this simpler and faster). We have never noticed this as an issue, and indeed we find that SolverStudio starts up faster than many other Excel add-ins, adding no appreciable delay to Excel’s launch time. However, we recommend that an internet connection be available at least during the first installation.

SolverStudio installs for the current user only. If you want to install for all users, you need to do a manual install of the registry entries. (For Office 2007, you also need to install this hotfix (KB976477) and make the registry edits detailed in KB976811. Note, however, that on a 64 bit version of Windows, the new registry entry should go under HKEY_LOCAL_MACHINE\SOFTWARE\Wow6432Node\Microsoft\Office\12.0\Common\General\) We are working on making this easier!

We have listed some of the known issues below.

Like many add-ins, SolverStudio will not work with some versions of Office 365 installed from the Windows Store; see this OpenSolver page for more info.

Exception from HRESULT: 0x800401A8
If you get an error “SolverStudio Error: Unable to run model. Exception from HRESULT: 0x800401A8”, then Excel is having difficulty talking to SolverStudio. This article suggests your local .Net cache is corrupted; to fix this please try running …\SolverStudio\SolverStudio\AdvancedInstallers\RefreshClickOnceCache.bat

## 552 thoughts on “Help & Feedback”

1. Hi everyone. Is there a way to run multiple instances of SolverStudio so that I solve multiple problems in different Excel files? Thanks guys!
• Can you please explain in a little more detail? You want to launch multiple instances of Excel (with SolverStudio) to all be running simultaneously? That should be possible for SolverStudio, but you will have to work out how to launch multiple copies of Excel (and not just one instance of Excel showing as multiple windows).
2. Hi all, very interesting site!! I have a question, because I'm not so expert in all these tools. I have a huge excel with many formulas (few thousands of variables), in some cells (as variables) we have some binary entries and some integer , on other different cells calculated through excel formulas and on the variables we have the constraints. Most of the excel functions are not linear ("IF", etc.). I was thinking to use a non linear tool to optimize the function defined in excel. Which of the tools described could help me? Thanks a lot!! Michele
3. Hi everyone, we are currently trying to run SolverStudio in a German (CH) language and regional setting. We adapted the SolverStudio.xlam to accept local range definitions (using semicolons instead of commas as list seperators) when creating the data items. It works perfectly for "Show/Hide Data" and "Edit data" functionalities. BUT when trying to run the model, we get a "data item creation error" and SolverStudio tries to initialize the data item with commas again, although data ranges under the "Edit data" list are still working and listed with the locally correct semicolons. Does anyone have advice on where this "data item creation error" is raised, so we could potentially fix this last problem. It is obviously not raised within the SolverStudio.xlam. Any tips are highly appreciated! Thanks in advance and best, Chris
• Thanks for posting about your work on modifying SolverStudio to support the German language. We designed OpenSolver to be international but SolverStudio has not had the same work to support differences between languages. SolverStudio uses a mix of VBA, Python and C#, and so there could be issues in any of these layers. Please feel free to keep asking questions and point out problems where you get stuck to see of we can help. Can I ask what you want to use SolverStudio for? Andrew
5. I have problem with PuLP. How can I optain binary LpVariable? I set: "setvars = LpVariable.dicts("Setups",set_up,0,1,LpBinary)", but variable receives floats values.
6. I found the solution. The problem was that solverstudio doesnt work in excel installed from store. I didnt even know I was using excel from store (probably some upgrade reinstal original office). After office reinstallation solverstudio works fine!! :) thanks for help :)
• Thanks Andrew M. The thing is that despite installation worked fine I cant see solverstudio in data section. Also its not in blocked add-ins. Maybe because its being canceled by Opensolver (installed) somehow?
• Strange it is not working. OpenSolver will not conflict with it; they work fine together. Did you install it under the same account as you are testing with? Installing as an Administrator will not work, I suspect, if you then test it as a non-admin. Please see the AdvancedInstallers folder (under SolverStudio/SolverStudio) for a full un-install script "UninstallEverythingByBruteForce", and then try again. Hope this helps, Andrew
• Thanks Andrew M. Solverstudio was succesfully installed but it still didnt appear in data section in Excel. Also its not in blocked Add-ins. Where could be problem? Could be there problem that Opensolver add-id is installed together?
• Strange it is not working. OpenSolver will not conflict with it; they work fine together. Did you install it under the same account as you are testing with? Installing as an Administrator will not work, I suspect, if you then test it as a non-admin. Please see the AdvancedInstallers folder (under SolverStudio/SolverStudio) for a full un-install script "UninstallEverythingByBruteForce", and then try again. Hope this helps, Andrew
• Oh thanks, Andrew M! I create other user than admin and on that user count it works. But still I dont understand why I could not install it as admin and use it as admin.. never mind - important is i can use it now! Thanks a lot!
7. Hi Andrew, I am trying to solve a model using AMPL. One of the constraints I am trying to implement involves variables being dependent on the assignment next to it. For example, if Bar A is assigned to Warehouse 1, then Warehouse 2 must have either Bar B or a Dummy variable assigned to it. Do you have any suggestions for implementing this? Thanks
8. Hi Andrew, i have gotten help from Robert Fourer who kindly converted my gurobi py problem to AMPL. He has given me the data file and the model as well. I'm still trying to figure out how to formulate the data file using solver studio but in the mean time i thought i would try to verify Roberts' data file so i made a SheetData2.dat. On running solver studio, i get NameError:global name 'resultsfilename' is not defined. what do i need to do in solver studio to check the result? data file is set MINES := NORTH SOUTH EAST WEST ; param nYears := 5 ; param: royalties extractLimit oreQuality := NORTH 5e6 2.0e6 1.0 SOUTH 4e6 2.5e6 0.7 EAST 4e6 1.3e6 1.5 WEST 5e6 3.0e6 0.5 ; param blendedQuality := 1 0.9 2 0.8 3 1.2 4 0.6 5 1.0 ; param minesLimit := 3 ; param sellPrice := 10 ; Model file is set MINES ordered; param nYears integer > 0; set YEARS = 1..nYears; param royalties {MINES}; param extractLimit {MINES}; param oreQuality {MINES}; param blendedQuality {YEARS}; param discount {y in YEARS} = (1/(1+1/10.0)) ** (y-1); param minesLimit; param sellPrice; var Out {MINES,YEARS} >= 0; var Quan {YEARS} >= 0; var Work {MINES,YEARS} binary; var Open {MINES,YEARS} binary; maximize Profit: sum {y in YEARS} sellPrice * discount[y] * Quan[y] - sum {m in MINES, y in YEARS} royalties[m] * discount[y] * Open[m,y]; subject to AtMost3Mines {y in YEARS}: sum {m in MINES} Work[m,y] <= minesLimit; subject to Quality {y in YEARS}: sum {m in MINES} oreQuality[m] * Out[m,y] = blendedQuality[y] * Quan[y]; subject to OutQty {y in YEARS}: sum {m in MINES} Out[m,y] = Quan[y]; subject to ExtractLimit {m in MINES, y in YEARS}: Out[m,y] <= extractLimit[m] * Work[m,y]; subject to WorkingOpen {m in MINES, y in YEARS}: Work[m,y] <= Open[m,y]; subject to SubsequentOpen {m in MINES, y in 1..nYears-1}: Open[m,y+1] <= Open[m,y];
9. My SolverStudio is not working anymore, I uninstalled then reinstalled the latest version but even the Transportation-PuLP example file is not working. The issue seems to be on the prob.solve() line. The error message I get is: Error executing the model code: WindowsError Traceback (most recent call last): File "C:\SolverStudio_00_09_03_0020160520\SolverStudio_00_09_03_0020160520\SolverStudio\IronPython\Lib\suprocess.py", line 675, in __init__ ...\IronPython\Lip\subprocess.py", line 887, in _execute_child File "", line 32, in ...\PuLP\src\pulp\pulp.pu", line 1641, in solve ...\PuLP\src\pulp\solvers.py", line 1303, in actualSolve ...\PulP\src\pulp\solvers.py", line 1360, in solve_CBC WindowsError: [Errno 13] Permission denied I use to be albe to run SolverStudio before, but now it fails on every file and models I have saved. Any idea?
• Sorry it is not working. Can you please send me ([email protected]) the text from the About SolverStudio window so we can see what version of Excel you are running? Microsoft may have made a breaking change... Andrew
• Can you please try running CBC.exe from the command line? It will be in the SolverStudio folders (under solvers, I think). For some reason, Python is not able to start this exe file. You could also try testing OpenSolver which also runs CBC to solve problems. Andrew
• Hi Andrew, may i ask why the following python code does not run or optimise in solver studio? http://www.gurobi.com/resources/examples/mining i am trying to replicate this example in solver studio but it doesn't work. its meant to be python.
10. Hello, I have a problem to install. Can you help me? Here is the error message: The solution cannot be installed because it is signed by a publisher whom you have not yet chosen to trust. If you trust the publisher, add the certificate to the Trusted Publisher list.
11. Hi Andrew, I'm trying to build some pyomo models in SolverStudio, but running into an error inside RunPyomo.py. I believe it is because the way RunPyomo.py parses the JSON output does not handle cases where variables aren't associated with indexing sets. Specifically, the error returns on line 388: name = varName[:varName.index("[")]; and my result file has a variable with no indexing set (thus '[' is not found). I looked at the "Development" page on this website, but I am not exactly sure how to test any changes I make to the parsing code... > Also note that you can test snippets of your code by running them in SolverStudio under the language “PuLP (IronPython)”, and then copying these snippets into the “init.py” or “RunXXXX.py” file. I tried to copy the contents of the RunPyomo.py file into the PuLP (IronPython) code box, but it didn't seem to like that. Are you please able to provide some guidance? As an aside, am I correct in assuming SolverStudio is closed source?
• Sorry it is not working for un-indexed variables. To test out a fix, you can copy the code to read in the solver result file into an IronPython window in SolverStudio, and then test the changes you make to the code (which we would welcome back as a contribution). To test these changes in RunPyomo.py, you will need to quit Excel and then open it up again so it re-loads the RunPyomo.py code. And yes, the SolverStudio GUI interface is closed source, but the language support files are open. Andrew
12. Installed the latest version of solverstudio. Clicking on Show model does nothing
• Sorry it is not working. Are you using Office 360? Microsoft update this quite frequently. Another known is is having multiple screens of different resolutions. Can you perhaps send us the contents of your About SolverStudio box (copied as text, please) to [email protected] Thanks, Andrew
13. Hi, after installing for the first time SolverStudio, Pyomo and Cbc and setting Pyomo's variable path, when I open an Excel file with an optimisation model (such as pyomo's example) no code shows up on the window on the right (keeps grey). Is there any way to make the code show up on the "show model" option? Thanks a lot.
• There is a new issue in Excel caused by multiple monitors with different dots-per-inch values that can cause this. Could that be the problem? Andrew
• The Show Model window being shown is completely separate from the language (eg Pyomo) that is running. Can you email a screenshot and the contents of the About SolverStudio window (copied as text please) to [email protected] Sorry for the hassle. Thanks, Andrew
14. Hi, I have Pyomo 5.5 (CPython 2.7.15 on Windows 10) and Python 2.7.15:: Anaconda, Inc. And my system spec is a 64-bit operating system, x64-based processor. I also have Gurobi Academic license installed which I have been using with Pyomo and JuMP on my machine for some time now, but when I run the SolverStudio on my computer, I keep getting the following error: Error executing model: Unable to locate file 'Pyomo.exe' Traceback (most recent call last): File "C:\Users\myUsername\SolverStudio\PYOMO\SolverStudio\RunPYOMO.py", line 451, in File "C:\Users\myUsername\SolverStudio\SolverStudio\PYOMO\RunPYOMO.py", line 324, in DoRun Exception: Unable tol ocate file 'Pyomo.exe' However, I have added the directory - C:\Users\myUsername\Anaconda2\Scripts\pyomo.exe - of 'Pyomo.exe' to my system path environment variable, but I am still having the problem described above. I shall be glad for your help in this matter. Thank you in advance.
15. Hello, I have a feature question for CMPL... Am I able to set a max run time in seconds? i.e. Somewhere in the beginning as an option %arg -solver cbc %arg -ignoreZeros %display var xij1* %display var xij2* %display var u_i1* %display var u_i2* %display var y* Thank you
• The argument for the maximum running time is solver specific. Please take a look in the manuals. (Solver parameters for GLPK and CBC can be found at the end of the CMPL manual) This should work for CBC %opt cbc sec 120
16. Hi, the current VSTO is signed with an expired certificate. It expired on 23rd of April 2018. Is it possible to recreate a new package using a valid cert? Regards
17. Errors with modeling multiple Traveling Salesman Problem (mTSP)... emulated page 6-7 of http://oaktrust.library.tamu.edu/bitstream/handle/1969.1/5825/etd-tamu-2006A-INEN-Na.pdf?sequence=1 Please help with the below errors I am getting: ... > create model instance ... > CMPL model generation - failed ## CMPL ERRORS ## > pyCmpl error: Cmpl finished with errors error in file model.cmpl at line 31 : syntax error, unexpected SYMBOL_VAR ... _________ %arg -solver cbc %display var xij1* %display var xij2* %display var u* %display var v* parameters: dist1:=((100000,5,6,10,8),(5,100000,5,12,12),(6,5,100000,8,10),(10,12,8,100000,6),(8,12,10,6,100000)); dist2:=((100000,5,6,10,8),(5,100000,5,12,12),(6,5,100000,8,10),(10,12,8,100000,6),(8,12,10,6,100000)); nrOfCities:=5; cities:=1..nrOfCities; variables: xij1[cities,cities]: binary; #first machine xij2[cities,cities]: binary; #second machine u[1..5*2]: integer[0..]; v[1..5*2]: binary; y: real[0..]; objectives: distance: y ->min; constraints: y>=sum{i in cities,j in cities : dist1[i,j]*xij1[i,j]}; y>=sum{i in cities, j in cities : dist2[i,j]*xij2[i,j]}; sos_i {j in cities: sum{i in cities: xij1[i,j] + xij2[i,j]}=1; } sos_j {i in cities: sum{j in cities: xij1[i,j] + xij2[i,j]}=1; } noSubs {i:=cities, j:=cities, ij: u[i] - u[j] - nrOfCities * v[i] - nrOfCities * v[j] + 1 <= (nrOfCities-1)*(1-xij1[i,j] - xij2[i,j]) ; }
• Hi John, there are two issues: The expression ij im the line noSubs {i:=cities, j:=cities, ij: is not correct. Did you mean i==j? The second problem is in the body of this loop: Unfortunately, it is not possible to use variables within brackets. The following expression is not really elegant but should work: <= (nrOfCities-1) - (nrOfCities-1) * xij1[i,j] - (nrOfCities-1) *xij2[i,j] Mike
18. Hi Andrew, Can't seem to find anywhere online how to I show the problem status as it loads/solves (vars loaded, gap to optimal, etc.)? I am using PuLP. Am I missing something? Thanks
19. Hi Andrew, is SolverStudio available for Excel 2016 for Windows? I could install it but see the input model screen grayed out. Any plans to extend compatibility to Excel 2016?
• Sorry it's not working. We think the issue is a known Microsoft problem to do with multiple monitors and different resolution scaling. Could this be the cause of your problem? Andrew
• Hello Andrew, Same issue with the grayed out model here, however the problem seems to be just with showing me the model, SolverStudio can still read and solve the model that was edited in an older version of office I have installed in another computer I can remotely access. Almost everything is working fine! Could I edit the model in another software (VS Code for example) and then use the Excel just to solve it? Where do I have to look for the *.lp file? Being able to run PuLP from within Excel saved me a lot of time generating reports and sharing it with other coworkers with less understanding of programming languages, thanks a lot!
• Sorry it is not working; I have emailed to request the About SolverStudio text so I can what versions of Excel work and which break SolverStudio. Something must have changed... Andrew
• Just to register the workaround that worked for me after the emails that we have exchanged (I believe it may help someone else if this info is here): Andrew suggested to me to go to File / Options / General and select “optimize for compatibility”. After changing that is necessary to restart Excel. The gray box was gone and I was able to see and edit the model again. Office seems to be doing some weird things with add-ins in these last updates releases, hope that when Excel 2019 comes we don’t get the same issues again… Thanks again for the support!
20. Hello, I have used Excel based Solver for optimization. Now, I have a situation where number of constraints (2400 constraints) and variables (2100 variables) are more than that can be handled by Solver. The optimization problem has Binary variables, Linear Objective Function and Quadratic constraints. While searching web, I came across SolverStudio and I installed on my PC. I am going thru example files and this website and have NO idea where and how to start. How to convert my existing model into something that SolverStudio can understand? Any help would be greatly appreciated. Thanks
• Well done on moving to SolverStudio. I suggest you start by writitng out, on paper, a mathematical description of the problem you want to solve. This can then be entered into SolverStudio. However, the quadratic constraints make it more complicated, and so most of the modelling languages in SolverStudio cannot be used. I would suggest you try AMPL via NEOS, choosing a non-linear solver like BONMIN or COUENNE. Good luck. Andrew
21. Hi, Andrew Is there a way to lock the Model window in excel? I do not want end users to touch and change the codes there. thanks Jing
• No you cannot lock the model window but that's a good idea. I will add it to me to-do list. Andrew
22. I am having trouble getting to NEOS server to work. I've tried testing the connection, and it says that NEOS doesn't respond. But everything appears to be ok when I go to the NEOS server website. Is anyone else having this problem?
• Sorry about it not working. NEOS made a change from http to https; we have a new version that works over https. It will be released shortly. Andrew
• OK. Thanks. At least I'm not going crazy! And thanks for the reply on multi-dimension variable outputs. I haven't yet had a chance to implement it.
23. I've been using SolverStudio for a couple of years now, and I love it. It is so much faster to build a model without worrying about the input/output and have everything be in Excel. I've run into a problem. I'd like to output a variable with 3 dimensions to Excel. The only way I've been able to do it is to set a new 2 dimensional variable = to the 3d variable with one of the dimensions fixed. Let's say I have 3d_variable[i,t,y], with y index being 1 to 5. I can set 2d_variable_1[i,t] = 3d_variable[i,t,1], 2d_variable_2[i,t] = 3d_variable[i,t,2] and so on. But that becomes pretty tedious if, say, y is actually 1 to 30. Is this the only way to do it in SolverStudio?
• Thanks for the kind comments. Look at "dictH" in "Iron Python Data Items" in "Python Examples.xlsx" for a 4D array; this has 2 indices in the rows, and two indices in the columns (or vice versa). In Python, this gives a dictionary of the form below. A 3D array is built in a similar fashion. There are other examples in the different example sheets. Cheers, Andrew dictH = {('B', 1.0, 'R', 'Y'): None, ('A', 2.0, 'P', 'X'): 3.0, ('A', 1.0, 'P', 'X'): 'f', ('A', 1.0, 'P', 'Y'): 'g', ('A', 2.0, 'P', 'Y'): 4.0, ('B', 1.0, 'P', 'X'): 2.0, ('A', 2.0, 'S', 'Z'): None, ('B', 1.0, 'P', 'Y'): None, ('A', 2.0, 'R', 'Y'): None, ('B', 1.0, 'S', 'Z'): 'g', ('A', 1.0, 'R', 'Y'): None, ('A', 1.0, 'S', 'Z'): 'i'}
24. Hello I am trying to use Julia Examples with solverstudio i have use the "Download Julia and Run Installer" button and Julia v6.0 already installed But when i click "Open External Julia Shell" It said "An Error occurred:Julia cannot be found, perhaps because it is not installed" I had tried to click "SolverStudio settings" but got no respond Please help Thanks
• We are close to releasing a new version; perhaps you could try again when we do so. We'll test your scenario as part of the pre-release steps. Andrew
25. Hello Andrew, I read at some point in this thread that this is free for commercial use. Is that correct? Thanks for the great plugin!
26. ## Downloading GDX result file ## Unable to download GDX result file ## GAMS did not complete; no solution is available. ## Done But I was able to verify there is a solution when I ran it. Any idea why my solverStudio is not able to download the solution? job number: 5771112 pw = iaSPUYbu
• NEOS has switched to https; we are working on a new release to fix this. Sorry about the hassle, but we hope to release soon. Andrew
27. Hi Andrew. I am using Excel 2016 but "show model" option doesn't work for me. Any idea?
• Just the "standard" ones, of uninstalling and re-installing. Please also check Excel has not disabled the addin (see Google for how). If it still fails, please get back to us. Andrew
28. Hi Andrew, I have been using OpenSolver for my fully linear problem. It works great, but I am looking to improve the speed even more. My problem was the speed of the problem setup (approximately 30 seconds, I have 2000 variables and 4000 constraints). Do you think I can improve it to less than 2 seconds with SolverStudio ? I ask because I kind of have an entry cost with the modelling langage etc ... Thank you very much Andrew !
• Yes I think there is a good chance if the optimisation step is fast. Please let us know how you get on. Andrew
• Works perfectly ! Just by adapting the code in your VBA example, I reached < 1 second ! Thank you very much for your work, I will talk about SolverStudio to my network :)
• So pleased it worked; this is one of our SolverStudio goals, even though you have to work a bit harder to re-build your model. Andrew
29. Hi Andrew -- First, I want to thank you for your awesome tool. I downloaded the latest version, SolverStudio_00_09_03_00 20160520.zip, and I was able to set it up for my problem. I am using AMPL on NEOS to model and solve. However, when I try to solve the model, I am getting the following error: NEOS Connection Error: An error occurred due connecting to NEOS, perhaps because there is no internet connection or NEOS is currently offline. No results are available. I know NEOS website was up and working. I am not sure where the error is coming from. If you could provide any pointers regarding the above error, that would be great. Thank you for your time. Appreciate it.
• Sorry it is not working. I am getting the same error. Let's check again tomorrow, and if there are still problems, I will ask my student who is working on SolverStudio to investigate. Andrew
• Hi Andrew - Thank you very much for the quick reply. I am looking forward to hearing from you. Best Regards Saravanan.
• We believe this is due to NEOS switching from http port 3332 to https port 3333 a while back. To fix this, a find/replace ran on the AMPL python files for the NEOS solvers should work. You need to change: "http" -> "https" "3332" -> "3333" across multiple lines in multiple python files in the AMPL solver directory. We will release a new version shortly that addresses this. Thanks for bringing this to our attention. Andrew
• Hi Andrew - Thanks for the suggestions. I modified AMPL python files as you mentioned. It is working now. Thanks again, Best, Saravanan.
30. Hi, I run example model and got the error: "Unable to locate file 'Julia.exe' .....line 283 in .....line 190 in DoRun Exception: Unable to locate file 'Julia.exe" How can i solve the problem?
• Please use the SolverStudio Settings menu item to see where SolverStudio is looking for Julia, and set this to a new location. I assume you have installed Julia? Andrew
• I have the same problem with python.exe I used SolverStudio setting to see where it is looking for python and i put it in the new location but i still have the same error ? PS: I have installed Python.
31. I had problems with the CBC, during the resolution it stops working and displays an error. Is it because the problem is too big?

thank’s for help.

• Please see the OpenSolver help pages where we talk about upgrading CBC to a newer version. It may not be problem size. Andrew
32. Hello, thank you for the tool, im loving it. Recently i updated my excel to the 2016 version and now i cant get the Edit Data Form to open, i get an error saying "Error in loading DLL". Could you help me solving this problem please? Thank you
• This is not an error I have seen before, so I'm not sure what to try next. Sorry to ask the obvious, but have you uninstalled SolverStudio, and then reinstalled it? Please uninstall using the UninstallEverythingByBruteForce.bat file in the AdvancedInstallers just to be sure it has uninstalled fully. There are options to investigate further by turning on VSTO logging (SolverStudio uses VSTO); again please see the Advanced Installers folder (and do some VSTO Googling) to do this. Repairing your Excel installation can sometimes help. Please let us know what you learn. Andrew
33. Hi. Love the Solver but I have a question. I have searched the site and some of the code examples to find out how to edit data items in PuLP. No luck. If I have a new matrix at the moment I have to set the ranges for each data item and run the solver. Next time the matrix may be a different size and I need to reset the data items. Is there a command to set a data item in code? Is there a file which can be hacked to achieve this?
• Thanks for the nice feedback. To handle data items that change in size, either define them with a range that is always big enough, and leave blanks rows/columns (which SolverStudio will ignore), or use a dynamic range in the data item definition; see this Microsoft page . Hope this helps. Andrew
• OK! That was easy! Thanks.
34. Hi, I am trying to minimize a function with two integer variables and two continuous variables in excel. Presently the objective function is defined in excel, is it possible to refer the objective function from the spreadsheet cell. i want to use GAMS solver to solve this problem. Here is the problem details: Sheet: Residue Minimise Cell: H8 by changing cell : B2:B3 , Integer from 1 to 25 by changing cell : B4:B5 , continuous from 0.5 to 0.9999 constraints: B2<B3
• You cannot use a spreadsheet calculation as part of the formulae in your GAMS model; all calculations that depend on the decision variables must be in the GAMS model. Andrew
• Please try one of our examples first. If you can find a similar example that is working, then you can copy that. If not, then please tell us more about your model, perhaps via email to [email protected] Andrew
36. Why is there a limit on the number of rows that solver studio can handle. After 30 rows it starts to combine everything after into one giant box
• This is just a visual effect when we show Data Items so we don't crash Excel with too many boxes drawn on the screen. It still works fine. Andrew
37. Hi...we're using Solver Studio with PuLP, and wondering if its there's a way to get the Lagrange multipliers (shadow values) from the results of our optimization. I couldn't seem to find any documentation about doing so. Thanks! Wesley
• Hi Wesley, It is possible to obtain shadow prices and reduced costs in SolverStudio/Cmpl by using the keyword .dual in SolverStudios data editor. Please take a look at the CMPL example file (transportation). Mike
38. Hello, I am creating an Excel-based tool for scheduling a workforce (using AMPL+NEOS)and with every run of the model the input parameters change (i.e. the number of jobs or days). I found that I was unable to dynamically edit the data input for each run and having to manually edit it was tedious. I was able to create vba code to save the parameters to a dat file for AMPL but unable to overwrite the application created data file and I was wondering if there was a way to do so? Thanks
• I would use VBA to change the cells, solve the model, and then copy out the results. Or you can wait for our new Scenario Explorer which does all this for you... Andrew
• Hi Andrew, can you tell me when the scenario explorer might be available? This is something I would be very interested in. Also, will the explorer be available with solver studio or with open solver only?
• Scenario Explorer is having a long gestation period. We hope to make progress over this coming Dec-March period. Andrew
39. Very interested in the product I have no programming experience but want to learn.Im trying to find a optimal fantasy sports lineup based on maximum pts while staying within a salary cap. Each player has name, position ,salary,pts and team. A lineup has max and minimums rules on the positions a maximum salary and maximum times you can use someone from the same team. I currently do this with opensolver I have a macro setup in a loop to give my many lineups but it takes a long time. Which examples should I look at or where should I begin on SolverStudio? Thanks for your help and time
• The PuLP or CMPL examples perhaps a good place to start as these are both completely free. Good luck, Andrew
40. Hi, I have a problem with the install. The following error always occurs. "The following Microsoft Office solution cannot be installed due to a general error: SolverStudio.vsto. 0x80070002"
• I am sorry it is not working. Are you perhaps using Officer 365? This can sometimes have updates that break backwards compatibility. I would suggest you try downloading and installing ".Net Framework 4" and "Microsoft Visual Studio 2010 Tools for Office Runtime"; please see the updated instructions on this error on the Download and Install page. Please let us know if this helps.
41. Hi, I'm having some Trouble with the Installation of SolverStudio on my Laptop. Its running with Windows 10 and Office 2016. I extracted the files as you explained in the download section. However, everytime I try double click the "Setup"file, Windows gives me the following errorcode: 0x8007007E. Do you already have experience with this kind of error and have a suggestion to solve it? I´d be grateful for your advice. Thanks a lot in advance!
• Sorry it is failing. It works fine for me on a similar setup. You can try opening the solverstudio.vsto file and ignoring setup.exe. There are some other suggestions on the SolverStudio Web site for installing the components individually. Also if you google that error you may get some ideas... my googling suggests some file cannot be found but I don't know why that be happening for you. Please try unzipping it again so all files are extracted into one folder. Make sure you unblock the .zip before doing this. Please let us know how you get on. Andrew
• I have posted more suggestions including downloading and installing ".Net Framework 4" and "Microsoft Visual Studio 2010 Tools for Office Runtime"; please see the updated instructions on this error on the Download and Install page. Please let us know if this helps.
42. Hi, thank you so much for the great tool you have created. I would like to run a linear program, however with a quadratic objective function. Do you know, how I can p.ex. install/add CLP as a solver here? Or do you have any suggestions on other open source solvers to use? I managed to get AMPL/Gurobi to work, however there is the 300 variables constraint. thanks again for your great work, Mark
• Pleased SolverStudio is proving useful. You can install the CLP solver in the same folder as CBC, or anywhere on your system if it is added to the PATH environment variable. Let me know if this works... Andrew
• Hi Andrew, thank you for the quick response. I downloaded the CLP binaries from this link https://www.coin-or.org/download/binary/Clp/ and unpacked them into the cbc folder. unfortunately I had to stop there, as I could not find the place on where to change the PATH environment, and also in the script, when I p.ex. change the code in the CMPL examples to "%arg -solver clp" I get the error that the solver has not been found. Please apologize if my question is very entry-level but I hope you might have kind of a step-by-step guideline to help me through here. thank you in advance and best regards, Mark
• You have made good progress. What folder did you put clp into? It should be the SolverStudio folder that contains cbc in which case you don't need to change the path. Check the About SolverStudio menu to see where your solvers are. You can also install the coinor optimisation suite and solverstudio should be able to see it. Let us know how you get on. Andrew. PS I would recommend you avoid non linear objectives if possible as they are often slow to solve. PPS. You might like to try Gurobi as well as a solver.
43. I have been using SolverStudio successfully for some time but I have a new and weird problem today. I am calling SolverStudio from VBA (which I have done successfully in the past). Recently, it seems that in the moment that the sheet where the model resides becomes inactive, the model "disappears". Then whenever the VBA code gets to the part where it is supposed to solve the model it says "There is no model to Solve. Please type in your model". If I stop the VBA after such point, the model does not appear back again. Furthermore, I can't find any action that I can take without closing EXCEL that brings the model back. If I save the Excel file after the model has disappeared to a new filename, let's say "Version2" , close EXCEL, and re-start it, the model is present even in the "Version2" file. i.e. the model has not been deleted, it has just become invisible somehow. If I start EXCEL and then use the "Solve Model" button on the ribbon (before I use VBA and when the model is still visible) the model solves just fine. If I start the VBA code while on any other Worksheet in the Workbook is Active besides the one where the model resides (which is the normal case for this tool), then all the models disappear immediately when VBA starts before any actual lines of code are executed (confirmed by puitting a breakpoint on the first executable line in the VBA). However, if I start the VBA code while the sheet where the model resides is active, the model does not disappear (but note that it is not possible for the VBA code to run from start to finish without the Worksheet having the model becoming inactive at any point). This is all very strange because I have run this system successfully in the past. I can't figure out if I have changed some sort of setting somewhere that is relevant (if so what could it be?) or if perhaps the file has been corrupted (if so how to repair?). Any ideas or tips on what could be causing this weird behavior would be greatly appreciated. This is SovlerStudio v 0.09.01 and EXCEL v 14.0.7172.5000 and using PuLP.
• I think I figured this one out.... So I was activating the sheet that had the model on it before I was trying to run the model BUT at the time I was activating the sheet I had Application.EnableEvents=FALSE. This seemed to have an impact on whether SolverStudio could find the associated model when the sheet was activated.
• Well done sorting this out. You are using SolverStudio in ways that I have never tried. I can see now that disabling events would cause problems; well done figuring that out. Good luck. Andrew
44. I have a small (voluntary) shift planning modell and want (amongst others) to minimize within-week shift changes. But I don't want do name my weekdays (1..5) instead of (Mon, Tu, We, Th, Fr), especially not in the row- and column headings of the excel sheet. It would be harder to use for input. I think I will switch to AMPL/GMPL to describe the model. 15 years ago when I was doing (salaried) optimization I had used GAMS and ILOG OPL Studio (not both at the same time). Do you think in my situation swichtching to AMPL/GMPL is an advisable way to go? By the way: at that time we used Excel as a front-end for the modell. The guys from GAMS privided to us some DLLs so that wo could do the interfacing using VBA. In contrast to SolverStudio the model script file was not integrated into the Excel-Workboot. We stored only it's name in a cell in the workbook. A last question concerning SolverStudio: Is the script defining the model (the thing what I see when I click "Show Model") associated with the workbook or is it possible to compare two models - structural slightly different e.g. objective/constraint exchange - using two worksheets in one workbook ?
• Thanks for your comment; it sounds as though you have been doing some pioneering work. GAMS also kindly provided us with DLL's to access their solver from Excel, but using C# not VBA. Some users find AMPL/GMPL easier than Python/PuLP, but it is less flexible. In Python, you can show Mon Tue etc on the sheet, but then convert these to indices in the Python code if you wish. Or, you can use a combination of, eg, (Week 1, Mon) as a two-row index for your table; this will look sensible on the sheet, and work in PuLP (with a bit of code to get the "next" day). To answer your question, the model is worksheet-specific, so one model per sheet. Good luck, Andrew
45. Hi! If suddenly the Solver Studio has stopped working, but is still showing enabled in Excel, what could be the error? It's not displaying any errors, just nothing happens when we attempt to use Solver Studio on a workbook that we had previously used it with. Otherwise no changes to the system have occurred. Thanks!
• That is unusual; sorry it has happened to you. I would suggest the standard "I don't know" IT answer of uninstalling (using the uninstaller in the SolverStudio folder), and then install again using the setup.exe or SolverStudio.vsto. Your workbooks won't lose any of their models. Please let us know if this fixes it. Andrew
46. Dear Andrew, I wonder if there is some equivalent of the following AMPL expression subject to Balance {p in PROD, t in DAYS: ord(t) > 1}: Make[p,t] + Inv[p,prev(t)] = Sell[p,t] + Inv[p,t]; especially if DAYS means {‘Mo’, ‘Tu’, ‘We’, ‘Th’, ‘Fr’]. I mean any ordered set but not consecutive integer numbers. I don’t know how specify ord(t)and prev(t)in PuLP. If It is not possible in PuLP; is there any other Python based algebraic modelling language I could use. But I would prefer one which is available in SolverStudio as I would like to use Excel for data entry of parameters and presentation of optimization results. Best Regards Rolf
• Python has good loop constructs that would do what you want. I suggest you look at list examples in Python. Also try asking on the pulp forum if you are still stuck. Andrew
• Andrew, thank you so much for looking at my problem. It is not so easy th find teh pulp forum. When trying first I ended at the mailing-list of [email protected] which was somewhat wrong. From my little knowledge of Python I assume that an ordered dictionnary is required to get the next/previous key. Maybe I should rephrase my question: How can I tell SolverStudio that some of my ranges I define using Edit Data habe to be treated as an ordered set and should be used as the keys of an ordered dictionnary? Rolf
• Yes, an ordered dict would work, or perhaps a list of tuples. However, these are not supported in the SolverStudio interface. I'd suggest you use an index your entries, with indices of 1, 2, 3, ...; then you can use +1 or -1 to go to the next item. Hope this helps. Andrew
47. Hig People I have the following problem. When I compilate GAMS model through SolverStudio, the results do not export to excel. Below is the output of SolverStudio Iter Phase Ninf Infeasibility RGmax NSB Step InItr MX OK 0 0 1.9800000013E+13 (Input point) Pre-triangular equations: 48 Post-triangular equations: 14 1 0 1.9799999994E+13 (After pre-processing) 2 0 1.8441187753E+04 (After scaling) 10 0 7 3.5529857506E-01 1.0E+00 F T 21 1 2 2.1377580817E-08 1.1E-03 9 1.0E+00 1 T T ** Feasible solution. Value of objective = 1150.32455993 Iter Phase Ninf Objective RGmax NSB Step InItr MX OK 23 3 6.2064851475E-10 7.4E-09 8 ** Optimal solution. Reduced gradient less than tolerance. --- Restarting execution --- model.gms(303) 2 Mb --- Reading solution for model MEGC1 --- model.gms(328) 3 Mb *** Status: Normal completion --- Job model.gms Stop 10/11/16 09:17:05 elapsed 0:00:00.096 ## GAMS completed successfully. ## Reading results... ## No results were loaded into the sheet. ## Done I am using the option display for export results, however the tables in excel do not update. Please, any help about this issue Best Regards,
• Do the GAMS examples included with SolverStudio work for you? If not then something has gone wrong. For your own code perhaps your solution is not changing... we only load in changed cells. Perhaps clear the cells first to be sure. If it still fails please compare your code with our examples. Hope this helps. Andrew
48. Dear Andrew, I am developing a tool which assigns students to working places. Here, I have one set S for Students and I want to create an indexed set Dpr(s) which contains the presence days for all students. I have set up the data as matrix in excel, e.g. student A is working on days 1-10, student B on days 1,5,6,9 and so on. I am solving with Pyomo and receiving following error: ERROR: Unexpected exception while loading model: 'IndexedSet' object has no attribute 'concrete' ## Error: The Pyomo output file did not contain any valid data. ## Pyomo did not return a valid solution. The sheet has not been changed. ## Done My code: model.Students = Set() model.Presence_days = Set(model.Students) As this is exactly the syntax from the literature, I cannot explain to myself why this happens... As the array of days of student A has length 10 and for B only length 4, I also tried to fill the blanks with 0s, which also didn't help. Can you tell me the meaning of the error message? Many thanks in advance! Best, Mark
• I suspect there is something unusual with your data and/or the interaction of this data with the model. I suggest you look at the Pyomo data file that SolverStudio is creating from your spreadsheet (using SolverStudio's View Working Files menu); this should give you some clues. The Pyomo support folks at their forum may be able to help (which I see you have already found!). Andrew
• Is it possible that SolverStudio only accesses data that is in the Excel Worksheet? I tried to create a list of sets manually in the code: model.Presence_days = Set(initialize=[(1,2),(1,2)]) This set somehow isn't recognized by SolverStudio at all, as it doesn't appear in the data file. In the beginning of my modelling approaches I also tried to create model.Days = RangeSet(10), which also didn't appear in the data file. Am I missing something?
• Data you create will not be written to SolverStudio's data file... that is only used to transfer data from the sheet to the modelling language. Andrew
• Dear Andrew: I am a long-time GAMS user and am interested in SolverStudio. However, it is not clear to me how to enter multidimensional sparse parameter data. If I have data indexed over the simple indexes i,j,k,l, I'd like to be able to define those four indexes in Excel and then enter the parameter data as I would in a database. I've looked at the various examples, but it's not obvious to me how to enter higher-dimensional data, particularly using GAMS. Thanks -
• I'd start by looking at "Python Examples.xlsx" at the "dictH" example on "IronPython Data Items"; this will show you how to lay out data with 4 indices. This should then let you create the data item to load into GAMS. Please let us know how you get on (and perhaps send us an example to add to our workbooks...). Andrew
49. How to specify command line options to solver Hi, I am trying to set command line options to glpk solver. I tried defining a named range GMPLOptions with content mipgap 0.01 (following pyomo examples). However, this does not seem to work. When I check the screen output for solver studio, it is as following: GLPSOL: GLPK LP/MIP Solver, v4.60 Parameter(s) specified in the command line: --cpxlp 7780-pulp.lp -o 7780-pulp.sol So it seems that the solver is not able to pickup the option. I tried naming the range to GLPKOptions, but that too did not work. When I run the above command (from command prompt) with --mipgap 0.01, it works. Wondering what should be the way to pass additional command line options when the solver is glpk. Would appreciate any help or pointer. Thanks Sakib
• I see from your output that you are using the GLPK solver from PuLP (not from GMPL as I'd first assumed). You need to pass any options to Solver using the PuLP syntax given in the PuLP documentation. Hope this help. Andrew
50. Dear Adrew, CBC and GLPK use only maximum 25% of CPU on my computers. I run SoverStudio on Windows 7 64 bit with i5-4670S CPU and Windows 8.1 64 bit with i5-4300M CPU. Why do the solvers use so little of CPU power? Can I increase the load of CPU?
• They are single threaded, and so only use 1 core out the four you have available. You can compile CBC to run in parallel, but it is rather experimental. Andrew
51. Getting this error with the AMPL(Neos) Transportion example: ## NEOS Status: 4 running, 2 queued jobs ## NEOS job submission failed Error=Error: this solver is not allowed to be used via this interface ## AMPL did not complete; no solution is available. The sheet has not been changed. ## No results were loaded into the sheet.
• The NEOS connection seems to be working, it says to be alive. And I have set the email address. But when I switched from the Gurobi solver to CPLEX and the job gets accepted. So it seems to be solver related. Can you run AMPL on Gurobi?
• This suggests that Gurobi cannot be used when accessing NEOS using the REST API that we make use of. Andrew
• I have opened a ticket at NEOS.
• It seems Gurobi will no longer be available. Here is the response I got: [quote] The Gurobi license for NEOS does not permit connections via XML-RPC. If you look at a solver page for Gurobi, for example, https://neos-server.org/neos/solvers/milp:Gurobi/AMPL.html, you will see that the XML-RPC interface is disabled. The license only allows submissions via the web interface. If you were able to submit jobs via XML-RPC previously, that was a mistake on our part introduced during an update to the NEOS codebase. [/quote]
52. Hello! I receive the following error: Error executing model code: Mr. Smith Traceback (most recent call last): File "", line 16, in KeyError: Mr. Smith line 16 is my objective function: prob += lpSum([Penalty_nass[t]*qty[t][d] for t in Student_types for d in Days]+ [assign[s][w][d]*Distance[v,w] for v in Supervisors for s in Students_supervised for w in Working_places for d in Days] + [Penalty_wp_change[t]*wpchange[s][d] for t in Student_types for s in Students_from_type for d in Presence_days]), "Objective" Mr. Smith appears as supervisor in the middle part in the parameter Distance[v,w]. The parameter looks as follows: Mr. Smith, Peter Mr. Smith, Nico Mr. Miller, Thomas Mr. Miller, Markus Mr. Miller Andi I am still very new to SolverStudio and believe that I've made a typical beginner fault. I am thankful for any help! Mark
• Sorry, wrong parameter: Mr. Smith, 5 0 0 10 5 Mr. Miller, 0 5 5 7 0 Thus a distance matrix to working places 1 to 5
53. Dear Mason, I'm using CMPL in SolverStudio for Excel. It seems that I found a minor bug. When I comment a line with # the text is highlightned green, and it works fine. When I put some text as a comment between /* and */ the code is not highlightened green. However, the solver interprets the text as commented. Am I right, or I did something wrong? P.S. Thank you for the plug-in, I love it!
• Thanks for reporting this bug in our code colouring. I have noted it, and will try to fix this in the next release. Cheers, Andrew
54. Hello! Thank you for this awesome software. Something happened to me that has never happened before. I got this output: ## Scanning model for sets and parameters ## Building input file for 4 sets and 6 parameters ## Writing simple parameters... 3 items written. ## Writing sets... 4 items written. ## Writing indexed parameters... 3 items written. ## Running Pyomo... ## Pyomo model file: C:\Users\Augusto\AppData\Local\Temp\SolverStudio va2hauoo\model.py ## Pyomo data file: C:\Users\Augusto\AppData\Local\Temp\SolverStudio va2hauoo\SheetData.dat ## Running: C:\Python27\Scripts\Pyomo.exe --solver=cplex --solver-options=timelimit=3600 --save-results="C:\Users\Augusto\AppData\Local\Temp\SolverStudio va2hauoo\Sheet" "C:\Users\Augusto\AppData\Local\Temp\SolverStudio va2hauoo\model.py" "C:\Users\Augusto\AppData\Local\Temp\SolverStudio va2hauoo\SheetData.dat" --json WARNING: converting to the 'pyomo solve' subcommand [ 0.00] Setting up Pyomo environment [ 0.01] Applying Pyomo preprocessing actions [ 0.03] Creating model [ 0.11] Applying solver [ 0.22] Processing results Number of solutions: 1 Solution Information Gap: 0.0 Status: optimal Solver results file: C:\Users\Augusto\AppData\Local\Temp\SolverStudio va2hauoo\Sheet [ 0.24] Applying Pyomo postprocessing actions [ 0.25] Pyomo Finished ## Pyomo run completed. ## No results were loaded into the sheet. ## Done Any help about the reasons for which, the results didn't load to the sheet? Thx in advance
• If the results are the same as the current values on the sheet, then no new values will be loaded. However, I cannot be sure if that was the case here. You can manually check the Pyomo solution file by using the View Working Files menu. Andrew
• This depends on you doing it in the modelling language. Any solution on the sheet becomes available to the modelling language, and so these values could be given to the solver as a starting solution.
• I am using PULP and a loop to repeatedly add cuts and resolve. I don't go out of my way to reset/re-declare the variable array before resolving, so it has the previous solution in there when the solver is called again. Does that mean it uses this as an the starting solution or is there a special command you have to use during the call to solver that tells it to? Thanks
• I'm not sure what PuLP does. For an LP, you need to pass a basis in to be useful. For an IP, a feasible solution can help in branch and bound. (For non-linear models, a starting solution is much more useful than it is for an LP or IP.) You cna try the PuLP forums, perhaps; Stu watches those. Cheers, Andrew
55. Hello! I'd like to know if using pyomo in solver studio adds parameters involving the gap tolerance or if they are not touched (they keep the default values). Thanks in advance
56. Some on can help me? i received this message : " An erro ocurred while installing systen component for SolverStudio. Setup cannot continue untill all systen components have been successfully installed" How can i solve this problem?
• That error message is not very helpful. I would try repeating it while logged in as an administrator and then doing it again as your usual logon. Andrew
• That message is not very helpful :-( However I would try installing again under an administrator account and then again under your usual account. You need to have Internet access as well. Andrew
57. Hi Andrew, I've used SolverStudio on other machines, and I recently downloaded it to a new laptop. However, I'm getting a error when I try to run my models. On the first line of code it outputs the following. Do you know what may be causing this? Thanks for your help! syntax error in model code: unexpected token 'NAMEOFMYSET' File "",line 3 set NAMEOFMYSET; ^ SyntaxError: unexpected token 'NAMEOFMYSET'
• Yes, I did choose the wrong language! Thank you!
• I have not tried SciPy. It might work? However if it has C code in it then it might take some work. Andrew
58. Hi Andrew, I've seen examples of DipPy in SolverStudio but they seem to be using an external installation of Python. Have you every used DipPy with IronPython? Can you point me to any instructions on how to install DipPy into IronPython? Cheers, Rachel
• Rachel: It's not possible (or, more precisely, I have never tried to do it!). The issue is that DIPPY is a Python wrapper around the C DIP code. We'd have to re-create these wrappers again to do it in the IronPython world. I'll ask one of the DIPPY creators, Ted Ralphs, tomorrow just to make sure I'm not misleading you. I assume your admin folks are not keen on a Python install? Andrew
• It might be possible to wrap the DIP code using SWIG to expose it to C#, which would then be accessible from IronPython. But it would not be a trivial task, I suspect. A>
59. Row/Constraint generation possible? I am solving a TSP and would like solve a relaxed problem and then find and eliminate sub-tours by adding that constraint and resolving. Can this kind of procedural iteration be done in PuLP (Iron Python) in SolverStudio model coding window on the right? Or would it be easier to control the iteration (detect/add subtours constraints) from VBA and call Solverstudio solver from VBA using a VBA loop. Any suggestions? Kind Regards, Nathan
• Nathan: I'd definitely do this in Python/PuLP, not VBA. Re adding cuts, you might want to try asking in the PuLP support group; the link is on http://solverstudio.org/languages/pulp/ I have not done this myself, and expect that it is possible. Please let us know how you get on. Andrew. PS: Installing Dippy is another option; that works with SolverStudio via External Python.
60. Hi, thank you so much for this powerful application, which helps me a lot. Do you think there is an easy way to make SolverStudio independent of a current worksheet. I would like to develop an Excel-Addin, where important code (e.g. SolverStudio code) is not in the workbook. Thank you! Ömer
• SolverStudio won't let you do that. It would be a big job for you to write such an addin that did not also rely on SolverStudio. You could do it using your own addin to create Custom XML data in a workbook which SolverStudio then ran. Still a big job. Andrew
61. Is it possible to call a function in one sheet to another sheet. If yes then whats the syntax?
62. Is there a better way to handle blank cells in Data Items? I setup one Data Item in SolverStudio, let's call it TABLE, with an Excel table as the data range (including the header row of the table) and no index ranges. My Python script then loops through TABLE[row,col] to grab the values that interest me. This gets messed up when the Excel table had blank cells however, as the way SolverStudio seems to make Data Items available in Python by defining them as lists (of lists, in the case of TABLE) with blanks removed. For example, if my TABLE is 2x2 and has one blank cell in the first column of the second row, then (in Python) TABLE[0] has length 2 and TABLE[1] has length 1, and TABLE[1,0] returns the value stored in cell B2 rather than the intended B1 (blank).
• The next version will make this work better but for now you need to index the table. Andrew
• Thanks Andrew. For now I'm using the VBA commands from Python to read the data values: TABLE = Application.Range("TABLE").ListObject var[row,col] = TABLE.DataBodyRange.Cells(row+1,col+1).Value() It's slow but it works :)
• Pleased you got it working. It will be much faster if you copy all the cells in TABLE in one operation, instead of one cell at a time. Eg: TABLE = Application.Range("TABLE ").Cells.Value2 print TABLE[0,0], TABLE[0,1]
• Yes, but there may be typing issues to address, so updating & then writing back something you have read from a range into Python would be best; try it and please tell us what you learn. Andrew
63. Hello, I just started to work with Solverstudio (and thereby pyomo), and I tried to use different solvers to solve the "Transportation-Pyomo" problem in the "Pyomo Examples" file. The cbc and ipopt solver give warnings but still solve the problem. However the lpsolve solver and my installed glpk solver give errors and no solution. Is there someting I can do to fix it? This is the logfile I got: WARNING: Couldn't create 'pyomo.core.data.parse_table_datacmds'. [Errno 13] Permission denied: 'c:\\program files\\python35\\lib\\site-packages\\pyomo\\core\\data\\parse_table_datacmds.py' ERROR: "[base]\site-packages\pyomo\opt\base\solvers.py", 570, solve Solver (asl) returned non-zero return code (255) ERROR: "[base]\site-packages\pyomo\opt\base\solvers.py", 575, solve Solver log: LEX ERROR : # lineno 1 syntax error on line 1 Unable to read model. [ 0.22] Pyomo Finished ERROR: Unexpected exception while running model: Solver (asl) did not exit normally ## Error: The Pyomo output file did not contain any valid data. ## Pyomo did not return a valid solution. The sheet has not been changed. ## Done
• Do you mind seeing if Pyomo works for these models when solving them outside SolverStudio? All the data files SolverStudio creates for Pyomo can be found in the working files folder accessible under the SolverStudio File menu. This will tell us if it is a Pyomo or a SolverStudio issue. Thanks, Andrew
64. how do i create a path for running glpk solver. whenever i run prob.solve(glpk()) it throws an error saying access to glpk.exe failed.
65. when i try to write the values back into excel i am getting an error: expected index value, got float # Copy values of decision variables into the sheet for t in periods[start:(int(horizon_length)+start)]: production[t] = x_t[t].varValue setupbinary[t] = y_t[t].varValue inventory_p[t] = I_t[t].varValue n = sum([x_t[tau].varValue for tau in range((start + 1), int(t)+1)]) print n cumulative_dv[t] = n #line with error Kindly help me solve this issue
• It depends on your language. Typically it is a solver option... check the language documentation. Andrew
66. Hi , I have about about 900 decision variables and 300 binary. i am using PULP and it uses cbc solver to solve the lp. My computer has been running the optimization problem for more than 30 minutes and is yet to give the result. How long does it usually take. whats the best way to solve the problem at a quicker pace.
• Integer programmes can vary hugely in solve times. There is an art and skill to creating models thatcan be solved quickly. Andrew
67. When i solve for a LP problem with around 1200 decision variables, the cbc.exe just keeps running . How much time does it usually take for such problems to be complete. How to reduce it .
• You can set an integer gap tolerance of say 20%. This will more quickly find a solution within 20% of optimal. Andrew
68. Hi, I have create a LP model in the opensolver. I was confronted with huge calculation times, 24 hours +. My model is quite large so i was looking for other methods to solve it and found your solver. (4915 variables) I was wondering, is it possible to automatically load in my opensolver/excelsolver model in the solver studio? Thanks for your help!
• No, sorry; there's no automatic OpenSolver to SolverStudio translator. You need to build the model again using a modelling language. Andrew
69. Error: 'list is read only'. Cannot be assigned to . How to create a write into list in excel solverstudio.
70. hi, in my excel file i have columns that are affected by the decision variable values and these columns that are affected by the value of the decision variables are included in my constraints. how do i formulate that. should i create seperate dictionaries for these columns and incorporate the formulas(which contains the decision variables). If yes, how. Any examples?
• Yes, you need to replicate the formulae as constraints in the SolverStudio model (i.e. in AMPL, or PuLP etc). Andrew
71. Are you going to develop a version for Mac? The tool is so convenient in Windows, and really hope there will be a mac version
• Thankz for your nice comments. It is not easy to build for a Mac as we use a C# Office Add-In which is not supported on Mac. But it may be in the future. Andrew
• Thanks for the reply. Hope there will be a mac version in the near future. Excellent work.
72. Hi, i had a previous installation of SS with Excel 2016. I downgraded to 2013 since i experienced many AppHangB1 problems (?) After that i unistalled previous version of SS and tryed to install last version. After unblock the .zip i double cliccked on the setup.exe but it seems the program just flashes the window "setup is inizialing components"..without actually starting, if i double click on the .vsto nothing happens...do you have any idea on why ? Could it be a "security issue" with the new 2013 Office installation or something else ? Regards Fabrizio
• I run SolverStudio happily in 2010 and 2013. Please try running the uninstall programme (SolverStudio\Uninstall.exe) provided with SolverStudio; it works hard to really uninstall it. You can enable VSTO logging to see if any error messages are being generated; the file SolverStudio\SolverStudio\AdvancedInstallers\EnableVSTOErrorAlerts.bat will do this for you. Please also try the RefeshClickOnceCache in the same folder. Hope this helps. Andrew
73. Why is there a limit on the dimensionality of the data? I see that you can define multiple row and column indices, but why is there a limit in the number? For example, I can define a data variable with 5 columns of data as the first indexed range, but not 6.
• Thanks for the reply Andrew. I really appreciate this great tool and your efforts! I explored my previous issue some more. You're right there is no dimensionality limit, but there is still an error that I had misinterpreted. It is caused when the size of the table's data is small compared to the size of the index ranges. The smallest example of this is trying to define a SolverStudio data on the table: ________C0 R0__R1__N0 This returns the error "The index cell ranges do not match the size of the data range table. You must specify a row index for each row and a column index for each column. Indices can be pairs or triples in a single index range." In this simple example, the data value for N0 was intended to be indexed by [(R0,R1,C0)]. SolverStudio checked the dimensions of the ranges for the first indexes [(R0,R1)] and second indexes [(C0)] and saw a mismatch, by probably assuming that the lone data value N0 was intended to be indexed by [(R0,C0)] and that R1 was not intended to be a part of the first index range. Maybe the part of SolverStudio that checks the dimensions of the index ranges for a new data variable could be modified, but you might have design reasons for this. Note that if there were more rows to the table, you don't see the error anymore. For example, this works: ________C0 R00_R01_N0 R10_R11_N1
• My alignment characters got removed for some reason, but the first example is intended to be a table with 2 row indices and 1 col index, and only 1 data value. The second example is the same, but now with 2 data values.
• Alex. Thanks for your detailed comment. We try to have a minimal interface and guess what the user wants. In your example I would have guessed it was a mistake and that's why SolverStudio also guesses. The alternative is a more complicated GUI... I do occasionally consider an Advanced mode that would give the user far more options to remove the guessing. Perhaps it will happen one day! Thanks again. Andrew
• Thanks Andrew. An advanced mode would be interesting, but you're right that that adds GUI complexity. I'll work around this for now.
74. Hi Andrew, just passing by to thank you for this great tool. I was able to model a big Workforce Scheduling problem using PulP and solve it under an acceptable time frame (that us to take me a lot more time). As an aside note: Do you have any plans to launch a Standalone version? (So the Workbook could be distributed with it and call it from VBA, no installation required)
• Thanks for the positive feedback. I had not thought of a standalone version; it would be hard to do given the tight Excel integration. But, I will give it some thought. Thanks again, Andrew
75. Thanks for your help Andrew. I'm struggling to install COUENNE myself, and I'm need to distribute this model to end users who are non-technical. Is there an easy way to install/distribute it, or are there any other solvers that might be easier to install? Many thanks, Sam
• Hello Sam, I don't know if this is of any help to you, but i'm currently using OpenSolver for solutions that need to be distributed to other users. It does not require installation, so i just set up a shared folder containing both the OpenSolver files plus the Spreadsheet containing the model. In the Workbook Open event, i add the following piece of code to open it (if it hasn't been opened yet) Sub putSolver() For i = 1 To Application.AddIns2.count If Application.AddIns2(i).Name = "OpenSolver.xlam" Then GoTo jump Next Workbooks.Open (ActiveWorkbook.Path & "\OpenSolver.xlam") MsgBox ("Please, use the Open Solver button to run the model under the Data Command Bar") jump: End Sub Instead of asking the user, you could also run OpenSolver from VBA using a logic similar to the one in the following link, or with SendKeys command, or anything more fancy, rsrs https://www.add-in-express.com/forum/read.php?FID=5&TID=2581 Att, Julio
• Julio: OpenSolver is a bit easier to distribute than SolverStudio; SolverStudio requires an installer to embed itself with Excel. I like your approach to loading OpenSolver; very nice. Andrew
• Sam: Being non-linear, Couenne needs a non-linear modelling tool such as Pyomo, AMPL, GAMS or Julia. With these languages, installing Couenne into the SolverStudio Solvers folders should work, as should installing anywhere else as long as it is on the PATH variable. Hope this helps. Andrew
76. Hey guys, I've developed a MIP model on solverstudio using PuLP/CBC that works great! However, due to changes in the modelling requirements, it looks as though the model will have to become non-linear (or an extremely large LP). What do you recommend as the best non-licensed modelling language/solver combination for MINLPs to use within SolverStudio? As an extra curve ball my office firewall prevents access to the NEOS servers. Many thanks, Sam
• I would first work really hard to keep it linear. If this failed, I would try Julia and JuMP; it can build non-linear models, and then you can combine it with COUENNE; see the solver table at http://www.juliaopt.org/. I would love to know how you get on. Andrew. PS: The latest Julia/Jump release broke SolverStudio; the next SolverStudio release will fix this (or I can email you some updated files if needed; just finished them today).
77. I am trying to use solve a model using SolverStudio and AMPL on NEOS using CPLEX. NEOS lists the CPLEX solver as one of its solvers, but CPLEX is not showing up on my list of SolverStudio solver options that are listed on the AMPL-->Choose Solver Tab. Do you know how to fix this? I should mention that the model does solve if I directly use SolverStudio to call AMPL/CPLEX WITHOUT NEOS.
• Use the SolverStudio menu to update the list of available NEOS solvers. It will talk to NEOS and update the list. Hope this helps. Andrew
78. Hey guys, I'm trying to use the numpy package within my SolverStudio model. What's the best way to install this package to IronPython without having to use an external python module? I see the /site-packages directory refers to the site.py script for details on importing 3rd party packages, however I couldn't quite make sense of it. Thanks in advance!
• I have never tried to get NumPy working with Iron Python. Stackoverflow has this question about doing this; apparently it is possible. Please let us now how you get on. Andrew
79. Hi guys, Before anything, let me say "nice work" I am having a problem a problem when solving using ironPython and cbc. The error reported is in line 1363, in solve_CBC pulp.solvers.PulpSolverError: Pulp: Error while trying to execute cbc.exe Although, it is working fine in CPython. Do you have any idea how to fix this. Thanks,
• Sorry it is not working. As a first check, do the standard PuLP examples provided with SolverStudio work for you? Andrew
• Hi Andrew, Unfortunately the PuLP examples using internal solvers are not working. The Pulp examples using CPhyton are working fine. I have the COINOR Binary installed in my machine, so I do not now if this install break something. Anyway, I have reinstalled SolverStudio, but the errors are the same. I was planning to present same examples to my students using only IronPython, so thats why I am trying to fix it. Thanks again, Eduardo
• Please post or send (via email to [email protected]) a copy of the text from your About SolverStudio box. This will show the location of the CBC that you are running. Please also try, using the menu SolverStudio : SolverStudio Settings, changing the copy of CBC that SolverStudio is using to the one downloaded with SolverStudio (in a SolverStudio folder). Please let me know how you get on. Andrew. PS: I assume you have the latest SolverStudio version (SolverStudio Add-in Version 0.9.1.0). Thanks, Andrew
• Hi Andrew, I have just sent a email with a copy of about box of solverStudio. I tried your suggestion, but I did not find the CBC location in the solver studio window. May be this location is set up internally by PuLP. By the way, I have PuLP installed in CPython also. Thanks, Eduardo
80. I keep receiving an error when running my AMPL model through the NEOS-server using SolverStudio version 0.09.01 20150813 d. The error does not occur when I run my model using my local AMPL and solver application (however, I need the additional processing power provided by NEOS to solve more complex problems). The same error occurs when I run the 'Transportation-AMPL NEOS' example problem provided in the SolverStudio package. The error is as follows: ----- Error executing model code: 'namespace#' object attribute 'ThreadAbortException' is read-only Traceback (most recent call last): File "C:\Program Files\SolverStudio\SolverStudio\AMPLNEOS\RunAMPLNEOS.py", line 732, in File "C:\Program Files\SolverStudio\SolverStudio\AMPLNEOS\RunAMPLNEOS.py", line 730, in DoRun File "C:\Program Files\SolverStudio\SolverStudio\AMPLNEOS\RunAMPLNEOS.py", line 700, in DoSolveOnNeos File "C:\Program Files\SolverStudio\SolverStudio\AMPLNEOS\RunAMPLNEOS.py", line 506, in runAMPLonNEOS AttributeError: 'namespace#' object attribute 'ThreadAbortExeption' is read-only ----- Your help is much appreciated. Thanks.
• Sorry it is not working. NEOS have made recent changes which have broken our code (which turned out not to be working well with their's). We have fixed this in OpenSolver; the SolverStudio fix is coming in the next release.
81. Hi! After trying to solve model using SolverStudio I get this message: WARNING: The addition operator has been used on JuMP expressions a large number of times. This warning is safe to ignore but may indicate that model generation is slower than necessary. For performance reasons, you should not add expressions in a loop. Instead of x += y, use append!(x,y) to modify x in place. If y is a single variable, you may also use push!(x, coef, y) in place of x += coef*y. This application has requested the Runtime to terminate it in an unusual way. Please contact the application's support team for more information. terminate called after throwing an instance of 'std::bad_alloc' what(): std::bad_alloc ## Julia did not complete (Error Code 3); no solution is available. ## Done What is the source of this problem? My lack of experience? Or something else? Please suggest me, what to study and how to fix it. Thank you very much! ... I can send the file if necessary. Thank you!
• This "terminate" message indicates that Julia has crashed. I suggest you try running the same model and data files in the Julia console. (Use the SolverStudio View Working Files menu to find the data file SolverStudio.jl needed to run outside SolverStudio.) If the crash happens again, the Julia people are the ones to contact. If not, then please contact us again. Andrew
• Let me return to this warning: WARNING: The addition operator has been used on JuMP expressions a large number of times. This warning is safe to ignore but may indicate that model generation is slower than necessary. For performance reasons, you should not add expressions in a loop. Instead of x += y, use append!(x,y) to modify x in place. If y is a single variable, you may also use push!(x, coef, y) in place of x += coef*y. When I try to append and push variables, SolverStudio says, that ERROR: append! has no method matching append!(::Variable, ::Variable) Maybe I don't understand something? Here's a piece of code that SS doesn't like, I think: prQMach[:,:,:,:,:,:]=nullVar prQ[:,:]=nullVar prQMD[:,:,:]=nullVar prBMD[:,:,:]=nullVar load[:,:,:,:,:]=nullVar machineBasLoad[:,:,:]=nullVar for k=1:Products, b=1:Basises, u=1:Machines if accPrBas[k,b]==1.0 && accPrMach[k,u]!=0.0 for d=1:Days, n=1:5 for bb=1:Batches xi+=1 prQMach[k,b,bb,u,d,n]=x[xi] prQ[k,d]+=x[xi] prQMD[k,u,d]+=x[xi] prBMD[b,u,d]+=x[xi] load[k,b,bb,u,d]+=(accPrMach[k,u]*x[xi]) machineBasLoad[b,u,d]+=(accPrMach[k,u]*x[xi]) end end end end Thanks
• I suggest you direct this question to the JuMP folks; SolverStudio is simply passing your code to Julia to run. I personally have never used append. Sorry I cannot be more helpful. Andrew
82. Hi, I am making an automated program to run an optimization of an assignment problem using a solver and data that is different every time I run it. The data tables are created with Macros and are updated frequently, changing the size of the tables. I would like to know if there is a way of running a Macro that assigns the data in the Data Items Editor, changing the range of the tables the solver uses. I am running GAMS language to solve the problem. Any help would be very useful. Greetings Max
• Max: I will email you a copy of the "Advanced Data Items" spreadsheet that will be appearing in the next release. That will show you how to do this either by having empty cells in the range, or, using a formula for the range. Andrew
• Hi Andrew, I would like to apply a RHS ranging to a model that I am working on in the same way as applied in the example in Transportation-PuLP RHS Ranging from PuLP Examples. I started my model in Solver, and have successfully run it in OpenSolver. In the original model (Solver) I built a Macro that re-ran the model and copied the results within the spreadsheet. Is it possible to have Opensolver re-run over a range of RHS variables? OR Transfer my OpenSolver model to SolverStudio and incorporate RHS Ranging? Thanks Arthur
• I would modify your VBA code to use OpenSolver instead of Solver, and change it to use the Quick Solve function which will run much faster for each different RHS. SolverStudio would work as well, but you need to build the model again in PuLP or another language. Andrew
83. Hi, Andrew! Is it possible to return arrays into Excel sheet as arrays? ={1;2;3;4;...} I tried to do this using Julia: trial="={" for k=1:Products trial=string(trial, ";", getValue(shipping[k,1])) end trial=string(trial,"}") This code creates a good array formula for Excel, but I couldn't return it to the "Trial" cell. I used: Trial=trial and Trial=getValue(trial) Where's my error? Thank you.
• Alexei: SolverStudio does not support arrays stored in a single cell, sorry. It is, however, an interesting idea for future support... Andrew
• I've made it with the following code: trial=Array(Any, Products, Basises) for k=1:Products, b=1:Basises trial[k,b] = ";" end for k=1:Products, b=1:Basises, d=1:Days trial[k,b]=string(trial[k,b], ";", getValue(prBasQ[k,b,d])) end and this function in VBA: Function ArrayScan(what As String, number As Long) Dim old_text, new_text, new_string As String old_text = ";" new_text = "a" new_string = WorksheetFunction.Substitute(WorksheetFunction.Substitute(what, ";", "Ô", number), ";", "À", number) ArrayScan = WorksheetFunction.Substitute(Mid(new_string, WorksheetFunction.Search("ô", new_string, 1) + 1, WorksheetFunction.Search("à", new_string, 1) - WorksheetFunction.Search("ô", new_string, 1) - 1), ".", ",") * 1 End Function
84. Is this available for office2011 on Macs
85. Hi I had a built a model using solverstudio few months back. Now, when I opened my excel file again and clicked on show model, it shows blank. How can I retrieve my model? Thanks Ankur
• One of my users had this problem once. The model had magically moved to another sheet... Check other sheets to see if you find it. HTH, Mario
• I had a look at the file, and it seemed to be user error - there was an OpenSolver model, but no SolverStudio data items or model. Andrew
87. Hi Andrew, Love your product, very straight forward to use. Do you have CVXPY in your development roadmap? Looking to get access to the ECOS solver. Kind regards, Sam
• Thanks for the feedback. You should be able to use CVXPY by installing it into your own Python installation (i.e. an external-to-SolverStudio CPython you have installed), and then choosing "Python (external)" as your language. Please let me know if it works. Andrew
88. When I run SolverStudio I get an error since the temporary file appear to go to Users\Adolfo\AppData\Local\Temp which for some reasons has limited permissions except for administrator. So I have to go into the permissions and give rights to everyone, which makes it work but is a bit of a hassle since there are some folders that return "Access Denied" and thus you have to keep clicking the "Continue" dialog boxes. I have to do it every time I restart the computer. I have googled the problem in general and it could be that there is an Adobe product that resets the temp folder permissions. But I prefer to not uninstall the Adobe products. One option is to avoid the problem launch Excel as administrator, but that is a bit of a hassle, particularly if I set it up for multiple users. Is there an easy way to change the temp directory? I am running Excel 2013 on Windows 10.
• Sorry, SolverStudio has no way to move the temp folder. This temp folder location is provided by Windows (through a system call); there is no way that it should locked for writing - that will break all sorts of things! Andrew
89. Hi -- i don't manage to download the zip file. I've got an error message when i try to unzip it. It seems that the server does not work very well. Can you please provide a miror link or send the model to me by email? Thank you very much
• We only have one server, sorry. Please try another browser or computer; the file on the server is not corrupted, and so it is the downloading that's failing for some reason.
90. Hi Andrew, First let me say thanks for making this available. I'm planning to write a rather large mixed integer model and use the full version of Gurobi, but given the size of the problem I think I will need to work my way through my data in pieces. Without getting into too much detail, it will be a production scheduling problem, hourly basis for 20 years. I will likely do monthly time steps and the first month will be have no constraints on the starting conditions, but each month there after will need to read in the output from a previous solver run as initial constraints for the next time step. I was thinking I would do this suing a dynamic named range but cannot seem to get it to function properly in the data definition dialogue. I saw a previous thread related to this from 2012 where you indicated that this wasn't yet a part of SolverStudio but you were going to try an add it. Did this ever become part of the program or do you have any ideas of a work around? I suppose I could hold the data in python an continue to reform the problem within the python and just write the output at the very end. Perhaps this is the most reasonable approach or do you have any additional suggestions? Thanks again, Nick
• The dynamic range works... what's not working for you? I would store all the data on the sheet and step thru processing it in Python. You could also have a manual mode where the users sets the range they want to solve. We r doing something similar for car hire. Andrew
• The next version will include such examples. In the mean time, I have just emailed you this example file. Hope this helps. Andrew
91. My students have been having great success using Solver Studio to some some larger problems (about 5,000 decision variables). I'm wondering is there is sensitivity analysis output (Lagrange multipliers, slacks, and perhaps range of basis) information available from the non-proprietary solvers. For larger problems, some of this information is really useful. Thanks!
• Pleased SolverStudio is proving useful. In SolverStudio, you can access anything available from the solvers. The AMPL examples show how to get dual variables from CBC (and other solvers). You can look at PuLP's "pulpTestCheck" to see how to access duals (using the .pi suffix on each constraint); this is not the best example, so we will add a SolverStudio example to our next release. Hope this helps (somewhat). Andrew. PS: You can always ask on the PuLP forum https://groups.google.com/forum/#!forum/pulp-or-discuss
92. Hi Andrew, Thank you for this usefull tool, I'm using it in 32 bits PCs, but I have some issues in 64 bits machines with GAMS. Excel stops with a message: "Solver Studio is busy running your optimization model" Operating system: Windows 8.1 and Windows 10, 64 Bits; GAMS 23.9.2 Other solvers run OK On a 32 bits machine with Windows 8.1, GAMS run OK. In the event viewer you can see: Aplication: EXCEL.EXE Framework Version: v4.0.30319 Description: the process was terminated due to an unhandled exception. Exception information: System.AccessViolationException Stack: gdxcs.dll_gdxopenwrite(Int32, System.String, System.String, Int32 ByRef) gdxcs.dll_gdxopenwrite(Int32, System.String, System.String, Int32 ByRef) GamsSupport.OpenGDX(System.String, Boolean, Boolean) Microsoft.Scripting.Interpreter.FuncCallInstruction5[[System.__Canon, mscorlib, Version=4.0.0.0, Culture=neutral, PublicKeyToken=b77a5c561934e089],[System.__Canon, mscorlib, Version=4.0.0.0, Culture=neutral, PublicKeyToken=b77a5c561934e089],[System.Boolean, mscorlib, Version=4.0.0.0, Culture=neutral, PublicKeyToken=b77a5c561934e089],[System.Boolean, mscorlib, Version=4.0.0.0, Culture=neutral, PublicKeyToken=b77a5c561934e089],[System.Int32, mscorlib, Version=4.0.0.0, Culture=neutral, PublicKeyToken=b77a5c561934e089]].Run(Microsoft.Scripting.Interpreter.InterpretedFrame)……
• Sorry GAMS is failing in some circumstances. Your error message suggests that the GAMS DLL we use is giving an error which we need to track down the cause of. It would be great if you could help with this as I don't have a 64 bit test system. The version of .Net (and hence the GAMS DLL's) we run depends not on the machine's bitness, but instead on the bitness of the Office version. Can you please confirm that the crashes happen in 64 bit Office? SolverStudio's About box will help with this. I am on 32 bit Office on a 64 bit machine, which SolverStudio reports as: Is64BitOperatingSystem=True, Is64BitProcess=False, .Net CLR (version 4.0.30319.18408), 32 bit Can you please check this on the problem machine(s). Things we can try next are updating the DLL's (which I have started doing at my end), and checking that we are calling them correctly in the 64-bit case. I'd really appreciate your help as we work thru these steps. Thanks, Andrew
• The new version of Solver Studio works fine on 64-bit Excel. In my Windows 10 I need to install Solver Studio as administrator, after that, both GAMS as GAMS-NEOS runs OK. Andrews, thank you very much, Eduardo.
93. Hello Andrew, I am facing some issues getting solverstudio to work in excel 2007. There are no problems with the installation as such. I am not able to activate the add-in inside excel. When I try to activate it, it gets deactivated on it's own. It would be great if you could help me out with this. Thank you. Regards, Bharath
• Sorry it is not working. Have you tried an earlier version of SolverStudio? We don't have Excel 2007 to test against unfortunately. But it would be good to know I'd older versions of SolverStudio work so we can see what has changed to stop it working. Andrew
• There is a good page at http://blogs.msdn.com/b/haahmadi/archive/2010/05/26/my-vsto-add-in-does-not-load.aspx that details debugging Excel add-ins. This includes setting VSTO_SUPPRESSDISPLAYALERTS, which I suggest you do at a command line (type CMD into the Start search) using "SETX VSTO_SUPPRESSDISPLAYALERTS 0". Please let me know what you discover. Andrew
• Sorry it is not working. I've changed the settings to use a different download mechanism; please let me know if this fixes your problem. Thanks, Andrew
95. Hi, I am wondering how to specify a three dimensional variable (Schedule[Shift,Day,Nurse]) using Solver Studio. I notice that the interface only has two possible indices and that a previous comment mentioned that one index should be specified as a pair (Shift, Day) and the other as a singleton (Nurse). However, it is not clear what the proper syntax is for inputing the pair. Is it (Shift, Day), [Shift,Day], {Shift, Day}, or something else? I keep receiving the message that whatever I put in is "not a valid range". Thank you for your help.
• Upon re-reading your question, I think that you are referring to the creation of the data item in the Data Items editor. The index range should be entered by dragging over the cells that contains the pairs of values; you should not be typing in names of other data items. See the example I refer to next. If you are asking about the Python syntax (are you using Python?) for working with a data item, then you can "print" the variable to see how it is being indexed. You can also look at the "Python Examples.xlsx" file to see how data items appear in Python. For example, a variable with 4 indices (2 in rows, 2 in columns) gives "dictH = {('B', 1.0, 'R', 'Y'): None, ('A', 2.0, 'P', 'X'): 3.0, ('A', 1.0, 'P', 'X'): 'f', ('A', 1.0, 'P', 'Y'): 'g', ('A', 2.0, 'P', 'Y'): 4.0, ('B', 1.0, 'P', 'X'): 2.0, ('A', 2.0, 'S', 'Z'): None, ('B', 1.0, 'P', 'Y'): None, ('A', 2.0, 'R', 'Y'): None, ('B', 1.0, 'S', 'Z'): 'g', ('A', 1.0, 'R', 'Y'): None, ('A', 1.0, 'S', 'Z'): 'i'}". Hope this helps, Andrew. PS: I now have a ToDo item of adding multiple indices to the PuLP examples...
96. Hi Andrew, I've the next error when I use Pyomo: ## Scanning model for sets and parameters ## Building input file for 2 sets and 6 parameters ## Writing simple parameters... 0 items written. ## Writing sets... 2 items written. ## Writing indexed parameters... 6 items written. ## Running Pyomo... ## Pyomo model file: C:\Users\EPRE\AppData\Local\Temp\SolverStudio 34gddyft\model.py ## Pyomo data file: C:\Users\EPRE\AppData\Local\Temp\SolverStudio 34gddyft\SheetData.dat ## Running: C:\Python\Scripts\Pyomo.exe --solver=cbc --save-results="C:\Users\EPRE\AppData\Local\Temp\SolverStudio 34gddyft\Sheet" "C:\Users\EPRE\AppData\Local\Temp\SolverStudio 34gddyft\model.py" "C:\Users\EPRE\AppData\Local\Temp\SolverStudio 34gddyft\SheetData.dat" --json usage: Pyomo solve [options] [] Pyomo solve: error: argument --json: expected one argument WARNING: converting to the 'pyomo solve' subcommand ## Error: The Pyomo output file did not contain any valid data. ## Pyomo did not complete; no solution is available. The sheet has not been changed. ## Done Regards
• Sorry it is not working. What drain of photo and of SolverStudio are you using? SolverStudio... SolverStudio settings will let you see the Photo you are using if you right click the file. Please also email me your about SolverStudio window. A dot mason at Auckland dot a dot nz. Andrew
• I have just downloaded the latest Pyomo (version 4.1.something), and get the same error as you report. Something has changed in Pyomo that is breaking SolverStudio. I have asked for more information on the Pyomo forum, and will release a new version once I understand what needs to be changed in SolverStudio to support the new Pyomo version. Thanks for your patience. Andrew
• Hi Andrew, My Pyomo version is 4.1.10527 and CPython version is 3.4.3. Thanks you for this excellent tool. Best regards.
• We have made progress on this --json issue. As detailed in this Pyomo Forum post, Pyomo 4.1 removed the --json option that SolverStudio uses. The Pyomo development team has kindly agreed to reinstate this --json option (thanks Bill), which will make the next release compatible with SolverStudio. Please watch the Pyomo site for news on the update becoming available.
97. Hi Andrew, Thanks for this great tool! I had no experience at all in PulP, but was able to quickly develop and test a Solverstudio model for assigning tasks to workers in a production line environment for handicapped people. I got to know Solverstudio through your INFORMS Transactions on Education publication. I will definitely use this tool in my classes. Btw, we met each other in Istanbul, EURO 2003 conference, where you attended my talk on scheduling trainees using a branch-and-price method where the columns represent activity schedules instead of employee schedules. After the session, we had a short conversation on my and your research. Not sure if you remember, but since it was my first presentation ever on a conference, I will of course always remember it...
• Pleased that you find SolverStudio useful. Do you have anything written on your application? It looks like a nice use of optimisation. Yes, I remember meeting in Istanbul; it was good to see you again yesterday in Glasgow at Euro 2015.
98. Hi Andrew, Awesome job on SolverStudio. I'm at a container terminal and we're exploring a large optimization problem with an extremely large state space that needs to be present maximized data over user controlled ranges. we're gradually getting closer to coming up with a way of doing this, and solverstudio has been instrumental in that process. but anyhow, onto the question :) while we're running through iterations of a particular model, what looks like a command line window flashes repeatedly. Is there any way to turn this off? due to the number of iterations we're running, I'm trying to scrape up every spare millisecond, and it looks like quite a bit of time is being spent redrawing said window. let me know!
• I guess you are using PuLP. PuLP flashes up the CBC solver. There is a TODO request in for PuLP to change this behaviour. However, in the mean time, you may want to just edit the PuLP Python code (if you are feeling a little adventurous). Hope this helps. Andrew
99. I have a problem working with numeric indexes when writing in CMPL. Is there an update?
• Hi Andrew, There was actually a bug SolverStudio/Cmpl that I have fixed this morning. Andrew Mason will release that soon. In the meantime you can use the patch that I will send to you directly. Cheers, Mike
100. Hi Andrew, Both Gams and Solverstudio are downloaded into my computer, however, even the examples do not work. The problem probably arises from the file "C:\Users\alper\Desktop\SolverStudio\SolverStudio\GAMS\RunGAMS.py" line 127 which is "PGX = SolverStudio.OpenGDX(dataFilename,False)". What should I do to fix this problem? Thanks
• Sorry it is not working. Please tell us the exact error that is given, and can you also please send us a copy of the About SolverStudio box text (all the text, please, not a screenshot)? Thanks, Andrew
• Thank Andrew for your reply. I sent the email with details, looking forward to your news.
101. First of thanks for doing this excellent work....I can hardly believe how awesome it is! I'm running OpenSovler v2.6.2 with 32-bit Windows, VBA7, Excel 14.0 I am working with a MILP that is ~5700 vars (~1000 binary), 9000 rows. I have to solve this model about 260 times with different data to simulate a "project life", so I am very happy to have the QuickSolve feature. The QuickSolve works in under 1 minute on my modest PC. One thing I have found is that if I close the file and re-open it, the quick solve initialization is lost and I have to initialize the QuickSolve all over again which takes ~30 mins. Last night I left the file open to avoid this issue, but when I came back this morning, it still told me there was no quick-solve and it had to be re-initialized. Note that it does not loose the model as other posts here suggest. I can open the model dialog and everything is all wired up like I left it, just no quick solve until I re-initialize. Does this situation sound normal / reasonable, or is something messed up? Is there a way to save the quick-solve initialization independently or else make it "stick" to the EXCEL file so it does not have to get re-built all the time?
• There is no way to save a QuickSolve data setup. If you know VBA, you could work out how to write this data to a file - it would be a great contribution to OpenSolver! I don't know why it disappeared overnight; please try this again. By the way, this is an OpenSolver question, not SolverStudio (where you have posted).... but SolverStudio would make your model run much faster, and then not need QuickSolve. Andrew
• I look forward to seeing your blog post... please put the link up here for us to see. Cheers, Andrew
103. Hello Andrew, I solve some problems in excel using cbc. After solving, it takes solver studio a lot of time to write the solutions back into excel. Do you have any idea why this is the case and do you have any idea on how to speed things up? Thanks for the tool, Nik
• Thanks for your kind words, and sorry it is going slowly. What language are you using? Is your data very large?
• The result file contains about 49000 entries. I'm using CMPL. Thanks for the fast response.
• Just to specify further, after the solution is written to the .csol file, it takes about one hour to load the data back into excel.
• An hour - that's very slow. However, 59,000 values is a large problem. Would you mind sending me your spreadsheet (to a dot mason at auckland dot ac dot nz) so that I can take a look at it. I'd also like to share it with the CMPL developer, Mike Steglich, who has written the interface code. We may be able to speed things up. Thanks, Andrew
• I found the bottleneck and fixed the code that will be released soon. Mike Steglich
104. Hi, Thanks for the add-in it have been very helpful. I am using gmpl language and I want to know if there is a command to stop de model and that this take the OPTIMAL LP SOLUTION FOUND so the model does not do the integer optimization. Or there is a way to put the function --tmlim nnn in the model. How can I write it? Thank you for your help
105. Hi, Firstly, yhank you very much for this amazing add In, I've been learning how to use it in my school and now I'm trying to use it at work. I have a quite big problem of affectation to solve, so I divided the problem in diffrents parts. For some sections It worked well and for others it runs for an unlimited time and I have the following message error: Pupl : Erros while trying to execute cbc.exe It's excatly the same code than my others section so that strange.. Any Idea of where could my mystake be? Thanks a lot!
• If it runs for a long time and then gives an error, then the CBC solver is probably running out of memory. I'd try running on a 64 bit system, simplifying your model, and increasing the bound gap. Can you tell me what course this is part of, and who your lecturer is? I'd like to contact him or her for any feedback they might have. Thanks, Andrew
106. Thanks for your comments. I have seen those weakref errors when debugging after running PuLP. If you can work out why they are occurring, I'd love to know. Adding an option to set -X:Tracing and -X:Frame (& maybe -X:FullFrames) is a good idea; I have added it to my TODO list. In the meantime, you can do your tests using IPW.exe (included in the IronPython directory) after exporting your data by running under CPython and saving the SolverStudio.py file. Please let me know how you get on. Andrew
• Thank you Andrew, yes, to use ipy.exe is exactly what I did to chase the weakref bug. FYI, I sent an email to IronPython describing the problem and included a script that should raise the exception after trying 100.000 times the "ofending" code. It works on my side. The email is in the following link (I isolated the problem to WeakSet to leave the pulp and abc.py stuff out): https://mail.python.org/pipermail/ironpython-users/2015-April/017442.html If you can run my test script at your side to check if you can replicate it, would be nice (ipy.exe and/or solverstudio). Please let me know... Cheers, Andres PS: Other topic: Please, say yes to fill the blank excel data with "None" ;-) (Just making annoying but friendly pressure!! Don't take it seriously. I learned it from my daughter and usually works very well when persuading me :-) )
• That is a really good bug report you have filed. And good to see that it has been repeated by another user. Let's hope it gets fixed. I have yet to update SolverStudio to IronPython 2.7.5 (and so I am not sure if this happens under 2.7.5), but that is also on my TODO list. Thanks, again, for following this up. Andrew
107. Hi Andrew, is there a way to run IronPython with -X:Tracing and -X:Frame (maybe -X:FullFrames) options inside OpenStudio? As our python code inside Openstudio get more complex would like to perform some debugging with "inspect" module (Without this flag inspect.currentframe() return "none" -> means not possible to get the call stack exploring inspect.currentframe().f_back ) EXPLANATION WHY I NEED IT: Same code behave sometimes different with CPython, that is why like to debug in IronPython itself. Also I have a nasty random bug in IronPython, that happens to me when the Pulp model is too big (IronPython start to throw SystemError and ValueError in _weakrefset.py module). By fortune, I managed to make a workaround in _weakrefset.__contains__(), but like to go to the bottom of the problem and report it to Pulp or IronPython for a fix. Cheers, Andres PS: Glad to see that you are trying to implement an interactive console. Will be great if you manage to achieve it!
108. I'm trying to run PuLP solver from Excel 2007 VBA macro in a loop (to use different constraints' values). I use the same VBA code provided in "Using SolverStudio". But only the last iteration of the loop is getting complete - looks like as soon as "Solve Model" command has called, macro doesn't "wait" for model completion (which takes ~5 sec) and goes to the next iteration,"resets" the problem, and re-runs it. I've tried to use Application.Wait after calling the command, but it just stops everything, including model solving. Is there any way to do it? (probably it's more the question about VBA, not solver studio though)
• The VBA call is simply clicking the Solve button, and then not waiting for SolverStudio to run. Adding better VBA support is on my TODO list, but won't happen soon. Instead, I would suggest you do this all in Python instead of VBA. Hope this helps, Andrew
• Hi there, First of all - I'm really enjoying using solverStudio (with pyomo), I've built a great little model! I've used your VBA code to call the model from VBA - but as someone else has asked, I'd like to first run the model with a certain constraint turned off, then check if the constraint is breached, and if so re-run it (takes much longer with this constraint). I'm wondering if you have any examples where you use Python/pyomo code to do this, as I'm not sure it can be done in VBA? Or any other suggestions? Thanks! Maya
• Thanks for the positive feedback. You should control Pyomo using Python; this is a much better way (and faster) than using VBA. So, your "model" will actually contain all the logic to add the constraint if it is needed. Hope this helps. Andrew
• Hi there again! I've got another really cool optimisation model written in Pyomo. I want run the optimisation a number of times (over the months of a year, where i have one year of data). Again, the same problem as before - VBA doesn't let me "wait". You say I could do it in Python, but i'm not exactly sure how? Could you please give me an example? E.g. Do i put this code in the SolverStudio window, along with my Param, Var declarations / constraint declarations / objective function ? Thanks so much!
• Fixed - use the VBA code "DoEvents"
109. Dear Andrew, I have a problem in running an example of model. Basically, I don't see the output variables in my excel sheet (I have correctly set their cell range and index range). After pressing the Solve Model button, this is the output: ## Attempting to fix minor errors ## Fixing model commands ## Building input file for 2 sets and 3 parameters ## Writing simple parameters... 0 items written. ## Writing sets... 2 items written. ## Writing indexed parameters... 3 items written. ## Running AMPL... ## AMPL.exe file: C:\SolverStudio\SolverStudio\AMPL\ampl.exe ## AMPL model file: C:\Users\claudio\AppData\Local\Temp\SolverStudio c1hemjwq\model.txt ## AMPL data file: SheetData.dat exit code 18446744072635810101 ## AMPL run completed. Opening results file... ## No results were loaded into the sheet. ## Done I really don't know what's wrong here. Any help is appreciated. Thank you!
• I have never seen "exit code 18446744072635810101" before. This is AMPL output, not SolverStudio's, so I think something unusual is going on with your AMPL installation. Can you try "Open AMPL Shell" from SolverStudio's AMPL menu, and see what that says? You can also start the AMPL License Server (again using SolverStudio's AMPL menu) and see what that reports. Andrew. PS: Have you tried running one of the AMPL examples provided with SolverStudio to check they are working?
110. Hello Andrew, I'm currently using SolverStudio (AMPL on NEOS) and I think I have some problems with the limitations of the variables and constraints. When one of my sets goes over a total of 130, then SolverStudio doesn't seem able to solve the problem. When that set is less or equal to 130, it takes 10 minutes to solve. (You need to know that that number 130 has to be multiplied by itself and then by 24, according to our program)
• As problems get larger, the can suddenly become very hard to solve. I think that's what's happening in your case. I suggest you try a larger tolerance to get a sub-optimal solution more quickly. Andrew
• I'm not sure what solver you are using, but it will have a solver option (eg MIPGap for Gurobi) you can set; please Google it. Andrew
111. Dear Andrew, thanks again for your great Excel Plug-in! Now, looking forward your help: I'm using a SolverStudio for a new LP model, unfortunately the data needed for the model came with some random holes (blanks, missing data, etc) from the data source (which is it imported using Excel Query). The problem is that when SolverStudio reads the data items from the tables (ranges), it doesn't reflect the missing data in Pulp environment. For example, if I have the following data in the excel datasheet: Same as previous example, but with better format...
	A	B	C	D	E	F
1
2		Id	X	Y	Z
3		1	Orange	NewYork	Mazda
4		2	Apple		Ford
6		4	Banana	Berlin	Toyota
7

…and "DataA" is defined as a range B3:E6 (without any index). By executing "print DataA" in Pulp, I get: [(1.0, 'Orange', 'NewYork', 'Mazda'), (2.0, 'Apple', 'Ford'), (3.0, 'Madrid', 'BMW'), (4.0, 'Banana', 'Berlin', 'Toyota')] You can see there aren't any "None" in the blanks, which difficult the model logic, as there is no way to figure out if the missing data is in column X or in column Y. What is the missing data? Not possible to know in Pulp environment. Is it possible for you to fix it and include the "Nones" for the blank cells in data in your next SolverStudio release (when 2D Indexes are not in use)? Looking forward your kind comments on this, as the workaround was very "ugly" in Excel Query and not straightforward (a lot of if...isnull in every column to replace the nulls with a void string, making impossible to read or modify later on the query) Best regards, Andres Sommerhoff PS 1: Why I'm mentioned the 2D Index? Because, if I define "DataB" the same range of the example above, but with a 2D Index (row and columns), I'm able to get the "None"s! But for me is not a solution (because, the execution is very slow for a big DB, maybe because the representation for a 2D Index is very verbose and not compact enough for my problem). Just to illustrate, if I execute "print DataB" in Pulp, I get: {(1.0, 'X'): 'Orange', (2.0, 'X'): 'Apple', (3.0, 'Z'): 'BMW', (1.0, 'Z'): 'Mazda', (2.0, 'Z'): 'Ford', (1.0, 'Y'): 'NewYork', (2.0, 'Y'): None, (4.0, 'Z'): 'Toyota', (3.0, 'Y'): 'Madrid', (4.0, 'Y'): 'Berlin', (3.0, 'X'): None, (4.0, 'X'): 'Banana'} PS2: Please note that with a single Index (for the rows), the problem still happens (missing data is not translated to "None").
• Thanks for your comment. This behaviour is by design (although I am always open to feeback about bad design decisions!). An indexed set of data can have blanks in the indices and associated rows/columns. As you have seen, SolverStudio ignores those blanks. This allows the user, for example, to simply delete a column in a table and still have things work. The best approach in your situation would be to index the rows and the columns (i.e. a 2D-index). If this is proving too slow, then I need to make it work faster. The current code builds a full dictionary that does not exploit the tabular structure; my TODO list has on it an item to do this more efficiently by exploiting the tabular structure. I will give this some more thought. In the meantime, I have added under an example of directly accessing a range.
• Thank you Andrew for your prompt reply. I will take a look on the example of accessing directly to a range. Maybe I can use it as a workaround. Beside the speed/memory issue (which is in the python code, *not* in Solverstudio), the other problem with 1D and 2D index is that they don't support duplicated items (I want all the logic in one place, in this case in python). This duplication could be an error for one table, but for other data source can be part of the structure and that duplication part of the meaning (and willing that python logic deal with it). To add a column with an artificial id index? Not necessary as not using indexes in Solverstudio is represented as a python list that came with an intrinsic index from 0 to n for each row. Very simple, but then the blank cells are my headache ;-) as my previous post. I don't know if it will compromise your design or affect non Python code, but maybe it will be possible for you to add an user option to tell Solverstudio what to do with Excel blank cells? Something like: "Skip missing data cells / Use 'None' for missing data". Is that possible? Looking forward your kind comments on this idea and congratulation again for you great software! Cheers, Andres PS1: For the moment, the single "direct" way to return data from python to excel is using a 2D index. Is not possible to allow 1D or none indexed data be "writable" by Python and reflect the changes back to excel? I'm trying to avoid 2D index (maybe with unproven and unfunded justification), because the implementation is a python dictionary what its keys are the col and row names of the table. That means that the column names are repeated in each row, which is fine for small and medium data size, but seems memory hungry for big tables to me... (more than 50.000 rows in my case) PS2: I unfortunately didn't figure out your justification "This allows the user, for example, to simply delete a column in a table and still have things work.". Not big deal, but maybe you was thing in "inserting" a blank column, then still have things work? Feel free to not consider this last comment, if you think it is not important the discussion...
• Thanks for the feedback. Yes, the lack of 2D arrays is a big memory hog. I should perhaps look at included NumPy to help with this, but the raw .Net access also seems to be a good option. For languages such as Python and Julia, such access makes sense; in languages like AMPL dictionaries are more natural. Allowing nulls to be passed thru to the model may make sense in Python and Julia; I will think about that. Re the deleting a column, I should have said deleting the contents of a column, but your example of inserting a column also makes sense. Please let me know how you get on with the direct access as per my example. Cheers, Andrew
• Thank you Andrew, it worked very fast when reading the a big table directly by COM (as per your example "directly accessing a range"). I tested in IronPython and in CPython an it was almost the same speed. However, there still a big penalty if you leave the data in same sheet as the python script. You will notice it when OpenSolver is "preparing environment". Below my benchmark: Total Time took by OpenSolver for a data of 175.000 cells (9.700 rows and 18 cols): Data and code in "same" sheets: between 6 and 8 seconds. Data and code in "different" sheets: 0,65 sec CPython and 0,95 sec in IronPython *Total Time means: (1) click "Solve" + (2) Openstudio "Preparing Environment" + (3) user Python script + (4)reporting back (note: no writes in big table!) The step three, script in python took in both cases just 0,12 sec in CPython and 0,59 sec in IronPython. The script (similar to your "direct range access" example) was mainly for reading and passing all the data to a python 2D list like the following code (works only in IronPython):  rng=ActiveWorkbook.Sheets("Data").Range("Test").Value2 b=list(rng) step = 1 + rng.GetUpperBound(1)-rng.GetLowerBound(1) table = [b[i:i+step] for i in xrange(0, len(b), step)]  For doing almost the same (building a big python 2D list), Opensolver "Preparing Environment" took 6-8 sec. Of course, if the data was in other sheet, "Preparing Environment" was not perceived and you can safe this 6-8 secs. The good thing is that your recommendation is fast, but most important to me, is that it fills the blanks with "None", so the data keeps its consistency!!! Despite we have this fast "direct" alternative, could you still try to fill the blanks with "None" for the normal data access in python? Of course, if you also manage to give the same speed, it would be also great, but my personal priority is for **consistency**! Cheers, Andres PS: for CPython, the excel COM object syntax is not 100% the same :-( , and I found a bug in SolverStudio.py (an exception breaks the excel COM object loading) when working with an excel file from a network drive. Apparently, SolverStudio.py is confused with my "N:" drive versus the "\\fileserver\andres$\" in the line "name = moniker.GetDisplayName(context, None).lower()", so they don't find a match in line "if (lowerCaseName == name) or name.endswith('\\' + lowerCaseName):". I can comment further if above was too summarized... Meantime Workaround: open the excel file from a local HD... • Thanks for that detailed analysis. I still have on my TODO list to make dictionaries faster, and also to add simple 0, 1, 2, ... indexing as an option (which would result in IronPython arrays being created, perhaps complete with the null's). I am still thinking about the best options for adding nulls. I had not come across that bug with mapped drives. My beta code now fixes it; watch out for the next release with this fix. Thanks for reporting this issue. Andrew 112. Dear Friend, Is there a way to print the cmd log from CBC (msg=1) to console or excel? Thanks, Nathan • I am wondering the same thing. Using prob.solve(COIN_CMD(msg=1)) gives us all this great information about the solution path, but then disappears as soon as it's done. It's useless without some way to retrieve the log. Anyone know how? • This is not something that SolverStudio has control over. However, I've asked Stu Mitchell, the PuLP developer, and will forward any suggestions. Please also try asking in the PuLP forums as well: [email protected] 113. Hello Andrew, I am currently using SolverStudio in my master's research and would like to know if there is an official way to cite it in a paper? It has endured pretty well some problems of binary integer programming using NEOS + AMPL + CBC. Thanks once again for the amazing add-in! Eldemar • Pleased it has been useful. Please cite this as: Mason AJ (2013) SolverStudio: A new tool for better optimisation and simulation modelling in Excel. INFORMS Trans. Ed. 14(1):45–52. Thanks, Andrew. PS: It would be great to add a link on this site to your thesis once it is published. Would that be possible? • Thanks for the quick answer Andrew! I can send the link to you without any problems, but there are going to be some months for the text to get finished. Another detail is that the work is being written in Brazilian Portuguese, but I hope to submit a paper about it in English this year, I guess that this one will be more widely useful. Eldemar 114. The file SolverStudio_00_06_12_00 20150216.zip cannot be open, in fact it seems to be a problem with the download process. Please revise the file and the link in download page. Perhaps yo could send to me a link by mail. Thanks in advance. • This is working ok for me (but with a slightly different file name from the one I expected - I have filed a bug report about this.) The file is large (as a result of adding a new language in the latest experimental version.) The direct link is http://solverstudio.org/?wpdmdl=608 Hope this helps, Andrew 115. I'm trying to use SolverStudio with COOPR following your instructions. I can run models directly using pyomo or even form scripts using a tird-party editor. But when i try to solve one of the examples of SolverStudio, I allway obtain an error Here the output ## Scanning model for sets and parameters ## Building input file for 2 sets and 3 parameters ## Writing simple parameters... 0 items written. ## Writing sets... 2 items written. ## Writing indexed parameters... 3 items written. ## Running COOPR... ## COOPR model file: C:\Users\David\AppData\Local\Temp\SolverStudio iyzgznup\model.py ## COOPR data file: C:\Users\David\AppData\Local\Temp\SolverStudio iyzgznup\SheetData.dat ## Running: C:\Python27\Scripts\pyomo.exe --solver=cbc --save-results="C:\Users\David\AppData\Local\Temp\SolverStudio iyzgznup\Sheet " "C:\Users\David\AppData\Local\Temp\SolverStudio iyzgznup\model.py" "C:\Users\David\AppData\Local\Temp\SolverStudio iyzgznup\SheetData.dat" [ 0.00] Setting up Pyomo environment [ 0.00] Applying Pyomo preprocessing actions [ 0.00] Creating model [ 0.07] Applying solver [ 0.12] Processing results Number of solutions: 1 Solution Information Gap: 0.0 Status: optimal Function Value: 8400.0 Solver results file: C:\Users\David\AppData\Local\Temp\SolverStudio iyzgznup\Sheet [ 0.12] Applying Pyomo postprocessing actions [ 0.12] Pyomo Finished ## COOPR did not complete; no solution is available. The sheet has not been changed. ## Done So, as you can see in last two lines no solution is finally obtained in the excel sheet. I cannot solve this problem Can you help me? Thanks a lot in advance • Thanks for the feedback. It looks like you are using an older version of SolverStudio - please try the new experimental version "SolverStudio_00_06_12_00 20150216". Please note that COOPR/Pyomo is now just called Pyomo, and has been updated to version 4 (see http://pyomo.org). Please let me know if this fixes the problem. Andrew 116. Can I reference a cell in the spreadsheet which contains the objective function. Kind of like how I just point OpenSolver to a cell to maximise/minimise, or do I have to re-create the objective function in SolverStudio (python)? 117. Hi, I am having a problem solving a MIP. I am using SolverStudio for a scheduling problem with 25,000 variables and 74,000 constraints. The initial formulation solved to optimal in about 5 minutes. But, when I changed the objective function slightly to encourage smooth schedules, the problem ran for 100 hours before it even found it's first feasible solution! This is very troubling because the optimal solution I found in 5 minutes in the original problem is a perfectly suitable feasible solution in the second problem because the problems are identical, except for the small change to objective function. So, why is it taking 100 hours to find what should take 5 minutes? Can anyone help me make sense of this? Thanks, Nathan. • This is not uncommon in integer programmes; small changes can be significant in that they cause solutions to become fractional (eg x=0.77), and then the solver spends lots of time trying to make things integer. Getting the "right" objective function and model form is part of the art of modelling. Sorry I cannot be of much more help. Andrew • Hey Andrew, Thanks for your reply. I understand what you are saying and completely agree that small changes in formulation can have dramatic effects on computation times, but in this case when I said that the solution is feasible for both problems, I do mean feasible for all constraints, including the integer constraints. Has anyone run into any challenges getting SolverStudio/CBC to find an initial feasible solution on medium sized problems? If so, does anyone know any tips/tricks to help the solver along? Thanks, Nathan NathanLPetty at gmail dot com 118. Hello, I'm interested whether it is possible to catch a calculation finished event with VBA. As I would like to do multiple calculations after eachother. Kind regards, Joe 119. Hi, I have encountered an error message during setup, it said: "Setup has detected that the file 'C:\Users\owner\AppData\Local\Temp\VSD4D66.tmp\VSTOR40\vstor40_x64.exe' has changed since it was initially published. Click OK to retry the download, or Cancel to exit setup." I clicked OK and the same message pops up again. I did unblock and I did run the setup by right click and choose run as an administrator, but it pops up everytime. I went to check the VSTOR40 folder and it was empty inside. I deleted the VSD4D66.tmp and run the setup again, the same error message pops up again. What else should I do to solve it? Thanks alot! • This looks like a problem with the installation of VSTO (Visual Studio Tools for Office), which the standard Windows installer sets up for SolverStudio as part of the installation process. It is not something that I have come across before, sorry. A bit of Googling suggests that you should try the manual installation process for VSTO (as detailed on the Download and Install page). Please also try running as an administrator during the install. Please let us know how you get on so others can learn from your experience. Andrew 120. Hey, We werk with ampl 3D now we got the problem that th sum of all the rows must be 1 except for row 1 that row may have a higher value. Someone who have an idea? 121. Is it possible to define three dimensional variables? I noticed in edit data there are only two possible indices. • Yes; one index should be tuples (i.e. pairs), and the other singletons, eg:  Young Old Tall Male 3 4 Tall Female 5 7 Short Male 2 7 Short Female 4 9 Hope this helps, Andrew 122. Hi Andrew, Thanks for an awesome package. I have been using the latest (SolverStudio 0.6.03 (20140829)) to solve large MIP. It works flawlessly on MS Office 2010 (32-bit) and 2013 (32-bit) with local AMPL. It ran into following problem with AMPL on Neos (Gurobi solver: ## Scanning model for sets and parameters ## Building input file for 14 sets and 39 parameters ## Writing simple parameters... 4 items written. ## Writing sets... 2 items written. ## Writing indexed parameters... 14 items written. ## Running AMPL... ## AMPL model file: C:\Users\Suresh\AppData\Local\Temp\SolverStudio l01qlr4g\model.txt ## AMPL data file: SheetData.dat NEOS Status: 16 running, 0 queued jobs NEOS job submission failed Error=Error parsing XML file: line 65, column 62 ## AMPL did not complete; no solution is available. The sheet has not been changed. ## No results were loaded into the sheet. ## Done Please help. Thanks. • Thanks for the bug report; sorry this is happening. Can you please confirm that the "Transportation-AMPL NEOS" example in "AMPL(NEOS) + GMPL Examples.xlsx" works ok? Assuming it does, can you please send me your spreadsheet (to: a dot mason at auckland dot ac dot nz) to debug the issue. Thanks, Andrew 123. I am trying to download SolverStudio in my mac OSX, how I can do that? I downloaded Solverstudio file, but the setup exe doesn't open, can someone help me? • Sorry, but SolverStudio only works for Windows (because the underlying Microsoft add-in technology does not run on a Mac). 124. Hi , i had downloaded the opensolver addin on client machine and now he do not want to use it. could you please tell me how to uninstall the addin. Please let me know the steps as client strictly want to get it uninstalled. Thanks • Do you mean OpenSolver or SolverStudio? OpenSolver does not install itself permanently unless the user requests this (which can be done and undone in the About OpenSolver dialog). SolverStudio can be uninstalled using the Control Panel or via the About SolverStudio dialog. I hope this helps. Andrew 125. I am using Solver Studio with COOPR while testing COOPR examples after first installation, it return that there is one solution then report Pyomo Finished then the result is not affecting excel file and show the following ## COOPR did not complete; no solution is available. The sheet has not been changed. please help 126. Hello Andrew, I'm using SolverStudio for a big MIPL problem written in AMPL (I'm using cbc on NEOS to solve it), the model does some computations to generate the constraints and there is some time-consuming tasks to be done before the solver is called. Using small datasets the model works fine, send everything to NEOS, AMPL presolve does its job smoothly, cbc gives me the answer and everything works pretty well. But when I increase the size of the datasets some problems arise: 1) After the job is submitted to NEOS and starts running I got the following error message: NEOS Status: Waiting in queue... NEOS Status: Running... TimeOut/IO Error: No NEOS response in 60.0s. Killed NEOS job #00000000 If I go to the NEOS website and manually submitting the files generated by SolverStudio I got the model solved in a few minutes, but it would be great to have the results loaded in Excel directly. Is there a way to increase the time limit to get a response from NEOS before killing the job? 2) The second issue is a minor problem, since it does not stops me from getting my results, but I think you would like to know about it: when the size of my datasets increases I can click in the "Solve Model" only once. The second time I try to solve the model I got an error which says: An unexpected error ocurred while building the Python data for the data item 'XXX'. Please check the definition of this data item. The error was: , insufficient memory for this operation. (Exception of HRESULT: 0x8007000E (E_OUTOFMEMORY)) But when I close Excel (saving the worksheet) and reopen the exact same file the model is built and submitted correctly again. The data item 'XXX' is actually very large, it is a 2500x2500 matrix of zeros and ones. As I can click click the button once and SolverStudio does its job correctly I think maybe the memory is not cleared of the variables after the procedure of generating the model is finished. As I mentioned before, this is a minor issue, since I'm kind of crazy for building matrices of this size, but I thought you would like to know about this limitation. The real problem is only the "TimeOut/IO Error" of not getting a response from NEOS after 60 seconds, for me this time is too limiting, any help in increasing this time would be very appreciated. Sorry for the wall of text and congratulations for the excelent piece of software! Eldemar • forgot to mention: I'm SolverStudio 0.06.03 (2014.08.29) on Excel 2013 32 bits (Office 365) on a Win 8.1 Pro machine with 8GB RAM and i7 3770 CPU @ 3.40GHz • Eldemar: Thanks for your post. I am sorry SolverStudio is not working perfectly for you. I am puzzled by the timeout, as I would have expected NEOS to respond within 60s even if AMPL is busy. I will ask my NEOS colleagues what is going on. In the meantime, you can remove the timeout by making the following changes in the file SolverStudio\SolverStudio\AMPLNEOS\RunAMPLNEOS.py # Change to a 60s timeout (by default, there is no timeout) oldTimeout = socket.getdefaulttimeout() # Comment out the following line to remove the timeout # socket.setdefaulttimeout(60) Also, can I check if you have "Run in short queue" turned on? If so, you are limited in your solve time by NEOS to no more than 5 mins. This could perhaps cause the problem (but I have not tested this). Please try turning off the "Run in short queue" first, and then let me know. I was hoping that the memory would all be looked after by the .Net garbage collector, but clearly that is not the case. I will add clearing of memory to the TODO list. Thanks for alerting us to this. Thanks, Andrew • Thanks for the solution! Commenting out the line you mentioned solved it, the process was not killed and I got the results back in my worksheet after some time of waiting. The option to "run in short queue" was not checked since the beggining. This is a model which runs an algorithm inside it to generate the constraints and I know it take some time to process, actually I'm pretty happy with the performance of SolverStudio + AMPL + CBC + NEOS, they are handling the problem within minutes, at first I wasn't even sure this problem would solve in the time limit of 8 hours of NEOS, but it is a good surprise for me that they can solve it that fast (even if it was 7.9 hours I would be happy, because that IS fast for the problem I'm dealing with). As always, thanks for the quick reply and for sharing the software! Eldemar 127. Hi Andrew, The SolverStudio 0.6 is giving the same problem, “Could not add reference to assembly system.IO.”, on one of the machines as reported earlier by Pirre (July 14) and Shareth Hariharan (May 1). I would like to request you to kindly send me the fix mentioned in their posts. Thanks. 128. Hello, cbc crashes with error -2147220503. I do.wloaded the most recent version of cbc.exe(win 64 bt) but problem still exists. How can i solve? Thank you Regards, Giuseppe • This looks like a CBC bug, which I suggest you report to the CBC developers (including a copy of the CBC input file produced by PuLP with your report). The CBC list is often used for this, the details of which I have copied below. Send Cbc mailing list submissions to [email protected] To subscribe or unsubscribe via the World Wide Web, visit http://list.coin-or.org/mailman/listinfo/cbc or, via email, send a message with subject or body 'help' to [email protected] You can reach the person managing the list at [email protected] 129. Hi, I'm using SolverStudio to solve large MIP model, but for big istances after about 30 min of processing the model output write this message: _________________________________________ There may be further error information in the clone logs. ran out of memory. 0 MIP simplex iterations 0 branch-and-bound nodes No basis. ___________________________________ I think that I need to set nodefile to 3 to overcome this problem but I do not understand how to use the command. # Solve the problem option solver cplex; option cplex_options 'nodefile=3'; solve; Where I can find the Log file? thanks for help and for this great software • You may be able to find the log file under SolverStudio's File.... View Working Files menu. However, this looks like an AMPL model you are solving. Please also refer to the AMPL documentation (available under the AMPL menu) or the cplex documentation. The AMPL forum may also be useful; see https://groups.google.com/forum/#!forum/ampl. Hope this helps. Andrew 130. Hi, i'm trying to solve a integer problem, but it needs to run rather long. With the option "fracGap" set on 0.03 the running time became acceptable. But is it possible for the cbc solver to use more than 1 core? That way i could maybe reduce the fracGap to 0.01. Thanks a lot • I don't know, sorry. I have asked Stu Mitchell, the PuLP developer, if he knows. However, you may be best directing this question to the CBC forum at http://list.coin-or.org/mailman/listinfo/cbc or perhaps launching CBC.exe and using the built-in help. Sorry I cannot be of more assistance. Andrew 131. Hi, I'm trying to test the AMPL on NEOS to check if I can use it for my model, (Using the Cosmic Computer tutorial) and get the following error message: getaddrinfo returns an empty list Any advice on where I can look for my mistake? Thanks • This means SolverStudio cannot access the NEOS servers. Do you have an internet connection that is working? (By the way, the latest version gives a more helpful error message.) Andrew • Hi Andrew My previous attempt was from my work computer and although I have access to internet at work I believed the firewall etc. may have caused the problem. I have now tried to run the Cosmic Computers model from home and get the same problem as reported by Shareth Hariharan on 1 May. “Could not add reference to assembly system.IO." As your proposed fix is not available on the forum can you pls share with me as well? Thanks Pierre 132. Hi Andrew, I am running out of memory when running PuLP (IronPython) which I suspect is because I have a large problem to solve and need to run 64-bit IronPython. Is this possible? I can use external 64-bit Python, but then adding new sheets to the work book is not possible (as far as I can tell) so the I/O using Excel gets complicated. • Mike. You can use external cpython and still access Excel as 'Application' and similarly use ActiveSheet. (These connect back to Excel via COM). Make sure you have: from SolverStudio import *. There is no support for 74 bit iron python unless your office is 74 bit. Andrew 133. We've been using SolverStudio for over a year to schedule production, we basically solve a part/tool assignment problem, daily. And we are very happy with the results so far. Usually the problem solves in CBC in seconds after the model is built (we are using PuLP). Occasionally, depending on how the data looks for the day (demand changes daily and the present-day schedule is different daily), the problem may take more than an hour (if this happens we stop it and change parameters) and today, for some specific setup, CBC crashed. I suspect the formulation might not be the most efficient but we have some 10,000 binary variables and 10,000 constraints (or so), and I assume CBC should be able to handle it (right?). Anyway I am wondering if there is a way to pass some parameter to CBC from SolverStudio to make it stop after some iterations or some time: we'd rather take a decent solution after a minute (or a few) than the perfect solution after an hour. And what would normally make CBC crash? • Mario. Thanks for your post. It is great to hear that SolverStudio has become such an integral part of your operation. Stu Mitchell, the PuLP developer, will be able to answer your question on a run-time (or node) limit for CBC; I'll ask him to reply to this post as well. Re CBC crashing, you may have run out of memory (because the problem was so hard to solve, and so there were lots of possible solutions to explore), or you may have found a CBC bug. The latest SolverStudio 0.6.0 lets PuLP run a 64 bit version of CBC if you are on a 64 bit machine, which may help (but note that this is still an experimental release). We can explore this CBC issue further if you are happy to share the model... Andrew • Hello, Thanks for the quick reply. What do I need to run the 64-bit CBC? As far as I can tell I have a 64-bit OS and processor but 32-bit Office. I will send you the file via email, but I haven't been able to reproduce the crash, it now just runs for a long time before I stop it. I loaded the mps files from to two setups that take a few seconds and too long in SolverStudio to NEOS, and they both solve in under a second there with XpressMP, linear solver (can't validate the solution though). We recently updated to 0.6.0 but I must confess I hadn't read the install instructions so I need to verify that all requirements are met (.Net, etc), however we had the same timeliness problems in the previous version. Mario • I just found this in the "About Solver Studio" window: Is64BitOperatingSystem=True, Is64BitProcess=False, .Net CLR (version 4.0.30319.18144), 32 bit • Hi Mario the answer to the time limit now depends on the specific version of SolverStudio you use, as Andrew changed some things in the back end on the latest release. You need to pass arguments to the CBC solver, which means you have to explicitly call it. >>> prob.solve(COIN_CMD(maxSeconds=60)) #will stop in one minute (latest version) >>> prob.solve(PULP_CBC_CMD(maxSeconds=60)) #will stop in one minute (older version) please check the status of the solution returned as you may abort before a feasible solution is found. Alternatively use of the 'fracGap=0.1' parameter will terminate when an integer solution within 10% of the lower bound is found. Look at the output from cbc 'msg=True' to sort out what is happening. Hard to know why cbc is crashing I suspect it is running out of memory if you are solving on a 32bit machine. Stu (pls contact me if you would like me to look at improving your model) • Stu, I tried the maxSeconds=60 and got a status of "not solved", but then I tried the fracGap=0.1 and it did work: solved in seconds. fracGap=0.01 also worked well. I assume 0.1 is a 10% gap to the linear solution(?) I don't own the business process so I can't tell if this degrades the solution, but since this is so much better than what we did before I doubt it will hurt. Time will tell. The other option I thought I could try (before the fracgap worked) was the scaling (since the parameter incentives we use to prioritize the first day in the schedule skew the solution) and the presolve (the XpressMP output from NEOS reduces the size considerably), so after googling a bit I found this potential set of CBC parameters: COIN_CMD(path=None, keepFiles=0, mip=1, msg=0, cuts=None, presolve=None, dual=None, strong=None, options=[], fracGap=None, maxSeconds=None, threads=None) Is that it? are these the default settings? what do they mean? maybe too many questions more appropriate for a CBC forum... A link to a CBC guide will help. I am going to consider my problem solved until I get new complaints from my users. Many thanks for your help and best regards, Mario • Mario; I'm pleased that increasing the branch and bound tolerance is working for you. Yes, 0.1 will be the gap between the linear (LP) and integer (IP) solutions. Well done finding those CBC options; they are not well documented. You can find out what options CBC offers (although these may not all be available from PuLP) by launching cbc.exe (as found in the Solvers directory of SolverStudio) and entering ?. Cheers, Andrew. PS: this was added in latest experimental version. • We are still happy with the latest fracGap addition but now I wonder how do I make sure I don't leave anything on the table so I'd like to give it a minute to find the best solution and then if it doesn't find it I'll take the one with the gap. How do I get the fracGap value to put it in the if statement? In the mean time I played with the solution status and similar logic: prob.solve(COIN_CMD(maxSeconds=60)) if LpStatus[prob.status] == 'Not Solved': prob.solve(COIN_CMD(maxSeconds=60,fracGap=0.1)) But I think my file gets confused and doesn't do what I expect (what I get if I solve the problems individually) My next goal would be to use the fracGap in the if statement Is it even possible to solve the same problem multiple times with pulp/python flow controls? Thanks again! • Mario, Stu Mitchel says: It sounds like what he needs is a callback which is not possible with the command line version of cbc. COIN_CMD does not do resolves either so the above code will just solve the same model twice and will not improve the solution. GUROBI supports callbacks, and possibly in the future there will be a better interface to the cbc library than COINMP which I do not recommend or distribute with pulp any more. Hope this helps (somewhat), Andrew 134. Hello Andrew, Thanks a lot for sharing this elegant tool with the community. I have found that Solver Studio 00.06 uses default locale settings to export data from Excel towards model data files. In Russian we use comma instead of dot for decimal separation. This leads to exporting "0,12" -> "0,12" to data models (I tried AMPL) that does not work. Switching locale settings resolves the problem, but I suppose that this is temporal work-around rather than a solution. • Hi. Thanks for reporting this issue, which we want to address. I have emailed you the following code to test: x=5.1 print x print "str(x)=", str(x) print "repr(x)=",repr(x) print '"%g" % x=',"%g" % x print "'{:g}'.format(x)=",'{:g}'.format(x) Thanks, Andrew • I wish to thank you again Andrew for the awesome job done in SolverStudio, this tool is just amazing! I also had some problems with the comma and dot issue that local settings can cause (same problem as Ilya Malyarenko), but now everything is sorted out and working just fine. I'm using AMPL in a Brazilian Portuguese computer, and I have tried just changing the configuration of Excel to use dots instead of commas but that did not worked, it is necessary to change in the region and local settings under control panel for AMPL to work fine. 135. Hi Andrew I'm trying to set up a production scheduling model. My model is not yet complete and I already have 3500 variables (Bin and continuous. What language would you recommend that I try to use for solving the model. I see you advised AMPL or GMPL for someone else. It seems that the student version of AMPL would be limited to 500 variables and the cost of the full version is prohibitive for me. Will the GMPL give a reasonable solving time if compared with AMPL? Would you recommend another language? Thanks • You can run AMPL on NEOS at no cost... check the NEOS terms and conditions. The GMPL solver is slower than some others such as CBC. PuLP is free and can use the CBC solver but a bit harder than AMPL. Hope this helps. Andrew 136. sorry sorted 137. Hello. Thought I had installed successfully but the show model feature does not seem to work thanks 138. I have been using opensolver very successfully to solve large but simple linear problems. However, my models are getting very big and I would like to use solverstudio but I cannot get the syntax. Could someone help me with the code for this simple model . My decision variables are cells AL3:AR5 and they are binary. My only constraints are that the variables should be changed so that AF3:AF9 = 4 and X10:X16<=5. What would I need to copy in. I would appreciate any help thanks • Pleased to hear that you are keen to try SolverStudio. It is bit of a step up from OpenSolver. I suggest that using AMPL on NEOS, or GMPL (which is very similar), might be easiest, in which case you should start by looking at the SolverStudio AMPL examples, and also reading the online AMPL documentation available under SolverStudio's AMPL menu. Please note that you need the latest SolverStudio to handle recent changes at NEOS. Good luck, Andrew 139. Hi Andrew, I have installed Solver Studio successfully (seems) on my PC (I have Windows 8 and Microsoft Office Professional Plus 2010 in Italian). I see the example file and I can set the language (AMPL) in the Excel spreadsheet. But when I click on "Solve" I get the following error message: SolverStudio Error: Unable to run model. Accesso al percorso (Access to the path) 'C:\Users\serena\AppData\Local\Temp\SolverStudio qc31adyx' negato (denied) and the solver does not work. What can I do to fix it? Thanks a lot. Pachi • I have seen this happen once before where the permissions for the Temp folder prevented the user (& hence SolverStudio) from accessing the temporary folders the SolverStudio was creating. You can fix these access permissions for your login ID, or simply run as administrator. Please let me know if this fixes the problem. Andrew 140. Hi Andrew, When I run AMPL on NEOS (e.g., the Transportation-AMPL-NEOS file) I get an error: "Could not add reference to assembly system.IO. Is NEOS having an issue with the XML RPC interface? or am I doing something wrong? Thanks! • Thanks for alerting me to this issue in the experimental release, which seems to only occur on some machines. Thanks also for trying my proposed fix and confirming it works. I will incorporate this in to the next release. Andrew 141. Is there a software license agreement or terms and conditions for the use of Solver Studio? I saw there was some listed in the documentation for the optional add-in products. Thank you. 142. Hi Andrew! great news you have a new version that is planned for working with Excel 2013. Thank you for that, however it was an "almost" in my case! Being positive, I believe I'm closer to make it works in my computer (Excel 32 bit in Win 7 64 bits). The problem and what I have tried: Something is not allowing me to run the "Show Model" feature. What could it be? Strangely, the "Show Model" works just one single time after installing 0.06 version. It was a empty excel and works fine there (I could write some code in the side box and I tried different menu options of that box with no problem). Then, I decided to see one of the excel examples included in SolverStudio directory, but the side box didn't show up in that excel window (but, It was still visible in the excel window of the empty excel file!!). Well, I tried a few times more and opened some other excel samples, but nothing. Then I restarted excel, and the "Show Model" stop to work completely (even for the empty excel file). Restart computer and reinstalling SolverStudio again didn't improve the situation. It may be not a clean install (not sure...), so I reinstalled again making sure there were no trace of an old conflicting version, but still did work. Just for your info, for that last reinstall followed these steps to be sure it was a 100% clean install: A) uninstall, B)restart system, C) delete all caches and old .dlls (including "...\AppData\Local\assembly\dl3\..." and "...\AppData\Local\Apps\2.0\..." ), D) restart system again and double check that full .Net 4 and VSTO 2010 were installed, E) unblock the .zip (I didn't that before), F) install SolverStudio via "setup.exe" with admin privileges (no errors reported!), G) restarting system and finally executing excel. Result: "Show Model" still not work (not working for empty Excel neither). Looking for a log file for figuring out what can be blocking the "Show Model", and found nothing.) Any ideas? Where can I find the SolverStudio log errors? (I have looked for *.log in the application dir, and in system event log). Hopping you can give me some guidelines... All your help will be highly appreciated! Cheers, Andres • Andres. Sorry it is not working for you. Thanks for reinstalling etc to help eliminate issues. Your system is the same as mine so I am puzzled. Can you please send me the text contents of the About Solverstudio box? Email a dot mason at auckland ac nz. Thanks. Andrew. PS. There is no log file but you can turn on VSTO .Net logging... see the Advanced Installers folder for details. • Thank Andrew for your fast reply! I sent the email with details, looking forward to your news. In the meanwhile I will try to turn on the logging to see if something is interfering with openstudio! 143. Hello Amas, I just installed Solver Studio for AMPL on NEOS. I ran your own file "transportation AMPL Neos" and it runs perfectly but it does not take decision variables into sheet. An error comes up saying that "display 2 1 10\n" on line 32 of file Sheet. It also says that "unexpected error has occurred when writing the new values to the sheet; the data item name is missing in the temporary file". Can you help me please? I have a model with more than 4000 variables and 1200 constraints and I need to know if Solvestudio is the solution I have been looking for with a fast constructing model. • This is the transportation ampl neos model: # Define our sets, parameters and variables (with names matching those # used in defining the data items) set Bars; set Warehouses; param costs {Warehouses, Bars}; param demand {Bars}; param supply {Warehouses}; var flow{Warehouses,Bars} >= 0; minimize Total_Cost: sum {i in Warehouses, j in Bars} costs[i,j] * flow[i,j]; subject to Supply {i in Warehouses}: sum {j in Bars} flow[i,j] Sheet; • NEOS on AMPL is not working currently because of a recent (perhaps accidental) change in NEOS. We have raised this with NEOS so they can address the issue. I have sent them more information today. Andrew 144. I'm trying to play around with SolverStudio, specifically to leverage the external python interface but I think I am missing something simple. I've managed to fumble around enough to know that when using "Python (external)" I have to... from SolverStudio import * ...to access the data declared as variables on the Excel sheet. However, I can't quite find a way to get access to the Python-Excel interface (e.g. SolverStudio.Application or Application). Trying the following has not worked:  from SolverStudio import Application   import SolverStudio.Application   import Application  So I think I am barking up the wrong tree. What is it I need to do here to manipulate Excel directly from Python? • Figured it out by manually importing win32com and working that way. However, given the discussion in "Using SolverStudio" it sounds like something may not be functioning as I have no access to a SolverStudio.Application object? • SolverStudio does not currently support .Application etc when running a model via external Python (i.e. a version of Python you have installed). However I have now added this capability (using win32com as you did); it will be in the next version. The internal Python, IronPython, provides the capabilities as documented. Hope this helps. Andrew 145. I just installed SolverStudio (Excel 2007, Windows XP pro SP3). Running the example for AMPL NEOS solves just fine -- and the Show Model command works -- but I'm getting an error message when I try to Edit Data or Show/Hide Data using those commands on the ribbon. The error messages are as follows: Compile error: Can't find project or library Unable to run the model data editor. Error=Exception from HRESULT: 0x800A9C68 Is this because it is looking for something that should be in a directory listed in the system PATH variable? Thanks! • This is probably because SolverStudio loads the Data Editor addin (SolverStudioTools.xlam in Application Files) to perform these tasks. This addin uses the RefEdit control, which sometimes gets broken. You can test this hypothesis by adding a RefEdit control to a VBA form; if it fails my hunch is correct. Repairing your Excel installation typically fixes this. Cheers, Andrew • Thanks Andrew, but RefEdit doesn't appear to be the problem. I was able to successfully add a RefEdit control to a VBA form (and run it). Is it possible the Excel installation needs repair anyway? • Never mind Andrew -- it turned out Excel needed an update, and upon installing it, all seems to be well. Thanks again for your help! 146. Is there a way to load 3-dimensional tables using StudioSolver? Or does the data wizard only support 2d? Thanks! 147. Good morning (evening or afternoon), I have been trying to use the GAMS on NEOS example 'Transportation-GAMS NEOS'. When i solve the model i seem to get the right values in the output details, but for some reason, the results fail to load back into the xls. Below is the tail end of the output text: Input /scratch/neos/solver/jobs/2287279/MODEL.gms Output /scratch/neos/solver/jobs/2287279/solve.out ## Downloading GDX result file ## Download complete ## GAMS completed successfully. ## Reading results... ## Error reading results ## Done When i view the text in the GDX results file there is the following message: The requested URL /neos/jobs/2287279-oeHdQzYt-out.gdx was not found on this server. Any thoughts? • No obvious thoughts (except a blocked port for ftp access?)... we will do some checks, and get back to you. Andrew • Thanks for alerting us to this problem. NEOS have improved the way they return results files, and so we need to update SolverStudio to work with the new NEOS approach. Keep an eye out for a new version of SolverStudio coming soon. Thanks again, Andrew • Just started using SolverStudio today and have the same issue. Any update or advice on another way to view results? Thanks! 148. Hi, I am very interested in getting a version of SolverStudio that works with Excel 2013 -- let me know when that happens, thanks! --William 149. Hello! I am another Excel 2013 user who is unable to get the ‘Show Model’ feature to function. I see that you were beta-testing a fix back in August 2013, and I am eager to see that fix deployed. More and more of my students are using Excel 2013 (comes standard on their new computers) and so I really need for SolverStudio to function on the newest Excel deployments. I love the application by the way. Again, looking forward to the deployment fix for getting the ‘Show Model’ feature activated in Excel 2013, thanks. --William McKibbin 150. I gather that SolverStudio / OpenSolver are not available for Mac/OSx. Can someone confirm? Assuming that's the case, is anyone aware of something similar for Mac, or efforts to port this? I have Excel 2011 for Mac. Thanks! 151. Hi I am new to solver studio add-in. Can you explain how to build a linear programming problem using solver studio? • I suggest looking carefully at the examples. There is also an AMPL tutorial under the Languages... AMPL page that might help. Good luck, Andrew • Already I have a problem with me. How to set data? 152. Hello, I'm using solverstudio with coopr-cbc and it works very well. However, I'm trying to use coopr-gurobi but it's not working. Here is the details on my screen: [ 0.00] Setting up Pyomo environment [ 0.00] Applying Pyomo preprocessing actions [ 0.03] Creating model [ 241.57] Applying solver ERROR: Unexpected exception while running model C:\Users\Utilisateur\AppData\Local\Temp\SolverStudio qn02p42p\model.py: Problem constructing solver Gurobi ## COOPR did not complete; no solution is available. The sheet has not been changed. When I used solverstudio with gurobi, it works well. When I used python and called gurobi, it works well. When I use solverstudio with coopr-cbc, it works well, but when I try to use solverstudio with coopr-gurobi, it crash. Any clue how to fix this? Thank you for you help. • Maybe it's the same trouble: the diet example solves with coopr-cbc but not with coopr-gurobi. 153. Is it possible to request the beta (or alpha) version that works with 2013? I understand and accept the risks. Thanks! Kevin 154. Hello there I have been using opensolver to solve large models for 12 months. It has been a real blessing. My models involve a large number of binary variables with a range of constraints which i set up using formulas in Excel. The only problem is that with large models it can be very slow (days in some cases). I was attracted by the notion the solverstudio would be quicker. However, am in right in saying that I will need computer programming experience to be able to use these models in solverstudio? I have a little bit of VBA but that is about it. Is there an easy way of converting the model in opensolver into the sort of computer language that is required in solverstudio. • PLeased OpenSolver is proving useful. To speed things up you could use SolverStudio, and, yes, you will need to learn a modelling language such as AMPL or GMPL (which are probably the easiest ones to start with, and pretty intuitive - SolverStudio comes with lots of examples). You can also switch to some of Frontlines's more advanced solvers which are faster at getting your model off the sheet. Cheers, Andrew 155. Hi Andrew, Thanks for this great tool! I just switched from OpenSolver to SolverStudio and really like the increased speed when building models! I am using GMPL and it works very well. There are two minor problems that I have with the SolverStudio and was wondering if you have an idea how to solve them: 1) For GMPL I would like to use the command line option --tmlim xx to set the time limit to xx seconds. In principal, this is quite easy to do by just adjusting the Python script that you are using. However, I would like to set the time limit within Excel to make adjustments easier. Is there a way to pass command line options from Excel to the Python script using the SolverStudio add-in? 2) I would like to write my model to the SolverStudio GUI using VBA code. I already figured out that the model is stored as a custom XML part within the Excel file. I am also able to access the custom XML and replace it by my new model definitions. However, the changes are not reflected in the GUI. I guess I oversee something here. Can you let me how you are writing the model from the GUI to the worksheet? Thanks and best regards, Mark • Mark, Thanks for your positive comments, and your extensive experimentation with SolverStudio... I am most impressed! To address your questions: 1/ We could easily add this option to GMPL by following the model used for Pyomo where the user defines a named range, say GMPLOptions, on the sheet, and enters options and values into this that are passed to the GMPL executable. The code for handling this is in the RunCOOPR.py file. If you were willing to make this change for GMPL, then we would love to incorporate that in the next release. 2/ SolverStudio does indeed save the model as Custom XML; the View Embedded XML menu shows you this content. Writing to this in VBA will be tricky as SolverStudio caches this in memory after opening the sheet. However, if you look at the RunAMPL.py file, you will see how you can modify the model from IronPython. Unfortunately, SolverStudio does not (yet) expose these methods for use by VBA; this is, however, on my TODO list. If you were willing to switch to PuLP, you would be able to do anything you want as you'd have access to a full language, and so could create customised PuLP models in response to user settings on the sheet. Can I ask what you are using SolverStudio for? It sounds like an ambitious project, and just what we need to motivate improvements to SolverStudio. Cheers, Andrew • Hi Andrew, Thanks for your reply! I am trying to solve a large facility location problem. The problem can be seen as a kind of set covering problem and I need to add one constraint for each demand point to model relation between demand and supply. In fact, the coverage constraints are very simple and can be written as Ax >= y, where A is a very large but sparse matrix. Therefore, I do not want to store the matrix A in my Excel model. Instead, I generate the constraints using a VBA macro. This is the reason why I would like to write my model to the SolverStudio using VBA and also why I would like to use a time limit. @1 Wouldn't it be sufficient to allow the user to define a named range (e.g. SolverOptions) in Excel that just contains a string a that is appended to the command line call of the solver(s)? Then it is up to the user to enter meaningful parameters (like "--tmlim 60 --fpump"). I have only little experience with Python so far, but if I understand the he RunCOOPR.py script correctly, then I could just use the same approach for the GMPL script and there are no changes required within the SolverStudio add-in, is this correct? In that case, I will give it a try and will share my modified script with you! @2 Your suggestion to use PuLP instead of my combination of VBA and GMPL sounds very promising! I was not aware of this option so far and will have a look at it! Best, Mark • You can often have more constraints than you need, and let the solver delete the ones that are redundant. This might be a useful approach for you. The single options string would be a possibility, but is harder for the user. I prefer the COOPR-style table (which we also use in OpenSolver). I'd welcome updated code from you if you implement this for GMPL. I hope PuLP works for you. Andrew • Sorry, it is still on the ToDo list. Can you perhaps tell me what you'd like to do from VBA? Just run the model, I assume? Andrew 156. Hi, I've just downloaded SolverStudio and I've got the same problem reported by min_w2013 (March 11, 2013 at 4:42 am): the 'Show Model' doesn't seem to do anything on the Excel 2013. I'm posting just to let you know that, different from the problem stated previously by min_w2013, it IS solving the models in the sample files, but my model that was already built within the OpenSolver Add-In failed. The Opensolver is doing its job very well, but I would like to learn to use the SolverStudio as well. I'm on a 64-bit Windows 8 machine and my Excel is the Home and Student 2013 (version 15.0.4517.1005) I understand a little bit of VBA and I'm trying some things in the SolverStudio code, but nothing sucessfull up to now, if there's anything I can do to help just let me know. Thanks for the awesome tools! (SolverStudio and OpenSolver) • Thanks for the feedback. We have a new beta version of SolverStudio that is Excel 2013 compatible, and has a number of other improvements. We are still testing this; it should be available soon. Cheers, Andrew • Hi Andrew! Your work to integrate solvers with excel is fabulous! Now I have updated to Excel 2013, but solverstudio is not compatible. Is possible to download the beta version you mentioned in above post? Can you share the link to download it? (I happy to play with the beta version, while waiting for the amazing next release that hopping it will be compatible with excel 2013!). Cheers, Andres • Thank you for this new version! It "almost" worked for me. Please see my today new post with the details. Maybe we can figure out what I'm doing wrong that is making the "Show Model" option fails to work. (Triple checked that SolverStudio 0.06 were 100% clean installed!) Chears, Andres • The language of my system and excel is "Spanish". However, I managed to run Solverstudio 0.05x in Excel 2010 (Excel and System in Spanish) without problems, before. Cheers! • Appreciate your work creating SolverStudio and making it available. Any news / progress on support for Excel 2013? Its now impossible to get Excel 2010 (well at least legally from Microsoft!) and so this is giving me problems getting coursework done! Thanks, Richard. 157. Hi, Thanks for making solverstudio this is very help full,I am going to make a very large scale MILP problem,i tried open solver but it is taking lots of time,I just want to know which modeling language will be most fastest to convert my problem so that i can learn it & implement it,I want to use an open source language. Thanks Kunal • GMPL will probably be fastest to build the model, but the GLPK solver it uses is typically not as fast as CBC. Pulp and COOPR give you access to CBC (a faster solver), but both of these are, I suspect, slower to build the model before solving it. The latter are both Python based, which is more powerful but perhaps harder to learn. Hope this helps, Andrew 158. Hi, I am having a problem running the installer, get an error: Name: SolverStudio From: file:///C:/Users/HP/Documents/SolverStudio_00_05_00_00 20130228/SolverStudio/SolverStudio/SolverStudio.vsto ************** Exception Text ************** System.Security.SecurityException: Customized functionality in this application will not work because the certificate used to sign the deployment manifest for SolverStudio or its location is not trusted. Contact your administrator for further assistance. at Microsoft.VisualStudio.Tools.Applications.Deployment.ClickOnceAddInTrustEvaluator.VerifyTrustPromptKeyInternal(ClickOnceTrustPromptKeyValue promptKeyValue, DeploymentSignatureInformation signatureInformation, String productName) at Microsoft.VisualStudio.Tools.Applications.Deployment.ClickOnceAddInTrustEvaluator.VerifyTrustUsingPromptKey(Uri manifest, DeploymentSignatureInformation signatureInformation, String productName) at Microsoft.VisualStudio.Tools.Applications.Deployment.ClickOnceAddInDeploymentManager.VerifySecurity(ActivationContext context, Uri manifest, AddInInstallationStatus installState) at Microsoft.VisualStudio.Tools.Applications.Deployment.ClickOnceAddInDeploymentManager.InstallAddIn() The Zone of the assembly that failed was: MyComputer Tried some common suggestions on the web, wasn't able to figure it out. Even if someone can point me out how to temporary switch off this 'trusted check' or another way to bypass this would be great. I am running Windows 8, 64 bits. On a side note, I first got another error, Setup has detected that the file vstor40_x64 has changed since it was originally published, which I worked around by manually installing the Microsoft Visual Studio runtime setup from Microsoft. • Thanks Andrew, the Unblocking of the zip file (General tab of the properties of the zip file, clicking Unblock) before unzipping and installing fixed this issue! 159. Hi, I'm experiencing an error when printing my results to a sheet. The problem is not with the model as I sent the output to a text file instead of a sheet and the results appear to be normal. The error is here http://i.imgur.com/aoa8w1Y.png Thanks for your help. This forum has already solved a bunch of my problems up to this point. • Have you defined a SolverStudio data item with the exact same name (including matching upper and lower case) on the spreadsheet? I'd check this carefully. Andrew PS: The comment about "option display1col..." is no longer relevant (and has been deleted from my current unreleased version). 160. Andrew, I was having difficulty with my VBA continuing on before the optimization is complete. The following is not a great solution, but perhaps it will help someone come up with something better. I used a WScript shell to create a 3 second popup asking the user if they want to cancel the optimization. The VBA checks if an output cell named "optcompete" indicates that the optimization is complete. If the optimization is not complete, a new popup takes its place. If the user is tired of waiting they can cancel the optimization (and then hit the cancel button provided with Solver Studio). As I said, not a great solution, but hopefully someone comes up with something better. Thanks, Steve Sub OPTIMIZE(sheetname) Const btnOK As Double = 0 Const iconInfo As Double = 64 'Show "Information Mark" icon. Dim WshShell, RetValue Set WshShell = CreateObject("WScript.Shell") Range("optcomplete").Value = "" Sheets(sheetname).Select 'VBA code to run Solver Studio 'On Error Resume Next Application.EnableEvents = True Dim RibbonPropPage As IAccessible Set RibbonPropPage = GetAccessible(CommandBars("Ribbon"), _ 37, _ CommandBars("Data").NameLocal) RibbonPropPage.accDoDefaultAction CHILDID_SELF Application.Wait DateAdd("s", 1, Now) Set RibbonPropPage = GetAccessible(CommandBars("Ribbon"), _ 43, _ "Solve Model") RibbonPropPage.accDoDefaultAction CHILDID_SELF Application.Calculation = xlCalculationAutomatic 'End Code from Andrew flag = 0 While flag = 0 RetValue = WshShell.Popup("Cancel Optimization?", 3, "Optimization Running", btnOK + iconInfo) If RetValue = 1 Then flag = 9 If Range("optcomplete").Value = 1 Then flag = 1 End If Wend Range("optcomplete").Value = "" If flag = 1 Then 'CREATE_MAP_SHEETS End If End Sub 161. In the sample of using SolverStudio with AMPL in Excel that I read, it was only two dimensions parameters or variables. Can I use SolverStudio with 3 or more than 3 dimensions in Excel? And how to use or change it to use in Excel. Thanks, Geng • Yes. You can do 3 indices either by having 3 index columns, as in: 1 1 P AAA 1 1 Q AAB 1 2 P ABA 1 2 Q ABB or by adding a column index, as in P Q 1 1 AAA AAB 1 2 ABA ABB Hope this helps, Andrew • Thank you so much, Andrew. God bless. Geng • Does this work for GAMS-NEOS as well? I seem to be having issues reading in the following table with columns 1 and 2 representing indices I1 and I2 and I3 being columns 3, 4 and 5: I1 I2 I3:A B C L 3 3 17 17 L 4 19 19 19 L 5 4 14 14 B 3 17 17 17 B 4 19 19 19 B 5 14 14 14 Thanks for any help! • This should work (in GAMS, GAMS NEOS or any other language). In the Data Items editor you should have 2 index ranges, one covering columns 1-2, rows 2-7, and the second covering row 1, columns 3-5 (being A B C, without the I3: text). Hope this helps. Andrew 162. Hi Andrew, I've really enjoyed using Solver Studio. I recently started using the new 0.5.0 beta and I've notice some glitchy behavior with the Python code switching between different spreadsheets. I wonder if this is due to something that I have in my code that may be somehow changing the spreadsheet that the code is associated with. I'm going to continue to investigate and see if this behavior occurs with the previous version of the software, but I am very intersted in trying out Pyomo. I have tried out the code for running the model from VBA, but I notice that the VBA continues on before it gets the results of the optimization. While I have some ideas of poor work arounds to handle this behaviour, I wonder if there may be an relatively simple and elegant solution that may be possible within the code. Thank you again for your service on this project. I look forward to seeing the results of your continued work. Thanks, Steve • Steve, Your email came at an opportune time. Someone else sent me a spreadsheet with VBA code, and I spotted the same faulty behaviour when switching between spreadsheets. It only occurs after you enable the VBA code, at which point Excel appears (to SolverStudio) to open up a new spreadsheet. However, Excel is in fact re-opening the existing spreadsheet, this time with the VBA code enabled. This makes SolverStudio very confused! SolverStudio must have always had this problem, right from the first release. I will be releasing a new version as soon as a I can (hopefully in a week or perhaps two). I've never used SolverStudio with VBA code. However, it seems as though what we need is something like a "SolverStudio.RunModel" command we can execute from VBA. Should not be too hard... it is on my ToDo list. Cheers, Andrew 163. Is there a way to display a string to the worksheet? I'm trying to find a way to store the result of a portfolio selection (63 binary digits) as a variable result. I convert it to a hex string and store it in a symbolic parameter. The end of my Sheet file has the following: MCportfolio [*] := 1 '&h817202f3e4655566269758484848c' 2 '&h817202f3e4655566269758484848c' 3 '&h817202f3e4655566269758484848c' ; But I get an error: Error executing PuLP model code. .... Exception: When writing new values for data item 'MCportfolio' to the sheet, an error occurred in line "1 '&h817202f3e4655566269758484848c'\n" when converting the last item "&h817202f3e4655566269758484848c" to a number. • I am facing this problem too with the latest 0.09.03 (2016.05.20) SolverStudio version. SolverStudio does write the index and string items correctly to the Sheet with single 'quotes' in response to AMPL printf "%s %Q" format, but unable to write it to the Excel spreadsheet. Is there any solution or workaround for this problem? Thanks. • Sorry it's not working. Are you able to make up a small AMPL example that you can send us (via [email protected]) that shows the problem? Then we can diagnose what's going on. Thanks, Andrew 164. I love the SolverStudio add-in. I've been able to do quite a bit with it! So much better than kludgy text files. I do have one nagging little problem with it: after closing Excel and re-opening, the SolverStudio menus (smiley face, etc.) on the ribbon bar (under the Data menu) have gone away. The only way to get them back is to go to the Excel Add-in's, un-check the add-in, click OK, then go back to add-ins, check the add-in again, and click OK. It is marked to "load at startup" and appears in my list as an active add-on, but something in the process of adding it to the ribbon is broken. This doesn't happen to other users on the same machine, just to me. Running the last stable version (4.9.26) on Excel 2010 (14.0.6129.5000 32-bit). • Thanks for your positive feedback on SolverStudio. I cannot imagine why SolverStudio is unstalling itself, but just for you and not other users of your machine. Have you tried uninstalling SolverStudio (using the Add/Remove Programs or similar control panel item), and then installing it again? If you are an advanced user, you may wish to see what registry entries there are for SolverStudio, and check these are the same as the other users have. (Use RegEdit to search for SolverStudio.) Please let me know how you get on. Thanks, Andrew • Sorry I didn't come back to update this sooner. Yes, I tried all the usual uninstall/reinstall things, but then realized I was having issues with all Excel ribbon customizations. It's a remote server so I couldn't reboot but I did log myself out and back in and it fixed the issue. Sorry for not doing that easy troubleshooting step first! 165. Hi... First of all: SolverStudios is great. After a little work it is possible to handle it easily... I write successfully a model, which optimize a transportationproblem with some constraints. The last thing I want to do is to start solving the problem by vba. But the code you put on the webside does not work. My excel sheet does not know the "GetAccessible"-command. I´m using excel 2010... Perhaps I have to add a library. But I don´t know which one. Furthermore I don´t really know HOW to add any library... I hope you can help me... Thanks Maria • Thanks for the positive feedback. I have not personally used the code and so don't have any tips for you. But please do let us know what is needed to make it work. Thanks. Andrew • You need to include an accessibility module in order for the code to work. See this site http://stackoverflow.com/questions/15113874/is-there-a-way-of-showing-a-customui-tab-in-word-when-the-document-is-opened for the code, and that got it working for me. I think that could definitely be clarified though on the usage page. • Thanks, Kyle; I've added your note to my code sample. Pleased to see people using SolverStudio in interesting ways. Andrew 166. Is there a version of SolverStudio that will work with Excel 2013? Would appreciate the help. I was able to download Solverstudio and it shows up on the Data Ribbon, but when I try to look at the model or run it, it doesn't look like it's working. Any ideas? • We've never tried SolverStudio in Excel 2013. Can you be more specific in how it fails? Thanks, Andrew • Hello. 1. The install of SolverStudio seems to go through fine, no errors and the Solver Studio icons show up on the Data Ribbon. 2. When I select the Data Items Editor, it come up fine and lets me add informaiton into it. 3. When I select the Show/Hide Data, it labels the data on the worksheet as well as expected. 4. When I select About Solver Studio, the window shows up and gives me an option to uninstall which also seems to work fine. 5. When I select Show Model, nothing happens. I beleive I am supposed to see a window to the RH side of the page with the model, correct? 6. When I select Solve Model, a window quickly pops up and goes away, but I don't beleive it working as it is not changing any of the results. For learning/testing purposes, I am using a very simple problem that is solved by the standard Excel Solver, which as you know is limited which is why I am trying to use SolverStudio instead. Thank you for the promot response. • Thanks for all that info. It sounds as though Show Model is indeed failing under Excel 2013; I'm very sorry about that. It will take some time for me get access to Excel 2013, so please excuse the delay in this being fixed. By the way, what version of Windows are you using? Also, to do a better test of solving the model, would you mind deleting the decision variables (so these cells are all blank), and then clicking Solve Model and seeing if these decision variable values re-appear? Thanks, Andrew • 1. I am running Window 7 (Home Premium) SP1. 2. I deleted the decision variables using the Edit Data window and then when I clicked Solve model, neither is the solution changed nor the variables re-appear. Just as before a window quickly pops-up and goes away with no change in any data. • Thanks for the extra information. I will try to mimic your environment to work out the problem. Cheers, Andrew 167. Hello, I’m Geng. I try to use SolverStudio to find a solution for production planning problem in excel 2010. I can find the best solution when the Times period is 1..10. But when I set the Times period to be 1..12, I can’t find the best solution, The computer took more than 5 minutes for solving and then Model output shown: “There may be further error information in the clone logs. ran out of memory. 0 MIP simplex iterations 0 branch-and-bound nodes No basis. 115 Gomory cuts 402 mixed-integer rounding cuts ## AMPL run completed. ## Results loaded for data items: Batch Produce InvPro BackOr Raw1 Raw2 Raw3 InvRaw ## Done” I don’t know how to fix it. What’s the cause of this problem and how can I fix this problem to get the best solution. This is the model: ##Planning Model## ##Sets## set Products; #Products set Champoo; #Champoo product set Cream; #Cream product set Herbs; #Type of herbs set Times ordered; #Times period ##Parameters## param costP {Products}>=0; #Production cost param costOp {Products}>=0; #Operation cost param costInv {Products}>=0; #Inventory cost param costBac {Products}>=0; #Backorder cost param maxbatch>=0,integer; #Max batch in each period param demand {Products, Times}>=0; #Demand of each product and period param nbppb>=0,integer; #No.product per batch param maxbac>=0; #Maximum backorder param maxinv>=0; #Maximum inventory param costRaw1 {Herbs}>=0; #Raw material cost (planting) param costRaw2 {Herbs}>=0; #Raw material cost (fresh buying) param costRaw3 {Herbs}>=0; #Raw material cost (buying ready to use) param costInvR {Herbs}>=0; #Inventory raw cost in each herbs param initialInv {Products}>=0; #Initial inventory of each products param initialBac {Products}>=0; #Initial backorder of each products param maxRaw1 {Herbs}>=0; #Max raw from planting param maxRaw2 {Herbs}>=0; #Max raw from fresh buying param rateUseCh {Herbs}>=0; #Rate of Herbs that use to produce champoo param rateUseCr {Herbs}>=0; #Rate of Herbs that use to produce cream param maxInvR {Herbs}>=0; #Max inventory of raw of each herbs param initialInvR {Herbs}>=0; #Initial inventory of raw of each herbs ##Variables## var Batch {i in Products, t in Times}>=0,integer; #No. of batch to produce in each period var Produce {i in Products, t in Times}>=0; #No. of product that produced var InvPro {i in Products, t in Times}>=0; #No. of inventory of each product in each period var BackOr {i in Products, t in Times}>=0; #No. of backorder of each product in each period var Raw1 {h in Herbs, t in Times}>=0; #No. of raw from planting var Raw2 {h in Herbs,Times}>=0; #No. of raw from fresh buying var Raw3 {h in Herbs, t in Times}>=0; #No. of raw from buying ready to use var InvRaw {h in Herbs, t in Times}>=0; #No. of raw that inventory in each period ##Opjective function## minimize TotalCost: sum {i in Products}(costOp[i]*sum{t in Times}Batch[i,t])+ sum {i in Products}(costP[i]*sum{t in Times}Produce[i,t])+ sum {i in Products}(costInv[i]*sum{t in Times}InvPro[i,t])+ sum {i in Products}(costBac[i]*sum{t in Times}BackOr[i,t])+ sum {h in Herbs}(costRaw1[h]*sum{t in Times}Raw1[h,t])+ sum {h in Herbs}(costRaw2[h]*sum{t in Times}Raw2[h,t])+ sum {h in Herbs}(costRaw3[h]*sum{t in Times}Raw3[h,t])+ sum {h in Herbs}(costInvR[h]*sum{t in Times}InvRaw[h,t]); ##Constraints## subject to No_produce {i in Products, t in Times}: Produce[i,t]=nbppb*Batch[i,t]; subject to No_batch {t in Times}: sum{i in Products}Batch[i,t]1}:Produce[i,t]+InvPro[i,t-1]+BackOr[i,t]-demand[i,t]-BackOr[i,t-1]=InvPro[i,t]; subject to No_backorder {t in Times}: sum{i in Products}BackOr[i,t]<=maxbac; subject to LastBac {i in Products}: BackOr[i,last(Times)]=0; subject to max_inventory {t in Times}: sum{i in Products}InvPro[i,t]<=maxinv; subject to max_herb1 {t in Times, h in Herbs}: Raw1[h,t] Sheet; display InvPro > Sheet; display BackOr > Sheet; display Raw1 > Sheet; display Raw2 > Sheet; display Raw3 > Sheet; display InvRaw > Sheet; Please help me to fix this problem. Thank you. Best regard! Geng • I think you are creating a large problem, and that's why ity is taking a long time to solve. Andrew 168. Hello, I'm Keng. I used ampl (student version) with Openstudio in Excel 2010. I have a problem when solve the problem. Model ##Planning Model## ##Sets## set Products; #Products set Champoo; #Champoo product set Cream; #Cream product set Times; #Period of times ##Parameters## param costP {Products}>=0; #Production cost param costOp {Products}>=0; #Operation cost param costInv {Products}>=0; #Inventory cost param costBac {Products}>=0; #Backorder cost param maxbatch>=0,integer; #Max batch in each period param demand {Products, Times}>=0; #Demand of each product and period param nbppb>=0,integer; #No.product per batch ##Variables## var Batch {i in Products, t in Times}>=0,integer; #No. of batch to produce in each period var Produce {i in Products, t in Times}>=0; #No. of product that produced var InvPro {i in Products, t in Times}>=0; #No. of inventory of each product in each period var BackOr {i in Products, t in Times}>=0; #No. of backorder of each product in each period ##Opjective function## minimize TotalCost: sum {i in Products}(costOp[i]*sum{t in Times:t>0}Batch[i,t])+ sum {i in Products}(costP[i]*sum{t in Times:t>0}Produce[i,t])+ sum {i in Products}(costInv[i]*sum{t in Times:t>0}InvPro[i,t])+ sum {i in Products}(costBac[i]*sum{t in Times:t>0}BackOr[i,t]) ##Constraints## subject to No_produce {i in Products, t in Times:t>0}: Produce[i,t]=nbppb*Batch[i,t]; subject to No_batch {t in Times:t>0}: sum{i in Products}Batch[i,t]0}:Produce[i,t]+InvPro[i,t-1]+BackOr[i,t]-demand[i,t]-BackOr[i,t-1]=InvPro[i,t]; # Get data from the sheet data SheetData.dat; # Solve the problem # option solver cplexamp; # Use this for a full AMPL installation option solver cplex; # Use this for the student version of AMPL solve; # Write the solution to the sheet (using 1-column style) option display_1col 9999999; display Batch > Sheet; display Produce > Sheet; display InvPro > Sheet; display BackOr > Sheet; And the error C:\Users\Administrator\AppData\Local\Temp\SolverStudio\model.txt, line 33 (offset 1248): syntax error context: >>> subject <<0}: Produce[i,t]=nbppb*Batch[i,t]; ## AMPL did not complete; no solution is available. The sheet has not been changed. ## Done I need to fix this problem, if you can, please! Best regard. Keng Lis 169. Andrew, I am using the PuLP CPython (external) optimization option following the examples provided for Solver Studio. I’m afraid that this is a very basic question, but do you know how to pass the optional arguments of fracGap and maxSeconds to the solver (CBC, I believe). I’d like to see how close to optimal the algorithm can progress in a given amount of time. Thanks, Stephen • Stephen: You'll have to check the PuLP documentation as I cannot remember how this works. However, I found this comment from Stuart Mitchell that might be helpful: Cbc is the default PuLP solver, but you need to call it explicitly to send options. For example, if I want to see output from Cbc I do this: prob.solve(COIN_CMD(msg=1)) Heop this helps, Andrew 170. Andrew, I just wanted to circle back with you to let you know how I solved my problem of defining the data range of dynamic length for an example problem in which I have a list of population data for a number of postal ZIP Codes. The named ranges in Excel can accept dynamic ranges by using the OFFSET function. Your recommendation of the Name Manager was very helpful in understanding how you define your mathematical programming data. What a nice little tool! Here is how I defined the named ranges in VBA: Names.Add Name:="zipcode", RefersTo:="=OFFSET(Sheet1!R2C1,0,0,COUNTA(Sheet1!R2C1:Sheet1!R10C1),1)" Names.Add Name:="zipcode.dirn", RefersTo:="row", Visible:=False Names.Add Name:="population", RefersTo:="=OFFSET(Sheet1!R2C2,0,0,COUNTA(Sheet1!R2C2:Sheet1!R10C2),1)" Names.Add Name:="population.rowindex", RefersTo:="=OFFSET(Sheet1!R2C1,0,0,COUNTA(Sheet1!R2C1:Sheet1!R10C1),1)", Visible:=False Names.Add Name:="population.rowindex.dirn", RefersTo:="row", Visible:=False Names.Add Name:="population.badindex", RefersTo:=1, Visible:=False This technique may be also used for defining named ranges for returning optimal solution values of dynamic length. Thanks! Stephen • Stephen: Please you got this working; you are definitely using SolverStudio in innovative ways. I need to get around to documenting the way SolverStudio stores its data items as it may be useful for other people. Cheers, Andrew • First of all thank you again for a great product. I am trying to "graduate" a model from OpenSolver to SolverStudio. And I am also making my ranges named and dynamic in a very similar way as described above. My question is what would prevent a particular dynamic named range from being the index of another? Say I have three ranges: parts =OFFSET(parts!$A$2,0,0,COUNTA(parts!$A$2:$A$9999),1) parts_priority =OFFSET(parts!$F$2,0,0,COUNTA(parts!$A$2:$A$9999),1) parts_re =OFFSET(parts!D2,0,0,COUNTA(parts!A2:A9999),1) parts_demand =OFFSET(parts!$E$2,0,0,COUNTA(parts!$A$2:$A$9999),1) I was able to index parts_priority and parts_re with parts but I cannot add parts as an index for parts_demand. I tried the stable version (0.4.9.26) but learned that dynamic ranges only work in 0.5.0.0. I thought maybe some of the formulas (if, vlookup, etc) might be a problem but replaced the formulas with their resulting values and it didn't help. Another strange behavior (related or not) is that sometimes, after I make a change to a named range in the "data items editor" the range disappears, but when I reopen the editor it reappears again. Interestingly enough, after I changed the above ranges to be dynamic and even though I could not add the index to parts_demand, the problem still solves. In the model (I am using PuLP) I wrote: for p in parts: prob += parts_demand[p] >= lpSum(([vars[((i,j),k)] for (i,j) in mattool if i == p]) for k in workdays) so it seems to work. So is it really necessary to index the range in the data editor or just using parts_demand[p] suffices? May be the data is indeed indexed but the editor is not displaying it right? If it matters: I use Windows XP, Office 2007, PuLP (with the Python from SS) Thank you and best regards • Great to see you moving from OpenSolver to SolverStudio, and experimenting with the new dynamic range support. I have looked at your spreadsheet (thanks for emailing it), and have been able to add 'parts' as an index for 'parts_demand' (by typing in 'parts' (without quotes) as the index for parts_demand in the Data Items editor). Can you please give more details explaining how it did not work for you? Thanks, Andrew • Well, I also type it: open the data items editor, select the parts_demand item, click on the first index range, type 'parts' without the quotes, click update data item, at this point parts_demand becomes invisible, click close, when I reopen the data items editor the parts_demand becomes visible again but it is no indexed. Other data items also become invisible when I edit them but when I reopen the data items editor they are visible and indexed correctly. Weird. • Mario, This disappearing is a known bug that occurs when names exist as both local (to the sheet) and global (to the workbook). It is fixed in my unreleased beta; I will email this beta to you, and would welcome feedback. Thanks, and sorry about the glitch. Andrew • I am not sure really how but the indexing problem is fixed. And I was using the original beta version from this site. I removed and re-added the indexing and indexed ranges for the Nth time and the index finally stuck (to be clear: it works in both betas but I actually fixed it in the original beta). I must have been doing something wrong before, in a very old file that's seen lots of changes... Thank you for your help and patience. Solver Studio is absolutely great. Now I am on to deal with PuLP and Python. Best regards. • Mario: I'm pleased it worked. I will try to check the code at some stage to see why it might have been doing the right thing on your system. Cheers, Andrew 171. Does SolverStudio use the ODBC table handler for passing data to AMPL? • No; it passes the data as a text data file. You can see this file being used in the examples as: data SheetData.dat; Cheers, Andrew 172. Hi, I've installed SolverStudio on Win 7 x64 with Excel 2010, called setup.exe, started the examples.xlsx and pushed F5 on the first example: SolverStudio Error: Unable to run model. Failed to load language 'IronPython 2.7': Methode nicht gefunden: "!!1[] Microsoft.Scripting.Utils.ArrayUtils.ConvertAll(!!0[], System.Converter2)". I do have an independent installation of IronPython 2.7.2.1. What's might be the problem? Any ideas? Thanks, Christian • Sorry for the noise... My installation of IronPython 2.7.2.1 was the problem. Upgrading to 2.7.3 fixed the problem. • Pleased that it worked after upgrading; your experience will be useful for others. I'd also love to hear how you are using SolverStudio, and if it meets your needs. Thanks, Andrew 173. I've done a little more research and what I would really like to do is to enter into the Data Items Editor values such as the following: Cell Range: OFFSET(I4,0,0,COUNTA(I4:I1000000),1) Index Range: OFFSET(H4,0,0,COUNTA(H4:H1000000),1) While it currently only seems to require absolute references, it seems that the above may be possible since such value do show up in the Cell Range when these data ranges are setup using the Excel Name Manager. Everything seems to export just fine to Python so long as you don't need to use index ranges. My problem is that while index ranges may be useful but not necessary for exporting data to Python, they seem to be necessary when importing data back into Excel. Is this true or am I missing something? Your help is much appreciated. • Thanks for your feedback; great to see you using SolverStudio. We are looking at changing SolverStudio to allow computed ranges to be used in the Data Items editor. As you say, it almost works now. In the meantime, you can achieve a similar effect by using Excel to insert a table. This table will then grow (i.e. add more rows) as you add data below the table, and will automatically expand the associated defined names used by SolverStudio. You are right that putting data back onto the sheet normally requires indices. You can see how these indices are set up using a tool such as Name Manager. You can also just write directly to the sheet if you are using Python, using the Application object which SolverStudiuo makes available, as in: Application.ActiveSheet.Range("A1").Value = 10 or r=Application.ActiveSheet.Range("A1:C5") r.Cells(1,1).Value = 10 Cheers, Andrew 174. This is a great service to the OR community. I am making a spreadsheet to allow users to enter their own data and thus the size of the data ranges will vary. Can you add/change/delete the data ranges defined in the Data Items Editor using VBA or have it dynamically defined? • Steve; Excuse the very late reply. If you first create your data as an Excel Table, then adding rows will automatically grow the table. Defining your data item in terms of columns of this table will then ensure the data item also grows as the users adds new rows. Alternatively, you can define the data ranges using VBA (see the comment above for more details), or (in the latext version of SolverStudio), you can define data items using formulae such as Offset(). Hope this helps, Andrew 175. "Click code" for calling SolverStudio from vba on excel 2010 :) On Error Resume Next Application.EnableEvents = True Dim RibbonPropPage As IAccessible Set RibbonPropPage = GetAccessible(CommandBars("Ribbon"), _ 37, _ CommandBars("Data").NameLocal) RibbonPropPage.accDoDefaultAction CHILDID_SELF Application.Wait DateAdd("s", 1, Now) Set RibbonPropPage = GetAccessible(CommandBars("Ribbon"), _ 43, _ "Solve Model") RibbonPropPage.accDoDefaultAction CHILDID_SELF Application.Calculation = xlCalculationAutomatic • Fantastic; thanks for working that out, and even more for sharing it. I will put it up on the main site as well. Cheers, Andrew. PS: Any reason why you enable calculation afterwards? SolverStudio should not be permanently changing this... 176. This is pretty neat! Thanks for developing this. I see that you are using python 2.7. It would be really neat if your installer could detect current installations of python and use that instead of a different installation. Then I should be able to access all my other python libraries. i.e. numpy, scipy, cantera, etc. • Thanks for your positive feedback. The good news is that, when the language is "Python (external)", SolverStudio already does exactly what you are suggesting. If the user has a version of Python installed, then SolverStudio will find that. (It searches of all the directories in the system PATH looking for python.exe.) Otherwise, it will use its own Python files. Do let me know if this works for you. Andrew • I didn't realize that! It works great on my machine (win xp, excel 2007, python 2.7). Have you tried it with python 3.X yet? I'll have to try it on my machine at home and let you know how it works (win 7, excel 2010, Python 3.2). I found your examples, however do you have any quick tutorials (i.e. on I/O between python code and excel values etc.)? • We do now have a tutorial, but it is for AMPL (which is probably not what you want); See http://twiki.esc.auckland.ac.nz/twiki/bin/view/OpsRes/CosmicComputersSolverStudio We don't have any other tutorials, sorry, but you may find it helpful to look thru the .py files in the language folders (such as AMPL, GAMS etc). I have not tried Python 3.X. We do some 'unusual' things to trap changed variables, so I'm not sure if it will run ok. Do let me know if you try it. Thanks, Andrew • Two items that I think would be huge benefits to your code: 1) Add support for writing numpy arrays (2D) to the excel sheet 2) If the excel variable does not have enough cells, let the variable expand to fir the data? • Those are some good ideas. We will be working on this over Dec/Jan, so there may be some improvements on their way. Cheers, Andrew 177. Hi! Another question: If I have a parameter that's a function of a from-location and a to-location, but the values in the sets from-location and to-location are the same, do I need to define them as different sets? I know that for a single index, the set does not need to have the same orientation as the parameter indexed by it, but then when there are two indices, it seems to matter which is horizontal and which is vertical. Thank you! • It is not so much which index goes in a row or column that is important, as the order in which they are listed in the definition of the data item. This is the same order as you have to use in the GMPL code. Cheers, Andrew • In SolverStudio, when you set up the data item you determine which index is the first index, and which is the second. You can set up your data to have the first index being either a row index or a column index; SolverStudio allows both options. I'm not sure this answers your question, but I hope it helps. Andrew. 178. Hello there! Thank you for your suggestion to build my model in SolverStudio instead of in OpenSolver in order to cut down the run-time. I'm currently in the process of converting the model to SolverStudio, but I've run into some problems. This is my first time using GMPL, so I'm not sure if I'm just using the language wrong or if there's something particular I need to know about using GMPL with SolverStudio. I'm trying to define a decision variable as a function of another decision variable: [definitions of sets here: TimeSteps, Modules, Waves, ModulesFrom] [definition of params including carton_flows{ModulesFrom, Modules}] var work_initial {TimeSteps,Modules,Waves}, >=0; var work_done {TimeSteps,Modules,Waves}, >=0; [objective function here] subject to WorkFlow{ t in TimeSteps, m in Modules, w in Waves}: work_final[t,m,w]=work_initial[t,m,w] + sum{m1 in ModulesFrom}work_done[t,m1,w]*carton_flows[m1,m]; I'm getting errors like "t is undefined." If you could give me an example of how I should be defining a decision variable as function of other decision variables and parameters, I would really, really appreciate it! I haven't been able to find any sample code online that demonstrated how to do this. Thank you very much!!! Best Kathy Lu • My quick glance at your model suggests it is ok, but I have not used GMPL for many problems, and so must be missing something. You can also try the student version of AMPL as another option. Andrew • It should be possible, but I have never done this. It involves calling C# from VBA. I will add this to my (rather long) to-do list, but don't hold your breath. In the meantime, you could use VBA to simulate a click on the SolverStudio Solve button; you will need to do some Googling to work out how to do this; I found this site which may work: http://social.msdn.microsoft.com/Forums/eu/isvvba/thread/537b6039-988a-41a9-ae35-df7423634f27. Please let us know if you succeed. Cheers, Andrew • If you look at the 'Transshipment-PuLP' worksheet in 'SolverStudio Examples.xlsx', you can see an example (eg 'costs') of a parameter indexed by two parameters which are simply listed as pairs. Eg: From To Cost Youngstown Albany 0.5 Youngstown Cincinatti 0.35 Youngstown Kansas City 0.45 You could add your third index as a new (third) column in the index range. Or, you could add a new third index as a header row to create a table of costs, with the first index being a tuple (stored as two columns, as above) and the third as single values in the new row. Does this help? Andrew 179. Hello people! First of all, thank you for devoting your valuable time and resources to the deploy of those great tools. I have downloaded the SolverStudio_00_04_01_34 file and unzipped it. However, when installing it (my machine is a Dell computer. Intel Core Duo (2 CPU) with 3GB of RAM. And the operative system is Windows 7 Enterprise SP1, with MS Excel 2007). It pops up this message: Configuration system failed to initialize. And inside the "Details" textbox it says this: Name: From: file:///D:/workdir/SolverStudio/SolverStudio.vsto ************** Exception Text ************** System.Configuration.ConfigurationErrorsException: Configuration system failed to initialize ---> System.Configuration.ConfigurationErrorsException: The 'configuration' start tag on line 12 position 2 does not match the end tag of 'system.data'. Line 162, position 7. (C:\Windows\Microsoft.NET\Framework\v4.0.30319\Config\machine.config line 162) ---> System.Xml.XmlException: The 'configuration' start tag on line 12 position 2 does not match the end tag of 'system.data'. Line 162, position 7. at System.Xml.XmlTextReaderImpl.Throw(Exception e) at System.Xml.XmlTextReaderImpl.Throw(String res, String[] args) at System.Xml.XmlTextReaderImpl.ThrowTagMismatch(NodeData startTag) at System.Xml.XmlTextReaderImpl.ParseEndElement() at System.Xml.XmlTextReaderImpl.ParseElementContent() at System.Xml.XmlTextReaderImpl.Read() at System.Xml.XmlTextReader.Read() at System.Xml.XmlTextReaderImpl.Skip() at System.Xml.XmlTextReader.Skip() at System.Configuration.XmlUtil.StrictSkipToNextElement(ExceptionAction action) at System.Configuration.BaseConfigurationRecord.ScanSectionsRecursive(XmlUtil xmlUtil, String parentConfigKey, Boolean inLocation, String locationSubPath, OverrideModeSetting overrideMode, Boolean skipInChildApps) at System.Configuration.BaseConfigurationRecord.ScanSections(XmlUtil xmlUtil) at System.Configuration.BaseConfigurationRecord.InitConfigFromFile() --- End of inner exception stack trace --- at System.Configuration.ConfigurationSchemaErrors.ThrowIfErrors(Boolean ignoreLocal) at System.Configuration.BaseConfigurationRecord.ThrowIfParseErrors(ConfigurationSchemaErrors schemaErrors) at System.Configuration.BaseConfigurationRecord.ThrowIfInitErrors() at System.Configuration.ClientConfigurationSystem.EnsureInit(String configKey) --- End of inner exception stack trace --- at Microsoft.VisualStudio.Tools.Applications.Deployment.ClickOnceAddInDeploymentManager.GetManifests(TimeSpan timeout) at Microsoft.VisualStudio.Tools.Applications.Deployment.ClickOnceAddInDeploymentManager.InstallAddIn() -------------------------- ¿What could be wrong? • Thank you very much. The problem is solved. I edited my machine.config file and deleted the lines that caused the conflict. Perhaps I will later notice I screwed up another program, but at least right now everything is working fine and the installation run smoothly. 180. i figured that out after spending couple of hours on it. some documentation could help new users! Thanks Andrew!! Appreciate your quick response • Pleased you got it working (presumably by dropping the ODBC stuff?). Yes, documentation is on my to-do list.... 181. Solved the problem, thanks! 182. Hi I am a GMPL fan. I am facing problem reading setting up data for GMPL model using its supported format: table markets IN "iODBC" 'DSN=glpk_excel;UID=G:\Open Solver\text.xlsx' 'SELECT * FROM [Sheet1$]' : J <- [ MARKET ], b ~ DEMAND; don't have a clue whats going wrong. the solver log shows it tries to read data from SheetData.dat in temp folder, and gives error. Detail log: GLPSOL: GLPK LP/MIP Solver, v4.47 Parameter(s) specified in the command line: --model C:\Users\ma\AppData\Local\Temp\SolverStudio\model.txt --data SheetData.dat Reading model section from C:\Users\ma\AppData\Local\Temp\SolverStudio\model.txt... C:\Users\ma\AppData\Local\Temp\SolverStudio\model.txt:51: warning: data section ignored 51 lines were read Reading data section from SheetData.dat... 5 lines were read Reading markets... Failed to connect The driver reported the following diagnostics whilst running SQLDriverConnect IM002:1:0:[Microsoft][ODBC Driver Manager] Data source name not found and no default driver specified C:\Users\ma\AppData\Local\Temp\SolverStudio\model.txt:30: error on opening table markets MathProg model processing error Thanks for help!
• I'm pleased to see that you are using GMPL - you are probably one of our first such users. I am not a GMPL expert, and don't know why you are getting an error. The SheetData.dat section seems to be working fine - that is how I pass data into GMPL (which I do for every set or param whose name matches a data item in your spreadsheet). Why don't you try your ODBC command outside SolverStudio - I suspect it will have the same problem. Are you allowed to have spaces in your ODBC filename? Have you got data in the model file? That seems to be causing the "data section ignored". More generally, why are you using ODBC? SolverStudio will give you direct access to data in the spreadsheet if you define a data item without requiring any of the ODBC stuff. Have you tried this approach? I look forward to hearing how you get on. Andrew.
183. Was able to instal SolverStudio and run a test model but on exiting excel and restarting it found the model was not saved. Any suggestions as to what I may be doing wrong? Many thanks.
• I don't think it will be you doing anything wrong. Can you please try (1) changing one of the example models in the samples spreadsheet provided in the download, saving it, closing and re-opening it, and then (2) creating a new spreadsheet, adding a model and then closing/opening the sheet. I'd like to know if either of these work. (I'm assuming the latter is what you did before that did not work.) Also, what version of Excel and what Operating System are you using? And are you saving the sheet as a .xlsx file (not .xls)? Thanks, Andrew
• Many thanks for your prompt response. The tests were run on a Win XP platform using Excel 2007. The file was saved as '.xlsx'. I made another attempt. I modified the transportation gmpl example and saved it as a new file. In the same excel file I added another sheet and pasted a small generation capacity expansion model that I had used elsewhere and was able to run it. On closing and opening this file the models were still there and both work (though the results write to excel was slow). On my previous attempt, with no changes to the original model, reopening the excel file showed a blank model editor window. So, baffling! What I noticed is that in the model editor window the menu File/View Working Files points to the \Local Settings\Temp\SolverStudio directory of the user folder. Should this be so? If the Temp folder is cleared (for example by a cleanup utility) will the model not be available? Is there some way to change where SolverStudio saves the working files?
• The model should be saved inside the .xlsx file; the temp folder is just used during model runs for temporary files. Would you mind emailing me ([email protected]) the file that did not save the model (if you still have it)? That might give me some clues! I will keep it confidential. By the way, each sheet (not workbook) can have its own model. Were you perhaps looking at a different sheet? At least it seems to be working now. Thanks, Andrew
• I'm having trouble storing different models in different sheets...I see that the models are stored internally in Excel, but it is glitchy panning through the different sheets and SolverStudio model windows. For example, in the Solver Studio Examples file, sometimes when panning between the different sheets, the appropriate model does not appear in the model window. There appears to be no pattern to it; I can click on a few different sheet tabs and the appropriate model appears for each one, and then I click back to one of those sheets and sometimes the wrong model appears. In bumpy's case, this was likely the issue, as he probably had sheet(s) in his workbook with no models saved in them, and thus switching back and forth between the sheets would sometimes reveal a blank model window, leading to the appearance the model was deleted when in fact it was just hidden by this glitch. Any insight?
184. hi, can we use SolverStudio for nonlinear models?
• Yes, as long as the modelling language and solver handle non-linear problems. You need to check these individually - I'm not sure which ones do. Please let us know how you get on. Andrew
185. Hallo, I have briefly tested SolverStudio and it seems to be a great tool. Your way of integration PuLP models with excel data is really smart. I had some basic experience with Python and PuLP before trying SolverStudio. I like PuLP, but reading Excel data was the most painful part for me. I created function reading named ranges using xlRD package. SolverStudio is much more comfortable to use for this purpose, except the situations, where my Python codes with PuLP models are used in web environment. I will do some more testing and I will mention SolverStudio to my students as a good example of easy to use open source modeling tool.
• Thanks for your positive feedback; most appreciated. I would welcome feedback from your testing and any comments your students have. Cheers, Andrew
186. Andrew, can you add possibility to use an external text editor (I prefer emeditor).
• It is something I have thought about. It will take a bit of coding to make work, but should not be too hard. I will add it to my to-do list (which is, unfortunately, much longer than the time I have available!) I'd welcome any other comments or suggestions you might have. Cheers, Andrew
187. I found how to re-enable the add-in. Thanks a lot for you work! Waiting for the new version.
188. Sorry, Andrew! “SolverStudio Tools” are in the active add-ins list and also in the "Disabled Application Add-ins" list. What can I do to enable the add-in?
189. "SolverStudio Tools" are in the active add-ins list. Buttons are not visible. :(
190. I tried to solver large LP problem using gurobi engine (776 vars, 117 conds <=, 297 conds =). Process of reading data from excel was so long that i terminated it. After termination addin buttons from Data disappearred. Reinstallation of SolverStudio does not help.
• Oleg: Thanks for your feedback; very much appreciated. You will be pleased that I have improved SolverStudio to do batch read/writes of data, which has dramatically increased the data transfer speed. A problem which I gave up on before now takes less than 10s to set up the data before the run and load the spreadsheet after the run. I will be uploading this new version shortly. Cheers, Andrew. PS: Has SolverStudio been disabled by Excel, which may be preventing the buttons from showing? You can check under the Excel menu under Options, Add-ins, under the "Disabled Application Add-ins" section of the add-ins list. Please let me know if this fixes it.
191. thank you! I have problems with localized version of MS office (russian). 1) when excel book (or list) name is in russian - model is not saved 2) output window does not display utf8 correctly
• Thanks for the feedback. I will look at both these issues when I have a chance. I'd appreciate any other comments - did your models run ok, for example? Andrew
192. Hi Andrew, thanks a lot for the updated in the package. I tried to re-run the models I created with the previous release using PulP/IronPython and I get the following error (it happens also with the models in SolveStudio Examples.xls): Error executing PuLP model code.: Traceback (most recent call last): File "", line 5, in File "C:\Users\Massimiliano Manfren\Documents\SolverStudio\SolverStudio\PuLP\src\pulp\__init__.py", line 33, in File "C:\Users\Massimiliano Manfren\Documents\SolverStudio\SolverStudio\PuLP\src\pulp\pulp.py", line 97, in File "C:\Users\Massimiliano Manfren\Documents\SolverStudio\SolverStudio\IronPython\Lib\string.py", line 83, in ImportError: No module named re The code was working normally with the previous release Further, If I run the same model with CPython I get the following error: SyntaxError: Non-ASCII character '\xff' in file C:\Users\Massimiliano on line 1, but no encoding declared; see http://www.python.org/peps/pep-0263.html for details Can you suggest me some workaround? Thanks a lot
• Massimiliano: I have found the problem (a missing DLL reference), and will upload a new version tomorrow. Thanks so much for the feedback - it is such an important part of the development process. Cheers, Andrew
193. Hi, I'm experiencing some problems with the installation of the upgrade of SolverStudio (the initial release was fully functional on my pc), when I download the .zip package and unblock it I'm not able to extract the whole package, it contains only ampl folder and I don't find the setup.exe. Thanks a lot for the help and the work you are doing with SolverStudio!