Multiple linear regression in VBA

F

fboehlandt

Active Member
Joined
Sep 9, 2008
Messages
334
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
 

Excel Facts

Highlight Duplicates
Home, Conditional Formatting, Highlight Cells, Duplicate records, OK to add pink formatting to any duplicates in selected range.
Sort by date Sort by votes
Hi Florian

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

Code: 
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
 
Upvote 0
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:

Code: 
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
 
Upvote 0
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:

Rich (BB code): 
ReDim yfactor(1 To Obs, 1 To 1)

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

Rich (BB code): 
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.
 
Upvote 0
How would you do this with formulas?? Your help is much appreciated in advance!

pgc01 said:
Hi Florian

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

Code: 
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
Click to expand...
 
Upvote 0
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.
 
Upvote 0
You must log in or register to reply here.

Similar threads

Q
  • Question
Drawbacks for checking whether input functions are valid in a textbox
Replies
0
Views
335
quemuenchatocha
Q
D
  • Solved
Carryover variable to called sub
Replies
3
Views
208
dlowell09
D
D
  • Question
VBA Multiple "conditional" page breaks
Replies
3
Views
780
Domenic
D
P
  • Question
Copy data from one table to multiple table.
Replies
0
Views
358
patapsychonaute
P
A
  • Question
Perform Linear and Logarithmic Regressions in a Data Set with Multiple "x variables"
Replies
1
Views
501
kweaver
kweaver

Forum statistics

Threads
1,214,979
Messages
6,122,552
Members
449,088
Latest member
davidcom

Share this page

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top