How to apply Linest function in VBA?

Question

I trying to get a third order LinEst function in VBA. However, the error always come out as 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

Ralph · Accepted Answer · 2016-04-09 20:17:34Z

xl is a Range and not an array. So, Ubound(xl) won't work. While I do not understand what you're code is trying to achieve, I believe that you are looking for something along the line like this:

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

Note, that I added a few Debug.Print which you might want to have a look at.

John Coleman · Accepted Answer · 2016-04-09 20:17:15Z

0

xl is declared to be a range and ranges don't have a Ubound.

Change the declaration of xl from Range to Variant and replace the line

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

by

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

I'm not sure if this will be enough to make your code run as expected, but it will at least get rid of the error that you describe.

answered Apr 9, 2016 at 20:17

John Coleman

52.1k7 gold badges59 silver badges127 bronze badges

Collectives™ on Stack Overflow

How to apply Linest function in VBA?

2 Answers 2

Comments

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Related