435,098 Members | 1,918 Online + Ask a Question
Need help? Post your question and get tips & solutions from a community of 435,098 IT Pros & Developers. It's quick & easy.

Solver function from within access/vba

 P: n/a 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] May 13 '06 #1
4 Replies

 P: n/a 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 May 13 '06 #2

 P: n/a 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] May 13 '06 #3

 P: n/a 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 May 13 '06 #4

 P: n/a 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 May 17 '06 #5

This discussion thread is closed

Replies have been disabled for this discussion. 