# Understanding arrays (VBA) & how to apply them effective



## lionelnz (Oct 29, 2007)

Hi all.

I am struggling a bit with understanding & applying arrays in VBA.

Quite often when trying to assit others with my limited knowledge of VBA the gurus of the board come up with far more efficient coding using arrays which (mostly) baffles me.

I have done a google for arrays/VBA & mostly what I get is limited info on how to apply them in a given situation.

I also have JW's XL 2003 VBA book & I am starting going thru that from beginning to end.

What I am looking for is some info that goes into the nuts & bolts of how they work, different applications of with examples of syntax.

If anyone has any suggestions I would appreciate it.

Thanks Lionel


----------



## Andrew Fergus (Oct 29, 2007)

Hi Lionel

There is an article here that might help:
http://msdn2.microsoft.com/en-us/library/aa140074(office.10).aspx

In a nutshell, an array variable in VBA is just a bunch of variables.

For example, you could declare 10 variables in VBA like this:
Dim x1 As Long, x2 as Long ...... x10 as Long

or you could use an array like this:
Dim x(9) as Long

This creates 10 variables of the type Long with read/write access - you access the variable using the syntax x(0), x(1) ... to x(9).  You set the variable like you would any other variable, like this:
x(0) = something

If you tried to set x(10) to a value you will get a 'subscript out of range' error given the highest position number is 9 and you tried to assign something to the 10th position.

The example above is a one dimensional array.  There is only one dimension of length 10 (i.e. 0 to 9, not 1 to 9) - arrays always start at position 0 so thats why I only went to 9 and not 10.  You could declare 10 and not use the 0 position - that is a matter of personal preference.

You can have multi-dimensional arrays, e.g. a two dimensional array.  If you try to visualise a 2-dimentional array think of a blank spreadsheet that has x columns and y rows.  The way to declare a 2 dimensional array would be like this :
Dim x(9,9) as Long
This creates 100 variables which can be accessed like this:
x(0,0), x(1,0), x(2,0) ..... x(9,0)
x(0,1)  x(1,1), x(2,1) ..... x(9,1)
.
.
.
x(0,9)  x(1,9), x(2,9) ..... x(9,9)
These 100 variables can only hold 'Long' values given that is how we delcared the array.

If you wanted to store multiple types of values (i.e. numbers and text) within the array then you would declare it as a Variant.

Say for arguments sake you wanted to store 100 pieces of data in an array where the first field was a number, and the second was text then you would do so like this:


```
Dim MyArray(99,1) as Variant
'2nd dimension:
' use first position for number
' use second position for text
Dim LoopCount as Long

For LoopCount = 0 to 99
  MyArray(LoopCount,0) = 100 - LoopCount
  MyArray(LoopCount,1) = "My Text"
Next LoopCount
```

This will store the values 100 to 1 in the first position of the 2nd dimension (i.e. row A in a spreadsheet, columns 1 to 100) and will store "My Text" in the second position of the 2nd dimension (i.e. Row B in a spreadsheet, columns 1 to 100).  If you are finding the column and row analogy seems backwards, then you could think of it as 100 rows and 2 columns instead of 100 columns and 2 rows.  So long as you are consistent with yourself.

IMO the real power of multi-dimensional arrays is accessing the variable names via a loop, like this:

```
Dim X(9, 9) As Long
i = 0
For loop1 = 0 To 9
    For loop2 = 0 To 9
        i = i + 1 'for example
        X(loop1, loop2) = i
    Next i2
Next i1
```
There are a number of examples of these techniques here on MrExcel, for instance, the solution I provided in this thread employed multi-dimensional arrays:
http://www.mrexcel.com/board2/viewtopic.php?t=271038

You can also reset the size of the last dimension in an array using the Redim statement (check it out in your help file) and if you combine that with the 'Preserve' statement then you won't delete the existing contents of the array when you increase the size of the last dimension.  And if you aren't sure how big the array will be at the start of your code, then you create it like so : 
Dim myArray() as ....
and then set the actual size later using a Redim statement, like this:
Redim MyArray(MyVar).

Lastly, you will find you can have more than 2 dimensions, this is harder to visualise but you can have 3 dimensions in an array like so:

Dim x(500,9,2) as Variant

Notice the sizes of the dimensions do not need to be the same - they can be any positive value (within reason).  This array is in 3 dimensions and has created (or set aside the space for) 15030 variables (i.e. 501 x 10 x 3).

I hope this gets you started!  I recommend you experiment with a few loops and arrays to get the hang of them.

HTH, Andrew


----------



## lionelnz (Oct 29, 2007)

Thanks heaps Andrew! That is a good start in English to understand this stuff.

Lionel


----------



## Andrew Fergus (Oct 30, 2007)

You're welcome!  I think I got my column and row references around the wrong way in my Excel analogy......


----------

