Using Application.WorksheetFunction.Ln(...) in VBA

  • Thread starter Thread starter doco
  • Start date Start date
D

doco

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
 
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
 
Figured it out - thanks a bunch!

doco

doco said:
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
Click to expand...
 
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
 
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.
 
Back
Top