A complete guide to learning Array in Excel VBA programming. If you have done programming in any language C, VB, Java, Python, etc., you must have definitely heard and known of Array. The array is a data structure that stores data in a sequence. Each of its elements can be accessed via its sequential index number.

Array in VBA is also no different. The only difference, between the array of any other language and VBA, is – Array in VBA not only stores sequential data but also stores the number of dimensions and number of elements in each dimension. We will discuss more in detail other differences and the common thing about array in VBA and other languages.

Like any other variable, you can declare one array by using the Dim statement. In the case of array declaration, your array variable should have open and close parenthesis (), unlike any other variable type. This is the sign that the declared variable is of array type and not scalar type.

Syntax:

Dim VariableName( [ subscript ] ) [ As type ]

Dim Dim is a keyword to define any Variable in Excel VBA. It is a short form of Dimension. It is used to Declare a variable in VBA.

VariableName any valid variable name you would like to declare.

subscript Subscript is used to define the upper bound limit of your array or both upper and lower bound limit. In VBA it is allowed to provide a lower bound limit as well. It is optional

Type This is the data type of elements inside the declared array, you are going to store. It is optional. The default type is Variant.

1. Example 1:
   To define a lower bound and upper bound, you need to use the word to in between two limits as shown in the below code. The first limit

   Dim Arr(5 to 10) As Integer
   

2. Example 2:
   Only with an upper bound limit.

   Dim Arr(10) As Integer
   

In the above example 2, Arr is an Array variable to store Integer type of data with upper limit of 10.

In the declaration there is no lower limit defined. Only the upper limit is defined.

In this case, what should be the lower limit? In such a case default lower limit, that is Zero – 0, is applied. Zero is the default lower limit of an array – if the lower limit is not already defined OR there is no Option Base statement set in the module where the array is declared. You can refer to the below picture where the default lower limit is Zero – 0



Default Lower Bound

What is Option Base Statement in Excel VBA Module?

This is a statement that can be defined at a module level. Using this statement, you can define the lower bound of all arrays declared in that module where there is no lower bound defined already.

Option Base 1

The above statement should be written at the top of the module. Note that the Option Base statement can set only 2 values 0 or 1. You can not define any other value here other than 0 or 1.

You can refer to the below image to see how the default lower bound is changed by using the Option Base statement in the same array.



Default Lower Bound in Array

Declare an Array Variable with upper and lower bound

It is possible to declare an array variable with both lower bound and upper bound specified. In this case, the Option base statement or default value of the lower limit will have no impact on the lower limit of the array.

Option Base 1

Sub arrayExample()
        Dim Arr(5 To 10) As Integer
        Debug.Print "Lower Bound: " & LBound(Arr)
End Sub

You can see in the below picture that after running the above code, the lower bound of the array is set to 5 which is defined in the array declaration.



Custom Lower Bound

Important

1. When lower bound limit is defined at the time of declaration then Option Base statement has not effect on the lower bound limit. This is the reason, it is a good VBA programming practise to define your Array variable with both lower and upper bound limit. This way even if some one modifies the Option Base value, your array will remain unaffected.

---

2. You must have noticed in other programming languages, the array has a default lower limit to 0 or 1 and you can not define or change it yourself. This is one of the significant differences between array in VBA and other languages.

Note that in Excel VBA, your lower and upper limit in the array can be negative numbers too. Excel VBA array would work perfectly for negative lower and upper bound limits.

Sub arrayExample()
        Dim counter  As Integer
        Dim Arr(-5 To 10) As Integer
        Debug.Print "Lower Bound: " & LBound(Arr)
        counter = 0
        For Each Item In Arr
            counter = counter + 1
            Debug.Print counter
        Next
End Sub

In above example, Arr variable can hold total 16 integers ( -5 to 0 to 10 = 10+5+1 = 16). Refer to the below image



Negative Upper and Lower Bound

The Upper bound of an array should always be bigger than the lower bound limit. Therefore, In the case of negative bounds, the upper bound integer value should be bigger than the lower limit because of simple maths. The bigger the negative number, the lower the value it has.

The following code will declare an array that can hold 6 integer values where the index will start from -15 and goes until -10. (-15, -14, -13, -12, -11, -10).

Sub arrayExample()
        Dim counter  As Integer
        Dim Arr(-15 To -10) As Integer
        Debug.Print "Lower Bound: " & LBound(Arr)
        counter = 0
        For Each Item In Arr
            counter = counter + 1
            Debug.Print counter
        Next
End Sub

Declaring an Array by Assigning an Array Values

If you have a variant type of variable then you can assign an Array value directly to the variable and that will be converted as an array variable.

Sub arrayTwo()
        Dim arr As Variant
        arr = Array(1, 2, 3, 4, 5)
        
        For Each Item In arr
            Debug.Print Item
        Next
End Sub

In the above example, you can see that there is no upper limit defined anywhere. After assigning the value, the array variable will set the upper bound limit as the total number of items inside the array values. You do not need to specify it specifically.

Declaring an Array without any upper bound limit

In Excel VBA, it is possible to declare an array variable without any lower or upper bound limit.

Sub arrayWithoutLimit()
        Dim arr() As Variant
        Debug.Print (UBound(arr))
End Sub

In the above example, you can see there is no upper or lower limit defined inside the parentheses. In such a case, if I try to get the upper or lower limit of the variable, then it will throw Subscript out of range error as shown in the below image. You can not assign any value to any of its indexes. It will throw the same error.



Subscript out of range


In the above example, if you notice, array variable is defined of type Variant.

To use this variable, you need to initialise the array variable first. Since this is a type Variant, initialisation can be done in two ways:

1. You can assign an existing array that already has some values or upper limit defined like shown below:


Initialize an Array by Assigning Value

In such a case you don’t need to define the upper limit specifically. The Upper Bound limit is set automatically by the total number of items stored in the array you assign it to.

Important note:

Above method is possible, only when the array type is defined as Variant. In case of any other type, above code will throw Type mismatch error.



Type Mismatch Error

2. The second method is to use ReDim keyword to size or resize a predefined array. Let’s take a look in detail : What is it and how does it work?

What is ReDim an Array

As the name suggests, ReDim is a keyword that is used to declare an Array variable again. For an already defined variable, if you use the Dim keyword again, VBA would not accept it but if you want to redefine your array variable during execution of the program, you can use the ReDim keyword to do so.

This is a very useful way of defining an array when the size of the array is not known at the start of the program. Or you want to change the size of the array at run time.

Based on the above definition, it is clear that ReDim can be done anytime during the program execution. That means, it is possible that the array already has some values inside. So the question is what happens to the existing data in the array? Let’s take a look.

Syntax:

ReDim [ Preserve ] VariableName ( subscript ) [ As type ]

ReDim: is the keyword to size or resize an already declared array variable. This is to redeclare an existing variable.
Preserve is an optional keyword. It is used to indicate what should be done with the existing data in an already defined array while re-declaring it.
VariableName name of variable which you want to redeclare it.
subscript Is the new upper and lower bound limit you want to change.

In the above syntax, the only part you are NOT familiar with is the keyword Preserve. Rest are same as Dim statement explained above.

Declaring an Array without Preserve

While re-defining your array variable Without Preserve keyword, you will lose all the existing data inside the array. This is the default behaviour.

Option Base 1

Sub Default()
    Dim arr() As Variant
    arr = Array(1, 2, 3, 4, 5)
    Debug.Print ("Before ReDim: " & UBound(arr))
    Debug.Print (readArray(arr))
    ReDim arr(8) As Variant
    Debug.Print (vbNewLine & vbNewLin & "After ReDim: " & UBound(arr))
    Debug.Print (readArray(arr))
End Sub

Function readArray(arr() As Variant)
    Dim msg
    For Each Item In arr
        msg = msg & Item & ", "
    Next
    Debug.Print ("Items: " & msg)
End Function

After running the above code you can see that array variable arr has all values stored and its upper bound limit is set to 5. But after you ReDim the same array variable to increase the size of the array to 8 without Preserver Keyword, then new Array has upper bound limit as 8 but all the 5 items stored are lost. You can refer to the below picture.



ReDim without Preserve

Declaring an Array with Preserve

It is used when you want to preserve the existing data in the array as it is. I have used the same piece of code as above. The only difference is the i have used Preserver keyword while ReDim the variable.

This time you can see all the 5 items are stored as it is in the new variable. Other 3 spots in array has its default value.

Sub Default()
    Dim arr() As Variant
    arr = Array(1, 2, 3, 4, 5)
    Debug.Print ("Before ReDim: " & UBound(arr))
    Debug.Print (readArray(arr))
    ReDim Preserve arr(4 To 8) As Variant
    Debug.Print (vbNewLine & vbNewLin & "After ReDim: " & UBound(arr))
    Debug.Print (readArray(arr))
End Sub

Function readArray(arr() As Variant)
    Dim msg
    For Each Item In arr
        msg = msg & Item & ", "
    Next
    Debug.Print ("Items: " & msg)
End Function



ReDim with Preserve Keyword

How ReDim works?

1. ReDim creates a new Array variable with the specified size mentioned in the Subscript.
2. Copies the existing data from old variable to new one [if Preserve keyword is specified else default values]
3. Remove the old variable and release the memory

Reducing the size of an Array with Preserve

It is possible to reduce the size of an existing array which has data in every cell. As I explained above – ReDim creates a new Array with the expected size and then if Preserve Keyword is specified, It will copy the data from old to new Variable and then release the old variable.

Data copy process happens from lower limit to upper bound limit sequentially. This process stops the moment it reaches to the upper bound limit of the new array defined.

Therefore, you will see only those items which can fit in the new size and remaining will be ignored. You refer the below image



Reduce the Size of an Array

Don’t miss this info

1. ReDim statement is used to size or resize an Array variable that is already formally defined in your program.
2. Preserve keyword can preserve all the existing data while re-sizing your array at run time.
3. You can not change the data type of an already declared array variable.
4. You can not change the dimension of an array using ReDim statement.
5. Using ReDim statement, you can size the upper and lower bound limit of an array.
6. In the case of using Preserve keyword, you can not resize the lower bound limit of the array.

Types of methods on Excel VBA Array

To know about all the methods used on Excel VBA array refer to my next article here.