Regressions in VBA

  • Hi gurus..

    was wondering if anyone has written macros that put results of regression estimation into specific cells - e.g. storing R-sq, intercept etc into directly specified ranges/cells .. as opposed to having the whole summary report appear after the running of the regression.

    There's gotta be a way of doing this, I'll check out some websites, and post the answer if find one.

    Thanks a lot,


  • Hi
    I've never worked with regression calculations but I gather your'e looking to summarise only the final rather than intermediate results, and perhaps for a number of scenarios.

    If this is correct you will probably find that Data Tables will assist.

    Take a look at

    If you're able to determine where the end results will appear (cell reference or offset etc) it will help ...but even if you can't and a macro or search can achieve the answer (cell positio) then the Data Table approach should work.

    Robert Hind
    Perth - Western Australia

  • The worksheet function LINEST can return an array of all of the major regression results (see help for this function). You probably can use it in your macro (I've never actually tried this) and then pluck out just the values you want to return to your worksheet.

Participate now!

Don’t have an account yet? Register yourself now and be a part of our community!