VBA Array Command: Syntax, Examples, and Practical Uses

VBA & macros · Updated November 18, 2026

In VBA, the “array command” most people mean is the Array function: it creates an array of values, which you can store in a Variant and read by index. For example, cities = Array("Oslo", "Lima", "Seoul") creates a one-dimensional array; use LBound and UBound to find its valid index range.

What the VBA Array Function Does

Array is a built-in VBA function, not a separate command or a declaration statement. It packages the values you pass into a Variant that contains an array. That makes it convenient for a short list of values you want to loop through, compare, or pass to another procedure.

For example, a macro might keep a small set of department names in an array rather than writing a separate line of code for each name. Arrays are useful when a procedure needs to process several related values using the same logic. They can also reduce repeated worksheet reads when values are collected into memory and handled together.

The values do not have to share one data type: an array made with Array can contain strings, numbers, dates, or other values. Each element is held as a Variant value, so this flexibility is useful for mixed data but may be less suitable than a specifically typed array when you need a large, uniform collection.

Syntax and a Basic Example

The syntax is Array(arglist), where arglist is a comma-separated list of values. In ordinary use, assign the result to a variable declared as Variant. The following procedure prints three city names in the VBA Immediate window:

Sub PrintCities()
    Dim cities As Variant
    Dim i As Long

    ' Create an array and print each value in the Immediate window.
    cities = Array("Oslo", "Lima", "Seoul")

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

Open the Immediate window in the Visual Basic Editor with Ctrl+G on Windows to see the output. On a Mac, the Visual Basic Editor is also available in Excel, though keyboard shortcuts can differ by keyboard and version; use the editor’s View menu if the shortcut does not work. Before running a macro that changes a workbook, save a copy: VBA macros cannot be undone with Ctrl+Z.

The function call uses parentheses because it returns a value that is being assigned. A common mistake is to declare the variable as though Array were a data type. It is not: use Dim cities As Variant, then assign the result of the function.

Indexes, Lower Bounds, and Looping

An array’s index is the number used to address one of its elements. In the example, the first city is accessed as cities(0) in the usual default setup, followed by cities(1) and cities(2). However, do not rely on a presumed lower bound in reusable code. A module’s Option Base setting can affect the lower bound of an unqualified Array result. Check the actual bounds with LBound and UBound.

LBound(array) returns the smallest valid index, and UBound(array) returns the largest. These functions matter because an array with three values does not necessarily have indexes 1 through 3. A loop from zero to the number of elements can therefore skip an item or attempt an invalid index if the lower bound differs from what the code assumes.

For a multidimensional array, specify the dimension as the second argument, such as UBound(data, 2) for the upper bound of dimension two. In practice, using the bounds functions makes loops more resilient when code receives arrays created elsewhere, including arrays returned by other procedures or functions.

When to Use Array, Split, or a Typed Array

Choose the array approach that matches how the data is created and used. Array is suited to a short, explicit list written into the code. Split is often more convenient when values start as one delimited string, while a typed dynamic array gives you control over the element type and size.

  • Use Array for a small, known list such as status labels or a few test values.
  • Use Split to turn text such as "red,green,blue" into an array of substrings. The delimiter can be supplied as an argument.
  • Use a typed array such as Dim scores() As Long when the elements should all be numbers and you want a dynamic array whose size you set with ReDim.
  • Use a Collection or Dictionary when you need collection behavior such as adding items conveniently, looking up items by key, or managing a changing set of objects.

Array creates a one-dimensional array. Writing nested calls, such as Array(Array(1, 2), Array(3, 4)), creates an outer array whose elements are themselves arrays; it does not create a conventional two-dimensional grid. For row-and-column data, declare or resize a true two-dimensional array instead.

Resizing Arrays and Working with Two Dimensions

A dynamic array is declared with empty parentheses and given bounds later with ReDim. For example, a program that starts with three scores and may add another can use a typed dynamic array. ReDim Preserve retains existing values when the array is resized, subject to VBA’s rules: for a multidimensional array, only the final dimension can be resized while preserving data.

Sub ExtendScores()
    Dim scores() As Long

    ' Allocate three values, then grow the one-dimensional array.
    ReDim scores(0 To 2)
    scores(0) = 72
    scores(1) = 85
    scores(2) = 91

    ReDim Preserve scores(0 To 3)
    scores(3) = 88
End Sub

For a fixed two-dimensional structure, declare both dimensions explicitly, for example Dim table(1 To 3, 1 To 2) As Variant. The first index identifies the row-like position and the second identifies the column-like position if you choose to use that convention. VBA does not require those meanings, so keep the same convention throughout the procedure and document it when the array is passed between routines.

Use ReDim only when the array is dynamic. A fixed-size declaration already determines its bounds, and trying to resize a fixed array is not the right approach. Also note that repeatedly resizing an array inside a large loop can be inefficient; when the expected size is known, allocate enough space in advance or collect data using a structure designed for incremental additions.

Using Arrays with Excel Worksheet Data

Arrays are useful in Excel automation because VBA can work with a block of values in memory instead of reading and writing individual cells repeatedly. A range containing multiple rows and columns, when read through its Value property, is typically represented by a two-dimensional Variant array. The first dimension corresponds to worksheet rows and the second to columns.

That is different from the one-dimensional array returned by Array. When sending a two-dimensional array back to a multi-cell range, the dimensions should match the target range. A one-dimensional list should not be assumed to fill a vertical range correctly merely because it has the same number of items. You can build a two-dimensional output array or use a worksheet operation appropriate to the data shape.

As a practical example, code can read a block into a Variant array, loop over its row and column indexes, calculate or validate values in memory, and assign the completed array back to a range of the same size. This pattern can be faster than cell-by-cell processing for larger blocks, but the code must account for the range’s dimensions and any empty or error-valued cells.

Excel for Microsoft 365 on Windows supports VBA through the desktop application and Visual Basic Editor. Excel for Mac also supports VBA, although some controls, references, and operating-system-specific features differ. Excel for the web does not run VBA macros; a workbook containing VBA must be opened in a desktop version of Excel to execute them.

Common Errors and How to Avoid Them

Array bugs are often indexing or type issues rather than problems with the Array function itself. Check the variable declaration, bounds, and expected shape before debugging the loop that processes the values.

  • “Subscript out of range”: The requested index is outside the array’s bounds. Use LBound and UBound rather than assuming the array starts at zero or one.
  • Variable declared with the wrong type: A variable receiving the result of Array should normally be a Variant. Array is not a type that can be used in an As declaration.
  • Unexpected number of dimensions: An array made by Array is one-dimensional. If code expects a row-and-column structure, create a two-dimensional array instead.
  • Uninitialized dynamic array: Calling LBound or UBound on a dynamic array that has not been allocated with ReDim can raise an error. Track whether allocation has occurred before asking for its bounds.
  • Unexpected worksheet output: Confirm that the array’s dimensions match the destination range. A range read from a sheet and a list created with Array do not necessarily have the same shape.

Also distinguish an array containing an empty string from an array with no elements. An empty string is still a value at an index. If a procedure can receive either an unallocated array or a populated array, define and check that condition explicitly rather than assuming that a blank-looking value means there are no array elements.

Frequently Asked Questions

What is the VBA array command?
The commonly intended term is the VBA Array function. It returns a Variant containing an array from the values in its argument list; it is a function, not a separate command statement.

How do I create an array in VBA?
Declare a Variant and assign the function result, for example Dim items As Variant followed by items = Array("A", "B", "C"). For an array with a specified element type or adjustable bounds, declare a dynamic array and use ReDim.

Does a VBA Array start at zero or one?
The lower bound depends on the applicable VBA settings, including Option Base for an unqualified Array call. Check it with LBound instead of assuming a particular starting index.

Can Array create a two-dimensional array?
Not as a conventional two-dimensional array. It creates a one-dimensional array; nested Array calls produce arrays inside array elements. Declare a two-dimensional array or use ReDim with two dimensions for row-and-column data.

What is the difference between Array and Split?
Array takes separate values as arguments. Split takes a string and separates it at a delimiter, returning an array of substrings. Use the function that matches the form your source data already has.

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.