Geek Answers Handbook

How to apply the Linest function in VBA?

I am trying to get a third-order LinEst function in VBA. However, the error always appears as the expected array when it reaches Ubound (xl).

Option Explicit
Sub RB()

Dim xl As Range, e As Double
Dim yl As Range, s As Variant
Dim X



With ThisWorkbook.Worksheets("Sheet1")
Set yl = .Range(.Cells(17, 7), .Cells(93, 7))

Set xl = .Range(.Cells(17, 1), .Cells(93, 1))

ReDim arrX3(1 To UBound(xl), 1 To 3) As Double
For i = LBound(xl) To UBound(xl)
arrX2(i, 1) = xl(i, 1)
arrX2(i, 2) = xl(i, 1) * xl(i, 1)
arrX2(i, 3) = xl(i, 1) * xl(i, 1) * xl(i, 1)
Next

X = Application.LinEst(yl, arrX3)
.Range(.Cells(12, 12), .Cells(15, 14)).Value = Application.Transpose(X)

End With    
End Sub
+4
source share
2 answers

xlis a range and not an array. This way Ubound(xl)will not work. Although I do not understand what you are trying to achieve, I believe that you are looking for something like this line:

Option Base 1
Option Explicit

Sub RB()

Dim xl As Range, e As Double
Dim yl As Range, s As Variant
Dim X As Variant, i As Long

e = 76

With ThisWorkbook.Worksheets("Sheet1")
    Set yl = .Range(.Cells(17, 7), .Cells(e - 1, 7))
    Set xl = .Range(.Cells(17, 1), .Cells(e - 1, 1))

    Debug.Print "First row in xl is " & xl.Row
    Debug.Print "Range xl has " & xl.Rows.Count & " rows"
    Debug.Print "Last row in xl is " & xl.Rows.Count + xl.Row - 1


    ReDim arrX3(1 To xl.Rows.Count, 1 To 3) As Double
    For i = 1 To xl.Rows.Count
        arrX3(i, 1) = xl.Cells(i, 1)
        arrX3(i, 2) = xl.Cells(i, 1) * xl.Cells(i, 1)
        arrX3(i, 3) = xl.Cells(i, 1) * xl.Cells(i, 1) * xl.Cells(i, 1)
    Next i

    X = Application.LinEst(yl, arrX3)
    .Range(.Cells(12, 12), .Cells(15, 14)).Value = Application.Transpose(X)

End With

End Sub

Please note that I have added a few Debug.Printthat you might want to see.

+1
source

xldeclared as a range, but ranges do not have Ubound.

Change the declaration xlfrom Rangeto Variantand replace the line

Set xl = .Range(.Cells(17, 1), .Cells(93, 1))


xl = .Range(.Cells(17, 1), .Cells(93, 1)).Value

, , , , , , .

0

More articles:


All Articles