ExcelBanter - Macro

Heera,

If you dimension a variable with () at the end of the variable name (e.g.
Dim dblArr() As Double), then you are letting the computer know that you have
an array variable. If there is no data within the (), then you must
dimension the variable in your code with the ReDim statement (e.g. Dim
dblArr() As Double | ReDim dblArr(5)). Otherwise, you can size the array
within the Dim statement (e.g. Dim dblArr(5) As Double). The number within
the () signifies the number of elements in the array. If the array is zero
based (which is the default setting unless signified otherwise), then
dblArr(5) will contain 6 elements (i.e. an element for positions 0, 1, 2, 3,
4, and 5). So, dblArr is one variable which can hold 6 different values.
This is useful because you don't have to dimension 6 separate and unique
variable for 6 values, rather you dimension one variable to hold the 6 values.

The For i = 0 To 5 loop, the program is looping through the elements of the
array. (Rather than using hard coded values of 0 and 5, use LBound and
UBound). The first time through the loop looks like this -- myArray(0) =
Rnd. So, myArray(0) holds a random number. The second time through the loop
looks like this -- myArray(1) = Rnd. Again, myArray(1) holds a random
number. The process repeats through the loop. This loop is assigning values
to the specified array element (or index).

The For Each n In myArray loop loops through all the elements in myArray.
This could also have been written as For n = LBound(myArray) To
UBound(myArray). The For Each loop is a loop designed for collections.
(Collections are a group of "related" items. For simplicity and illustrative
purposes (and without getting into the nitty gritty), worksheet is singular
and worksheets is plural. Worksheets is a collection). Since an array is a
collection (i.e. it is a group of related items), the For Each loop will loop
through each item of the collection (i.e. each element of the array). In
general, a For Each loop takes less time to execute than a For Next loop.
The For Each n In myArray loop is looping through each element in the array
and printing the value to the Immediate Window (View | Immediate Window).
This allows you to see the values that were stored in the array from the For
i = 0 To 5 loop.

I hope this is clear enough. (See the VBE help documentation on Declaring
Arrays, ReDim Statement, and LBound and UBound Function. This documentation
should provide an initial starting point and should provide some more color
on the matter).

Best,

Matthew Herbert

"Heera" wrote:

Hi all,

I am not able to understand the array concept and from where the i & n
came in, can any one simplify the below mentioned macro for me so that
i can understand.

sub macro()

dim myarray(5) as double
for i 0 to 5
myarray(i) =rnd
next i

for each n in myarray
debug.print n
next n

end sub

Heera Chavan