Regression in VBA


G

Guest

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
 
Ad

Advertisements

J

John Coleman

Noah said:
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
 
Ad

Advertisements

J

John Coleman

John said:
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
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Top