Excel VBA Arrays: What They Contain and How to Use Them

VBA & macros · Updated November 21, 2026

An Excel VBA array contains an ordered set of elements that a macro can access by index; those elements can hold values such as numbers or text, or references to objects. Arrays let VBA work with many related items as one variable, but an array is not itself a worksheet range or a special Excel formula.

What an Array Contains

Think of an array as a row of labeled storage spaces. Each space is an element, and its index identifies where a value is stored. For example, an array of product names might contain one name per element, while a two-dimensional array might hold rows and columns of worksheet data. The array can live in memory while a macro runs, without placing anything in cells.

The kind of data an array can contain depends on how it is declared:

  • A typed array, such as Dim quantities() As Long, holds values of the declared type. This is useful when the data is consistently numeric, textual, or another specific type.
  • A Variant array, such as Dim data As Variant when assigned a range’s values, can contain values with different subtypes. A worksheet block might include numbers, text, dates, and empty cells.
  • An array can also hold object references, such as references to Excel worksheets. Object variables require VBA’s object-assignment rules, including Set when assigning an object reference.

When people ask “what does a VBA array contain?”, they may also mean “how do I test whether an array contains a particular item?” VBA does not provide a general-purpose Contains method for every array. For a simple one-dimensional array, you typically loop through its elements and compare each one with the value you are looking for.

How Indexes and Bounds Work

An array’s bounds are its lowest and highest valid indexes. VBA’s default lower bound is 0, unless the module uses Option Base 1; an array declaration can also state its bounds explicitly. Explicit bounds make the code’s indexing clear and avoid relying on a module-level default.

For example, this fixed-size array has three elements, at indexes 0, 1, and 2. The loop uses LBound and UBound so it follows the actual bounds instead of assuming where the array starts:

Sub ShowArrayItems()
    ' Save a copy of the workbook before running a macro.
    Dim cities(0 To 2) As String
    Dim i As Long

    cities(0) = "Oslo"
    cities(1) = "Lima"
    cities(2) = "Seoul"

    For i = LBound(cities) To UBound(cities)
        Debug.Print cities(i)
    Next i
End Sub

In the VBA editor, Debug.Print writes each value to the Immediate window; it does not put the values in worksheet cells. The Windows shortcut to open the Visual Basic Editor is Alt+F11. On Mac, Option+F11 may work, sometimes with Fn as well depending on the keyboard; Excel for the web does not run VBA macros.

Bounds matter because using an index outside the valid range causes a “Subscript out of range” error. A loop that starts at 1 will fail if the array begins at 0, and a loop that goes one item past UBound will also fail. Using LBound and UBound is a reliable habit, particularly when arrays come from different sources.

Fixed-Size and Dynamic Arrays

A fixed-size array has its bounds set in its declaration, as in Dim scores(1 To 5) As Long. It suits situations where the number of elements is known in advance. A dynamic array leaves the bounds open until the macro determines how many elements it needs:

Sub BuildDynamicArray()
    ' Save a copy of the workbook before running a macro.
    Dim scores() As Long
    Dim i As Long

    ReDim scores(1 To 5)

    For i = LBound(scores) To UBound(scores)
        scores(i) = i * 10
    Next i

    Debug.Print scores(3)
End Sub

This example prints 30. ReDim sets the bounds of a dynamic array while the procedure runs. Calling ReDim again without Preserve discards the array’s existing contents. ReDim Preserve retains existing values while resizing, but in a multidimensional array VBA permits changing only the upper bound of the last dimension with Preserve. That restriction can affect how a growing table-like array should be designed.

For a dynamic array, Erase releases its allocated storage. For a fixed-size numeric array, it resets the elements to zero; the reset value differs for other data types. If a dynamic array has not been allocated, trying to use its bounds or access an element can cause an error, so code should allocate it before looping through it.

One-Dimensional and Multidimensional Arrays

A one-dimensional array is a single sequence, like a list of names or IDs. A two-dimensional array has two indexes, often understood as rows and columns. VBA can also work with arrays that have more dimensions, though each extra index can make the structure harder to follow.

For example, a two-dimensional array might use values(row, column). The row and column indexes must both be within their respective bounds. This structure is useful for worksheet-shaped data, but it does not automatically know anything about worksheet addresses: an array’s first row is simply the first array index, not inherently worksheet row 1.

When writing a loop over a two-dimensional array, check the bounds for each dimension with the optional dimension argument to LBound and UBound. For example, LBound(values, 1) returns the lower bound of the first dimension, and UBound(values, 2) returns the upper bound of the second. This is especially helpful when the array’s dimensions are not known in advance.

Arrays and Worksheet Ranges

A common Excel VBA pattern is to read a block of cells into an array, process the values in memory, and write the result back. Reading a multi-cell range through its Value property returns a two-dimensional Variant array. Its first dimension corresponds to rows and its second to columns. A single-cell range is an important exception: its value is returned as a scalar rather than a two-dimensional array.

Sub ReadRangeIntoArray()
    ' Save a copy of the workbook before running a macro.
    Dim data As Variant

    data = ThisWorkbook.Worksheets("Sheet1").Range("A1:C3").Value
    Debug.Print data(1, 1)
End Sub

Assuming the range has its usual top-left cell at A1, data(1, 1) corresponds to A1, and data(3, 3) corresponds to C3. These indexes describe positions inside the returned array, not the worksheet’s actual row and column numbers. The code expects the named worksheet and range to exist.

For a multi-cell range, assigning a compatible two-dimensional array to a same-sized range can write many values in one operation. This often avoids repeated cell-by-cell interaction. Make sure the destination dimensions match the array’s row and column counts; otherwise, the assignment may fail or produce unintended results. Excel’s Value2 property is another option for transferring values and avoids the Currency and Date subtypes used by Value; choose based on whether those type distinctions matter to the macro.

Checking Whether an Array Contains a Value

For a one-dimensional array of strings, a straightforward membership check is a loop. This function compares text without regard to letter case, so “Pear” matches “pear.” It returns True as soon as it finds a match and False if the loop finishes without one.

Function ContainsText(ByRef items() As String, ByVal sought As String) As Boolean
    Dim i As Long

    For i = LBound(items) To UBound(items)
        If StrComp(items(i), sought, vbTextCompare) = 0 Then
            ContainsText = True
            Exit Function
        End If
    Next i
End Function

Sub CheckForText()
    ' Save a copy of the workbook before running a macro.
    Dim fruits(1 To 3) As String

    fruits(1) = "Apple"
    fruits(2) = "Pear"
    fruits(3) = "Plum"

    Debug.Print ContainsText(fruits, "pear")
End Sub

The example prints True. StrComp with vbTextCompare makes the comparison case-insensitive; use vbBinaryCompare when capitalization should matter. The function is deliberately limited to an allocated, one-dimensional string array. A Variant array, a multidimensional array, or an uninitialized dynamic array needs appropriate handling rather than simply passing it to this function. For very large arrays, the same linear search checks elements one by one, so its work increases with the number of elements.

Common Array Errors and Misconceptions

Many array errors come from assumptions about how data is indexed or typed. A few checks can make debugging easier:

  • Wrong starting index: Do not assume an array starts at 0 or 1. Check its bounds, or declare the bounds explicitly.
  • Confusing an array with a range: An array is stored in VBA memory; a range is a set of worksheet cells. Reading a range can create an array, but the two are not interchangeable in every expression.
  • Assuming every range returns an array: A single-cell range returns one value. Code that indexes that result as though it were a two-dimensional array can fail.
  • Resizing and losing values: A plain ReDim resets the existing array contents. Use Preserve when appropriate, keeping its last-dimension limitation in mind.
  • Mixing data types: A typed array may not accept a value that cannot be converted to its declared type. A Variant array is more flexible, but flexibility can make unexpected values easier to overlook.

Remember that VBA macros can change workbook data, and their actions cannot be reversed with Ctrl+Z. Save a copy before running a macro that edits or deletes worksheet content. Macro availability also depends on the Excel application and security settings; Excel for the web can open a macro-enabled workbook but cannot run its VBA code.

When to Use an Array

Arrays are useful when a macro needs to process a collection of values, especially when it can read a worksheet block once, perform calculations in memory, and write a result back in a batch. They can also keep related values organized without creating a separate variable for every item. A typed array is a good fit when the element type is known; a Variant array is convenient when values come directly from a mixed worksheet range.

An array is not always the best tool. If the data is naturally represented by worksheet cells and only a few values need to be handled, direct range references may be simpler. If a collection needs features such as adding or removing items more flexibly, VBA’s Collection object or a dictionary may suit the task better, though each has its own behavior and requirements. Choose based on how the data changes and how the code will use it, rather than using an array simply because one is available.

Frequently Asked Questions

Does a VBA array have to contain the same type of value?
A typed array contains elements of its declared type. A Variant array can hold values with different subtypes, which is useful for mixed worksheet data. The choice affects both flexibility and how clearly the code communicates the expected data.

How can I find the number of elements in an array?
For a one-dimensional allocated array, subtract LBound(array) from UBound(array) and add 1. For a multidimensional array, calculate the length of each dimension separately by supplying its dimension number to both functions.

Can a VBA array start at 1?
Yes. Declare explicit bounds, such as Dim items(1 To 5) As String, to make a 1-based array. The module-level Option Base 1 can also change the default lower bound for declarations that omit one, but explicit bounds are often easier to read.

Can I put an array directly into worksheet cells?
Yes, a compatible two-dimensional array can be assigned to a worksheet range of matching size. For a multi-cell range, reading its values also produces a two-dimensional array. A single-cell range behaves differently, so account for that case if the range size can vary.

Is there a built-in VBA function that checks whether an array contains an item?
There is no universal built-in Contains function for VBA arrays. A loop is a common solution for general arrays. The Filter function can search a one-dimensional array of strings, but it is not a general substitute for searching arrays of every type or shape.

Do VBA arrays work in Excel for the web?
VBA arrays are part of VBA code, and Excel for the web does not run VBA macros. Use desktop Excel to run VBA; workbook data and other supported features may still be accessible through the web version.

Related

Try it: the Column Letter to Number: aA is 27, XFD is 16,384. Converts both ways.

More in VBA & macros

Sources: Function behaviour and menu paths: Microsoft Support documentation for Excel. Method & sources

Spotted an error in this guide? Let us know.