An array is a data construct that holds multiple pieces of data. It is useful for storing and manipulating related data as a unit rather than having to refer to each individual piece of information. An example would be an array called Calendar that holds the names of the months. This would be an example of a static, one-dimensional array with twelve elements. Arrays can be multidimensional as well.
Arrays are used to reference, calculate and display multiple variables by using a single name and one or more reference numbers. This makes writing, reading and modifying code much easier. In the calendar example, think of displaying the months of the year on the screen.
You could write:
Print “March”, etc.
Or you could put the array in a loop such as this:
For vMonth = 1 to 12
Now think of the same situation, but with a variable with hundreds of elements.
There are two different types of arrays in Visual Basic for Applications (VBA), static or fixed-size, and dynamic or variable size. A static array is used when you know exactly how many elements you will need, such as the months in a year. Dynamic arrays are used when you don’t know how big an array to create. These are resized dynamically as the need arises. Static arrays are easier to code and manipulate. To create the array for our calendar you would write:
Dim sCalendar(12) as string.
Using an Array in a Function
Using a static array in a function is much like any other variable, but to return an array from a function, that function must be declared correctly. The syntax for declaring a function to return an array is
Function FunctionName() as string.
The variable FunctionName will hold the array in the function and return it from the function. The function at this point doesn’t care whether the array is static or dynamic. The parenthesis after the function name are the critical part of the declaration. This designates an array is being passed.
Functions in VBA
An example of using static arrays in functions in VBA would be a simple function that asks the user to input a number from 1 to 12 and then returns the name of the month. This VBA function could be written to a macro in Excel that asks the user which month he wants to see data for and then uses a function built in to VBA, such as VLOOKUP, to find and display the information that corresponds to that month.