Reply to thread

Message: [QUOTE="Kan, post: 6439490"] 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 [/QUOTE]

Verification