I have seen some answers that partially address what I'm trying to get at (20299452 and 20357379). However, this requires one to painstakingly figure out the API. Can someone send me an example of a function that uses the xla solver code to figure out a result based on a sheet range passed into it.

Perhaps one of the attached files will help? Also have a look at:

http://peltiertech.com/Excel/SolverVBA.html#Solver2#ixzz0L9C0FED6&C

Patrick

solver-VBA-03.xls

Solver-automation-near-matches.xls

I think you mean it can't be done purely programmatically.

However a quick question for you. Why is it so important not to use cells containing data? It could be done on a temporary worksheet if needs be.

Patrick

I agree with Patrick. I don't believe it is possible to use Solver purely in VBA. You must rely on the worksheet to contain the cell containing the result, the cells being varied and the constraints. You must therefore "roll your own" algorithm to solve your real problem.

Recognizing these limitations, I have written VBA user-defined functions in my day job to solve engineering problems of a trial and error variety. The Excel worksheet serves as the source of raw data and a repository for the answer (returned as an array by my UDF). The engineering logic of the iterative solution was entirely contained in my VBA code.

If you take the above approach, you may find your first attempt is either very slow to calculate or else never finds the answer. If so, then you need to improve the technique used to produce the next guess. I have had good success using the Newton-Raphson method in which you bound the solution on the high and low side, use the midpoint for your next guess, and cut the range in half based on whether the result is high or low--the new range should still contain a high and low bound. Obviously, this approach works best if the result rises or falls continuously over the range.

Another approach I have used with success is to guess a value, then perform the calculations and calculate the value of the guessed parameter as the last step. For example, assume the speed of a car. Calculate the wind drag at that speed and the needed HP from the engine. Using that engine HP, calculate the rpm of the engine and hence the speed of the car. Repeat until the wind drag and engine HP agree.

I have also worked on problems where there are several nested requirements that all have to be satisfied. Sometimes you can solve for all of them at once. In other cases, I have needed to solve for requirement A, then solve for the requirement B, etc. In solving for requirement B, you will probably need to adjust the goal for requirement A and repeat those calculations. Since requirement A gets calculated many times, it is important to have very efficient logic to determine its value.

Brad

The best way forward would be for you to upload your file with a full description of the problem on which you are wanting to use Solver.

Patrick

I haven't tried it, but I suspect that the Software Development Kit offered by Frontline Systems (developer of Solver) will allow you to do what you want. You can download a fully functional version for a 15 day free trial. After that, the cost is $1495 plus $500 annual support.

http://www.solver.com/sdkplatform.htm

Brad

That's a brave suggestion as it's lots of dosh just to hide the workings of Solver. It would be less expensive to buy a new pc and do the calculations on that! However perhaps jnash67 has his reasons for wanting to do it all in code rather than the simplest, quickest method. Having said that, so far, no reason has been given for wanting to do all in code.

Patrick

This one is on us!

(Get your first solution completely free - no credit card required)

UNLOCK SOLUTION
What you describe wanting to do is exactly what I had to do for my heat transfer programs. They are inherently iterative problems, so I ended up using one or the other of the approaches described in http:/#a25823200

Brad

In other situations, I have used a Do Until...Loop block of code with the Until condition being an accuracy test on the convergence. You should also have an iteration counter (and test) to prevent infinite loops. Obviously, the same type of approach could apply to a For...Next block of code.

```
Function SteamOrificeSize(FlowLbHr As Double, PupstreamPsig As Double, PdownstreamPsig As Double, PipeIDinch As Double, _
Optional TsteamF, Optional Cd As Double = 0.61) As Double
'Returns the orifice diameter (inch) for steam passing through a sharp-edged orifice. Assumes _
sea-level elevation. Basis: Perry's Handbook 7th edition, p. 10-14 through 10-16 _
with operation beyond critical pressure ratio permitted
'FlowLbHr is the mass flow of steam (lb/hr) through the orifice
'PupstreamPsig is the pressure (psig) of the steam upstream of the orifice
'PdownstreamPsig is the pressure (psig) of the steam downstream of the orifice
'PipeIDinch is the ID (inch) of the pipe upstream of the orifice
'TsteamF is the temperature of the steam entering the orifice
Dim Beta As Double, BetaOld As Double, CriticalPressureRatio As Double, DensityUpstream As Double, _
DownstreamPsia As Double, FlowLbSec As Double, Gc As Double, k As Double, _
KK As Double, Msg, OrificeArea As Double, R As Double, UpstreamPsia As Double, Y As Double
Dim i As Long
Dim temp
k = 1.323 'Ratio of Cp/Cv for steam
Gc = 32.17 'Gravitational constant
CriticalPressureRatio = (2 / (k + 1)) ^ (k / (k - 1)) 'Valid for Beta < 0.2
UpstreamPsia = PupstreamPsig + 14.7
DownstreamPsia = PdownstreamPsig + 14.7
R = DownstreamPsia / UpstreamPsia 'Pressure ratio
If R <= CriticalPressureRatio Then
'Cd = 0.75 'See Perry's Handbook discussion on p. 10-16. _
Apotex SO364983 gave more accurate results when Cd=0.65 for pressure drop over the critical
Msg = "Delta P = " & Format(UpstreamPsia - DownstreamPsia, "##0.0") & _
" exceeds critical pressure drop of " & _
Format(UpstreamPsia * (1 - CriticalPressureRatio), "##0.0") & " psig"
'temp = MsgBox(Msg, vbInformation + vbOKOnly)
Else
End If
If IsMissing(TsteamF) Then 'Find the upstream steam temperature
TsteamF = SatTemp(UpstreamPsia) 'Assume the upstream steam is saturated
Else 'Use the specified value
End If
DensityUpstream = 1 / VgSteam(TsteamF, UpstreamPsia) 'Density of the upstream steam
FlowLbSec = FlowLbHr / 3600 'Steam flow rate in lb/sec
BetaOld = 0.2 'Ratio of assumed orifice diameter to pipe ID
For i = 1 To 10
Y = 1 - (1 - R) * (0.41 + 0.35 * BetaOld ^ 4) / k 'Expansion factor (valid when r>CriticalPressureRatio)
KK = Cd / Sqr(1 - BetaOld ^ 4) 'Coefficient of discharge corrected for Beta ratio
'Orifice area found by Eq. 10-20 in Perry's Handbook. Reference to Eq. 10-8 on p. 10-16 is incorrect!
OrificeArea = FlowLbSec / (KK * Y * Sqr(2 * Gc * 144 * (UpstreamPsia - DownstreamPsia) * DensityUpstream)) 'Ft2
SteamOrificeSize = 12 * Sqr(4 * OrificeArea / Application.Pi()) 'Orifice size (inches)
Beta = SteamOrificeSize / PipeIDinch 'Ratio of calculated orifice diameter to pipe ID
If Abs(Beta - BetaOld) < 0.001 Then
Exit For 'Loop has converged
Else 'Loop hasn't converged yet
BetaOld = Beta 'Update BetaOld for next iteration
End If
Next i
End Function
```

This one is on us!

(Get your first solution completely free - no credit card required)

UNLOCK SOLUTION
Experts Exchange always has the answer, or at the least points me in the correct direction! It is like having another employee that is extremely experienced.

Jim Murphy

Programmer at Smart IT Solutions

When asked, what has been your best career decision?

Deciding to stick with EE.

Mohamed Asif

Technical Department Head

Being involved with EE helped me to grow personally and professionally.

Carl Webster

CTP, Sr Infrastructure Consultant

We've partnered with two important charities to provide clean water and computer science education to those who need it most. READ MORE

Connect with Certified Experts to gain insight and support on specific technology challenges including:

- Troubleshooting
- Research
- Professional Opinions

Experts Exchange is the only place where you can interact directly with leading experts in the technology field. Become a member today and access the collective knowledge of thousands of technology experts.

*This site is protected by reCAPTCHA and the Google Privacy Policy and Terms of Service apply.