Link to home
Start Free TrialLog in
Avatar of jnash67
jnash67

asked on

Excel VBA - Use solver purely programmatically

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.
Avatar of patrickab
patrickab
Flag of United Kingdom of Great Britain and Northern Ireland image

jnash67,

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
Avatar of jnash67
jnash67

ASKER

These are all examples of using the solver traditionally, with values in cells being changed to arrive at an optimal value.  

I want to build a function that takes in some data, uses the quantitative capabilities of the solver to arrive at some result, and then return the result.  If cells are changed, it can't be done in a function.

I would like to be able to use the solver purely programmatically, through the API that one can presumably figure out through a time consuming process of examing the solver code that I am trying to avoid.
>If cells are changed, it can't be done in a function.

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
Avatar of byundt
jnash67,
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
jnash67,

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
jnash67,
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
Hi 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
Avatar of jnash67

ASKER

The reason is that I want to achieve a fairly complex calculation that requires iteration, in a function that returns to a spreadsheet cell the result of the calculation.  I want the function to take inputs from the sheet, but the function itself can't make changes to the sheet, other than to the cell in which it is located.
SOLUTION
Avatar of patrickab
patrickab
Flag of United Kingdom of Great Britain and Northern Ireland image

Link to home
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
Start Free Trial
jnash67,
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
Avatar of jnash67

ASKER

Do you have some VBA code you can share that implements the iterative solution that I can use as a starting point?
Here is a relatively simple example of the approach where you make a guess to seed the solution, then calculate the next guess from the results of the current iteration. For this particular problem, 10 iterations were sufficient to converge with the desired accuracy.

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

Open in new window

ASKER CERTIFIED SOLUTION
Link to home
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
Start Free Trial
Avatar of jnash67

ASKER

Thanks for the help.  You convinced me that I couldn't do what I wanted to do using solver and I ended up rolling my own iteration solution in the spirit of those suggested by byundt.
jnash67 - Thanks for the points - Patrick