Creating and Using One-Dimensional Arrays in VBA

Imagine a situation where you want to store a list of items in VBA code, such as a simple program to manage a shopping list. An inexperienced programmer may think about creating a new variable for each item to be stored in the list, such as itemOne = “Bread”, itemTwo = “Milk”, etc. While this would work, it’s incredibly inefficient and makes the data difficult to work with later in the procedure. A more experienced programmer will look at the data structure and determine that one-dimensional arrays provide the flexibility needed to efficiently work with lists.

Note: Finding arrays difficult to work with or annoyed by their constraints? Depending on your needs, VBA collections may be a better alternative.

Example File

The following macro-enabled Excel file contains all the example scripts presented in this post. Macros must be enabled upon opening.


This post within the Getting Started with VBA series begins the focus on Arrays in VBA.

A shopping list provides a great analogy for an incredibly useful tool available in VBA – the array. An array is a data structure consisting of a number of elements (strings, numeric values, objects) which can be accessed during any point of a procedure. One-dimensional arrays are a specific variant of an array which represents a simple list. Multi-dimensional arrays will be discussed in the next post.

Going back to the shopping list example, in VBA’s terms, we can store the entire shopping list in a simple one-dimensional array. For all intents and purposes, the sheet of paper would be the array (let’s call it, myShoppingList), while the items on the list would be the individual elements.

Array Terminology


Elements/values in the list are assigned a number known as a key. The key represents the elements placement in the array. For example, the first item is typically assigned 0 (zero), second is assigned 1, etc. An array key can start at any number, but standard arrays always usually start at 0 (zero).


You may also hear positions of elements in an array referred to by their array index — this is sometimes used interchangeably with a key, but contains one important difference. An array index refers to a position of a value in an array relative to the starting position. The first value of an array has an index of 0 (zero), the second is 1, etc. The important difference is that a key can start at any number, while an index will always refer to the first value as 0 (zero). For example, in the array declared as myArray(25 to 100), the index of myArray(25) is 0 (zero), while the key of myArray(25) is 25. Think about it this way: an array key is the number used to unlock that specific index’s value.


Elements of an array refer to specific string or numeric values of a specific array key. Generally, the primary purpose of an array is to store an element which can be retrieved at a future point in the procedure.

Array Bounds (Lower-bound and Upper-bound)

Another unique feature in VBA — the lower-bound and upper-bound (often refered to as lbound and ubound, or sometimes lower-limit and upper-limit) refer to the range of lowest key and highest key. In many other languages, array bounds refer to the array index, as the key does not necessarily need to be a number.

Static One-Dimensional Arrays

Creating a static array requires a process similar to building any other variable — it must be declared and assigned values. There are some differences in syntax:

  1. The size of the array must be provided in declaration. The format is as follows:

    Where 0 is the lower-bound, and 10 is the upper-bound. The lower bound identifies the first item in the array list, while the upper bound identifies the last time. In the myShoppingList example, an array that can hold up to 11 elements between keys 0 and 10.
  2. When assigning values to the array, the array key must be specified following the array name. This will place the value in that keys order within the array.

Here’s an example of a basic setup for the array myShoppingList.

Determine Array Lower and Upper Bounds

Two global VBA functions exist to determine the lower and upper bounds (key) of an array. These are the UBound() and LBound() functions. Syntax is as follows:

These two functions are useful for certain loop types, as well as determining if array values exist within a key range.

Printing Arrays

While creating a one-dimensional static arrays are straightforward, there are many ways to print arrays to return array values within Excel. Most of the methods discussed in this post focus on printing arrays on a spreadsheet, but arrays can also be returned through message boxes, or used to run other operations.

Explicit Print

The most basic way to print one-dimensional arrays is by explicitly calling each array element through its key. This is the least effective way to print an entire large array, but is an effective means of returning specific elements.

Join() Function

The Join function will convert an array into a string by joining array values. The function can take two arguments, with the second argument being optional.

The following script will print the shopping list array with commas as the delimiter.

If the second argument (delimiter) is omitted, the array will print with one space between each value. This is the simplest way to display one-dimensional arrays, but it does not permit the printing of elements in different cells.

For Each Loop

If the need requires each array element to be printed in different cells, one-dimensional arrays fit quite nicely within a For Each loop. As discussed in the previous post on For Each loops, each value within the array will be iterated through. The following subroutine provides a basic example of how to iterate through an array using a For Each loop.

For Loop

A basic For loop can be used to cycle through all or select elements in an array. Using the LBound() and UBound() functions, a procedure can automatically specify the start key and the end key for the loop to cycle through.

By placing the LBound() and UBound() functions within the For statement, the counter variable i will iterate from the start key to the end key. Note that 1 is added to the counter i when specifying the cell to print on. Given that the array key begins at 0 (zero), and Excel row counts begin at 1, it’s important to add 1 to the cell row coordinates to avoid an out-of-range error.

While this example returns the exact same result as its For Each loop counterpart, the For loop variant provides some added flexibility. If needed, the start and end count does not necessarily need to be the lower-bound or upper-bound of the array — any start and end point within the full array range will suffice. For example, the following for statement will only cycle through the 3rd through 5th index (returning “Chicken, Bananas, Nuts”):

However, it’s important to note that if a For loop does cycle outside of the range of an array, a debug error will appear. For example, iterating from keys 3 to 15 will not work, as the upper-bound of the array is 11.

Do While Loop

Do Loops can also be used to cycle through an array. To implement a Do loop, the counter value must be initiated prior to loop entry, and a counter increment statement must exist within the loop.

Using the Do While variation of a Do loop, the UBound() function can be used to test when a counter is within the bounds of an array.

Iterating through an array with a Do While loop is an effective way to efficiently combine other conditionals in the For statement. Similar to the For statement, the range of the array access can also be limited through statements such as this:

Do Until Loop

The Do Until variation is very similar to the Do While variation. Using the UBound() function, the Do While loop can iterate through an array until the counter is out of the array bounds.

While its functionality is near identical to the Do While loop, the Do Until loop may provide for flexibility with random events. For example, if a random number generator creates an integer value between 1 and 20, a Do Until loop can continue running until a certain random number is created. This can also be achieved through the Do While variation through a “Not” operator, but the Do Until variation will be more clear and concise.

Dynamic One-Dimensional Arrays

Static Vs. Dynamic Arrays

Up to this point, this post has used static arrays as examples. An array is considered “static” when its size is known and never changes during script execution. If the size of an array depends on other factors within the code or application (such as number of rows completed, or number of variables that are not empty), the array is considered dynamic.

Creating Dynamic One-Dimensional Arrays

A spreadsheet often lends itself to the need to create a dynamic array. When building robust Excel applications, you will certainly encounter a situations where arrays must be built from data a user enters into the spreadsheet. One of the main restrictions with static arrays is that the size of the array must be known–and hard coded–into the array declaration. With that, there’s no way to modify the size of a static array within a VBA script.

This is where the strength of the dynamic array comes in. Dynamic one-dimensional arrays are effectively static arrays that are resized during the execution of a VBA script. There are two approaches to working with dynamic arrays:

  1. Resizing an array prior to its creation (but after declaration).
  2. Resizing an array within a For loop.

Resizing an Array Prior to Creation

One limitation within VBA is the inability to assign values to variables during variable declaration. For example, if the following code were permitted, setting dynamic arrays would not be an issue.

Two things are occurring in this sample which are not permitted in VBA.

  1. There is an attempt to assign a value to integer i during its declaration. Unlike many other programming languages (including VBAs successor,, VBA does not permit values to be assigned to variables during declaration.
  2. There is an attempt to set an upper-bound of the string Array myArray during it’s initial declaration. Again, this is not permitted in VBA. Only a integer literal can be used to define both the lower and upper bound of an array within the declaration statement.

To get around this limitation, VBA offers the ability to resize arrays through the ReDim statement. Unlike the initial declaration, the ReDim statement can accept integer variables for the lower and upper bound of string values. The following script will work as intended.

Note that the integer variable i on line 10 is used to identify the upper-bound of array myArray on line 11 when it is resized. This allows the array to store 3 values, from key 0 to 2.

The remainder of the subroutine–similar to the examples earlier in this post–simply assigns values to each array key and prints values on the spreadsheet with a For Each loop.

Resizing an Array within a For Loop

The previously mentioned approach is effective if there’s a component of the procedure that determines the size of an array prior to the creation of an array. What if the size of an array truly is not known prior to creation? This can be a frequent occurrence in Excel, as a procedure may want to build an array based on a flexible-sized list that a user creates in a spreadsheet. Going back to the shopping list example — it’s very rare that all shopping lists will always contain 11 items. Let’s say that a user wants to build a shopping list to make french toast: Bread, Milk, and Eggs. The user does this by listing an item in each cell, starting in cell A1 and moving down.

One way to support this is to resize the array upper-bound within a loop. That is, for each iteration, the upper-bound increases by 1. One important catch to note, however, is that although the ReDim statement will allow modification to the size of an array, it will also remove the contents of the array. This is where the Preserve statement comes in. By including a ReDim Preserve statement, the size of an array can be increased while preserving contents.

The primary functions of of this script are to take a column of varying length, convert it to an array, then print the array as a string 2 rows underneath the list. To accomplish this, the following steps take place:

  1. A Do loop iterates through a column of cells until it reaches an empty value.
  2. For each iteration, the upper-bound of the array is increased by 1 through the ReDim statement, while the arrays contents are preserved using a Preserve statement.
  3. The value of the current cell (as identified by the row counter i + 1) is inserted into the corresponding array index.
  4. Upon exiting the loop, the array is converted into a string and printed 2 rows below the last item in the list.
A dynamic one-dimensional array created from column A, printed on row 5.
A dynamic one-dimensional array created from column A, printed on row 5.

Other Notes

  • Need to sort an array? See our post on using bubble sort with one-dimensional arrays and other VBA data structures.
  • While VBA does permit the lower-bound key to begin at any number, all subsequent keys must be in sequential order. If a key needs to be skipped, assign either an empty string or 0 (zero) to that key.
  • While not required, standard programming practice is to begin an array key at 0 (zero) so a key matches an index. A drawback of this–especially when creating an array from data in a spreadsheet–is that the counter must be adjusted to the proper row. For example, a spreadsheet does not have a row zero and there may be a column header. In that case, 2 would need to be added to the counter for each iteration.

Leave a Reply