LOL, some of these ideas seem like you are using a sledge hammer to pound in a thumbtack! How about this, a Newton Raphson routine you can copy and paste into an Excel macro sheet. Set up to find the root of the given equation.
Option Explicit
Sub FindRoot()
' Subroutine to find root of equation using Newton Raphson iteration
Dim Pi As Double, A As Double, B As Double, Y As Double, Ans As Double, Check As Double
Dim FirstRow As Long, FinalRow As Long, NPts As Long, I As Long
Pi = 3.14159265358979
' Figure out how many points there are to compute
FinalRow = Range("A65536").End(xlUp).Row
FirstRow = 10
NPts = FinalRow - FirstRow + 1
' constants
For I = 1 To NPts
' Constants A and B are in first and second column, respectively
A = Cells(FirstRow + I - 1, 1).Value
B = Cells(FirstRow + I - 1, 2).Value
' Target Y value for function A/cos(x)+B*sin(x) is in column 3.
Y = Cells(FirstRow + I - 1, 3).Value
' Zero out 'x'==Ans and check value, Check
Ans = 0#
Check = 0#
Ans = NRFunction(A, B, Y)
Check = Y - gfunc(Ans, A, B, Y, A, A)
' Print out answer "x" in 4th column, Check value of function is in 5th column.
' ///////////////////////////////// OUTPUT ///////////////////////////////////////
Cells(FirstRow + I - 1, 4).Value = Ans
Cells(FirstRow + I - 1, 5).Value = Check
Next I
' ///////////////////////////////// OUTPUT ///////////////////////////////////////
End Sub
Function NRFunction(A As Variant, B As Variant, C As Variant) As Double
' send in Y as the value of function
Dim g As Double, dg As Double, C1 As Double, C2 As Double
Dim tol As Double, err As Double, xnew As Double, Pi As Double
Dim iter As Long
Pi = 3.14159265358979
tol = 0.000001
' Some constants that may or may not be needed.
C1 = 0# 'if needed
C2 = 0# 'if needed
' Starting value. Depending on function, may have to be very close to the root you are seeking.
xnew = Pi / 4#
err = 10000000000#
iter = 1
Do While err > tol
g = gfunc(xnew, A, B, C, C1, C2)
dg = dgfunc(xnew, A, B, C, C1, C2)
xnew = xnew - g / dg
err = Abs(g / dg) / Abs(xnew + g / dg) 'scales the error
iter = iter + 1
Loop
NRFunction = xnew
End Function
Function gfunc(x As Variant, A As Variant, B As Variant, C As Variant, C1 As Variant, _
C2 As Variant) As Double
gfunc = C - (A / Cos(x) + B * Sin(x))
End Function
Function dgfunc(x As Variant, A As Variant, B As Variant, C As Variant, C1 As Variant, _
C2 As Variant) As Double
dgfunc = -(A * Sin(x) / (Cos(x) * Cos(x)) + B * Cos(x))
End Function
