Two questions

P

P. Dileepan

[1] When I run the code with a user form with RefEdit
controls, when I click in the worksheet to highlight cell
ranges, the arrow keys, Control-Home key, etc. are not
working. Is there a setting that I have to initialize in
the code?

[2] I am using Application.Run "ATPVBAEN.XLA!Regress"
and it works fine. I want to know whether I have to give
only Range objects for Y and X range. Can I give array
objetcs for Y and X range?

Thank you.

-- Dileepan
 
J

Jerry W. Lewis

Re. Question [2]:

The Regress sub only takes ranges, hence the dialog that states
"Regression - Input Y range must be a contiguous reference" if you try
to pass it an array.

Jerry
 
P

P. Dileepan

-----Original Message-----
Re. Question [2]:

The Regress sub only takes ranges, hence the dialog that states
"Regression - Input Y range must be a contiguous reference" if you try
to pass it an array.
Click to expand...

Thank you for answering my questions. I have a followup.

How can I pass an array as a range? Here is a sample
code:
============================
For i = 1 To n
X(i, 1) = i
X(i, 2) = i*i
Next

AddIns("Analysis ToolPak - VBA").Installed = True
Application.Run "ATPVBAEN.XLA!Regress", Range
(dataRange), _
X, False, False, , "temp", False, False, _
False, False, , False
==============================

The y-range is fine, but X is an array and I am getting
an error. How can I pass X as a "contiguous reference"?

Thank you,

-- Dileepan
 
T

Tom Ogilvy

Write it to a range on the worksheet and then pass that.

--
Regards,
Tom Ogilvy


P. Dileepan said:
-----Original Message-----
Re. Question [2]:

The Regress sub only takes ranges, hence the dialog that states
"Regression - Input Y range must be a contiguous reference" if you try
to pass it an array.
Click to expand...

Thank you for answering my questions. I have a followup.

How can I pass an array as a range? Here is a sample
code:
============================
For i = 1 To n
X(i, 1) = i
X(i, 2) = i*i
Next

AddIns("Analysis ToolPak - VBA").Installed = True
Application.Run "ATPVBAEN.XLA!Regress", Range
(dataRange), _
X, False, False, , "temp", False, False, _
False, False, , False
==============================

The y-range is fine, but X is an array and I am getting
an error. How can I pass X as a "contiguous reference"?

Thank you,

-- Dileepan
Click to expand...
 
J

Jerry W. Lewis

As Tom and I both noted, Regress only accepts worksheet cell range
references as input for x and y. Alternately, everything calculated by
the ATP regression tool is either directly returned by the LINEST
worksheet function, or is easily computable from the values returned by
LINEST. You can call LINEST in VBA as Application.LinEst( ) and you can
pass arrays to LINEST.

Jerry
 
Top