Arrays

A standard structure for storing data in any programming language is an array. ‘Whereas individual variables can hold single entities, such as a number, date, or string, arrays can hold sets of related data. An array has a name, as does a variable, and the values stored in it can be accessed by an index.

For example, you could use the variable Salary to store a salary:

Salary = 34000

But what if you wanted to store the salaries of 16 employees? You could either declare 16 variables: Salary1, Salary2, up to Salary16, or you could declare an array with 16 elements.

Declaring Arrays

Unlike simple variables, arrays must be declared with the Dim statement followed by the name of the array and the maximum number of elements it can hold in parentheses, for example:

Dim Salary(15)

Using the salary example, Salary is the name of an array that holds 16 values the salaries of the 16 employees). Salary(O) is the first person’s salary, Salary(1) the second person’s salary, and so on. AU you have to do is remember who corresponds to each salary, but even this data can be handled by an array. To do this,  you’d declare another array of 16 elements as follows:

Dim Names(15)

and then assign values to the elements of both arrays:

This structure is more compact and more convenient than having to hardcode the names of employees and their salaries in variable:

Optionally, you could specify the type of the arrays element with the As keyword:

Dim Names(15) As String
Dim Salary(15) As Long

All elements in an array have the same data type. Of course, when the data type is Variant, the individual elements can contain different kinds of data (objects, strings, numbers, and so on).

Specifying Limits

By default, the first element of an array has index O. The number that appears in parentheses in the Dim statement is the array’s upper limit (or upper bound) and is one less than the array’s total capacity. In the previous example, the two arrays contained 16 elements each.

The array’s first element doesn’t need to be 0. You can specify the lower limit (or lower bound) explicitly in the Dim statement:

Dim Names(1 To 16) As String
Dim Salary(1 To 16) As long

The lower bound can have any other value, provided it’s smaller than the upper bound. The following declarations are valid although most arrays start at 0 or 1:

Dim Array1(10 To 20) As Double
Dim Array2(100 To 900) As long

Scroll to Top