K

#### Kan

scale_factor doesnt declared as a compile error. I dont see any problem with

the code.

Help me on this.

Thanks.

Function dydx(expression, variable, Optional scale_factor) As Double

'Custom function to return the first derivative of a formula in a cell.

'expression is F(x), variable is x.

'scale-factor is used to handle case where x = 0.

'Workbook can be set to either R1 C1- or Al-style.

Dim OldX As Double, NewX As Double

Dim OldY As Double, NewY As Double

Dim delta As Double

Dim NRepl As Integer, J As Integer

Dim Formulastring As String, XRef As String, dummy As String

Dim T As String, temp As String

delta = 0.00000001

'Get formula and value of cell formula (y).

Formulastring = expression.Formula

OldY = expression.Value

'Get reference and value of argument (x).

OldX = variable.Value

XRef = variable.Address

'Handle the case where x = 0.

'Use optional scale-factor to provide magnitude of x.

'If not provided, returns #DIVO!

If OldX <> 0 Then

NewX = OldX * (1 + delta)

Else

If IsMissing(sca1e_factor) Or scale_factor = 0 Then

dydx = CVErr(xlErrDiv0): Exit Function

NewX = scale_factor * delta

End If

'Convert all references to absolute

'so that only text that is a reference will be replaced.

T = Application.ConvertFormula(Formulastring, xlAl, xlA1, xlAbsolute)

'Do substitution of all instances of x reference with value.

'Substitute reference, e.g., $A$2,

'with a number value, e.g., 0.2, followed by a space

'so that $A$25 becomes 0.2 5, which results in an error.

'Must replace from last to first.

NRepl = (Len(T) - Len(Application.Substitute(T, XRef, ""))) / Len(XRef)

For J = NRepl To 1 Step -1

temp = Application.Substitute(T, XRef, NewX & " ", J)

If IsError(Evaluate(temp)) Then GoTo ptl

T = temp

ptl: Next J

NewY = Evaluate(T)

dydx = (NewY - OldY) / (NewX - OldX)

End Function