need help to code this secant method in vba

[shm_yli]

Function Secant(R0 As Double, R1 As Double) As Double

' returns the root of a function of the form F(R) = 0
' using the secant method.

' R1 is a first guess at value of R that solves the equation
' R0 is a "previous" value not equal to R1.
' this function assumes there is an external function named F(r) that
' represents the function whose root is to be solved

Dim R As Double 'the current guess for root being sought
Dim Rold As Double 'previous guess for root being sought
Dim DeltaR As Double
Dim Iter As Integer 'iteration counter
Const Tol = 0.00000001 'convergence tolerance

Rold = R0
R = R1

'permit a maximum of 100 iterations
For Iter = 1 To 100
DeltaR = (R - Rold) / (1 - f(Rold) / f(R))
R = R - DeltaR
If Abs(DeltaR) < Tol Then GoTo solution
Next Iter

MsgBox "no root found", vbExclamation, "secant result"

solution:
Secant = R
End Function
Function f(R As Double)
f = a + b(Ln(R)) + c(Ln(R)) ^ 3
a = 1.29241 * 10 ^ -3
b = 2.341077 * 10 ^ -4
c = 8.775468 * 10 ^ -8
End Function
Sub secantmethod()

Rold = 18000
R = 20000
tolerence = 0.00000001

e = 0.00000001

bound = Abs((R - Rold))

Do While bound < tolerence
fxc = (3 * R) - (2 * R ^ 2) + (e ^ R) - 10
fxp = (3 * Rold) - (2 * Rold ^ 2) + (e ^ Rold) - 10
nxt = R - ((fxc * (Rold - R)) / (fxp - fxc))
Rold = R
R = nxt
bound = Abs((R - Rold))
Cells(i + 3) = r2
Cells(i + 4) = ea

Loop
MsgBox (nxt)
End Sub

i can't compute this at all, i'm a beginner at using this vba. i just use the code i see on internet to do that

 

[shknbk2]

I think this is a basic example of the secant method. The function f(x) is x^2 + 2X + 1 in this code; however, it's set up as a generic 4-term polynomial. Any other equation will need to be substituted. When the values of -3 and -2 are used for x0 and x1, the same result and number of iterations as an online calculator is reached.

You can try your formula, but note that Ln(x) by itself is not a vba code. Instead, use Application.WorksheetFunction.Ln(x).

Function f(x As Double) As Double
    Dim a As Double
    Dim b As Double
    Dim c As Double
    Dim d As Double
   
    a = 0
    b = 1
    c = 2
    d = 1
    f = a * x ^ 3 + b * x ^ 2 + c * x + d
End Function

Sub secantmethod()
    Dim x0 As Double
    Dim x1 As Double
    Dim x2 As Double
    Dim tolerance As Double
    Dim watchdog As Integer
    Dim watchdogMaxIterations As Integer
    Dim bound As Double
    Dim diverging As Boolean
    Dim firstDivergence As Boolean
   
    x0 = -3
    x1 = -2
    tolerance = 0.0001
    watchdogMaxIterations = 100
   
    bound = Abs(x1 - x0)
   
    Do While bound > tolerance
        x2 = x1 - f(x1) * ((x1 - x0) / (f(x1) - f(x0)))
        If Abs(x1 - x2) > bound Then
            If firstDivergence Then
                'diverging answer if bound is increasing over sequential iterations (I think)
                diverging = True
                Exit Do
            Else
                firstDivergence = True
            End If
        Else
            firstDivergence = False
        End If
        bound = Abs(x1 - x2)
        x0 = x1
        x1 = x2
        watchdog = watchdog + 1
        If watchdog > watchdogMaxIterations Then
            Exit Do
        End If
    Loop
    If diverging Then
        MsgBox "The equation is diverging with the initial values."
    ElseIf watchdog > watchdogMaxIterations Then
        MsgBox "The watchdog value was reached. The calculated root so far is " & x2 & "."
    Else
        MsgBox "The calculated root is " & x2 & " after " & watchdog & " iterations."
    End If
End Sub

 

[shm_yli]

I think this is a basic example of the secant method. The function f(x) is x^2 + 2X + 1 in this code; however, it's set up as a generic 4-term polynomial. Any other equation will need to be substituted. When the values of -3 and -2 are used for x0 and x1, the same result and number of iterations as an online calculator is reached.

You can try your formula, but note that Ln(x) by itself is not a vba code. Instead, use Application.WorksheetFunction.Ln(x).

Function f(x As Double) As Double
    Dim a As Double
    Dim b As Double
    Dim c As Double
    Dim d As Double
  
    a = 0
    b = 1
    c = 2
    d = 1
    f = a * x ^ 3 + b * x ^ 2 + c * x + d
End Function

Sub secantmethod()
    Dim x0 As Double
    Dim x1 As Double
    Dim x2 As Double
    Dim tolerance As Double
    Dim watchdog As Integer
    Dim watchdogMaxIterations As Integer
    Dim bound As Double
    Dim diverging As Boolean
    Dim firstDivergence As Boolean
  
    x0 = -3
    x1 = -2
    tolerance = 0.0001
    watchdogMaxIterations = 100
  
    bound = Abs(x1 - x0)
  
    Do While bound > tolerance
        x2 = x1 - f(x1) * ((x1 - x0) / (f(x1) - f(x0)))
        If Abs(x1 - x2) > bound Then
            If firstDivergence Then
                'diverging answer if bound is increasing over sequential iterations (I think)
                diverging = True
                Exit Do
            Else
                firstDivergence = True
            End If
        Else
            firstDivergence = False
        End If
        bound = Abs(x1 - x2)
        x0 = x1
        x1 = x2
        watchdog = watchdog + 1
        If watchdog > watchdogMaxIterations Then
            Exit Do
        End If
    Loop
    If diverging Then
        MsgBox "The equation is diverging with the initial values."
    ElseIf watchdog > watchdogMaxIterations Then
        MsgBox "The watchdog value was reached. The calculated root so far is " & x2 & "."
    Else
        MsgBox "The calculated root is " & x2 & " after " & watchdog & " iterations."
    End If
End Sub

Thank you for the reply, but can i ask some question and can you please help me in this?

f = a + b(Ln(R)) + c(Ln(R)) ^ 3 - 1 / T this is my function I forgot to insert the 1/t, the code you made does is have this function or different ? im not really good at programing so im confused at this , and can you help me on how i can insert the iteration on cells at excel ?

 

[shknbk2]

How are the variables, a, b, c, and T determined? I am assuming that R is the number the function is evaluating. I see that a, b, and c might also be what you put above (for example, a = 1.29241 * 10 ^ -3), but what about T?

 

[shm_yli]

How are the variables, a, b, c, and T determined? I am assuming that R is the number the function is evaluating. I see that a, b, and c might also be what you put above (for example, a = 1.29241 * 10 ^ -3), but what about T?

T = 18.99C,

 

[shknbk2]

Changing the formula f(R) to your equation would go something like this, but it would be best to see the equation written in a different format to make sure.

Function f(R As Double) As Double
    Dim a As Double
    Dim b As Double
    Dim c As Double
    Dim d As Double
   
    a = 1.29241 * 10 ^ -3
    b = 2.341077 * 10 ^ -4
    c = 8.775468 * 10 ^ -8
    f = a + b * Application.WorksheetFunction.Ln(R) + c * Application.WorksheetFunction.Ln(R) ^ 3 - 1 / 18.99
End Function

Assuming this is written correctly, the secant method using 18000 and 20000 for initial values blows up instantly and doesn't find any root. Is this equation solvable with the secant method?

 

[shm_yli]

Changing the formula f(R) to your equation would go something like this, but it would be best to see the equation written in a different format to make sure.

Function f(R As Double) As Double
    Dim a As Double
    Dim b As Double
    Dim c As Double
    Dim d As Double
 
    a = 1.29241 * 10 ^ -3
    b = 2.341077 * 10 ^ -4
    c = 8.775468 * 10 ^ -8
    f = a + b * Application.WorksheetFunction.Ln(R) + c * Application.WorksheetFunction.Ln(R) ^ 3 - 1 / 18.99
End Function

Assuming this is written correctly, the secant method using 18000 and 20000 for initial values blows up instantly and doesn't find any root. Is this equation solvable with the secant method?i

Changing the formula f(R) to your equation would go something like this, but it would be best to see the equation written in a different format to make sure.

Function f(R As Double) As Double
    Dim a As Double
    Dim b As Double
    Dim c As Double
    Dim d As Double
  
    a = 1.29241 * 10 ^ -3
    b = 2.341077 * 10 ^ -4
    c = 8.775468 * 10 ^ -8
    f = a + b * Application.WorksheetFunction.Ln(R) + c * Application.WorksheetFunction.Ln(R) ^ 3 - 1 / 18.99
End Function

Assuming this is written correctly, the secant method using 18000 and 20000 for initial values blows up instantly and doesn't find any root. Is this equation solvable with the secant method?

if change the value of T= 18.99C to 292.14 K?

 

[shknbk2]

You will just have to run it and see, but make sure the equation is correct.

 

[shm_yli]

You will just have to run it and see, but make sure the equation is correct.

Function f(R As Double) As Double
Dim a As Double
Dim b As Double
Dim c As Double
Dim d As Double

a = 1.29241 * 10 ^ -3
b = 2.341077 * 10 ^ -4
c = 8.775468 * 10 ^ -8
f = a + b * Application.WorksheetFunction.Ln(R) + c * Application.WorksheetFunction.Ln(R) ^ 3 - 1 / 292.14
End Function
Sub secantmethod()
Dim x0 As Double
Dim x1 As Double
Dim x2 As Double
Dim tolerance As Double
Dim watchdog As Integer
Dim watchdogMaxIterations As Integer
Dim bound As Double
Dim diverging As Boolean
Dim firstDivergence As Boolean

x0 = 18000
x1 = 20000
tolerance = 0.0001
watchdogMaxIterations = 100

bound = Abs(x1 - x0)

Do While bound > tolerance
x2 = x1 - f(x1) * ((x1 - x0) / (f(x1) - f(x0)))
If Abs(x1 - x2) > bound Then
If firstDivergence Then
'diverging answer if bound is increasing over sequential iterations (I think)
diverging = True
Exit Do
Else
firstDivergence = True
End If
Else
firstDivergence = False
End If
bound = Abs(x1 - x2)
x0 = x1
x1 = x2
watchdog = watchdog + 1
If watchdog > watchdogMaxIterations Then
Exit Do
End If
Loop
If diverging Then
MsgBox "The equation is diverging with the initial values."
ElseIf watchdog > watchdogMaxIterations Then
MsgBox "The watchdog value was reached. The calculated root so far is " & x2 & "."
Else
MsgBox "The calculated root is " & x2 & " after " & watchdog & " iterations."
End If
End Sub


like that ? how can i make the iteration appear on the excel ? like on the cells so that i can see the iteration ?

 

[shknbk2]

One way is to insert the Debug.Print line in the code you have above in the example below. This will print the calculated values for each iteration into the Immediate Window, which is usually at the bottom of the Visual Basic Editor under the window where all the code is. Putting the values into the spreadsheet will require a little more work, but especially knowing what is already in the spreadsheet, whether to delete everything, where to put the values, etc.

x2 = x1 - f(x1) * ((x1 - x0) / (f(x1) - f(x0)))
Debug.Print x2
If Abs(x1 - x2) > bound Then