Using LinEst function within VBA macro
 

I'm trying to perform a LinEst calculation (best fine line) within a VB
macro so I can use the resulting slope value for some othe
calculations.

As best as I can tell, I should be setting it up like this:

Code
-------------------
Value = Application.WorksheetFunction.LinEst(A1:A10,C1:C10
-------------------
However, when I try to compile this function, I get the followin
error:

Code
-------------------
Compile error:

Expected: list separator or
-------------------
I tried enclosing the ranges with quotes, but that didn't work either.
I could create a cell with the LinEst function in it then copy th
value, but that seems like a rather inefficient way for doing it.

Does anyone have an idea of how to make it work

--
Message posted from http://www.ExcelForum.com

Using LinEst function within VBA macro
 

Dim vValue as Variant
vValue =
Application.WorksheetFunction.LinEst(Range("A1:A10 "),Range("C1:C10"))

I didn't check your syntax, but as a minimum, you need to use range object
where you would use ranges in Excel.

I have posted serveral examples of using LinEst in VBA in the past. Search
google groups for examples.

http://groups.google.com/advanced_group_search?hl=en

--
Regards,
Tom Ogilvy

"RyanVM " wrote in message
...
I'm trying to perform a LinEst calculation (best fine line) within a VBA
macro so I can use the resulting slope value for some other
calculations.

As best as I can tell, I should be setting it up like this:

Code:
--------------------
Value = Application.WorksheetFunction.LinEst(A1:A10,C1:C10 )
--------------------
However, when I try to compile this function, I get the following
error:

Code:
--------------------
Compile error:

Expected: list separator or )
--------------------
I tried enclosing the ranges with quotes, but that didn't work either.
I could create a cell with the LinEst function in it then copy the
value, but that seems like a rather inefficient way for doing it.

Does anyone have an idea of how to make it work?


---
Message posted from http://www.ExcelForum.com/

Using LinEst function within VBA macro
 

OK, that gets me on the right track. What you posted works fine if yo
have a predefined range, however, I'm trying to set up a variabl
range.

I've tried the below code, but I get an error message
Code
-------------------
Slope = Application.WorksheetFunction.LinEst(Range("C" & MinRow & ":C" & MaxRow), Range("B" & MinRow & ":B" & MaxRow)
-------------------

Code
-------------------
Run-time error '1004'

Unable to get the LinEst property of the WorksheetFunction clas
-------------------
Any ideas

--
Message posted from http://www.ExcelForum.com

Using LinEst function within VBA macro
 

I tested in the immediate window:

Minrow = 3
maxrow = 11
Slope = Application.WorksheetFunction.LinEst(Range("C" & MinRow & ":C" &
MaxRow), Range("B" & MinRow & ":B" & MaxRow))
? isarray(slope)
True
? slope(1)
10
? slope(2)
-18.3333333333333

Couldn't reproduce a problem.

if you just want the slope, why not use the Slope WorksheetFunction

? Application.WorksheetFunction.Slope(Range("C" & MinRow & ":C" & MaxRow),
Range("B" & MinRow & ":B" & MaxRow))
10



--
Regards,
Tom Ogilvy

"RyanVM " wrote in message
...
OK, that gets me on the right track. What you posted works fine if you
have a predefined range, however, I'm trying to set up a variable
range.

I've tried the below code, but I get an error message.
Code:
--------------------
Slope = Application.WorksheetFunction.LinEst(Range("C" & MinRow & ":C" &
MaxRow), Range("B" & MinRow & ":B" & MaxRow))
--------------------

Code:
--------------------
Run-time error '1004'

Unable to get the LinEst property of the WorksheetFunction class
--------------------
Any ideas?


---
Message posted from http://www.ExcelForum.com/

Using LinEst function within VBA macro
 

Note that this could be a data problem. In the worksheet, Linest would show
#Value or #Ref as an example. When you use WorksheetFunction as a
qualifier, it will raise an error such as you cite.

If you just qualify it with Application, it will raise a soft error you can
text for.

Dim slope as Variant
Dim maxrow as long, minrow as long
maxrow = 11
minrow = 3
Slope = Application..LinEst(Range("C" & MinRow & ":C" & _
MaxRow), Range("B" & MinRow & ":B" & MaxRow))
if iserror(slope) then
Select Case slope
Case CVErr(xlErrDiv0)
MsgBox "#DIV/0! error"
Case CVErr(xlErrNA)
MsgBox "#N/A error"
Case CVErr(xlErrName)
MsgBox "#NAME? error"
Case CVErr(xlErrNull)
MsgBox "#NULL! error"
Case CVErr(xlErrNum)
MsgBox "#NUM! error"
Case CVErr(xlErrRef)
MsgBox "#REF! error"
Case CVErr(xlErrValue)
MsgBox "#VALUE! error"
Case Else
MsgBox "This should never happen!!"
End Select
Else
msgbox "Slope is " & slope(1)
End If

--
Regards,
Tom Ogilvy




"RyanVM " wrote in message
...
OK, that gets me on the right track. What you posted works fine if you
have a predefined range, however, I'm trying to set up a variable
range.

I've tried the below code, but I get an error message.
Code:
--------------------
Slope = Application.WorksheetFunction.LinEst(Range("C" & MinRow & ":C" &
MaxRow), Range("B" & MinRow & ":B" & MaxRow))
--------------------

Code:
--------------------
Run-time error '1004'

Unable to get the LinEst property of the WorksheetFunction class
--------------------
Any ideas?


---
Message posted from http://www.ExcelForum.com/

Using LinEst function within VBA macro
 

Nevermind, I figured it out. As they say, location is everything. I ha
it in a point in the macro where the range I wanted to perform a LINES
on didn't exist yet :rolleyes:

--
Message posted from http://www.ExcelForum.com