Quite often your VBA code will contain lots of data items that all have the same type. An efficient way to handle these items is to declare a single entity called an array that allows you to refer to any item using the array’s name and an index that identifies the item’s position in the array.
Using an array makes the code much shorter because you don’t need to have variable names for each item. For example, suppose you need to create 535 lines, all of type LineDetails. Using conventional variables, you would need to declare each item with a unique name, such as Linei, Line2. all the way up to Line535, to be able to refer to them in code. Your program would be extremely lengthy, with 535 declarations and 535 statements to access them. Using an array is the way around this kind of inefficiency.
Creating an Array
To create an array, you simply declare it, by providing its name followed by the number of elements required, less one, in parentheses. You need to subtract one because the first element starts at zero. You may also want to include its type (to avoid the assignment of the default Variant type). The interpreter allocates your specified number of consecutive memory locations with enough bytes to hold the values of all the array’s elements. For example, in translating the following:
the interpreter would multiply 536 by the number of bytes required to store a variable of the LineDetails type and allocate that memory to the array. Since an array is really just a group of variables all having the same type, arrays can be declared as any one of the types available for variables.
In terms of processing the array, you need only write the code to process one element and place it inside a loop structure. As described later in this topic, the For loop is ideal for array processing.
Individual data items stored in an array are called elements.
Accessing Array Elements
Accessing individual elements in an array is achieved using an index value. The array name identifies the first memory location, and the index value represents the position in the list of the item required, so it’s easy for the interpreter to retrieve the data for any element in the array. The first element in the array by default has an index value of zero, so that the interpreter simply multiplies the index of the array element required, by the amount of memory used to contain one element. The result is added to the starting memory location identified by the array’s name.
Visual Basic arrays are different from the arraying of objects in AutoCAD, which duplicates objects and lays them out in some formation.
Visual Basic arrays can be declared using several different formats for the dimension. For example:
In these array declarations, the first elements in StartPoint and EndPoint have an index of zero by default, but the first element in AllLengths has an index of 1 because it has been explicitly declared that way. When you specify a first element that is nonzero, the interpreter takes this into account every time it works out the memory location for the array element required.
Option Base Statement
Some people prefer to have the first element in all their arrays start from 1 rather than 0, since it is more logical to think of 1 as being the first element, 2 the second element, and so on. To ensure that this happens, you can always include the 1 to… clause when you declare an array. Or you can place the Option Base statement in the General Declarations section of the Code window to specify a new starting value for the default index. The following statement ensures that all arrays declared in the same UserForm or module will automatically have their first element start at 1:
Multidimensional Arrays
Most arrays represent a list of values and so have only one dimension, but twodimensional arrays are also popular for representing things like tables and matrices. For example:
When this declaration is interpreted, the first dimension is considered to be the number of rows in the table; the second dimension is the number of columns. Three-dimensional arrays are also quite common:
In fact, Visual Basic allows up to 60 dimensions.
