Return to VBA Code Examples

VBA – Declare (Dim), Create, and Initialize Array Variable

This tutorial will demonstrate how to Declare (Dim), Create, and Initialize Array Variables in VBA

What is a VBA Array Variable?

A VBA array variable can be thought of as a group of variables, stored under the same name and having the same data type. An array can store text, numbers, or objects.

You refer to an element in an array using its index number.

You can declare an array variable in the same way as you would declare any other variable by using the Dim, Static, Public or Private keyword.

Static Arrays

There are 2 types of arrays – Static and Dynamic. A Static array is declared with it size specified when you initially declare the array. It is also called a Fixed Array.

The above array is declared using the Dim statement at a Procedure or Module level, and the size of the array is 5 as we have not declared the LBound value of the array.

No, that’s not a typo! The Array size is 5, despite entering 4 into the array. This is because array indexes automatically start at zero.

Array Indexes

Array indexes automatically begin at zero unless Option Base 1 is declared at the top of your code module.

vba option base

If Option Base 1 is declared then arrays will automatically start at 1.

However, I find declaring variables in such a way to be problematic. Code reviewers may not be aware that arrays start at zero or of the Option Base 1 declaration

Instead, I prefer to explicitly declare the start and end positions of arrays:

Notice that when you do so, you can start the array at any number (not just 1 or 0).

Dynamic Arrays

A Dynamic Array variable is an array whose size can be changed at runtime. You declare dynamic variables without a size.

You can use the ReDim statement to specify the size of the Array after the array has been created.

You can resize a dynamic array at any time. However, when using the ReDim Statement, all existing values are erased. To preserve existing array values, use ReDim Preserve instead.

You can declare a dynamic array at a Procedure, Module or Global level, but you can only use the ReDim statement within a Procedure.

Variant Arrays

Variant arrays are Dynamic arrays that are easier to work with.

Notice that you don’t need to specify the data type (it is assumed to be variant) or the array size.

As we’ll see below, you can initialize variant arrays using the Array Function (no need to resize arrays first)!

Declare Module & Public Arrays

As shown above, arrays can be declared within procedures, for use within that procedure:

But they can also be declared at the Module or Global level.

In this example, the array variable can be called anywhere within this code module. Instead, you can declare a public array that can be used throughout your VBA Project (see next section).

VBA Coding Made Easy

Stop searching for VBA code online. Learn more about AutoMacro - A VBA Code Builder that allows beginners to code procedures from scratch with minimal coding knowledge and with many time-saving features for all users! automacro

Learn More!!

Declaring a Public Array

You declare a Public Static array as you would declare a Public Variable.

This declaration would need to go at the top of your module, below Option Explicit.  It could then be used throughout your VBA project in any module or procedure.

vba array public declare

If you declare the Array at the top of the module, but with the Dim keyword, then the use of that array is restricted to that individual module.  Trying to use the Array in a separate module would result in an error.

vba array public error

Initialize Arrays

You can assign values to a static array in the following way.

If you run the above procedure,  the value of 20 would be displayed in the immediate window.

vba static array example

You can also assign the values to a Dynamic Array in the same fashion

Array Function

However, with the variant array only, you can use the Array Function which may be easier than using the standard method.

VBA Programming | Code Generator does work for you!

Populate Array with Loop

You can also populate arrays by looping through a range of cells in Excel

Re-initialize Arrays

You can re-initialize your array at any stage of your code, but you will then lose the original value contained in that position in your array.

In the example above, the Static array will keep all the values, except the value in position 2 – that value will change to 200.

Using ReDim

If you are using a Dynamic Array, the ReDim statement is used to set the size of your Array.  You can use the ReDim statement subsequently in your code to change the size of the Array as many times as you need to.   The line of code below will re-initialize the intA Array to have a size of 2  (Remember – an Array index begins at 0!)

So the code including the ReDim statement would look like the example below.

If  you run the above procedure,  the value of 5 would be displayed in the immediate window, and then a value of 8 would be displayed once we have resized the array using ReDim and repopulated it.  However, as we have not populated IntA(2), and we did not use Re-Dim Preserve, the value in that position in the array will be removed and both position 3 and 4 in the array will be zero.

Using ReDim Preserve

If we use ReDim Preserve, it will keep the original values contained in the array.

In both message boxes displayed above, the number 9 will appear as the ReDim Preserve statement kept that value in that position.