vba Function code
in [Programming]
|
Prev: Program stops (creating pivot table) when too many records
Next: what is the formula for what I want to do
From: Kan on 4 Mar 2010 22:26 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
From: OssieMac on 5 Mar 2010 03:30
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 > |