Reply
 
LinkBack Thread Tools Search this Thread Display Modes
  #1   Report Post  
Posted to microsoft.public.excel.programming
external usenet poster
 
Posts: 6
Default 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   Report Post  
Posted to microsoft.public.excel.programming
external usenet poster
 
Posts: 6
Default 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   Report Post  
Posted to microsoft.public.excel.programming
external usenet poster
 
Posts: 6
Default 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   Report Post  
Posted to microsoft.public.excel.programming
external usenet poster
 
Posts: 6
Default 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   Report Post  
Posted to microsoft.public.excel.programming
external usenet poster
 
Posts: 27,285
Default 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
Search this Thread:

Advanced Search
Display Modes

Posting Rules

Smilies are On
[IMG] code is On
HTML code is Off
Trackbacks are On
Pingbacks are On
Refbacks are On


Similar Threads
Thread Thread Starter Forum Replies Last Post
Reuse Application.WorksheetFunction jlclyde Excel Discussion (Misc queries) 4 March 12th 09 09:32 PM
Application.WorksheetFunction error Ayo Excel Discussion (Misc queries) 4 May 16th 08 05:04 PM
Need help with Application.WorksheetFunction Ayo Excel Discussion (Misc queries) 4 May 14th 08 11:13 PM
Accessing WorksheetFunction.XX from regular C# Forms application Jes Ramsing Excel Worksheet Functions 1 June 16th 05 12:21 PM
Application.WorksheetFunction.Match problem Carl Brehm Excel Worksheet Functions 1 January 9th 05 03:08 PM


All times are GMT +1. The time now is 10:10 PM.

Powered by vBulletin® Copyright ©2000 - 2024, Jelsoft Enterprises Ltd.
Copyright ©2004-2024 ExcelBanter.
The comments are property of their posters.
 

About Us

"It's about Microsoft Excel"