VBA: LinEst Worksheet Function

[screech]

Greetings Excel Wizards,

I've been using a lot of worksheet functions in my VBA code. For example, one of them is c.Value = WorksheetFunction.StDev(DataRange)

But I get an error when I try c.Value = WorksheetFunction.LinEst(DataRange)

It seems pretty straight forward to me as I can get the formula to work in excel with test data, but the counterpart does not work in VBA. the data is not contiguous, but if it works for StDev, I thought it would work for LinEst. What's the deal with how I am doing things? Thanks for any help!

 

[Tom Urtis]

Contrary to the StDev and other worksheet functions which you have not had a problem with in VBA, what gave rise to your question here is that you did not take into account the fact that LinEst is an array function, meaning it cannot be entered as a constant value into a cell the way you would do with non-array worksheet functions using VBA.

You can do 1 of at least 2 things:

Either produce the value in a cell by using the FormulaArray method and then PSV that cell in the next codeline of your macro if desired,

or

Just enter the value as a constant similar how you were intending to do that with the WorksheetFunction method, but in a way that VBA understands given the array nature of the issue, which involves the Evaluate command.

Even though your data is not contiguous you still need to reference the range.

If the Known X is in range C6:C17, and the known Y is in D6:D17, the array formula in a worksheet cell would be
=LINEST(D6:D17,C6:C17,1)
confirmed with Ctrl+Shift+Enter, not just with Enter.


For that example range in VBA, the codeline would be (using your cell range variable "c"):

c.Value = Evaluate("LINEST(D6:D17,C6:C17)")


Just to test that, you can set up some numbers in the aforementioned range as a test, and execute this to see the proper result:

Range("A1").Value = Evaluate("LINEST(D6:D17,C6:C17)")

 

[screech]

"DataRange" is a variable range as defined by my VBA code. I've tried using your example with that variable range name, but I don't think I'm getting it. I don't get an error anymore, but my cell value is "#NAME?"

 

[Tom Urtis]

As often as not, variable ranges are variable because the last row changes. You probably know the 2 columns where the Known X and the Known Y data elements are located, and you probably know the first row of data.

Help take the mystery out of this thread by simply posting what you do know as far as an example range address, an anticipated column where Known X would be, and anticipated column of where Known Y would be, what makes the variable range variable, and what code you have attempted. If this is a monster macro, just post the relevant portions, as it sounds like you are close to what you want, at this point it's probably just a syntax issue with the Cells range property in the afore-posted Evaluate statement.

 

[screech]

Sure, I can try to take the mystery out with the variable range. There are two columns, one is a time stamp and the other is the corresponding data. The variable range names for these two columns is "TimeRange" and "DataRange". Based on many other criteria, the ranges can start at any row and end at any row, with many sections of data excluded in between and can be a total of thousands of rows. I can have VBA select these cells so I know this part is working correctly.

The ultimate goal is to take the selected times and matching data and find the slope of the line to determine how linear it is, compared to a slope of zero as a guide to see if the data collected is "stable" over a period of time. I thought the function "LinEst" would be good for doing this, though I am open to other suggestions.

I'm not an expert, but I think the problem is taking the range of desired cells in "DataRange" and turning it into an array so it can be used in the LinEst function. Just putting in something like c.Value = Evaluate("LINEST(DataRange, TimeRange)") doesn't seem to work (and I think it's for obvious reasons).

I really do appreciate the help with this. I struggle with the nuances of excel sometimes.

 

[Tom Urtis]

I just tested this codeline

Range("A1").Value = Evaluate("LINEST(DataRange, TimeRange)")

and it does indeed work for me, no problem.

Before testing, I named range C6:C17 as TimeRange, and D6:D17 as DataRange. Now I know your ranges are variable, but still, if at the point of macro execution they are each one column wide, and both have the same count of rows, then this will work for you as it does for me, unless there is something else going on in your workbook you are unaware of.

Other than that, I cannot duplicate your error.

 

[screech]

Thank you, Tom. There could indeed be something weird about my code. But you have given me hope!

EDIT: I've checked some things out... this is giving me the #NAME? error as well and could be linked to what I have going on in my code:

u = Range("A10:A15")
v = Range("B10:B15")
c.Value = Evaluate("LINEST(u, v)")

 

[pgc01]

Hi screech

You are using the strings "u" and "v" instead of the value of the variables u and v.

This works:

Sub MyLinest()
Dim rY As Range, rX As Range

Set rY = Range("A10:A15")
Set rX = Range("B10:B15")
Range("C1:D1").Value = Evaluate("LINEST(" & rY.Address & "," & rX.Address & ")")
End Sub

I must tell you, however, that I prefer to use the worksheetfunction. Here is the equivalent:

Sub MyLinest()
Dim rY As Range, rX As Range

Set rY = Range("A10:A15")
Set rX = Range("B10:B15")
Range("C1:D1").Value = Application.WorksheetFunction.LinEst(rY, rX)
End Sub

Hope this helps
PGC

EDIT: Switched X and Y.

 

[screech]

Hmm. I still get an error with all of these suggestions. Could it be possible that my "DataRange" and "TimeRange" variables are too long, as in I have too many individual non-contiguous sections selected and excel cannot list that many when calling the variable? Once upon a time, I had that problem with making a chart where it would be too many characters in the range text to make the chart. (It would truncate the range of cells.) Could this be my problem?

 

[pgc01]

I have too many individual non-contiguous sections selected

I have xl2000 and LINEST does not accept non-contiguous ranges.

Check:

http://support.microsoft.com/kb/214117

I also didn't hear that that changed for xl2003.

Are you sure that your version allows it?
PGC