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
Please post your help requests and feedback comments here.
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.
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...Young Old Tall Male 3 4 Tall Female 5 7 Short Male 2 7 Short Female 4 9Hope this helps, Andrewfrom 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 Applicationimport SolverStudio.Applicationimport ApplicationSo I think I am barking up the wrong tree. What is it I need to do here to manipulate Excel directly from Python?Application.ActiveSheet.Range("A1").Value = 10
or
r=Application.ActiveSheet.Range("A1:C5")
r.Cells(1,1).Value = 10
Cheers, Andrew