In this post we will show you how to use third-party solvers with the Microsoft Solver Foundation Add In for Excel. We assume that you have installed the Express version of Solver Foundation 3.1 from solverfoundation.com.

There are two steps in the process:

1. Registering the solvers you want to be able to use in Excel,
2. Optionally directing that a particular solver should be used to solve a model.

In this post we will show you how to register and provide directives for the Gurobi solver, since it is included in the Solver Foundation installation process. The steps are similar for other solvers.

Registration is done by providing a configuration file. This process is painful (and we hope to make it easier in the future), but you only need to do it once.

Configuration files are a standard mechanism for specifying information about how a .Net application should run. This configuration file has a section that describes the version of Solver Foundation that is installed (the version number for MSF 3.1 is 3.0.2.10889), and a section where plug-in solvers are listed. Here’s what you need to do:

1. Close Excel.
2. Open a command shell with administrator privileges: click the Windows start button, type “cmd” in the search box, right click on the “cmd” icon and select “Run as administrator”.
3. Go to the Solver Foundation installation directory. On many machines, this means typing “cd C:\Program Files\Microsoft Solver Foundation\3.0.2.10889\MsfForExcel”
4. Create a new text file called MicrosoftSolverFoundationForExcel.dll.config, for example by typing “notepad MicrosoftSolverFoundationForExcel.dll.config”
5. Paste in the following:
   

   <?xml version="1.0" encoding="utf-8" ?> 
   <configuration> 
     <configSections>
       <section name="MsfConfig" 
                type="Microsoft.SolverFoundation.Services.MsfConfigSection, Microsoft.Solver.Foundation, Version=3.0.2.10889, Culture=neutral, PublicKeyToken=31bf3856ad364e35" 
                allowLocation="true" allowDefinition="Everywhere" allowExeDefinition="MachineToApplication" 
                restartOnExternalChanges="true" requirePermission="true" /> 
     </configSections> 
     <MsfConfig>
       <MsfPluginSolvers> 
         <MsfPluginSolver name="gurobi" capability="LP" assembly="GurobiPlugIn.dll" 
                          solverclass="SolverFoundation.Plugin.Gurobi.GurobiSolver" 
                          directiveclass="SolverFoundation.Plugin.Gurobi.GurobiDirective" 
                          parameterclass="SolverFoundation.Plugin.Gurobi.GurobiParams" /> 
       </MsfPluginSolvers> 
     </MsfConfig> 
   </configuration>

6. Save the file and quit.

We have now registered the Gurobi solver to handle linear (LP) models. Once a solver has been registered, it is available for use in the Excel add-in for any spreadsheet. This is because the add-in reads the configuration file every time Excel boots. If you have an error in the configuration file, the add-in will report an error message. (If this doesn’t work, see the note at the end.)

Registered solvers are applied by default when you try to solve a model of the type specified in the config file. So, if you open up the “Supply Chain Planning” sample and click Solve, you can confirm that Gurobi was used by looking at the log:

Sometimes it is useful to change solver-specific settings for better accuracy or performance. This is accomplished by adding a directive to the model, found in the Directives tab. In Solver Foundation 3.1, registered solvers show up in the directives list alongside Solver Foundation’s built-in solvers:

Selecting the GurobiDirective item adds a directive to the model. In the lower portion of the modeling pane you can change any of the solver-specific settings you like. Each solver offers its own settings. For example, GurobiDirective has a Presolve property that controls which types of preprocessing rules are applied to a model before it is solved. Here we are changing the default setting to “None”:

(In Solver Foundation 3.1 when you save a model, third party directive options are not retained. This limitation exists because not all third party directives support saving their information to files. In future releases we hope to include this capability.)

It is possible to add more than one directive to a model. In this case, Solver Foundation will launch both solvers, effectively “racing” them against each other!

Important note for Visual Studio 2010 SP1 Users:

If you tried the preceding steps and no plug-in solvers appear to have been registered, and you did not encounter any error messages, you may have encountered an unfortunate breaking change due to Visual Studio Tools for Office (VSTO) 4.0 SP1. Here are the steps you need to take to get back on the right track:

1. Run Registry Editor (Start, type in “regedit”, hit Enter)
2. Navigate to HKEY_CURRENT_USER\Software\Microsoft\Office\
   Excel\Addins\MicrosoftSolverFoundationForExcel\Manifest.
   
   You should see something like the following in the right pane:

   

3. Double-click on Manifest and add “file:///” to the start of the text.
4. Click OK, and quit Registry Editor.
5. Restart Excel.