Solver function from within access/vba

Hello all,

I am making a simple msAccess aplication for some construction
calculus, I need however to calculate a parameter from an equation and
I am told this can be made with the solver function of Excel; however I
dont know if it is available in Access..

The equation is:

e^(2*k*u*h0/de) - 2*k*u*h0/de = d*k*u*r + 1

I need to obtain h0; I know the other values, so in short:

e^(A*h0) - B * ho = C

where A, B and C are constant values

Anyone has a clue on how can I solve this? thank you all!

--
Santiago Zapata
[http://www.santiagoz.com]

re: Solver function from within access/vba

It might not have solutions or have multiple (2) solution, depending on
A,B,C.

To find 1 solution, if exists, you could see it as :

h0 = Math.Log(C + B * h0) / A

and by iteration solve it.

Sorry not familiar with VBA but can write quickly some VB.NET that you
can easily adapt.


An example of result:
Dim A As Double = 12
Dim B As Double = 8
Dim C As Double = 30

A solution: 0,289632950361349 converged in 4 iterations

-tommaso


'------------------- adapt this and let me know if it works fine (must
adapt it to find ALL solutions)

Private Sub Button1_Click(ByVal sender As System.Object, ByVal e As
System.EventArgs) Handles Button1.Click

Dim A As Double = 12
Dim B As Double = 8
Dim C As Double = 30

Dim h0 As Double = 1

Dim MaxIteration As Integer = 10000
Dim Accuracy As Double = 0.000001

Dim NumIteration As Integer
Dim OldSolution As Double = h0
Dim New_Solution As Double
Do
New_Solution = Math.Log(C + B * OldSolution) / A
If Math.Abs(New_Solution - OldSolution) < Accuracy Then
Exit Do

OldSolution = New_Solution
NumIteration += 1
If NumIteration = MaxIteration Then Exit Do
Loop

If NumIteration = MaxIteration Then
Debug.WriteLine("Not converged to a solution")
MsgBox("Not converged to a solution")
Else
Debug.WriteLine("A solution: " & New_Solution & " converged
in " & NumIteration & " iterations")
MsgBox("A solution: " & New_Solution & " converged in " &
NumIteration & " iterations")
End If

End Sub

re: Solver function from within access/vba

tommaso.gastaldi@uniroma1.it wrote:[color=blue]
> It might not have solutions or have multiple (2) solution, depending on
> A,B,C.[/color]

After some deep analysis (hehe) I found that the equation was more like


e^(A*h0)+A*ho = C
[color=blue]
>
> To find 1 solution, if exists, you could see it as :
>
> h0 = Math.Log(C + B * h0) / A
>
> and by iteration solve it.
>
> Sorry not familiar with VBA but can write quickly some VB.NET that you
> can easily adapt.[/color]

Thanks for your iteration algorithm, I however adapted a newton-rapson
algorithm (found C sources) like this: (It *seems* to work..)

Adapted from
http://mathews.ecs.fullerton.edu/n20...rog_lnk_4.html

'---------------------------------------------------------------------------
' Algorithm translated to Visual Basic by: Santiago Zapata
' Based on Algo2-5.c from NUMERICAL METHODS: C Programs, (c) John H.
Mathews 1995,
' Dr. Norman Fahrer C Version

' Algorithm 2.5 (Newton-Raphson Iteration). To find a root
' f(x) = 0 given one initial approximation p_0 and using the
iteration
'
' f (p_(K - 1))
' p_k = p_(k-1) - ----------- for k = 1, 2, ...
' f '(p_(k-1))
'
'---------------------------------------------------------------------------
'User has to supply a function named : ffunction
' and its first derivative : dffunction
'---------------------------------------------------------------------------

Private Function ffunction(ho As Double, A As Double, B As Double) As
Double
ffunction = Math.Exp(A * ho) - A * ho - B
End Function

Private Function dffunction(ho As Double, A As Double) As Double
dffunction = A * Math.Exp(A * ho) - A
End Function

Private Function newtonRapson(P0 As Double, A As Double, B As Double)
Dim Delta As Double
Dim Epsilon As Double
Dim Small As Double
Dim Max As Integer
Dim cond As Integer
Dim K As Integer ' Counter for loop
Dim p1 As Double ' New iterate
Dim y0 As Double ' Function value
Dim y1 As Double ' Function value
Dim df As Double ' Derivative
Dim dp As Double
Dim RelErr As Double

Delta = 0.000001 ' Tolerance
Epsilon = 0.000001 ' Tolerance
Small = 0.000001 ' Tolerance

Max = 99 ' Maximum number of iterations
cond = 0 ' Condition fo loop termination

y0 = dffunction(P0, A)

For K = 1 To Max
If (cond) Then Exit For
df = dffunction(P0, A) ' Compute the derivative
If (df = 0) Then ' Check division by zero
cond = 1
dp = 0
Else
dp = y0 / df
End If

p1 = P0 - dp ' New iterate
y1 = ffunction(p1, A, B) ' New function value

RelErr = 2 * Math.Abs(dp) / (Math.Abs(p1) + Small) ' Relative
error

If ((RelErr < Delta) And (Math.Abs(y1) < Epsilon)) Then '
Check for
If (cond <> 1) Then cond = 2 '
convergence
End If

P0 = p1
y0 = y1
Next

MsgBox "The current " & Str$(K - 1) & "-th iterate is " & Str$(p1)
MsgBox "Consecutive iterates differ by " & Str$(dp)
MsgBox "The value of f(x) is " & Str$(y1)

If (cond = 0) Then MsgBox "The maximum number of iterations was
exceeded!"

If (cond = 1) Then MsgBox "Division by zero was encountered !"

If (cond = 2) Then MsgBox "The root was found with the desired
tolerance!"
newtonRapson = y1
End Function
[color=blue]
>
>
> An example of result:
> Dim A As Double = 12
> Dim B As Double = 8
> Dim C As Double = 30
>
> A solution: 0,289632950361349 converged in 4 iterations
>
> -tommaso
>
>
> '------------------- adapt this and let me know if it works fine (must
> adapt it to find ALL solutions)
>[/color]
SNIP Iteration Algorithm


Thanks for your help, please let me know if you have any observations
about my adaptation.

--
Slash
[http://www.santiagoz.com]

re: Solver function from within access/vba

Very nice.
It would be interesting to compare the respective performances of the 2
methods, to see which one tends to converge faster.
In case you do, let me know. I am curious about that....

Its seems you still have the problem to find the second solution...

-tom

re: Solver function from within access/vba

java.koder@gmail.com wrote:
[color=blue]
> tommaso.gastaldi@uniroma1.it wrote:[color=green]
> > It might not have solutions or have multiple (2) solution, depending on
> > A,B,C.[/color][/color]
[color=blue]
> Thanks for your help, please let me know if you have any observations
> about my adaptation.[/color]

Your function is like a lot of Access problems. I.e., different
conditions lend themselves to different solution techniques. Let's
look at the function.

f(x) = e ^ (Ax) - Ax - C

When x is very negative, f(x) looks similar to -Ax. When x is very
positive, f(x) looks like e ^ (Ax). The inflection point is when x =
0. The function is fairly flat near the inflection point. So f(x)
would be a somewhat reasonable way, for example, to approximate a
variable delay to an exponential function. The positive point where
f(x) = 0 can be thought of as the amount of time a delayed exponential
needs to break through a threshold. The minimum value of f(x) is 1 -
C, so C must be >= 1 for f(x) = 0 even to have a real solution. The
problem is that unless 1 - C is a reasonable distance below the x-axis,
the flat region has the potential to send p(k) off to places that even
Double will choke on. Once C is reasonably large, f(x) is so smooth
that convergence will happen quickly with almost any method. Note that
for this case the Newton-Raphson method will converge quite rapidly.
So I would check convergence for values of C slightly larger than one
with different values of A to see if Newton-Raphson is adequate for the
range of values you expect A to take.

James A. Fortune
CDMAPoster@FortuneJames.com