Is it possible to implement Newton's method *once* in VBA?

Question

I need to use Newton's method on closures.

Function f (x as Double, y as Double) as Double
    f = x^3-y
End Function

I get the value of y from a cell and then I would like to find out when f is zero. In the toy example above, if the cell contains y=8, then I would expect Newton's method to find a solution close to x=2.

My solution was to make a newton_solve_f function:

Function newton_solve_f (y as Double as Double) as Double
    Dim x as Double
    x = 0 'initial guess for x
    'do Newton's method to find x
    ...
    newton_solve_f = x
End Function

so in effect, I copy paste my code for Newton's method (taken from here) into newton_solve_f.

The problem is that I have several such fs (some with more than two arguments), and it would be really neat if I didn't have to make a separate almost identical newton_solve_f for every one of them.

How would you solve this in VBA?

In Python, for example, it's possible to solve this problem as follows:

def f(y):
    def g(x):
        return x^3-y
    return g

def newton_solve(f):
    #do newton's method on f(x)

newton_solve(f(3))

Here f(3) is a function, a closure of one variable. (The closure example on wikipedia is almost identical to this one.)

ps. I know Newton's method also needs the (partial) derivative of f, I'm actually doing something that's more like the secant method, but that's irrelevant for what I'm asking about

Answer 1

This seems to be asking 2 related questions:

1. how to pass a function as an argument in vba.
2. how to create a closure out of an existing function.

Unfortunately neither of these are really supported, however,

• for 1 you can generally work around this by passing a string function name and using 'Application.Run' to invoke the function.
• 2 is trickier if you have lots of functions with different numbers of parameters, but for a set number of parameters you could add extra parameters to the newton_solve function or maybe use global variables.

e.g.

Public Function f(x as Double, y as Double) as Double
    f = x^3-y
End Function


Function newton_solve_f (function_name as String, y as Double) as Double
    Dim x as Double
    x = 0 'initial guess for x
    'do Newton's method to find x

    ...
       ' invoke function_name
       x = Application.Run(function_name, x, y)
    ...
    newton_solve_f = x
End Function

Assuming f is in a module called 'Module1' you can call this with:

x = newton_solve('Module1.f', 3)

Note that the function you want to call must be public.

Answer 2

Closures are not part of VBA. But you can use static variables within a method scope. They cannot be used outside the method. If you want a variable to visible outside, then you have to use global variable. Preferable declare it public in a module.

We cannot define function inside function in VB. Tried to convert the code given in the link you have mentioned. I hope it helps you. Not well versed with php, but you can see the approach below and make changes accordingly.

Sub Test()
    Dim x As Double
    Dim y As Double
    Dim z As Double

    x = Cells(1, 1).Value
    y = Cells(1, 2).Value
    z = NewtRap("Fun1", "dFun1", x, y)
    Cells(1, 3).Value = z
End Sub

Private Function NewtRap(fname As String, dfname As String, x_guess As Double, y_value As Double) As Double
  Dim cur_x As Double
  Dim Maxiter As Double
  Dim Eps As Double

  Maxiter = 500
  Eps = 0.00001
  cur_x = x_guess
  For i = 1 To Maxiter
    If (fname = "Fun1") Then
      fx = Fun1(cur_x)
    ElseIf (fname = "dFun1") Then
      fx = dFun1(cur_x)
    ElseIf (fname = "f") Then
      fx = f(cur_x, y_value)
    End If
    If (dfname = "Fun1") Then
      fx = Fun1(cur_x)
    ElseIf (dfname = "dFun1") Then
      fx = dFun1(cur_x)
    ElseIf (dfname = "f") Then
      fx = f(cur_x, y_value)
    End If
    If (Abs(dx) < Eps) Then Exit For
    cur_x = cur_x - (fx / dx)
  Next i
  NewtRap = cur_x
End Function

Function f(x As Double, y As Double) As Double
    f = x ^ 3 - y
End Function

Function Fun1(x As Double) As Double
  Fun1 = x ^ 2 - 7 * x + 10
End Function

Function dFun1(x As Double) As Double
  dFun1 = 2 * x - 7
End Function

Answer 3

So to first summarise: You want to create a function that will find (using Newton-Raphson method) the roots of a function. You already have this written and working for certain functions but would like help expanding your code so it will work with a variety of functions with varying numbers of parameters?

I think you first need to think about what input functions you want it to cover. If you are only dealing with polynomials (as your example suggests), this should be fairly straightforward.

You could have general functions of:

Function fnGeneralCase (x, y, z, w, a1, a2, a3, b1, b2, b3, c1, c2, c3 as Double) as Double
    fnGeneralCase = a1*x^3 + a2*x^2 + a3*x + b1*y^3 + b2*y^2 + b3*y + c1*z^3 + c2*z^2 + c3*z + w
End Function

Function fnDerivGeneralCase (x, y, z, w, a1, a2, a3, b1, b2, b3, c1, c2, c3 as Double) as Double
    fnDerivGeneralCase = a1*3*x^2 + a2*2*x + a3 + b1*3*y^2 + b2*2*y + b3 + c1*3*z^2 + c2*2*z + c3
End Function

And just set the inputs to zero when you don't need them (which will be for the majority of the time).

So for your example calling:

answer = fnGeneralCase(guess, 0, 0, -8, 1, 0, 0, 0, 0, 0, 0, 0, 0)

basically gives:

function = x^3-8

If you want to include more than polynomials, this will get more complicated but you could still use the above approach...