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