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

1

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

2If 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)

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

Dim myFloatArray() as Float
ReDim myFloatArray(10)

4

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

Dim myDateArray() as Date
ReDim myDateArray (10)

5

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

Dim myBooleanArray() as Boolean
ReDim myBooleanArray (10)

6

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

7Here 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”

8In 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

Loops – For Each
Multidimensional Arrays
Working With Arrays 2

As always, a site wouldn't be anything without its users so please feel free to comment! We welcome any comments you have on the material and any suggestions you may have for future content.


In our humble opinion, it is worth commenting just so you can play with Fun Captcha below!

Leave a Reply

Your email address will not be published. Required fields are marked *