Re: can .range return a 1D array?

Breaking my own rules and answering 2 people in 1 post... (sorry, I'm
having a bad day)

On Wed, 14 May 2008 06:37:58 -0700, T Lavedas wrote:

On May 14, 8:35 am, "Charles Williams" <Char...@xxxxxxxxxxxxxxxxxx>
wrote:
Hi Bruce,

Excel always loads a range into a variant as a 2D array.

Usually its simplest to just process it as a 2D array, but you could
copy the data into a 1D array if you really need to, however the
performance impact of using 2 array indices is insignificant.


Consider the example of implementing an algorithm that "works best" with a
1D array. Consider the case where sometimes you want to call that code
with row (or portion of a row) worth of data and the SAME code with a
column (or portion there of) worth of data. In 1 case the subscripts are
(I,1), in the other they're (1,I). Yes, I know I could implement it with
2 loops going from lbound(x,1) to ubound(x,1) and lbound(x,2) and ubound
(x,2). Now suppose I (or someone else who borrowed the code) wants to
call it with an array created via the ARRAY function. Yep, could code
that too, but the code is *MUCH* simpler to understand (and less likely to
contain errors) if it treats the input as a vector rather than an array.

I know moan and groan, it's not going to change. I'll just live with it.

Charles
__________________________________________________ The Excel
Calculation Sitehttp://www.decisionmodels.com

"Bruce Bowler" <bbow...@xxxxxxxxxxx> wrote in message

news:pan.2008.05.14.12.09.35@xxxxxxxxxxxxxx

First, I apologize if this is the wrong group, but it seemed "close".
NB I'm calling this code from VBA in access, but you'll note there
are no access components (directly) involved, which leads me to
believe it's more likely an excel (or maybe VBA) "problem".

Feel free to redirect me and I'll be off if I was wrong...

I have the following bit of code...

{snip}

The code works *almost* as expected. The data is correct, etc, but
sData ends up being a 2 dimensional array (3,1) and I'd really like
it to be a 1 dimensional array (3). You and I can see that the
second dimension in the .range doesn't change. How can I convince
the computer to make sData a 1 dim array?

Thanks!
Bruce

OK, that is what I have found, though the documentation doesn't mention
it. It makes sense, since a work*** is a two dimensional structure
(rows and columns.

One thing I was surprised to discover is that the array has a base of 1
not the default zero of VBA. That is, the lower bound of the array is
always (1,1).

At least it got something right (not wanting to start a 0 vs 1 flame
war :-)

In this particular situation, it appears to me that the logic is of the
code posted is very confused. The FOR loop is completely unnecessary to
returning an array. In fact, it isn't - it's merely storing a different
array many times into the variable.

Actually the FOR loop is needed for other things (I neglected to include a
[snip - do stuff] after the "end with"). My fault.
.