Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
Using Application.WorksheetFunction.Ln(...) in VBA
Hello;
I have searched the help files, several programming in excel books and this forum to try to discover how to convert a Range object into it's constituent double values in order to use Ln(...) in module.. eg dLog = Application.WorksheetFunction.Ln(oXValues) where "oXValues" is a range of values from a worksheet. Obviously a range object is not a Double. I am trying to produce the Slope and Intercept coefficients for a graph. IE for Logrythmic regression one would use Slope(knownYValues, LN(knownXValues)) & Intercept(knownYValues, LN(knowXValues)). This works great when used in a spreadsheet. However, Type Mismatch is returned when using "dLog = Application.WorksheetFunction.Ln(oXValues)" in a module. How would the Slope and Intercept values be discovered in VBA? TIA doco |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
Using Application.WorksheetFunction.Ln(...) in VBA
Tom;
Thanks for the info. But I still get Type Mismatch as a result. Here is the code I am using: Private Sub optLog_Click() Dim oXRange As Range Dim oYRange As Range Set oXRange = Range(rfdRegX) ' from RefEdit control Set oYRange = Range(rfdRegY) ' from RefEdit control txtSlope = _ Format(Application.Evaluate("SLOPE(" & oYRange.Address(external:=True) & _ "LN(" & oXRange.Address(external:=True) & "))"), "##.000000") txtIntercept = _ Format(Application.Evaluate("INTERCEPT(" & oYRange.Address(external:=True) & _ oXRange.Address(external:=True) & ")"), "##.000000") ' print out formula to user StatusBar1.SimpleText = "Y = " & txtSlope & " * Ln(X) + " & txtIntercept Set oXRange = Nothing Set oYRange = Nothing End Sub TIA doco "Tom Ogilvy" wrote in message ... You trying to get LN to operate as an array formula - you will need to use the evaluate function to do that Sub Tester1() Dim oXValues As Range Dim oYValues As Range Set oXValues = Range("B13:B18") Set oYValues = Range("A13:A18") dSlope = Evaluate("Slope(" & _ oYValues.Address(external:=True) & ",LN(" _ & oXValues.Address(external:=True) & "))") dIntercept = Evaluate("Intercept(" & _ oYValues.Address(external:=True) & ",LN(" _ & oXValues.Address(external:=True) & "))") Debug.Print dSlope & ", " & dIntercept End Sub works for me. Regards, Tom Ogilvy doco wrote in message ... Hello; I have searched the help files, several programming in excel books and this forum to try to discover how to convert a Range object into it's constituent double values in order to use Ln(...) in module.. eg dLog = Application.WorksheetFunction.Ln(oXValues) where "oXValues" is a range of values from a worksheet. Obviously a range object is not a Double. I am trying to produce the Slope and Intercept coefficients for a graph. IE for Logrythmic regression one would use Slope(knownYValues, LN(knownXValues)) & Intercept(knownYValues, LN(knowXValues)). This works great when used in a spreadsheet. However, Type Mismatch is returned when using "dLog = Application.WorksheetFunction.Ln(oXValues)" in a module. How would the Slope and Intercept values be discovered in VBA? TIA doco |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
Using Application.WorksheetFunction.Ln(...) in VBA
Figured it out - thanks a bunch!
doco "doco" wrote in message ... Tom; Thanks for the info. But I still get Type Mismatch as a result. Here is the code I am using: Private Sub optLog_Click() Dim oXRange As Range Dim oYRange As Range Set oXRange = Range(rfdRegX) ' from RefEdit control Set oYRange = Range(rfdRegY) ' from RefEdit control txtSlope = _ Format(Application.Evaluate("SLOPE(" & oYRange.Address(external:=True) & _ "LN(" & oXRange.Address(external:=True) & "))"), "##.000000") txtIntercept = _ Format(Application.Evaluate("INTERCEPT(" & oYRange.Address(external:=True) & _ oXRange.Address(external:=True) & ")"), "##.000000") ' print out formula to user StatusBar1.SimpleText = "Y = " & txtSlope & " * Ln(X) + " & txtIntercept Set oXRange = Nothing Set oYRange = Nothing End Sub TIA doco "Tom Ogilvy" wrote in message ... You trying to get LN to operate as an array formula - you will need to use the evaluate function to do that Sub Tester1() Dim oXValues As Range Dim oYValues As Range Set oXValues = Range("B13:B18") Set oYValues = Range("A13:A18") dSlope = Evaluate("Slope(" & _ oYValues.Address(external:=True) & ",LN(" _ & oXValues.Address(external:=True) & "))") dIntercept = Evaluate("Intercept(" & _ oYValues.Address(external:=True) & ",LN(" _ & oXValues.Address(external:=True) & "))") Debug.Print dSlope & ", " & dIntercept End Sub works for me. Regards, Tom Ogilvy doco wrote in message ... Hello; I have searched the help files, several programming in excel books and this forum to try to discover how to convert a Range object into it's constituent double values in order to use Ln(...) in module.. eg dLog = Application.WorksheetFunction.Ln(oXValues) where "oXValues" is a range of values from a worksheet. Obviously a range object is not a Double. I am trying to produce the Slope and Intercept coefficients for a graph. IE for Logrythmic regression one would use Slope(knownYValues, LN(knownXValues)) & Intercept(knownYValues, LN(knowXValues)). This works great when used in a spreadsheet. However, Type Mismatch is returned when using "dLog = Application.WorksheetFunction.Ln(oXValues)" in a module. How would the Slope and Intercept values be discovered in VBA? TIA doco |
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
Using Application.WorksheetFunction.Ln(...) in VBA
The problem now is it doesn't give the same slope and intercept formula
result as a chart does. Is there a different process for charts? doco "doco" wrote in message ... Tom; Thanks for the info. But I still get Type Mismatch as a result. Here is the code I am using: Private Sub optLog_Click() Dim oXRange As Range Dim oYRange As Range Set oXRange = Range(rfdRegX) ' from RefEdit control Set oYRange = Range(rfdRegY) ' from RefEdit control txtSlope = _ Format(Application.Evaluate("SLOPE(" & oYRange.Address(external:=True) & _ "LN(" & oXRange.Address(external:=True) & "))"), "##.000000") txtIntercept = _ Format(Application.Evaluate("INTERCEPT(" & oYRange.Address(external:=True) & _ oXRange.Address(external:=True) & ")"), "##.000000") ' print out formula to user StatusBar1.SimpleText = "Y = " & txtSlope & " * Ln(X) + " & txtIntercept Set oXRange = Nothing Set oYRange = Nothing End Sub TIA doco "Tom Ogilvy" wrote in message ... You trying to get LN to operate as an array formula - you will need to use the evaluate function to do that Sub Tester1() Dim oXValues As Range Dim oYValues As Range Set oXValues = Range("B13:B18") Set oYValues = Range("A13:A18") dSlope = Evaluate("Slope(" & _ oYValues.Address(external:=True) & ",LN(" _ & oXValues.Address(external:=True) & "))") dIntercept = Evaluate("Intercept(" & _ oYValues.Address(external:=True) & ",LN(" _ & oXValues.Address(external:=True) & "))") Debug.Print dSlope & ", " & dIntercept End Sub works for me. Regards, Tom Ogilvy doco wrote in message ... Hello; I have searched the help files, several programming in excel books and this forum to try to discover how to convert a Range object into it's constituent double values in order to use Ln(...) in module.. eg dLog = Application.WorksheetFunction.Ln(oXValues) where "oXValues" is a range of values from a worksheet. Obviously a range object is not a Double. I am trying to produce the Slope and Intercept coefficients for a graph. IE for Logrythmic regression one would use Slope(knownYValues, LN(knownXValues)) & Intercept(knownYValues, LN(knowXValues)). This works great when used in a spreadsheet. However, Type Mismatch is returned when using "dLog = Application.WorksheetFunction.Ln(oXValues)" in a module. How would the Slope and Intercept values be discovered in VBA? TIA doco |
#5
Posted to microsoft.public.excel.programming
|
|||
|
|||
Using Application.WorksheetFunction.Ln(...) in VBA
It gives the same result as the functions when used in the worksheet, at
least for me. If those functions don't match the trend line in the chart, then I can't say - I have heard that the algorithm used for the chart trendline is more rigorous than the algorithm for Linest (and I would assume slope and intercept are related to Linest), but my understanding is that differences are only evident for unusual data sets (that would require special coding in the algorithm to eliminate rounding errors and so forth). I would check first and make sure I was comparing apples to apples. -- Regards, Tom Ogilvy "doco" wrote in message ... The problem now is it doesn't give the same slope and intercept formula result as a chart does. Is there a different process for charts? doco "doco" wrote in message ... Tom; Thanks for the info. But I still get Type Mismatch as a result. Here is the code I am using: Private Sub optLog_Click() Dim oXRange As Range Dim oYRange As Range Set oXRange = Range(rfdRegX) ' from RefEdit control Set oYRange = Range(rfdRegY) ' from RefEdit control txtSlope = _ Format(Application.Evaluate("SLOPE(" & oYRange.Address(external:=True) & _ "LN(" & oXRange.Address(external:=True) & "))"), "##.000000") txtIntercept = _ Format(Application.Evaluate("INTERCEPT(" & oYRange.Address(external:=True) & _ oXRange.Address(external:=True) & ")"), "##.000000") ' print out formula to user StatusBar1.SimpleText = "Y = " & txtSlope & " * Ln(X) + " & txtIntercept Set oXRange = Nothing Set oYRange = Nothing End Sub TIA doco "Tom Ogilvy" wrote in message ... You trying to get LN to operate as an array formula - you will need to use the evaluate function to do that Sub Tester1() Dim oXValues As Range Dim oYValues As Range Set oXValues = Range("B13:B18") Set oYValues = Range("A13:A18") dSlope = Evaluate("Slope(" & _ oYValues.Address(external:=True) & ",LN(" _ & oXValues.Address(external:=True) & "))") dIntercept = Evaluate("Intercept(" & _ oYValues.Address(external:=True) & ",LN(" _ & oXValues.Address(external:=True) & "))") Debug.Print dSlope & ", " & dIntercept End Sub works for me. Regards, Tom Ogilvy doco wrote in message ... Hello; I have searched the help files, several programming in excel books and this forum to try to discover how to convert a Range object into it's constituent double values in order to use Ln(...) in module.. eg dLog = Application.WorksheetFunction.Ln(oXValues) where "oXValues" is a range of values from a worksheet. Obviously a range object is not a Double. I am trying to produce the Slope and Intercept coefficients for a graph. IE for Logrythmic regression one would use Slope(knownYValues, LN(knownXValues)) & Intercept(knownYValues, LN(knowXValues)). This works great when used in a spreadsheet. However, Type Mismatch is returned when using "dLog = Application.WorksheetFunction.Ln(oXValues)" in a module. How would the Slope and Intercept values be discovered in VBA? TIA doco |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Reuse Application.WorksheetFunction | Excel Discussion (Misc queries) | |||
Application.WorksheetFunction error | Excel Discussion (Misc queries) | |||
Need help with Application.WorksheetFunction | Excel Discussion (Misc queries) | |||
Accessing WorksheetFunction.XX from regular C# Forms application | Excel Worksheet Functions | |||
Application.WorksheetFunction.Match problem | Excel Worksheet Functions |