Excel VBA Array Methods: Create, Resize, and Search

Excel VBA does not provide a single built-in “array methods” object: instead, it offers array functions and statements such as Array, Split, Join, LBound, UBound, ReDim, and Erase. Use them to create, inspect, resize, combine, and clear arrays; for Excel data, a common pattern is to read a range into a Variant array, process values in memory, then write the result back to a range.

What VBA means by an array

An array is a variable that holds a sequence or grid of values under one name. Rather than declaring separate variables for every item, you can store many related values and refer to each by an index. A one-dimensional array resembles a list; a two-dimensional array resembles rows and columns. Arrays are useful for processing data without repeatedly reading or writing individual worksheet cells.

VBA arrays are not the same thing as objects such as a Collection or Dictionary. They do not expose a general set of methods through dot notation. Instead, VBA supplies language statements and functions that operate on arrays. This distinction answers a common search question: there is no general-purpose VBA expression such as myArray.Add or myArray.Sort for a normal array.

Array indexes have lower and upper bounds. With the usual default setting, a manually declared array often begins at index 0, while arrays returned by some functions or populated from worksheet ranges may use different bounds. Check the actual bounds rather than assuming every array starts at zero or one.

Creating arrays with Array and Split

The Array function is a quick way to create a Variant containing an array of values. With names in a Variant variable, for example, the elements can be accessed using indexes. The lower bound for Array can be affected by the module’s Option Base setting, so use LBound when code needs to work regardless of the starting index.

Split is useful when a string contains values separated by a delimiter. It returns an array of substrings, which you can then loop over or inspect. For example, splitting a comma-separated string is convenient for simple input, but it is not a complete CSV parser: quoted fields can contain commas and require more careful handling.

Sub CreateAndInspectValues()
    Dim items As Variant
    Dim parts As Variant

    ' Create an array of values and split a delimited string.
    items = Array("red", "green", "blue")
    parts = Split("North|South|West", "|")

    Debug.Print items(LBound(items))
    Debug.Print parts(1)
End Sub

The example prints “red” and “South” under the standard behavior of Array with the default lower bound. The calls to LBound and UBound are preferable to hard-coding limits when looping, especially if the array’s origin is not certain.

Checking bounds and looping safely

LBound(array) returns the lowest valid index, and UBound(array) returns the highest. For a multidimensional array, supply the dimension number as the second argument, such as UBound(data, 2) for the second dimension. The number of elements in a one-dimensional array is its upper bound minus its lower bound plus one.

A conventional loop visits each valid index without assuming the array begins at zero. If an array might be empty or uninitialized, however, calling LBound or UBound can raise an error. Code that accepts optional or conditionally assigned arrays should check that the variable actually contains an array and handle an uninitialized array before querying its bounds.

Sub PrintArrayItems()
    Dim values As Variant
    Dim i As Long

    values = Split("apple,pear,plum", ",")

    ' Visit every valid element, whatever the lower bound.
    For i = LBound(values) To UBound(values)
        Debug.Print values(i)
    Next i
End Sub

IsArray can test whether a Variant contains an array, but it does not by itself prove that the array has been initialized or contains elements. Treat those as separate conditions when writing robust procedures, and avoid using an error-prone bounds check as though it were always safe.

Resizing dynamic arrays with ReDim

Use a dynamic array when its size is not known until the procedure runs. Declare it with empty parentheses, then allocate its bounds with ReDim. The bounds can use variables, which is useful when the number of records depends on a worksheet or user input.

ReDim normally discards the existing contents when it reallocates an array. Add Preserve when existing values must remain. With Preserve, VBA permits changing only the upper bound of the last dimension; you cannot change the number of dimensions or the lower bound. This restriction is especially important for two-dimensional arrays.

Sub GrowValues()
    Dim values() As Long

    ' Allocate three elements, indexed from 1 through 3.
    ReDim values(1 To 3)
    values(1) = 10
    values(2) = 20
    values(3) = 30

    ' Extend the upper bound while retaining existing values.
    ReDim Preserve values(1 To 4)
    values(4) = 40
End Sub

Repeatedly expanding an array by one element inside a large loop can be inefficient because preserving data may require copying it each time. If possible, determine the required size first and allocate once. If that is not possible, grow the array in larger increments or consider a Collection while the number of items is changing.

Joining, filtering, and clearing arrays

Join combines a one-dimensional array of strings into a single string, inserting a chosen delimiter between elements. It is the practical counterpart to Split for simple delimited text. Convert non-string values deliberately when needed; do not assume that every type will produce the intended text representation.

Filter returns a new array containing string elements that either include or exclude a search term. Its optional arguments control whether matches are included and whether comparison is binary or text-based. It is intended for string arrays, not a general numeric filtering operation. For numeric conditions, loop through the values and build a result array or use an appropriate worksheet operation.

Erase clears an array. For a dynamic array, it releases the allocated storage; for a fixed-size array, it resets the elements to their default values. If you need to reuse a dynamic array afterward, allocate it again with ReDim.

Use Join to assemble string elements with a separator.
Use Filter to select strings by whether they contain a search term.
Use Erase to release or reset array contents, depending on how the array was declared.

Working with worksheet ranges and arrays

For bulk Excel work, assigning a multi-cell range’s Value or Value2 property to a Variant is a fast way to load its values into memory. The result is a two-dimensional, one-based array: the first index represents the range’s row position and the second represents its column position. After processing the array, assigning it to a same-sized destination range writes the values back in one operation.

Sub CopyAndEditBlock()
    Dim ws As Worksheet
    Dim data As Variant
    Dim target As Range
    Dim r As Long

    Set ws = ThisWorkbook.Worksheets("Sheet1")
    Set target = ws.Range("E2:G10")

    ' Load a same-sized block into memory.
    data = ws.Range("A2:C10").Value2

    ' Change the first column's values in the in-memory array.
    For r = LBound(data, 1) To UBound(data, 1)
        If data(r, 1) = "Open" Then data(r, 1) = "Reviewed"
    Next r

    ' Write the 9-row by 3-column array to a matching destination.
    target.Value2 = data
End Sub

Here the source and destination are both nine rows by three columns, so their dimensions match. A single-cell range is an important exception: reading it into a Variant returns a scalar rather than a two-dimensional array. Code that accepts ranges of varying size needs to account for this case. Also choose the destination dimensions to match the array; a mismatch can cause an error or produce unintended results. Value2 avoids the Currency and Date subtypes used by Value, returning those cell values as numbers instead.

Before running a macro that changes worksheet data, save a copy of the workbook. VBA macros cannot be undone with Ctrl+Z, so a backup is the reliable way to recover from unintended changes.

Sorting and choosing an alternative

VBA does not provide a standard Sort method for ordinary arrays. If values are in worksheet cells, Excel’s sort features may be the simplest choice. In VBA, a procedure can also implement a sorting algorithm, but the right approach depends on array size, data type, and whether the original order needs to be retained. For large datasets, avoid repeatedly sorting or resizing through inefficient nested loops without considering performance.

Choose an array when you need indexed values and fast in-memory processing. A Collection can be more convenient when items are added or removed dynamically, while a Dictionary is useful when each item needs a key for lookup. These alternatives are objects with their own methods; they are not array functions. For Excel data analysis, worksheet features or formulas can be easier to maintain when the work is naturally expressed as a table operation.

Common array errors and fixes

Subscript out of range: An index falls outside the array bounds. Check the bounds with LBound and UBound, and remember that a range array is usually one-based.
Type mismatch: The code expects an array or a particular element type but receives a scalar or incompatible value. A one-cell range is a common scalar case.
Preserve limitation: A ReDim Preserve statement attempts to change a lower bound or a non-final dimension. Redesign the array layout or allocate a new array and copy the values.
Unexpected overwritten values: ReDim without Preserve discards previous contents. Use it only when the old data is no longer needed.
Wrong range output: The destination range does not have dimensions compatible with the array. Confirm the row and column counts before assigning values.

For debugging, inspect the array bounds and selected elements in the VBA editor’s Immediate window, which can be opened with Ctrl+G on Windows. On Mac, the VBA editor has an Immediate window as well, though available shortcuts can vary by keyboard and Excel version; use the editor’s View menu if a shortcut differs.

Frequently Asked Questions

Does VBA have an Array.Add method?
No. Ordinary VBA arrays do not have an Add method. Resize a dynamic array with ReDim Preserve, or use a Collection when adding items one at a time is more convenient.

How do I find the size of a VBA array?
For a one-dimensional array, calculate UBound(array) - LBound(array) + 1. For multidimensional arrays, calculate the bounds separately for each dimension. Make sure the array is initialized before calling the bound functions.

Can I resize a two-dimensional array with ReDim Preserve?
You can preserve existing values only while changing the upper bound of the last dimension. VBA does not allow Preserve to change the first dimension’s size or a lower bound. Copying values into a newly allocated array is one workaround.

Why does a range sometimes not become an array?
A multi-cell range assigned to a Variant produces a two-dimensional array, but a one-cell range produces a single value. Branch on the range size or normalize the single-cell case before using array indexes.

Can VBA sort an array directly?
There is no built-in general-purpose array Sort method. You can write or use a sorting routine, sort worksheet data with Excel, or choose another structure or approach appropriate to the task.

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.