Jesper Funch
New Member
- Joined
- Sep 9, 2014
- Messages
- 5
Hello.
I'm having problems with running Excel solver.
I am currently working with an rather large VBA "project", a project that just got handed over to me, so I have written any of the coding myself, and the original creator is no longer working at my company, so I have no chance of getting help from him. So that is why i'm reaching out to you.
To sketch up what the Excel document is capable of:
It generates slip curves from pressure, temperature and S/C ratio. All these variable are user inputs and there is a userform in which the users can put in the data.
When the user is done putting all the data a Excel solver runs to calculate the values needed for the generation of the slips curves. The slip curves are logarithme curves, reason for this is that negativ results (amount of different chemicals) cannot be negativ.
So if I want to generate a slip curve over the following values: Pressure - 15 kg/cm² ; Temperature - 700°C to 900°C ; S/C ratio 1,8.
No problems. The solver gives me the correct values and the curve is generated correctly.
However! If I want to generate 3 different slip curves (The number of pressure inputs defines the number of slip curves generated) and error in solver shows up.
For an example: 3 different pressures - 5, 10 and 15 kg/cm². The solver now gives negativ values for the slip curves 15 kg/cm², where as before, with only one pressure input (15 kg/cm²) the output was positive and correct.
The coding for the solver is as follows:
Sub rorreformer()
SolverReset
'Finder en løsning'
solveradd cellref:="$G$" & CStr(CO2row), relation:=3, formulatext:="R141C8"
solveradd cellref:="$G$" & CStr(CO2row), relation:=1, formulatext:="100"
solveradd cellref:="$G$" & CStr(COrow), relation:=3, formulatext:="0"
solveradd cellref:="$G$" & CStr(COrow), relation:=1, formulatext:="100"
solveradd cellref:="$G$" & CStr(H2row), relation:=3, formulatext:="0"
solveradd cellref:="$G$" & CStr(H2row), relation:=1, formulatext:="100"
solveradd cellref:="$G$" & CStr(Inertsrow), relation:=3, formulatext:="0"
solveradd cellref:="$G$" & CStr(Inertsrow), relation:=1, formulatext:="100"
solveradd cellref:="$G$" & CStr(CH4row), relation:=3, formulatext:="0"
solveradd cellref:="$G$" & CStr(CH4row), relation:=1, formulatext:="100"
solveradd cellref:="$G$" & CStr(H2Orow), relation:=3, formulatext:="0"
solveradd cellref:="$G$" & CStr(H2Orow), relation:=1, formulatext:="100"
solveradd cellref:="$D$" & CStr(X2row), relation:=3, formulatext:="R141C8"
SolverOk SetCell:="$D$" & CStr(Controlrow), MaxMinVal:=3, ValueOf:="0", _
bychange:="$D$" & CStr(X1row) & ":$D$" & CStr(X2row)
solversolve (True)
End Sub
There are three multiple solvers running. The values the solver refereres to are "inchangeable" and should not be the problem.
Please let me know if you need any more VBA code or Excel sheets.
Best Regards
Jesper Poulsen
I'm having problems with running Excel solver.
I am currently working with an rather large VBA "project", a project that just got handed over to me, so I have written any of the coding myself, and the original creator is no longer working at my company, so I have no chance of getting help from him. So that is why i'm reaching out to you.
To sketch up what the Excel document is capable of:
It generates slip curves from pressure, temperature and S/C ratio. All these variable are user inputs and there is a userform in which the users can put in the data.
When the user is done putting all the data a Excel solver runs to calculate the values needed for the generation of the slips curves. The slip curves are logarithme curves, reason for this is that negativ results (amount of different chemicals) cannot be negativ.
So if I want to generate a slip curve over the following values: Pressure - 15 kg/cm² ; Temperature - 700°C to 900°C ; S/C ratio 1,8.
No problems. The solver gives me the correct values and the curve is generated correctly.
However! If I want to generate 3 different slip curves (The number of pressure inputs defines the number of slip curves generated) and error in solver shows up.
For an example: 3 different pressures - 5, 10 and 15 kg/cm². The solver now gives negativ values for the slip curves 15 kg/cm², where as before, with only one pressure input (15 kg/cm²) the output was positive and correct.
The coding for the solver is as follows:
Sub rorreformer()
SolverReset
'Finder en løsning'
SolverOptions MaxTime:=100, Iterations:=1000, Precision:=0.0000000001, AssumeLinear:= _
False, StepThru:=False, Estimates:=1, Derivatives:=1, SearchOption:=2, _
IntTolerance:=0.0000001, Scaling:=False, Convergence:=0.0000000001, AssumeNonNeg:=False
solveradd cellref:="$G$" & CStr(CO2row), relation:=3, formulatext:="R141C8"
solveradd cellref:="$G$" & CStr(CO2row), relation:=1, formulatext:="100"
solveradd cellref:="$G$" & CStr(COrow), relation:=3, formulatext:="0"
solveradd cellref:="$G$" & CStr(COrow), relation:=1, formulatext:="100"
solveradd cellref:="$G$" & CStr(H2row), relation:=3, formulatext:="0"
solveradd cellref:="$G$" & CStr(H2row), relation:=1, formulatext:="100"
solveradd cellref:="$G$" & CStr(Inertsrow), relation:=3, formulatext:="0"
solveradd cellref:="$G$" & CStr(Inertsrow), relation:=1, formulatext:="100"
solveradd cellref:="$G$" & CStr(CH4row), relation:=3, formulatext:="0"
solveradd cellref:="$G$" & CStr(CH4row), relation:=1, formulatext:="100"
solveradd cellref:="$G$" & CStr(H2Orow), relation:=3, formulatext:="0"
solveradd cellref:="$G$" & CStr(H2Orow), relation:=1, formulatext:="100"
solveradd cellref:="$D$" & CStr(X2row), relation:=3, formulatext:="R141C8"
SolverOk SetCell:="$D$" & CStr(Controlrow), MaxMinVal:=3, ValueOf:="0", _
bychange:="$D$" & CStr(X1row) & ":$D$" & CStr(X2row)
solversolve (True)
End Sub
There are three multiple solvers running. The values the solver refereres to are "inchangeable" and should not be the problem.
Please let me know if you need any more VBA code or Excel sheets.
Best Regards
Jesper Poulsen