Working With Arrays 1

In this blog post we introduce arrays and give you some examples of them in action.

You can think of an array as a row of boxes with a number on each, 0 to n.  When we first declare an array we must at least state its type and may also state its size (we can set the size later if we wish).

Firstly, we will create an array that will hold Integer types (whole numbers).

Dim myIntegerArray() as Integer

The opening and closing parenthesis after the variable name are the indicator that myIntegerArray is an array. At this point VBA is aware that myIntegerArray will be an array containing Integers but it doesn’t know how large we want it.

In this example we will set the size of the array when we declare it.  We will make a 10 integer array. Each Integer takes up 4 bytes.

Dim myIntegerArray(10) as Integer

If we don’t want to set the size of the array when we declare it we can omit the number indicating the total items it can hold and use a redim statement to set the size later on in the code.

Dim myStringArray() as String
ReDim myStringArray(10)

VBA initialises Strings to “”, an empty String.  Each character of a string takes up at least 2 bytes.

Dim myFloatArray() as Float
ReDim myFloatArray(10)

VBA initialises Floats to 0.0.  A float takes up 8 bytes.

Dim myDateArray() as Date
ReDim myDateArray (10)

VBA initialises Dates to 00:00:00. A date takes up 8 bytes.

Dim myBooleanArray() as Boolean
ReDim myBooleanArray (10)

VBA initialises Boolean values to False. A Boolean takes up 1 byte.

Referencing Arrays

Continuing with our row of boxes analogy, an array is referenced by its name and the box we wish to work with. For example, to get the value of box 0 we use myIntegerArray(0); to reference box 9 we use myIntegerArray(9).

myIntegerArray(0) = 10
myIntegerArray(1) = 36
myIntegerArray(2) = 77
myIntegerArray(4) = 87
myIntegerArray(5) = -10

Here are some useful Strings, 10 Top-Level Domains:

myStringArray (0) = “UK” : myStringArray (5) = “ME”
myStringArray (1) = “RU” : myStringArray (6) = “COM”
myStringArray (2) = “HR” : myStringArray (7) = “INFO”
myStringArray (3) = “DE” : myStringArray (8) = “NET”
myStringArray (4) = “FR” : myStringArray (9) = “EU”

In this blog post we have shown you how to initialize an array, determine the number of elements it possesses and work with different array data types.

Related Posts