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

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

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

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

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