Can Excel VBA LinEst using dynamic Arr directly instead of Range?

[pzjoy]

Hello all MrExcel experts,

I try to fasten my vba code posted below, already use screenupdating false function, empty cache. Currently I need 3 steps to use LinEst function for an Arr:
Step1, write Arr to Range
Step2, get Range
Step3, use Range in LinEst

I want to know, can Excel VBA LinEst using dynamic Arr directly? Like "z1 = Application.WorksheetFunction.LinEst(Arr(z, 3), , False, True).

Other comments on optimizing speed also highly appreciated

------------------------------------------------------

Option Base 1
Sub faster()
Application.ScreenUpdating = False
Dim x, y, z As Integer
Dim x1, x2, x3 As Double
For x = 1 To 100

For y = 1 To 100​

ReDim Arr(1 To Range("a" & x).End(xlDown).Row, 1 To 3)​

For z = 1 To 100​

If ... Then​

x1 = ......​

Else If ... Then​

x1 = .......​

Else​

x1 = ......​

End If​

Arr(z, 1) = x1​

If ... Then​

x2 = ......​

Else If ... Then​

x2 = .......​

Else​

x2 = ......​

End If​

Arr(z, 2) = x2​

If Arr(z, 1)...And Arr(z, 2)... Then​

x3 = ......​

Else If Arr(z, 1)...And Arr(z, 2)... Then​

x3 = .......​

Else​

x3 = ......​

End If​

Arr(z, 3) = x3​

Next z​

Range("b1:b" & Range("a" & x).End(xlDown).Row) = Application.Index(Arr, , 3) 'Step1, write arr to range​

Dim y1, z1 As Variant​

y1 = Range("b1:b" & Range("a" & x).End(xlDown).Row).Value 'Step2, get range​

z1 = Application.WorksheetFunction.LinEst(y1, , True, True) 'Step3, use range in LinEst​

Cells(1, 3) = z1(3, 1)
Erase Arr​

Set z1 = Nothing​

Next y​

Next x
Application.ScreenUpdating = True
End Sub

 

[Jon Peltier]

Sure it can. I don't know what the Arr(z,3) is doing in your LinEst call, isn't Arr(z,3) a single value?

Also, z1(3,1) is simply R², so you could more easily compute WorksheetFunction.Correl and square it. Unless you have other plans for the output of LinEst that you haven't shown here.

Here are a couple quick routines showing WorksheetFunction.LinEst:

Sub DoLinEstFrom2DArrays()
  Dim x As Variant, y As Variant, lin As Variant
  
  x = ActiveSheet.Range("B4:B13").Value2
  y = ActiveSheet.Range("C4:C13").Value2
  
  lin = WorksheetFunction.LinEst(y, x, True, True)
  
  ActiveSheet.Range("H4:I8").Value = lin
End Sub


Sub DoLinEstFrom1DArrays()
  Dim x As Variant, y As Variant, lin As Variant
  
  x = Array(4, 5, 6, 7, 8, 9, 10, 11, 12, 13)
  y = Array(3.48, 5.1, 6.77, 7.13, 8.68, 9.15, 10.58, 11.64, 11.72, 13.05)
  
  lin = WorksheetFunction.LinEst(y, x, True, True)
  
  ActiveSheet.Range("K4:L8").Value = lin
End Sub

In the screenshot below, you can see two columns of input data (B4:C13) and three sets of LINEST output, one using the function in the workbook and two using the routines above.