Regression in VBA

[Noah]

I am interested in running a regression in vba, but I don't know what the
arguments are in the function. I tried looking in VB help and in the
discussion groups, but couldn't find anything where all of the arguments are
spelled out. Can anyone help me with what all of the arguments are with the
function:

Application.Run "ATPVBAEN.XLA!Regress", , , False, False, , ActiveSheet _
.Range("$A$1"), False, False, False, False, , False

Thanks!
Noah

 

[Niek Otten]

Hi Noah,

AFAIK, there is no REGRESS function in Analysis Toolpak.
BTW, if you have ATPVBAEN checked in Add-ins, you can use its functions in
VBA without the preceding filename.

For regression, look in here:

http://office.microsoft.com/en-us/assistance/HA011119631033.aspx

 

[John Coleman]

I am interested in running a regression in vba, but I don't know what the
arguments are in the function. I tried looking in VB help and in the
discussion groups, but couldn't find anything where all of the arguments are
spelled out. Can anyone help me with what all of the arguments are with the
function:

Application.Run "ATPVBAEN.XLA!Regress", , , False, False, , ActiveSheet _
.Range("$A$1"), False, False, False, False, , False

Thanks!
Noah

You could try turning on the macro-recorder while running the
regression tool and seeing what happens. In particular, look at the
code with the default and then see what happens when you change one of
the parameters. It wouldn't be surprising if the order of the
parameters in the function call mirrors the tab order in the form
and/or the order in which the options are explained in the help. For
example, the last False might refer to generating normal probability
plots.

There is a (quite good) book called "Microsoft Excel VBA: Professional
Projects" by Duane Birnbaum. It has a chapter on using Excel for curve
fitting and scientific data analysis. I don't know if he addresses how
to use ATPVBAEN there. I'll check when I get to work and can see my
copy.

Hope that helps

-John Coleman

 

[John Coleman]

You could try turning on the macro-recorder while running the
regression tool and seeing what happens. In particular, look at the
code with the default and then see what happens when you change one of
the parameters. It wouldn't be surprising if the order of the
parameters in the function call mirrors the tab order in the form
and/or the order in which the options are explained in the help. For
example, the last False might refer to generating normal probability
plots.

There is a (quite good) book called "Microsoft Excel VBA: Professional
Projects" by Duane Birnbaum. It has a chapter on using Excel for curve
fitting and scientific data analysis. I don't know if he addresses how
to use ATPVBAEN there. I'll check when I get to work and can see my
copy.

Hope that helps

-John Coleman

A better idea: If you add a reference to atbvaen.xls (tools
->references in the editor) then when you type regress( in the editor a
list of the named parameters appears, most of which are either
self-explanatory or make sense upon comparison with the regression
form.

From the object browser:

Sub Regress(inpyrng, [inpxrng], [constant], [labels], [confid],
[soutrng], [residuals], [sresiduals], [rplots], [lplots], [routrng],
[nplots], [poutrng])
Member of atpvbaen.xls.VBA Functions and Subs
Perform multiple linear regression analysis


Hope that helps a bit more.

-John Coleman