I specify that the solver works on Excel, with therefore the same calculation formulas

# MSF microsoft Solver Foundation

Hi there,

Sorry for my English.

Before I start laying out my problem, I'd like to clarify the situation:

I know that MSF (Microsoft Solver Foundation) is no longer maintained by Microsoft, but for now it's the only thing I managed to get working, except for one problem that I'll detail below.

If you have an alternative to offer me, free or not overpriced, I am a taker. During my previous requests on various forums, either I received no help, or I was told that MSF should no longer be used. So thank you for being more constructive about this.

The project:

I have to create software to automate the measurement of a spectrometer and then perform many calculations. The calculation part was until now with in Excel, with several Tab are 6 contain a solver.

It's a bit complicated for me to explain, but I'll try this approach:

- What works:

5 of these solvers are of an identical principle:

3 or 5 unknowns to find to optimize a result, but the calculation chain is linear:

Schematically: The unknowns ----> The calculations ----> The calculations ----> The calculations ----> etc... -----> the result to be minimized.

- The one that doesn't work:

The 6th Solver has much less calculation, I have 2 unknowns and a result to minimize too.

The concern is that the unknowns are used in the calculations but also in the final calculation of the result. As if it were a loop.

Chosen method:

Given that the user sometimes changes the Excel file with, you have understood, many calculations, I made the choice to reproduce all of the Excel cells in my code.

Each Excel cell containing formulas are symbolized by methods in my code, each tab corresponds to a class etc.

Methods used by the solver that contain one of the unknowns return Term. These methods are overloaded to return doubles once the solver result is known and therefore compute all data using the unknowns.

CODE

Here is a simplified example:

The cells (Methods) returning a double are called Cell_... (example: Cell_M18), the cells used for the solver are called Solv_M18().

the variables to find:

Decision Solver_L18;

Decision Solver_Q18;

There are several variable (here fixed) only doubles.

```
public partial class Form1 : Form
{
Decision Solver_L18;
Decision Solver_Q18;
bool C15 = true;
double D12 = 221.1;
double D13 = 221.5;
double E12 = 242.2;
double E13 = 241.1;
double Rth = 1.2;
double DP = 0.99;
double DU = 15;
double P1 = 0.1;
double U1 = 2.7;
double P2 = 0.14;
double U2 = 1.24;
double C1 = 5570.2;
double C2 = 28539.5;
double TM2 = 0.01;
double TM1 = 0.03;
double T1 = 0.99;
double T2 = 0.98;
double R = 1;
public Form1()
{
InitializeComponent();
StartSolver();
}
public void StartSolver()
{
try
{
var solverM6 = SolverContext.GetContext();
solverM6.ClearModel();
var modelM6 = solverM6.CreateModel();
//Instanciation des variables du Solver en format Real(double) Non Negative
Solver_L18 = new Decision(Domain.RealNonnegative, "L18");
Solver_Q18 = new Decision(Domain.RealNonnegative, "Q18");
modelM6.AddDecision(Solver_L18);
modelM6.AddDecision(Solver_Q18);
double K18 = Cell_K18();
double I18 = Cell_I18();
modelM6.AddConstraint("Contraint1", Solv_J18() <= K18);
modelM6.AddConstraint("Contraint2", Solv_J18() >= I18);
modelM6.AddConstraint("Contraint3", Solver_L18 <= 4);
modelM6.AddConstraint("Contraint4", Solver_L18 >= 0);
modelM6.AddConstraint("Contraint5", Solver_Q18 >= 0);
modelM6.AddConstraint("Contraint6", Solver_Q18 >= 1);
//Adding Solver Methods
modelM6.AddGoal("SolverM6", GoalKind.Minimize, Solv_R18());
// Solve our problem
var solution = solverM6.Solve();
// Get our decisions
double L18 = Solver_L18.ToDouble();
double Q18 = Solver_Q18.ToDouble();
string M1_QuatiteSolution = solution.Quality.ToString();
}
catch (Exception ex)
{
}
}
private Term Solv_R18()
{
Term res = Model.Sqrt(Model.Power(Solv_J18() - Rth, 2));
return res;
}
private Term Solv_J18()
{
Term res = Solv_O18() / Solv_P18();
return res;
}
private Term Solv_P18()
{
Term res = 0;
if (C15)
{
if (C1 < 2 * D12)
{
res = Model.If(Solv_M18() > TM1, (D12 * 2) / Solv_M18(), (D12 * 2) / TM1);
}
else
{
res = Model.If(Solv_M18() > TM1, C1 / Solv_M18(), C1 / TM1);
}
}
return res;
}
private Term Solv_M18()
{
Term res = 0;
if(Cell_E18() == 0)
{
res = T1;
}
else
{
res = T1 * Model.Exp(- Solv_N13() * Solv_N12() * Solver_L18);
}
return res;
}
private Term Solv_N13()
{
Term res = Solver_Q18 * P1 + (1 - Solver_Q18) * U1;
return res;
}
private Term Solv_O18()
{
Term res = 0;
if(C15)
{
if (C2 < D13 *2)
{
res = Model.If(Solv_N18() > TM2, (D13 * 2) / Solv_N18(), (D13 * 2) / TM2);
}
else
{
res = Model.If(Solv_N18() > TM2, C2 / Solv_N18(), C2 / TM2);
}
}
return res;
}
private Term Solv_N18()
{
Term res = 0;
if (Cell_E18() == 0)
{
res = T2;
}
else
{
res = T2 * Model.Exp(-Solv_N14() * Solv_N12() * Solver_L18);
}
return res;
}
private Term Solv_N12()
{
Term res = 1 / (Solver_Q18 * 1 / DP + (1 * Solver_Q18) * 1 / DU);
return res;
}
private double Cell_E18()
{
double res = 0;
if(C2 != 0)
{
if (C1 != 0)
{
if(C2 > E13 && C1 > E12 && C2 / C1 > R)
{
res = C2 / C1;
}
}
}
return res;
}
private double Cell_K18()
{
double res = Rth * (1 + Cell_H18());
return res;
}
private double Cell_H18()
{
double F18Po = Math.Pow(Cell_F18(), 2);
double Cnet208Pow = Math.Pow(C2, 2);
double Cnet148pow = Math.Pow(C1, 2);
Double res = Math.Sqrt(Math.Pow(Cell_F18(), 2) / Math.Pow(C2, 2) + Math.Pow(Cell_G18(),2) / Math.Pow(C1, 2)) ;
return res;
}
private double Cell_G18()
{
double res = Math.Sqrt(C1);
return res;
}
private double Cell_F18()
{
double res = Math.Sqrt(C2);
return res;
}
private Term Solv_N14()
{
Term res = Solver_Q18 * P2 + (1 - Solver_Q18) * U2;
return res;
}
private double Cell_I18()
{
double res = Rth * (1 - Cell_H18());
return res;
}
}
}
```

At first I really thought it was the 'Model.If' that didn't work, because I dont have this in the other solvers, but I removed them so either putting the value if true then the value if false example:

```
private Term Solv_P18()
{
Term res = 0;
//res = Model.If(Solv_M18() > TM1, C1 / Solv_M18(), C1 / TM1);
res = C1 / Solv_M18();
//res = C1 / TM1;
return res;
}
```

For this exemple doesn't work because Solv_M18() contains the variables to find.

But this :

```
private Term Solv_P18()
{
Term res = 0;
//res = Model.If(Solv_M18() > TM1, C1 / Solv_M18(), C1 / TM1);
//res = C1 / Solv_M18();
res = C1 / TM1;
return res;
}
```

This works because "res" actually returns a double.

When I say it works or not, it's because it blocks without giving me control, on:

```
var solution = solverM6.Solve();
```

Please help me, I really need a hand. I'm going to move on to another more urgent project, but I really need to finish this problem.

Please.....