LINEST Function in VBA

[Francine Kubaka]

Hi,

I would like to use the LINEST function on a multidimensional array in VBA
to avoid writing the array back to the worksheet and then running the
function on a range.

Question:

How can I make LINEST to work on an array, whether one- or
multidimensional?

Is the use of an array giving any performance gains over using a
range for LINEST?


Thanks a lot.

K

 

[Tushar Mehta]

Something along the lines of

Sub testIt()
Dim x As Variant, y As Variant, rslt As Variant, i As Long
ReDim x(1 To 10, 1 To 2)
ReDim y(1 To 10, 1 To 1)
For i = 1 To 10
x(i, 1) = CDbl(i): x(i, 2) = i ^ 2
y(i, 1) = x(i, 1) + x(i, 2) + Rnd() - 0.5
Next i
rslt = Application.WorksheetFunction.LinEst(y, x, True, True)
End Sub
rslt will be a multi-dimensional array (5 x 3) very much like the
result of LINEST in a worksheet.

Performance? Don't know. Check.

--
Regards,

Tushar Mehta, MS MVP -- Excel
www.tushar-mehta.com
Excel, PowerPoint, and VBA add-ins, tutorials
Custom MS Office productivity solutions

 

[Francine Kubaka]

Hi Tushar,

Thanks!!

I have an array - REGMODEL whose dimensions are (10,~2500).
Currently, I am reading three of the dimensions in the array into a range
and then apply LINEST:
X = [VARIABLE_A]
Y = Union([VARIABLE_B],[VARIABLE_C])

vREGRESSION = Application.WorksheetFunction.LINEST(X, Y, True, True)

I doubt that your solution, however elegant, would result in noticeable
acceleration because I would need to do multiple loops.

Thanks again,

F

 

[Tushar Mehta]

You are welcome. However, I have no idea what you write about.

I have an array - REGMODEL whose dimensions are (10,~2500).

10 rows by 2,500 columns?

Currently, I am reading three of the dimensions in the array into a range
and then apply LINEST:
X = [VARIABLE_A]
Y = Union([VARIABLE_B],[VARIABLE_C])

What does this do relative to REGMODEL?

vREGRESSION = Application.WorksheetFunction.LINEST(X, Y, True, True)

I doubt that your solution, however elegant, would result in noticeable
acceleration because I would need to do multiple loops.

What multiple loops? The one loop I used was to create test data.

Based on your original post it seemed you needed help using LINEST in
VBA with arrays. Now, it seems that's not the case. Oh, well!

--
Regards,

Tushar Mehta, MS MVP -- Excel
www.tushar-mehta.com
Excel, PowerPoint, and VBA add-ins, tutorials
Custom MS Office productivity solutions

Hi Tushar,

Thanks!!

I have an array - REGMODEL whose dimensions are (10,~2500).
Currently, I am reading three of the dimensions in the array into a range
and then apply LINEST:
X = [VARIABLE_A]
Y = Union([VARIABLE_B],[VARIABLE_C])

vREGRESSION = Application.WorksheetFunction.LINEST(X, Y, True, True)

I doubt that your solution, however elegant, would result in noticeable
acceleration because I would need to do multiple loops.

Thanks again,

F

Something along the lines of

Sub testIt()
Dim x As Variant, y As Variant, rslt As Variant, i As Long
ReDim x(1 To 10, 1 To 2)
ReDim y(1 To 10, 1 To 1)
For i = 1 To 10
x(i, 1) = CDbl(i): x(i, 2) = i ^ 2
y(i, 1) = x(i, 1) + x(i, 2) + Rnd() - 0.5
Next i
rslt = Application.WorksheetFunction.LinEst(y, x, True, True)
End Sub
rslt will be a multi-dimensional array (5 x 3) very much like the
result of LINEST in a worksheet.

Performance? Don't know. Check.

--
Regards,

Tushar Mehta, MS MVP -- Excel
www.tushar-mehta.com
Excel, PowerPoint, and VBA add-ins, tutorials
Custom MS Office productivity solutions