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!

 

[screech]

Are you sure that your version allows it?
PGC

I have xl2000, but I was hoping there was a way around it. Looks like I might have to do the ol' copy and paste trick to make the range a continuous selection before using the function -- something I was hoping to avoid because I think it's sloppy programming to do so.

 

[pgc01]

Hi screech

You don't have to copy and paste it manually.

You can do it in vba. Just declare an array for the Y values and arrays for your X variables.
Initialise the arrays with the respective multi-area range values and use those arrays to feed linest.

This is an example that can get you started:

- first I create a multi-area range just for testing.
The Y range is A10:A15,A20:A25,A30:A35, ... (10 areas)
One X variable in B10:B15,B20:B25,B30:B35, ... (10 areas)

- I create 2 arrays, one for the Y values and the other for the X values.

- I intialise the 2 arrays with the respective values

- I feed the 2 arrays into LineEst

Please try this code and then adapt it for your case.

Hope this helps
PGC

Sub MyLinest()
Dim rY As Range, rX As Range, rCell As Range, rArea As Range
Dim aY, aX, l As Long

'Initialise a multi-area range for the test A10:A15,A20:A25,A30:A35, etc
Set rY = Range("A10:A15")
Set rX = Range("B10:B15")
For i = 2 To 10
    Set rY = Application.Union(rY, Range("A" & i * 10).Resize(6))
    Set rX = Application.Union(rX, Range("B" & i * 10).Resize(6))
Next i

'Create the arrays to hold the range values
ReDim aX(1 To rX.Count)
ReDim aY(1 To rY.Count)

' Initialise the array with the X values
l = 1
For Each rArea In rX.Areas
    For Each rCell In rArea
        aX(l) = rCell
        l = l + 1
    Next rCell
Next rArea

' Initialise the array with the Y values
l = 1
For Each rArea In rY.Areas
    For Each rCell In rArea
        aY(l) = rCell
        l = l + 1
    Next rCell
Next rArea

Range("C1:D1").Value = Application.WorksheetFunction.LinEst(aY, aX)
End Sub