Error with variant variable in regression output

Question

I have a macro which is written to perform an OLS regression on data that is selected by the user. This is part of a larger add in that I am writing but I am stuck on what I think must be somewhat of a simple issue. I keep getting a subscript out of range error and I think its because I am getting a different sized matrix to what I am expecting.

The sub takes two variables as its arguments and calculated the OLS estimator given the specification. The y variable is always a n x 1 range (one column and multiple row) and the X variable is a n x m range (can be multiple columns and rows). When this function is used when X is a single column range, the For... Next block works for the following code:

For bcnt = 1 To k
    Cells(bcnt, 1).Value = b(bcnt)
Next bcnt

But if the X variable is a multiple column range this won't work and it has to be the following:

For bcnt = 1 To k
    Cells(bcnt, 1).Value = b(bcnt,1)
Next bcnt

I can't understand why as by my understanding b should always be a one dimensional array.

Would appreciate any help.

The actual sub:

Sub OLSregress(y As Variant, X As Variant)
Dim Xtrans, XtransX, XtransXinv, Xtransy As Variant
Dim outputsheet As Worksheet
Dim b As Variant

' The equation for this estimator is b=[X'X]^(-1)X'Y


Xtrans = Application.WorksheetFunction.Transpose(X)
XtransX = Application.WorksheetFunction.MMult(Xtrans, X)
XtransXinv = Application.WorksheetFunction.MInverse(XtransX)
Xtransy = Application.WorksheetFunction.MMult(Xtrans, y)
b = Application.WorksheetFunction.MMult(XtransXinv, Xtransy)
k = Application.WorksheetFunction.Count(b)

Set ouputsheet = Sheets.Add(, ActiveSheet)
ActiveSheet.Name = "Regression Output"
For bcnt = 1 To k
    Cells(bcnt, 1).Value = b(bcnt, 1)
Next bcnt

End Sub

Answer 1

When you are referring to a range or are bringing in data from a sheet the array is always a 2 dimensional array. The first dimension is rows and the second is the columns.

This is a common point of confusion in VBA for excel because it's done without your intervention.

---

Your code is correct.

For more in-depth information check out this post