Multiple linear regression in VBA

[fboehlandt]

Hi everyone,
I would like to run a multiple linear regression in vba. I have one dependent and three explanatory variables. I will have to use a macro of some kind, since I need to run too many regressions to do it manually. To simplify things a little bit:
- There will always be exactly three independent variables
- There are no missing values
- The data is allways numerical
I've already got four ranges defined: Yrange, X1range, X2range, X3range. I would like to take these ranges as input parameters for the regression model. The only two parameters I need are Sum Square for Regression (SSR) and the degrees of freedom. I understand that you can use excel's matrix formulas to calculate some of the input parameters, but one doesn't really get around vba. Does anybody know where I can find a (simple) source code allowing me to conduct a regression with three input parameters? Many thanks in advance

 

[pgc01]

Hi Florian

You could do it with formulas, but since you want a vba solution, try:

Sub GetRegressionStatistics()
Dim rY As Range, rX As Range
Dim vStat As Variant
 
Set rY = Range("YRange")
Set rX = Application.Union(Range("X1Range"), Range("X2Range"), Range("X3Range"))
 
vStat = Application.WorksheetFunction.LinEst(rY, rX, True, True)
 
MsgBox "Degrees of Freedom: " & vStat(4, 1) & vbNewLine & _
       "Sum Square for Regression: " & vStat(5, 1)
 
End Sub

 

[fboehlandt]

Thanks very much!
Thats pretty much what I'm looking for. I've got one problem left to sort out though. My cell ranges are defined as dynamic array variables:

ReDim yfactor(1 To Obs)
ReDim xfactor(1 To Obs, 1 To 3)
 
For i = 1 To Obs1
    yfactor(i) = WeightedVector1(i)
    xfactor(i, 1) = 0
    xfactor(i, 2) = 0
    xfactor(i, 3) = xfactor(i, 1) * xfactor(i, 2)
Next i
For i = 1 To Obs2
    yfactor(Obs1 + i) = WeightedVector2(i)
    xfactor(Obs1 + i, 1) = 0
    xfactor(Obs1 + i, 2) = 1
    xfactor(Obs1 + i, 3) = xfactor(Obs1 + i, 1) * xfactor(Obs1 + i, 2)
Next i
For i = 1 To Obs3
    yfactor(Obs1 + Obs2 + i) = WeightedVector3(i)
    xfactor(Obs1 + Obs2 + i, 1) = 1
    xfactor(Obs1 + Obs2 + i, 2) = 0
    xfactor(Obs1 + Obs2 + i, 3) = xfactor(Obs1 + Obs2 + i, 1) * xfactor(Obs1 + Obs2 + i, 2)
Next i
For i = 1 To Obs4
    yfactor(Obs1 + Obs2 + Obs3 + i) = WeightedVector4(i)
    xfactor(Obs1 + Obs2 + Obs3 + i, 1) = 1
    xfactor(Obs1 + Obs2 + Obs3 + i, 2) = 1
    xfactor(Obs1 + Obs2 + Obs3 + i, 3) = xfactor(Obs1 + Obs2 + Obs3 + i, 1) * xfactor(Obs1 + Obs2 + Obs3 + i, 2)
Next i
 
vStat = WorksheetFunction.LinEst(yfactor, xfactor, True, True)

Unfortunately, the LinEst function requires Range as input parameters. Is there any way to calculate the relevant statistic without having to copy the arrays to a worksheet first? thanks in advance

 

[fboehlandt]

Never mind, transpose(yfactor) does the trick here...thanks for your help again

 

[pgc01]

Hi again

You are right, Transpose() will do it because it will transform your unidimensional array into a Column bidimensional array.

It would be not necessary, however, if you declare the yfactor as a bidimensional Column vector, which is what Linest expects.

The yfactor declaration should be:

ReDim yfactor(1 To Obs, 1 To 1)

And the references to yfactor should include the 2nd dimension, for ex.:

yfactor(Obs1 + Obs2 + i, 1) = WeightedVector3(i)

This way you would not need to use the Transpose() method as the array was already in the correct format.

 

[fboehlandt]

I've implemented your solution as it seems less prone to errors or changes in the source data
thanks

 

[drewbridges]

How would you do this with formulas?? Your help is much appreciated in advance!

Hi Florian

You could do it with formulas, but since you want a vba solution, try:

Sub GetRegressionStatistics()
Dim rY As Range, rX As Range
Dim vStat As Variant
 
Set rY = Range("YRange")
Set rX = Application.Union(Range("X1Range"), Range("X2Range"), Range("X3Range"))
 
vStat = Application.WorksheetFunction.LinEst(rY, rX, True, True)
 
MsgBox "Degrees of Freedom: " & vStat(4, 1) & vbNewLine & _
       "Sum Square for Regression: " & vStat(5, 1)
 
End Sub

 

[pgc01]

Hi
Welcome to the board

Sorry, I don't understand exactly what you mean. You can use Linest() directly in the worksheet.

Please post an working example:
- input data and layout
- the logic
- expected output

No images or links. With recent versions of excel and browsers it usually works copying directly a table from excel to the browser. Else post a html table or simply data in text format, for ex. csv.