I'm trying to create a VBA macro that calculates the coefficients/T-stats and R-square for each variable with in a regression. The trick is that I have 10 candidate independent variables and I'd like to be able to test each combination of these variables.

I created some code that used fixed ranges for the Linest calc and that worked.
I got some help creating code that selected each unique combination.
But when I put these together - I get a runtime error 1004 on the linest.
I know that it has something to do with how I'm assigning the unique combination to a range that will be used in the Linest calculation.

I would appreciate any thoughts on how to correct this.

Regards - Marston

Here is the 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", "D2:D112", "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)
'visually make sure the correct range is being selected
'xRng = Range("A2:J112") ' this works when I utilize this instead
xRng.Select
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

See the response in m.p.e.newusers.

And please do not multi-post, especially across newsgroups.

You appear to be using Google Groups. Sometimes it is slow in displaying
new posts.

On 27/07/2011 19:45, wrote:
I'm trying to create a VBA macro that calculates the coefficients/T-stats and R-square for each variable with in a regression. The trick is that I have 10 candidate independent variables and I'd like to be able to test each combination of these variables.

I created some code that used fixed ranges for the Linest calc and that worked.
I got some help creating code that selected each unique combination.
But when I put these together - I get a runtime error 1004 on the linest.
I know that it has something to do with how I'm assigning the unique combination to a range that will be used in the Linest calculation.

I would appreciate any thoughts on how to correct this.

Regards - Marston

Here is the 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", "D2:D112", "E2:E112", "F2:F112", "G2:G112", "H2:H112", "I2:I112", "J2:J112")
Set yRng = Range("K2:K112")

Linest isn't the worlds most numerically stable algorithm - although
XL2007 implementation is somewhat better than earlier versions. You
should be aware that the answer it gives need not be entirely accurate.

You would be well advised to take a long hard look at the matrix of
possible dot products of your trial vectors of the form A.B/(|A|*|B|)

If any of these scalars are close to unity then there is a potential
risk of numerical instability problems when they are both used.

Regards,
Martin Brown