partial derivative of a function

[Ali Baba]

am trying to write a function to find the partial derivative of a function.
The function can then be called from a worksheet cell almost exactly as one
would write them normally.

For example,
we have this equation
f(Z1, Z2, Z3) = Z1^2+Z2*Z3^3-Z3^0.5

and Z1 = 2, Z2 = 4, Z3 = 6

if we want to find the partial derivative with respect to Z1 then:

= DerivativeX("Z1^2+Z2*Z3^3-Z3^0.5", 2,"Z1")
= 4

with respect to Z2
= DerivativeX("Z1^2+Z2*Z3^3-Z3^0.5", 2,"Z2")
= 216

and so on .............

So all I am doing is to substitute the values of Z2 and Z3 in the function
and then do a normal differentiation with respect to Z1. Similarity, I
substitute Z1 and Z3 to get the derivative with respect to Z2, .....


I don't know what is wrong with the code. So can anybody help me to correct
it and get it working.

Thanks for any help


____________________________________________________

Option Explicit
Dim Z1 As Double
Dim Z2 As Double
Dim Z3 As Double


Function DerivativeX(func As String, a As Double, V As String) As Double

Const h = 0.0001
Dim n1 As Double, n2 As Double

Z1 = Range("C13")
Z2 = Range("C14")
Z3 = Range("C15")


Select Case UCase(Left(V, 1))
Case Is = "Z1"
func = Replace(func, "Z2", Z2)
func = Replace(func, "Z3", Z3)
n1 = (eval(func, a + (h / 2), Z1) - eval(func, a - (h / 2), Z1)) / h
n2 = (eval(func, a + h, Z1) - eval(func, a - h, Z1)) / (2 * h)

Case Is = "Z2"
func = Replace(func, "Z1", Z1)
func = Replace(func, "Z3", Z3)
n1 = (eval(func, a + (h / 2), Z2) - eval(func, a - (h / 2), Z2)) / h
n2 = (eval(func, a + h, Z2) - eval(func, a - h, Z2)) / (2 * h)

Case Is = "Z3"
func = Replace(func, "Z1", Z1)
func = Replace(func, "Z2", Z2)
n1 = (eval(func, a + (h / 2), Z3) - eval(func, a - (h / 2), Z3)) / h
n2 = (eval(func, a + h, Z3) - eval(func, a - h, Z3)) / (2 * h)

End Select

DerivativeX = (4 * n1 - n2) / 3

End Function

Function eval(funct As String, Z As Double, V As String) As Double
eval = Evaluate(funct)

Select Case UCase(Left(V, 1))
Case Is = "Z1"
Z1 = Z
Case Is = "Z2"
Z2 = Z
Case Is = "Z3"
Z3 = Z
End Select

End Function