M
marston.gould
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
112", "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
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
112", "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