Runtime Error on Linest

[marston.gould]

I'm trying to run a regression and collect all of the coefficients/T-stats and R-square of each combination between a single dependent variable and 10 different independent variables.

In the example code I'll put below - I'm only trying to output the coefficient and T-stat of the first variable - and the r-square of the full regression. Eventually I'll need to do all variables.

The problem I'm having is when I test fixed range declarations in the independent variables - the LinEst function works. When I create a range based on dynamic ranges - it doesn't. Could use some help.

Thanks in advance.

See code below

Option Base 1
Option Explicit
Sub combinKofN()
Dim rngs
Dim nRngs As Long, maxCombin As Long, nCombin As Long
Dim nSelect As Long, i As Long, j As Long
Dim r As String
Dim xRng As Range
Dim yRng As Range
Dim v
Dim k As Integer

rngs = Array("A2:A112", "B2:B112", "C2:C112", "D2112", "E2:E112", "F2:F112", "G2:G112", "H2:H112", "I2:I112", "J2:J112")
Set yRng = Range("K2:K112")
yRng.Select
nRngs = UBound(rngs)
k = 0
For nSelect = nRngs To 1 Step -1
maxCombin = WorksheetFunction.Combin(nRngs, nSelect)
ReDim idx(1 To nSelect) As Long
For i = 1 To nSelect: idx(i) = i: Next

nCombin = 0
Do
' generate next combination
nCombin = nCombin + 1
r = rngs(idx(1))
For i = 2 To nSelect
r = r & "," & rngs(idx(i))
Next
Set xRng = Range(r)
'xRng = Range("A2:J112") 'this works when I use this
'here is where I have problems
v = Application.WorksheetFunction.LinEst(yRng, xRng, 0, True)
' ...coefficient
Range("M2").Offset(4 * k + 2, 0) = v(1, 1)
' ...T-stat
Range("M2").Offset(4 * k + 3, 0) = Abs(v(1, 1) / v(2, 1))
' ...R-squared
Range("M2").Offset(4 * k + 4, 0) = v(3, 1)
k = k + 1

If nCombin = maxCombin Then Exit Do

' next combination index
i = nSelect: j = 0
While idx(i) = nRngs - j
i = i - 1: j = j + 1
Wend
idx(i) = idx(i) + 1
For j = i + 1 To nSelect
idx(j) = idx(j - 1) + 1
Next
Loop
Next

 

[joeu2004]

The problem I'm having is when I test fixed range
declarations in the independent variables - the LinEst
function works. When I create a range based on dynamic
ranges - it doesn't. Could use some help.

The issue is not fixed v. dynamic range. The issue is range v. range
collection.

Consider the first iteration.... The string r becomes
"A2:A112,B2:B112,C2:C112,D2112,E2:E112,F2:F112,G2:G112,H2:H112,I2:I112,J2:J112",
so xRng becomes the collection of those ranges.

Apparently, WorksheetFunction.Linest does not work with range collections.
Not surprising: neither does the Excel LINEST function. Instead, as you
noted, xRng must be Range("A2:J112").

Ostensibly, we might write:

Set xRng = Range(rngs(idx(1)), rngs(idx(nSelect)))

Note that we do not need to construct the string r at all.

However, that will not work for you in general because Linest assumes that
you are referring to __all__ interstitial columns.

For example, in iteration 977, r becomes "A2:A112,J2:J112". You intend to
Linest to consider only a 2-column multiple regression using only A2:A112
and J2:112. But xRng will be set to Range("A2:J112"), which Linest will
interpret as a 10-column multiple regression.

I believe the only fix is to copy the selected columns of each combination
to a temporary contiguous range, and pass that temporary range to Linest.

The following demonstrates the intended change. Note that it is untested.
If you are unable to debug any hopefully-minor mistakes, let me know, and I
can spend more time with it.

Note: There is no benefit to using type Integer instead of type Long in
this context.

PS: When you get done with development, it would be prudent to bracket the
body of the macro with the following statements in order to improve
performance:

Application.ScreenUpdating = False
Application.Calculation = xlCalculationManual
Application.EnableEvents = False
[... rest of macro ...]
Application.EnableEvents = True
Application.Calculation = xlCalculationAutomatic
Application.ScreenUpdating = True

-----

Option Base 1
Option Explicit

Sub combinKofN()
Dim rngs
Dim nRngs As Long, maxCombin As Long, nCombin As Long
Dim nSelect As Long, i As Long, j As Long
'Dim r As String '***remove***
Dim xRng As Range
Dim yRng As Range
Dim v
Dim k As Long '***changed***
Dim nRows As Long '***new***


Set xRng = Range("X2") '***new***
Set yRng = Range("K2:K112")
yRng.Select
nRows = yRng.Count '***new***

rngs = Array("A2:A112", "B2:B112", "C2:C112", "D2112", _
"E2:E112", "F2:F112", "G2:G112", "H2:H112", _
"I2:I112", "J2:J112")
nRngs = UBound(rngs)

k = 0
For nSelect = nRngs To 1 Step -1
maxCombin = WorksheetFunction.Combin(nRngs, nSelect)
ReDim idx(1 To nSelect) As Long
For i = 1 To nSelect: idx(i) = i: Next

nCombin = 0
Do
' generate next combination
nCombin = nCombin + 1
For i = 1 To nSelect '***changed***
xRng.Offset(0, i - 1).Resize(nRows, 1) = _
Range(rngs(idx(i)))
Next
v = Application.WorksheetFunction.LinEst(yRng, _
xRng.Resize(nRows, nSelect), 0, True)
' ...coefficient
Range("M2").Offset(4 * k + 2, 0) = v(1, 1)
' ...T-stat
Range("M2").Offset(4 * k + 3, 0) = Abs(v(1, 1) / v(2, 1))
' ...R-squared
Range("M2").Offset(4 * k + 4, 0) = v(3, 1)
k = k + 1

If nCombin = maxCombin Then Exit Do

' next combination index
i = nSelect: j = 0
While idx(i) = nRngs - j
i = i - 1: j = j + 1
Wend
idx(i) = idx(i) + 1
For j = i + 1 To nSelect
idx(j) = idx(j - 1) + 1
Next
Loop
Next

xRng.Resize(nRows, nRngs).Clear '***new***

End Sub

 

[joeu2004]

Small improvements....

Set xRng = Range("X2") '***new***

Change to

Set xRng = Range("X2:X112")

Or more generally, after determining nRows:

Set xRng = Range("X2").resize(nRows)

xRng.Offset(0, i - 1).Resize(nRows, 1) = _
Range(rngs(idx(i)))

With the change above:

xRng.Offset(0, i - 1) = Range(rngs(idx(i)))

 

[marston.gould]

THank you!!