VBA for performing multiple regressions (like 800 of them) simultaneously!

[novice0317]

Hi guys,

I'm trying to write a VBA code to perform multiple regressions simultaneously. In this case, I actually have nearly 800 regressions that need to be done - so having it all automated would be a real timesaver! In my case, I have several cases for "y" (the output / dependent variable) to be regressed against the same two x's (input variables). My y's values are stored in range C5 through AFK11 (so C5 - C11 as one "y" case, D5 - D11 as another "y" case, E5 - E11 as yet another "y" case, etc.). My x's or input variables are only in two columns at the very end: AFL 5-11 (one set of x-variable values), the other being AFM 5-11 (the other set of x-variable values). Just wondering if it's possible to write a loop to do all these regression cases automatically using the Excel Regression feature ATPVBAEN.XLAM? If it turns out that this feature only allows regression of one set of y range values at a time, then would my other choice be to use LINEST, and if so how do I do loop it? Anyway here is my code so far:

Sub Regression()

Sheets("1").Activate

Application.Run "ATPVBAEN.XLAM!Regress", ActiveSheet.Range($D$5:$D$11"), ActiveSheet.Range("$AFL$5:$AFM$11"), _
False, True, , Worksheets("1"), False, False, False, False, , False

Application.Run "ATPVBAEN.XLAM!Regress", ActiveSheet.Range($E$5:$E$11"), ActiveSheet.Range("$AFL$5:$AFM$11"), _
False, True, , Worksheets("1"), False, False, False, False, , False

Application.Run "ATPVBAEN.XLAM!Regress", ActiveSheet.Range($F$5:$F$11"), ActiveSheet.Range("$AFL$5:$AFM$11"), _
False, True, , Worksheets("1"), False, False, False, False, , False

...(this code repeats for every single set of y's that I have, ie. G5:G11, H5:H11, I5:I11, J5:J11 against the same two independent sets of x variable values, AFL5:AFM11

End Sub

Please help me find a way to loop this so I don't have to write out all 800 lines of code lol, one for each set of "y" values! Thank you so much!

 

[Trevor_S]

How about using offset to do the loop?

For n = 1 to 800
Application.Run "ATPVBAEN.XLAM!Regress", ActiveSheet.Range($C$5:$C$11").Offset(0,n), ActiveSheet.Range("$AFL$5:$AFM$11"), _
False, True, , Worksheets("1"), False, False, False, False, , False
Next

 

[novice0317]

Hi Trevor_S, thanks so much for your help! I took your suggestion, and put in an offset to do the loop as you said, and it worked! Now I have another problem that I need help with though. How do I print / output the 800 sets of results? Ideally I would like to have VBA print or output all the results onto one single sheet (a new sheet other than the one that has all the original data) but how do I modify your command above to get it to do this? The alternative is we can have VBA output the results onto individual sheets, so 800 additional sheets total (one for each set of results), but how do I tell it to do that? Again the 1st option is preferred, but I suppose the 2nd option could work as well. Thanks so much again!

 

[Trevor_S]

I've never used the regression functionality, so I'm not sure what the various arguments are. Where do the results currently appear?