ExcelBanter - View Single Post

Martin Brown

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