Solver function from within access/vba

[java.koder]

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]

[tommaso.gastaldi]

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

[java.koder]

to**************@uniroma1.it wrote:

It might not have solutions or have multiple (2) solution, depending on
A,B,C.
After some deep analysis (hehe) I found that the equation was more like
e^(A*h0)+A*ho = 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.
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


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)

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

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

[tommaso.gastaldi]

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

[CDMAPoster]

ja********@gmail.com wrote:

to**************@uniroma1.it wrote:

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

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

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
CD********@FortuneJames.com