Thanks for the continued education on arrays. Sometime I actually
feel like I am clear up to about the third grade with arrays.<g>
Regarding Dimming as Long. I always use Long now since the new row
numbers in Excel are a million.
Long is the default TYPE returned/expected when getting/setting the
index of a row/col. It's been my observation that many people assign
Type Integer to vars used for refs to row/col positions/counts or loop
counters.
Is the Long with the counters any different than what I have in my
first post?
Nope!
As to using Index, I came across a discussion among MVP's using
Index. Seemed to be something kinda new..?
I keep this example as a cheater guide, but don't understand the two
1's. I typed =Index(... on the sheet and there are great similarities
as you would expect, but the little syntax window couldn't 'splain it
well enough to me.
- myRng, 1, 1,
There are 2 forms of the Index function: Array and Reference!
The assignment to 'vArr' in the *MyArryCellsRange()* example is
incorrect when using the 'Array' form of the Index function because it
only accepts 3 args: array(), row&, col&) as defined in the Function
Reference...
INDEX Uses an index to choose a value from a reference or array
Array form:
Returns the value of an element in a table or an array (array: Used to
build single formulas that produce multiple results or that operate on
a group of arguments that are arranged in rows and columns. An array
range shares a common formula; an array constant is a group of
constants used as an argument.), selected by the row and column number
indexes.
Use the array form if the first argument to INDEX is an array constant.
Syntax:
INDEX(array,row_num,column_num)
...where 'array' can be either a range of cells or an array constant.
Your sample code *does correctly example* using the 'Reference' form of
the Index function, though, because it assigns MyArray 'areas' (a
non-contiguous range)...
Reference form:
Returns the reference of the cell at the intersection of a particular
row and column. If the reference is made up of nonadjacent
selections,
you can pick the selection to look in.
Syntax:
INDEX(reference,row_num,column_num,area_num)
...where 'reference' is a ref to one or more cell ranges.
If you are entering a non-adjacent range for 'reference', enclose
'reference' in parentheses.
If each area in 'reference' contains only one row or column, the
row_num or column_num argument, respectively, is optional. For
example, for a single row reference, use
INDEX(reference,,column_num).
...and 'row_num' is the number of the row in 'reference' from which to
return a ref.
...and 'column_num' is the number of the column in 'reference' from
which to return a ref.
...and 'area_num' selects a range in reference from which to return the
intersection of row_num and column_num.
The first area selected or entered is numbered 1, the second is 2,
and so on. If 'area_num' is omitted, INDEX uses area 1.
For example, if 'reference' describes the cells (A1:B4,D1:E4,G1:H4),
then 'area_num' 1 is the range A1:B4, 'area_num' 2 is the range
D1:E4,
and 'area_num' 3 is the range G1:H4.
The above info is snipped from the help page. Read the entire help for
this function for further understanding. *Note* that this doesn't work
on 1D arrays!
--
Garry
Free usenet access at
http://www.eternal-september.org
Classic VB Users Regroup!
comp.lang.basic.visual.misc
microsoft.public.vb.general.discussion
