I use the 2007 office when I run this I got a error by saying the
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

Hi Kan,

In the following line you have use the numeric 1 instead of the alpha l in
the first instance of scale_factor.

If IsMissing(sca1e_factor) Or scale_factor = 0 Then

You are also missing an End If after the following code. See comment where I
inserted. Ensure that is where you wanted it.

If OldX < 0 Then
NewX = OldX * (1 + delta)
Else
If IsMissing(scale_factor) Or scale_factor = 0 Then
dydx = CVErr(xlErrDiv0): Exit Function
NewX = scale_factor * delta

End If 'appears to be missing in your code

End If



--
Regards,

OssieMac


"Kan" wrote:

I use the 2007 office when I run this I got a error by saying the
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