LINEST Function in VBA

  • Thread starter Thread starter Francine Kubaka
  • Start date Start date
F

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
 
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
 
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
 
You are welcome. However, I have no idea what you write about.
I have an array - REGMODEL whose dimensions are (10,~2500).
Click to expand...

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])
Click to expand...

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.
Click to expand...
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







Tushar Mehta said:
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
Click to expand...
Click to expand...
 
Back
Top