If you are attempting to create a worksheet formula, you can go into the
Help, Contents, then go under:
Microsoft Excel Help>Creating and Correcting Formulas>Array Formulas, About
array formulas and array constants
This shows what an array function would look like when it's entered.
{} - Everything within the curly brackets is the array (Excel automatically
puts this in when you press Ctrl-Shift-Enter)
, - This is a column separator
; - This is a row separator
The above is for constant arrays. Formula arrays as the help shows is done
slightly different, but still gets at the same thing
If you are attempting to use arrays within VBA, you can setup arrays with up
to 60 dimensions. There's a few ways of setting up arrays in VBA.
Option Base 1
The above code, when put in the global section of the module at the top (may
be placed directly below the code of "Option Explicit", but only without the
quotes), it forces all arrays to have a lower bound value of 1 instead of 0.
One exception to this rule, if the array is prequalified with an object such
as VBA.Array, that array is not impacted by the Option Base statement. If
you want to create a dynamic multiple array, ONLY the last dimension can be
changed, therefore, even though for a 2 dimensional array, the help file
states it's in Row/Column format, I say it's the reverse cause why would you
have the number of columns being variable with the number of rows fixed in
most cases?
To declare an array, use something like:
Option Base 1
Dim arrTarrif(2,20) As Variant 'This initially sets up a fixed array.
According to the help file, this would be a 2 row by 20 columns, but cause
you can only change the last dimension, I think of this as a 2 column by 20
rows.
You can also setup fixed arrays with a specified lower and upper bound
limits like the following:
Option Base 0
Dim arrTarrif(1 To 2, 20)
Note, in this case, the first dimension has 2 elements with their element
numbers being 1 and 2. The second dimension has 20 elements with their
numbers ranging from 0 to 19. Note the "1 To 2" as this specified the lower
bound value of 1 and the upper bound value of 2.
For a dynamic array, only the upper bound of the last dimension can be
changed, which can be setup like the following:
ReDim arrTarrif(2,UBound(arrTarrif,2)+1)
In the above case, it deletes all of the data that's within the array when
it redeclares the array with one more element of the second dimension. To
avoid losing that data, use the following code:
ReDim Preserve arrTarrif(2,UBound(arrTarrif,2)+1)
Each deminsion is in numeric order from it's lower bound limit to it's upper
bound limit by increments of 1.
NOTE: DON'T BE FOOLED BY THE HELP FILE that your array size is limited to
the amount of the RAM on your system cause that is NOT true, at least for
Excel 97, 2000, and 2002. I can't speak for 2003. Excel 97 and 2000 can
only take up to 80MB of RAM before it crashes, even if your system has 1.5GB
of RAM. Excel 2002 can only take up to 160MB of RAM before it crashes.
To record values within the array, you simply state it's element number of
each declared dimension like in the 2 dimension example:
For I = 1 to UBound(arrTarrif,2) Step 1
arrTarrif(1,I) = I * 0.20
arrTarrif(2,I) = I * 0.50
Next I
In the above example, it's making the assumption like Tarrif 1 is for
employees which only gets charged $0.20 per 15 minutes while all others gets
charged $0.50 per 15 minutes they are parked in the parking garage.
