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
Dim myStringArray() as String ReDim myStringArray(10)
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.
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
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”
Related PostsLoops – For Each
Working With Arrays 2