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:
Code:
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:
Code:
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
