Using LinEst function within VBA macro

[RyanVM]

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

 

[Tom Ogilvy]

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

 

[RyanVM]

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

 

[Tom Ogilvy]

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

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:

MaxRow), Range("B" & MinRow & ":B" & MaxRow))

 

[Tom Ogilvy]

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

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:

MaxRow), Range("B" & MinRow & ":B" & MaxRow))

 

[RyanVM]

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

 

[qle303]

using linest in vb without worksheet

I've scanned to the end of the web trying to figure out how run the linest command in VB without needing to access the worksheet itself. I've finally figure it out by trial and error so I hope this will help all of you.

Either in a function or sub, you can run the command. Assuming you know your x's and y's, or you can define them. Here's how to program it.

The important thing to remember is that you have to transpose your array, both the x's and y's. Let say that your x's and y's are

x = [1, 2, 3] and y = [4, 5, 6] you have to TRANSPOSE them to

x = [1,

2,

3]

y = [4,

5,

6]

You can do that by defining a 2 dimensional array with the first value equal the number of variables you have and the second to 0.

Here's an example in excel visual basic:

Function linestinVB(var1, var2, var3)

Dim xs(2, 0) As Variant 'define the 1D array with 3 variables in 3 rows
Dim ys(2, 0) As Variant 'define the 1D array with 3 variables in 3 rows

xs(0, 0) = 1 'or use an equation to as a function of var1, 2, and 3
xs(1, 0) = 2 'or use an equation to as a function of var1, 2, and 3
xs(2, 0) = 3 'or use an equation to as a function of var1, 2, and 3


ys(0, 0) = 4 'or use an equation to as a function of var1, 2, and 3
ys(1, 0) = 5 'or use an equation to as a function of var1, 2, and 3
ys(2, 0) = 6 'or use an equation to as a function of var1, 2, and 3

linestinVB = WorksheetFunction.LinEst(ys, xs)

End Function

All of the help I found online in the past are pretty old. So I'll try to post this solution to those sites as well.

Hope this help all of you. Sincerely Quang Le from Denver CO.