Excel VBA Array: The Complete Tutorial for Beginners

If you've been working with (or learning) Visual Basic for Applications, you're probably aware of the importance of having a solid knowledge of certain topics that influence data storage and manipulation. I've written detailed and comprehensive tutorials about several of these topics, including the following 2:

• Declare Variables In VBA For Excel: The How-To Guide.
  
  
• Excel VBA Data Types: The Complete Guide.

In this VBA tutorial, I focus on a topic that is closely related to the above:

Excel VBA arrays.

You'll be glad to know that, if you already have a basic knowledge of variables and data types (I cover these topics in the blog posts I link to above), you already have a good base knowledge that will help you understand and master the topic of arrays.

After all, arrays are (in the end) variables. Therefore, working with VBA arrays is (to a certain extent) very similar to working with regular variables. Arrays have, however, certain special characteristics and features that differ from those regular variables.

You might be wondering why should you bother learning about Excel VBA arrays if you already have a good knowledge of regular variables.

To put it simply:

You should learn to work with Excel VBA arrays because, among other benefits (as listed in Excel 2016 VBA and Macros), they:

• Allow you to group related data and, more generally, make data manipulation easier.
  
  
• Help you ease the process of getting information from data.
  
  
• Can make your code more readable and easier to maintain.
  
  
• Allow you to increase the speed of your VBA applications.

An indication of the power of VBA arrays is provided by author Richard Mansfield. In Mastering VBA for Microsoft Office 2016, Mansfield describes arrays as “kind of super variable[s]” or “variable[s] on steroids”.

My purpose with this VBA tutorial is to provide you with a comprehensive and detailed introduction to the topic of Excel VBA arrays that allows you to start using arrays now. The following table of contents lists the main sections of this blog post. Please feel free to use it to easily navigate to the topic of your interest.

This Excel VBA Array Tutorial is accompanied by an Excel workbook containing the data and macros I use in the examples below. You can get immediate free access to this example workbook by subscribing to the Power Spreadsheets Newsletter.

Now:

Even though I've already provided a basic description of arrays in the introduction above, let's start by diving deeper into the topic of…

What Is An Excel VBA Array

According to the Microsoft Dev Center, an array is:

A set of sequentially indexed elements having the same intrinsic data type. Each element of an array has a unique identifying index number. Changes made to one element of an array don't affect the other elements.

In Excel VBA Programming For Dummies, Excel guru John Walkenbach provides a broader definition of array by stating that:

An array is a group of variables that share a name.

These 2 definitions provide a good idea of the basic characteristics of a VBA array that you need to understand for purposes of this tutorial. These main characteristics of a VBA array are:

• For purposes of Visual Basic for Applications an array is, basically, a group of variables.
  
  
• The group of variables that make up an array have (i) the same name, and (ii) the same data type.
  
  
• The variables that compose an array are sequentially indexed. Therefore, each array element has a unique identifying index number.
  
  
• You can make changes to a particular array element without changing the other elements.
  
  At the same time, and as explained in Mastering VBA for Microsoft Office 2016, you can work with the whole array (all of its elements) at once

Let's move on to one of the most important topics of this tutorial:

How To Declare An Excel VBA Array

As explained in Excel VBA Programming For Dummies, you must always declare an array before using it.

From a general perspective, you declare arrays the same way you declare other variables. This is because an array is itself a variable.

As I explain in my separate VBA tutorial about declaring variables, you can generally use 4 keywords to declare a variable explicitly:

1. Dim.
   
   
2. Static.
   
   
3. Public.
   
   
4. Private.

You can generally use those same 4 statements to declare an array. Therefore, if you understand how to declare variables in VBA, you already have the basic knowledge that is required to declare arrays.

Another important similarity is that when declaring arrays, you can specify their data type (just as you do with variables).

At a very basic level, there's 1 main difference between declaring a regular (scalar) variable and an array:

When you declare an array, you usually have to specify the size of the array. When you declare a scalar variable (not an array), you don't have to specify its size.

As a consequence of the above, the 4 elements you must consider when building a statement to declare an array are the following:

• Element #1: The keyword you're using to declare the array.
  
  As I mention above, you can use 4 different keywords for these purposes: (i) Dim, (ii) Static, (iii) Public and (iv) Private.
  
  
• Element #2: The name of the array.
  
  
• Element #3: The size of the array.
  
  The following sections explain how this item differs depending on whether you're declaring a fixed or a dynamic Array.
  
  
• Element #4: The data type for the array.

Therefore, a statement that declares an array using the items above has (roughly) the following structure:

Declaring_Keyword Array_Name([Array_Size]) [As Data_Type]

Items within square brackets ([ ]) are optional. Within this statement:

• Item #1 (Declaring_Keyword) is 1 of the 4 keywords that you can use to declare an array (Dim, Static, Public or Private).
  
  
• Item #2 (Array_Name) is the name of the array.
  
  
• Item #3 (Array_Size) is the size of the array. This item is usually referred to as the array or dimension subscripts.
  
  The following sections focus on how you work with this particular item depending on whether you're declaring a fixed or a dynamic Array. Whether the array is fixed or dynamic determines whether the Array_Size is optional or mandatory.
  
  
• Item #4 (Data_Type): is the data type for the array.
  
  This item is optional.

Items #1 (Declaring_Keyword), #2 (Array_Name) and #4 (Data_Type) are substantially the same as those that you use when declaring variables in VBA.

Item #3 (Array_Size), as anticipated above, is the basic difference between declaring a scalar variable and an array. As a result of this root difference between variable and array declaration, there are 4 additional topics that you must consider when declaring an array (vs. when declaring a scalar variable):

• Topic #1: Array size and memory requirements.
  
  
• Topic #2: One-dimensional vs. multidimensional arrays.
  
  
• Topic #3: Fixed vs. dynamic arrays.
  
  
• Topic #4: Lower array bounds.

The following sections explain each of these topics. Let's start by taking a look at:

Array Size, Data Types And Memory Requirements

As I explain above, you can specify the data type of an array when you declare it. For general purposes, the explanations and comments about the topic that I provide in this tutorial are applicable.

As a consequence of this, whenever you don't declare the data type for an array, Visual Basic for Applications uses the default data type. This default data type is Variant. However, you may want to declare arrays using a different data type (other than Variant).

The main reason for this is that there's an inverse relationship between execution speed and the amount of bytes used by the relevant data. The more bytes your data uses, the slower the execution of your VBA application. In practice, this may not be a big issue, assuming that you're working on a computer with enough available memory. However, if you work with very large (particularly multidimensional) arrays, you may notice a difference in performance.

An exception to this rule is if you want the array to hold different data types. In such a case, the array data type must be Variant. This is because, as explained by Richard Mansfield in Mastering VBA for Microsoft Office 2016:

An array with the Variant data type can store multiple subtypes of data.

When deciding how to proceed, remember that different data types have different nominal allocation requirements. The following table provides a basic idea of how many bytes of memory are usually required by several of the main VBA data types:

The above values however, don't provide all the information you need to understand how much memory a particular array needs. More precisely:

When calculating how many bytes an array uses, you must generally consider the following 2 factors:

• Factor #1: The data type of the array.
  
  
• Factor #2: The number of elements in the array.

You can get an idea of the array size is determined by multiplying (i) the amount of bytes required by the relevant data type and (ii) the number of array elements. In mathematical terms:

Bytes used by array = (# of elements in array) x (bytes required by each element of array data type)

According to the Microsoft Dev Center (in the webpage I link to above), the maximum size of a VBA array depends on 2 main factors:

• Your operating system.
  
  
• Available memory.

As a general rule, execution is slower whenever you use an array whose size exceeds the RAM memory that's available in the system you're working with. This is because, as explained by Microsoft, “the data must be read from and written to disk”.

Now that you have a basic understanding of the relationship between array size, data types and memory requirements, let's move on to the topic of…

One-Dimensional And Multidimensional VBA Arrays

The Dim keyword is short for “Dimension”. As I quote in the post about declaring VBA variables, the only use of Dim in older BASIC versions was to declare the dimensions of an array.

VBA arrays can have up to 60 dimensions. However, in practice you'll usually work with (maximum) 2 or 3 dimensional arrays.

In order to understand what a dimension is, let's take a look at the simplest case: a one-dimensional array. One-dimensional arrays can be visualized as a single line of items. The following image shows an illustration of an 8-element one-dimensional array.

If you add an additional dimension, you have a two-dimensional array. You can think of such an array as a grid where the elements are arranged in rows and columns. The following image illustrates a two-dimensional array with 16 elements organized in 4 rows and 4 columns.

Notice how, in this particular case, I refer to each array element by using 2 numbers. The first number makes reference to the location within the first dimension (in this image the row) row where the element is located. The second number refers to the location in the second dimension (in this case, the column). I explain the topic of how to refer to array elements below.

If, once more, you add an additional dimension, you get a three-dimensional array. You can picture this array as a cube.

I can't provide an image to illustrate arrays of 4 or more dimensions. In any case, the purpose of the previous image is just to provide you a visual idea of what an array is.

Within the Visual Basic Editor, an array looks different. In the following sections, I provide several examples of statements that declare both one-dimensional and multidimensional arrays.

Fixed And Dynamic VBA Arrays

As I mention above, the basic difference you must be aware of when declaring an array (vs. a scalar variable) is that you usually specify the size of the array.

There are, however, 2 ways in which you can go about determining an array size:

• Option #1: You can specify the size of the array. This results in a fixed-size array (fixed array).
  
  
• Option #2: You can allow the size of the array to change as the relevant application runs. The result of this option is a dynamic array.

The usage of dynamic arrays is substantially the same as that of fixed arrays. The main difference between them is that fixed arrays are “un-resizable”.

As I explain above, the basic structure of the statement you can use to declare an array is as follows:

Declaring_Keyword Array_Name([Array_Size]) As [Data_Type]

I explain items #1 (Declaring_Keyword), #2 (Array_Name) and #4 (Data_Type) above.

I cover item #3 (Array_Size) in the following sections. Let's start by taking a look at…

How To Declare A Fixed One-Dimensional Excel VBA Array

As a general rule, you set the size of an array dimension (Array_Size in the array declaration statement above) through the following 3 items:

• Item #1: The first index number.
  
  
• Item #2: The keyword “To”.
  
  
• Item #3: The last index number.

In other words, the structure of the statement you use to declare an array can be rewritten as:

Declaring_Keyword Array_Name(First_Index_# To Last_Index_#) [As Data_Type]

This is perhaps the most basic array declaration you can make.

All the array declaration statements that I include as examples in this VBA tutorial use the Dim statement. Remember that, as I explain above, you can (theoretically) also use Private, Public or Static. The comments I make throughout this blog post generally apply to the cases where you're working with those other statements as well.

Let's take a look at 2 examples:

• Example #1: The following statement declares an array of 10 integers (elements 0 to 9):
  

  Dim myArray(0 To 9) As Integer

• Example #2: This statement declares an array of 20 strings (elements 0 to 19):
  

  Dim myArray(0 To 19) As String

Strictly speaking, you can set the size of an array by only specifying the upper index number. In other words, you can declare an array by omitting:

• Item #1: The first index number.
  
  
• Item #2: The keyword “To”.

Let's take a look at how the 2 statement examples above look like if I declare them without a lower index:

• Example #1: 10-integer array.
  

  Dim myArray(9) As Integer

• Example #2: Array composed of 20 strings.
  

  Dim myArray(19) As String

In such cases, Visual Basic for Applications assumes that the lower index number is 0 (by default) or 1 (if you use the Option Base 1 statement). I explain this topic below. For the moment, note the following 2 points:

• #1: The arrays declared by the statement samples above have 0 as lower index.
  
  
• #2: This way of declaring an array doesn't specify the number of array elements. It rather specifies the upper array bound.

As you start working with arrays, in any case, you may start realizing that including both an upper and a lower bound when declaring an array provides more flexibility than relying on the Option Base statement. Additionally, in certain cases, omitting the lower array bound may lead to bugs.

Due to, among others, these reasons, I personally prefer specifying both the upper and lower bound of an array over allowing the Option Base statement to determine the lower array boundary. VBA experts such as Chip Pearson (who I quote above) probably agree with this opinion. Chip's opinion, which you can find by following the link above, is that it's a…

Very poor programming practice to omit the lower bound and declare only the upper bound.

How To Declare A Fixed Multidimensional Excel VBA Array

The statement for declaring a fixed multidimensional array is very same to the statements that we've seen above to declare a one-dimensional array.

In practice, the main difference between declaring a one-dimensional and a multidimensional array is that, when declaring a multidimensional array, you separate the size of the dimensions with commas (,).

Therefore, the structure of the statement you use to declare an array with ## dimensions can be rewritten as:

Declaring_Keyword Array_Name(Dimension1_First_Index_# To Dimension1_Last_Index_#, Dimension2_First_Index_# To Dimension2_Last_Index_#, … , Dimension##_First_Index_# To Dimension##_Last_Index_#) [As Data_Type]

Let's take a look at some examples of fixed multidimensional array declarations:

• Example #1: The following statement declares a two-dimensional array with 25 integers. As I explain above, you can think of this array as a 5 x 5 grid.
  

  Dim myArray(1 To 5, 1 To 5) As Integer
  

  
  
  

• Example #2: This statement declares a three-dimensional array with 1,000 integers. Following the logic behind the illustrations above, you can picture this as a 10 x 10 x 10 cube.
  

  Dim myArray(1 To 10, 1 To 10, 1 To 10) As Integer

  
  
  

• Example #3: The following statement declares a four-dimensional array with 10,000 integers:
  

  Dim myArray(1 To 10, 1 To 10, 1 To 10, 1 To 10) As Integer

How To Declare And ReDim A Dynamic Excel VBA Array

As I explain above, the size of dynamic arrays changes as the relevant application runs. In other words (as explained in Excel VBA Programming for Dummies):

A dynamic array doesn’t have a preset number of elements.

The following are 2 of the main reasons to use dynamic arrays are:

• Reason #1: You don't know what is the required array size prior to execution.
  
  
• Reason #2: You want to optimize memory usage by, for example, allocating very large arrays for only short periods of time.

When declaring a dynamic array, you don't include a number of elements (Array_Size within the basic syntax above) in the declaration statement. This means that you leave the relevant set of parentheses empty.

The basic syntax of a statement declaring a dynamic array is, therefore, as follows:

Declaring_Keyword Array_Name() [As Data_Type]

The following sample statements declare dynamic arrays:

Dim myArray() As Integer

Dim myArray() As String

You can't, however, use a dynamic array until you have specified how many elements the dynamic array has. To do this, you use the ReDim statement.

Let's take a closer look at this topic:

ReDim Dynamic Array

The main purpose of the ReDim statement is to allow you to “reallocate storage space” for dynamic arrays. As I mention above, you use the ReDim statement to specify the number of elements that a dynamic array has.

You can't use the ReDim statement to resize a fixed array. As explained at the Microsoft Dev Center, whenever…

You try to redeclare a dimension for an array variable whose size was explicitly specified in a Private, Public, or Dim statement, an error occurs.

The basic syntax of the ReDim statement is, to a certain extent, similar to that you use when declaring an array (which I explain above). More precisely, the basic structure is as follows:

ReDim [Preserve] Array_Name(Array_Size) [As Data_Type]

Items within square brackets ([ ]) are optional.

This statement contains the following 5 items:

• Item #1: ReDim keyword.
  
  I explain the purpose of this keyword in the current section.
  
  
• Item #2: Preserve keyword.
  
  This item is optional. I explain its purpose and characteristics further below.
  
  
• Item #3: Array_Name.
  
  This is the name of the array you're working with. I explain this element in the section above that covers the topic of declaring VBA arrays.
  
  
• Item #4: Array_Size.
  
  This is the size of the array. This particular element is the one you're usually specifying when working with the ReDim statement.
  
  I explain how you deal with this item starting in this section.
  
  
• Item #5: As Data_Type.
  
  I introduce this particular item when explaining how to declare an array in a previous section. The comments I provide there generally apply to the cases where you're using the ReDim Statement.
  
  Despite the above, there are a couple of particular rules that apply when you're working with the ReDim statement. I explain these below.

As a general rule, you can use the ReDim statement as many times as you require in order to change the size of your dynamic arrays.

Despite the above, you can't always redimension an array. More precisely, whenever you pass a VBA array to a procedure by reference, you can't redimension that array within that procedure.

Let's take a look at some particularities of the ReDim statement, before moving on to a code example:

The ReDim Statement And Data Types

As a general rule, you can't use the ReDim statement to change an array data type that you've declared previously.

The basic exception to this rule are the cases where the array is contained in a Variant variable. In such cases, you can usually use item #5 above (As Data_Type] to change the data type.

Even in the cases where the array is contained in a Variant variable, you won't be able change the array data type if your ReDim statement uses the Preserve keyword. This is because, when you're using “Preserve”, data type changes are not allowed.

Independent of the above, note that in order to be able to resize an array contained in a Variant variable, you must declare the variable explicitly. The declaration statement must be before the statement that resized the array.

Now, let's go back to…

The Preserve Keyword

As explained in Excel 2016 VBA and Macros, the ReDim statement “reinitializes” the array you're working with. Therefore, as a general rule, ReDim erases any previously stored data within an array's elements. In other words, that old data is destroyed.

You can, however, avoid destroying all of the previously existing data by using the optional Preserve keyword within the ReDim statement. As implied by its name, you can use the Preserve keyword to preserve data within an array.

Using the Preserve keyword comes with some conditions attached. The following are the most relevant conditions you should be aware about:

• Condition #1: As explained in the previous section, data type changes aren't generally allowed.
  
  
• Condition #2: You can't change the number of dimensions of the array.
  
  
• Condition #3: You can only resize the last dimension of the array.
  
  
• Condition #4: You can only change the upper array bound.
  
  Seen from the opposite perspective, you can't change the lower bound of the array. If you try to do this, an error is caused.

Further to the above (although not strictly a condition), you may want to consider the fact that (as explained in Excel 2016 VBA and Macros), the Preserve keyword can slow down your VBA applications. This is the case, for example, when you have a large amount of data within a loop.

I provide some examples of ReDim statements that use the Preserve keyword below.

Reducing The Size Of A Dynamic Array

As a general rule, you can use the ReDim statement to reduce the size of a dynamic array.

In such cases, the data stored within the deleted elements is wiped out. This is the case even if you're using the Preserve keyword.

ReDim Statement To Declare Variables

From a theoretical point of view, the ReDim statement can be used to declare variables. This is the case if ReDim makes reference to a variable that doesn't exist at the module or procedure level.

Further to the above, as explained by Microsoft:

If another variable with the same name is created later, even in a wider scope, ReDim will refer to the later variable and won't necessarily cause a compilation error, even if Option Explicit is in effect.

Due to the problems/conflicts that may arise as a consequence of the above, it's advisable to avoid using the ReDim statement to declare variables. In other words, limit the use of the ReDim statement to the situations where you're redimensioning an array.

ReDim Statement Code Example

As explained by Excel authority John Walkenbach in Excel VBA Programming for Dummies, whenever you're working with a dynamic array, it's usual that…

The number of elements in the array is determined while your code is running.

Therefore, for purposes of the example below, let's assume the following 2 things:

• Assumption #1: You're working in a particular procedure that includes a variable named “dimensionSize”.
  
  
• Assumption #2: The dimensionSize variable contains a certain value.

If that's the case, the following sample statement uses the ReDim statement to change the size of the array:

ReDim myArray (1 to dimensionSize)

When working with a multidimensional array, you separate the size of the different dimensions with a comma (,). The logic is basically the same that I explain above for declaring a multidimensional array.

The following 3 statements are examples of how to use the ReDim statement when working with multidimensional arrays:

• Example #1: If you have 2 variables containing values (dimensionSize1 and dimensionSize 2):
  

  ReDim myArray (1 to dimensionSize1, 1 to dimensionSize2)

• Example #2: If you have 3 variables called “dimensionSize1” through “dimensionSize3”:
  

  ReDim myArray (1 to dimensionSize1, 1 to dimensionSize2, 1 to dimensionSize3)

• Example #3: If you have 4 variables (dimensionSize1 through dimensionSize4):
  

  ReDim myArray (1 to dimensionSize1, 1 to dimensionSize2, 1 to dimensionSize3, 1 to dimensionSize4)

Finally, the following statements are samples of how you can use the Preserve keyword within the ReDim statement to preserve data within the relevant array. I introduce the Preserve keyword above.

• Example #1: This example assumes that (i) the array (myArray) has 5 elements, and (ii) the dimensionSize variable has a value of 12. If that's the case, the following statement preserves the data stored within the first 5 elements, and adds 7 new elements to the array (6 to 12):
  

  ReDim Preserve myArray (1 to dimensionSize)

• Example #2: This example assumes that (i) myArray has 2 dimensions and 9 elements (3 x 3), and (ii) the dimensionSize variable has a value of 5. If this is the case, the following statement preserves the data stored within the array, and adds 6 new elements. The array size after this statement is 3 x 5.
  

  ReDim Preserve myArray (1 to 3, 1 to dimensionSize)

• Example #3: This example assumes that (i) myArray has 3 dimensions and 8 elements (2 x 2 x 2), and (ii) dimensionSize's value is equal to 5. In such a case, the following statements preserves the data stored in myArray and adds 12 elements. The resulting array size is 2 x 2 x 5.
  

  ReDim Preserve myArray (1 to 2, 1 to 2, 1 to dimensionSize)

When looking at these last 3 examples, remember that (as I mention above) you can only resize the last dimension of an array when using the Preserve keyword.

In example #1, you only have 1 dimension. That dimension is also the last dimension. In examples #2 and #3 you have more than 1 dimension. Therefore, in those 2 cases, I only change the size of the last dimension.

Lower Array Bounds And The Option Base Statement

As I explain above, it's not mandatory to express the lower bound of an array.

Doing so doesn't mean that there's no lower bound. As explained by Excel authorities Bill Jelen (Mr. Excel) and Tracy Syrstad in Excel 2016 VBA and Macros, when you do this you're actually “allowing” the Option Base statement to determine the lower bound.

When you do this, there are 2 options for array indexing:

• Option #1: The array is indexed from 0.
  
  
• Option #2: The array is indexed from 1.

You can determine which of the above 2 options applies to a particular array by using the Option Base statement.

The default indexing option is Option Base 0. Therefore, if you don't specify the base when declaring an array, it begins at 0. This is the most common standard in programming.

In order to implement option #2 and have the array be indexed from 1, you use the Option Base statement. To do this, enter the following statement at the top of the module you're working in and before any procedures:

Option Base 1

The following image shows the top of a particular module that includes both the Option Explicit statement and the Option Base 1 statement within the General Declarations section before the first procedure (One_Dimensional_Array_Declaration_1):

The Option Base statement:

• Can only appear once per module.
  
  
• Must “precede array declarations that include dimensions”.
  
  
• Only changes the lower bound of arrays within the particular module that contains the Option Base statement.

You can't use the Option Base statement to change the lower bound of arrays created using the ParamArray keyword. You may work with the ParamArray keyword to, among others, create User-Defined Functions that take an unlimited (up to 255) number of arguments.

Let's go back to the examples of array declaration statements that I provide in the previous sections about fixed one-dimensional and fixed multidimensional arrays. The following examples assume that the relevant statement is within a module that contains the “Option Base 1” statement, as I explain above.

• Example #1: Both of the following statements declare an array of 10 integers (elements 1 to 10):
  

  Dim myArray(1 To 10) As Integer

  Dim myArray(10) As Integer
  

• Example #2: These statements declare an array with 20 strings (elements 1 to 20):
  

  Dim myArray(1 To 20) As String

  Dim myArray(20) As String

• Example #3: The following statements resize an array with a number of elements equal to the value held by the dimensionSize variable (elements 1 to dimensionSize):
  

  ReDim myArray (1 to dimensionSize)

  ReDim myArray (dimensionSize)

How To Refer To An Array Element

The previous sections of this Excel tutorial focus on what an array is and how you can declare different types of arrays. This section explains how you can refer to a particular element within an array.

As a general rule, in order to identify a particular element of an array, you use its index number(s) for each of the array dimensions. Therefore, the basic syntax of the statement you use varies depending on how many dimensions the relevant array has. The number of index number(s) you must include in the reference is equal to that number of dimensions.

The following sections start by explaining how you can refer to an element within a one-dimensional array (the most basic case). I later show how the syntax varies (slightly) when you're referring to elements within multidimensional arrays.

How To Refer To An Element In A One-Dimensional Array

As explained in Excel 2016 Power Programming with VBA, you generally refer to an array element by using 2 items:

• Item #1: The name of the array.
  
  
• Item #2: A particular index or subscript number.

The basic structure of the statement you can use to refer to an element within a one-dimensional array is as follows:

Array_Name(Element_Index_Number)

Let's take a look at some examples:

The sample macro displayed in the following image (One_Dimensional_Array_Declaration_1), does the following 3 things:

1. Declares an array of 10 integers (1 to 10).
   
   This statement follows the rules to declare a VBA array that I explain above.
   
   
2. Initializes each array element and assigns the values 1 through 10 to each of these elements.
   
   This particular VBA tutorial doesn't focus on the topic of the different ways you can assign values to the elements of an array. Even though there are other ways to fill an array, the examples I use throughout this blog post individually assign a value to each array element.
   
   I may dive deeper into the topic of array filling in a future blog post. If you want to receive an email whenever I publish new content in Power Spreadsheets, make sure to register for our Newsletter now by entering your email address below:
   
   
3. Uses the MsgBox VBA function to display a dialog box with the value of the first element of the array (myArray(1)).

Notice how all of the element references within items #2 and #3 use the basic statement structure that I introduce above:

The effect of executing this sample macro is that Excel displays the following dialog box. Notice that the value within the message of the dialog is equal to that assigned to the first element of the array in the Sub procedure above.

The following section takes this a step further and shows you…

How To Refer To An Element In a Multidimensional Array

The logic to refer to an element within a multidimensional array, is similar. The main rules for making reference to such an element are the following:

• Rule #1: Include the relevant index number for each dimension of the array.
  
  
• Rule #2: Separate the different index numbers with commas (,).

As a consequence of these rules, the basic structure of a reference to an element within an array with ## dimensions is as follows:

Array_Name(Dimension1_Element_Index_Number, Dimension2_Element_Index_Number, …, Dimension##_Element_Index_Number)

The following sample macro (Two_Dimensional_Array_Declaration) does the same 3 things as the Sub procedure example in the previous section. More precisely, it:

1. Declares an array of 9 integers (3 x 3).
   
   I explain the syntax of this statement in a previous section of this tutorial.
   
   
2. Initializes each array element and assigns values 1 through 9 to them.
   
   
3. Displays a dialog box with the value of the first array element (1, 1).

The following image shows the dialog box that Excel displays when I execute this macro:

Notice how, as expected, the displayed value matches with that assigned to the first array element within the VBA code:

Let's take a look at a final example macro (Three_Dimensional_Array_Declaration), where the macro works with a three-dimensional array. This sample macro does the following:

1. Declares an array of 8 integers (2 x 2 x 2).
   
   
2. Initializes each array element and assigns values 1 through 8 to each element.
   
   
3. Displays a message box with the value assigned to the first array element (1, 1, 1).

The dialog box that Excel displays when I execute the macro is (as expected) substantially the same as that in the previous examples:

Notice how, once again, the value displayed by Excel matches that assigned to the first array element by the VBA code:

How To Erase The Data In An Array (Or The Array Itself)

As I explain above the ReDim statement generally wipes out the data within the array you're working with.

You can, however, erase the data stored within an array with a different statement: Erase.

The main purpose of the Erase statement is to:

• Reinitialize the elements of a fixed array.
  
  The Erase statement doesn't recover memory when working with a fixed array. It generally sets the array elements to the default values of the relevant data type.
  
  
• Release “dynamic-array storage space”.
  
  In the case of dynamic arrays, therefore, the Erase statement frees the memory that the array was using. This, basically, erases the dynamic array completely. Therefore, before you're able to use the array in the future, you must specify how many elements it has by using the ReDim statement. I explain how to do this above.

The basic syntax of the Erase statement is quite straightforward:

Erase arraylist

For these purposes, arraylist is the list of array(s) to be erased. If you're erasing more than 1 array, separate them with a comma (,).

The following sample statements show how you can use the Erase statement:

• Example #1: This statement erases the data stored within myArray (if it's fixed) or myArray itself (if it's dynamic):
  

  Erase myArray

• Example #2: The following statement erases the data stored within both myArray1 and myArray2 (if they're fixed) or the arrays themselves (if they're dynamic):
  

  Erase myArray1, myArray2

Conclusion

After reading this VBA tutorial, you probably have a very solid understanding of the topic of Excel VBA arrays. Among other things, you know:

• What are VBA arrays, and why they're useful.
  
  
• What are one-dimensional and multidimensional VBA arrays. This includes their commonalities and differences.
  
  
• What are fixed and dynamic arrays, how they're similar and how they differ.
  
  
• The relationship between array size, data types and memory requirements, and why this relationship is important.
  
  
• How can you declare an array depending on whether it's fixed (one-dimensional or multidimensional) or dynamic.
  
  
• How are lower array bounds determined if you don't explicitly specify them, and what is the Option Base statement.
  
  
• How to use the Erase statement when working with arrays.

This Excel VBA Array Tutorial is accompanied by an Excel workbook containing the data and macros I use in the examples above. You can get immediate free access to this example workbook by subscribing to the Power Spreadsheets Newsletter.

Books Referenced In This Excel Tutorial

• Alexander, Michael and Kusleika, Dick (2016). Excel 2016 Power Programming with VBA. Indianapolis, IN: John Wiley & Sons Inc.
• Jelen, Bill and Syrstad, Tracy (2015). Excel 2016 VBA and Macros. United States of America: Pearson Education, Inc.
• Mansfield, Richard (2016). Mastering VBA for Microsoft Office 2016. Indianapolis, IN: John Wiley & Sons Inc.
• Walkenbach, John (2015). Excel VBA Programming for Dummies. Hoboken, NJ: John Wiley & Sons Inc.