In VBA, declare an array with parentheses after its name, such as Dim scores(1 To 5) As Long, and refer to an element by putting its index in parentheses, as in scores(1) = 90. VBA also provides the Array function for creating a Variant that contains an array; it does not use square brackets for ordinary array declaration or indexing.
What VBA Array Notation Means
An array is one variable that holds multiple values of the same general purpose, addressed by numbered positions called subscripts or indexes. Instead of creating separate variables for a list of scores, for example, you can store the values in scores and select an individual value with scores(i). Parentheses are the key notation: they appear in declarations, when accessing elements, and when describing array parameters.
The declaration determines important details such as the array’s type, number of dimensions, and bounds. For example, Dim names(1 To 3) As String declares a one-dimensional array of three strings. The expression names(2) refers to its second position because the declared indexes run from 1 to 3. An index is not necessarily a count from one: arrays can start at zero or another explicitly specified lower bound.
Array notation is different from worksheet range notation and from dynamic-array formulas in Excel. A VBA array exists in memory while code runs; a worksheet range is a group of cells, and a formula such as SORT returns results to the grid. The concepts can work together, but their syntax and rules are not interchangeable.
Declaring Fixed and Dynamic Arrays
A fixed-size array has its bounds written in the declaration. Its size is set when the procedure or module is initialized, so it suits a known number of values. Here is a small example; save a copy of the workbook before running any macro, because VBA actions cannot be reversed with Ctrl+Z.
Option Explicit
Sub MakeFixedArray()
Dim scores(1 To 5) As Long
' Store a value at the first declared index.
scores(1) = 90
Debug.Print scores(1)
End Sub
A dynamic array leaves the bounds empty in its declaration and receives them later with ReDim. This is useful when the number of records is not known until the code runs. Declare the element type even though the bounds are not yet assigned:
Option Explicit
Sub MakeDynamicArray()
Dim names() As String
Dim itemCount As Long
' Set the required upper bound at run time.
itemCount = 3
ReDim names(1 To itemCount)
names(1) = "Ava"
Debug.Print names(1)
End Sub
In that example, names has indexes 1, 2, and 3. Until a dynamic array has been dimensioned, code should not try to access one of its elements. A fixed-size array can be redimensioned only in specific circumstances; dynamic arrays are the usual choice when the bounds must change.
Indexes, Bounds, and Option Base
If an array declaration omits an explicit lower bound, VBA normally starts it at zero. A module-level Option Base 1 statement can change the default lower bound to one for applicable declarations in that module. It must appear before procedures. Because a reader may not know which default a module uses, explicitly writing both bounds—such as 1 To 10—is often clearer and avoids relying on a hidden default.
Use LBound and UBound to ask an array for its actual lower and upper bounds. This is safer than assuming the first index is zero or one, especially when an array comes from another procedure or from the Array function. For a multidimensional array, provide the dimension number as the second argument, such as UBound(data, 2).
For example, a loop that works across the bounds of a one-dimensional array can be written as follows. The loop variable is declared, and the bounds are read rather than guessed:
Option Explicit
Sub PrintArrayValues()
Dim values(1 To 3) As Long
Dim i As Long
' Visit each valid index in order.
For i = LBound(values) To UBound(values)
Debug.Print values(i)
Next i
End Sub
Attempting to access an index outside the declared bounds commonly causes “Subscript out of range.” A loop that starts at zero will fail for an array declared from 1 to 3; a loop that ends at 10 will fail if the upper bound is 3. Matching loop limits to LBound and UBound prevents these mismatches.
Using the Array Function
The Array function creates an array value and returns it inside a Variant. This is convenient for a short list of values, but it is not the same as declaring a strongly typed array. Assign the result to a Variant, then access the elements with parentheses:
Option Explicit
Sub UseArrayFunction()
Dim colors As Variant
Dim i As Long
' Array returns a Variant containing an array.
colors = Array("red", "green", "blue")
For i = LBound(colors) To UBound(colors)
Debug.Print colors(i)
Next i
End Sub
By default, the indexes produced by Array start at zero. The module’s Option Base setting can affect that default. Consequently, code should use LBound(colors) instead of assuming the first element is colors(0). The function is handy when supplying a small, known list, but for a large or typed collection of values, a declared array may make the intended structure more explicit.
VBA does not provide a general square-bracket array-literal notation like some programming languages. In VBA, square brackets are commonly used as shorthand for evaluating an Excel expression or referring to a worksheet range in certain contexts; they are not the standard way to declare or index an array in a procedure.
Multidimensional Array Notation
Use a comma-separated list of bounds to declare multiple dimensions. A two-dimensional array can represent rows and columns, while a three-dimensional array can add another category such as month. Each element is accessed by providing one index for each dimension, in the same order as the declaration.
Option Explicit
Sub UseTwoDimensionalArray()
Dim sales(1 To 2, 1 To 3) As Double
' First index selects a row; second selects a column.
sales(1, 2) = 125.5
Debug.Print sales(1, 2)
End Sub
Here, the first dimension has indexes 1 through 2 and the second has indexes 1 through 3. The element sales(1, 2) means row 1, column 2 according to the program’s chosen interpretation. VBA does not automatically label dimensions as rows or columns; the code’s design establishes what each position means. For loops over a rectangular array, use LBound and UBound separately for each dimension.
Resizing Arrays with ReDim
ReDim sets or changes the bounds of a dynamic array. Without Preserve, redimensioning discards the array’s existing contents. With Preserve, VBA retains existing values that still fit, but there is a significant limitation: you can change only the upper bound of the last dimension. You cannot use Preserve to change the number of dimensions or the lower bound.
For example, a one-dimensional dynamic array can grow while retaining its existing values:
Option Explicit
Sub GrowArray()
Dim values() As Long
' Allocate two elements, then extend the upper bound.
ReDim values(1 To 2)
values(1) = 10
ReDim Preserve values(1 To 3)
Debug.Print values(1)
End Sub
The printed value is still 10 because Preserve retains the element at index 1. If the second ReDim omitted Preserve, existing contents would be lost. When repeatedly adding items to a large array, extending it for every item can be inefficient; code can allocate extra capacity in batches and track how many positions are in use.
Looping Through and Passing Arrays
A standard For loop is useful when the index matters, such as when one array position corresponds to a row number. A For Each loop is convenient when the code only needs each value. Its control variable should be declared as a Variant when iterating through an array of values:
Option Explicit
Sub PrintWithForEach()
Dim values As Variant
Dim item As Variant
' Iterate through each element without using an index.
values = Array("north", "south")
For Each item In values
Debug.Print item
Next item
End Sub
Procedures can accept arrays as arguments. For a typed array, a procedure parameter can be declared with empty parentheses, for example ByRef values() As Long; the calling code passes the array variable, not an individual element. A Variant parameter can instead accept a Variant that contains an array. Whichever approach is used, document expected bounds and dimensions, and avoid assuming the caller used a particular lower bound unless that is part of the procedure’s contract.
Arrays and Excel Worksheet Ranges
Arrays are often used to process worksheet data efficiently. In Excel VBA, assigning a multi-cell range’s Value to a Variant typically places the values in a two-dimensional, one-based array: the first dimension represents rows and the second represents columns. Assigning an appropriately sized two-dimensional array back to a range writes its values to the cells.
There is an important exception: reading the value of a single-cell range returns a scalar value rather than a two-dimensional array. Code that must handle both one cell and multiple cells should account for this difference rather than blindly applying array bounds functions to every range result. Also remember that worksheet row and column numbers are not automatically the same as array subscripts in every array you create; inspect the bounds and keep the mapping explicit.
When data is processed in memory, reading a block into an array, changing its values, and writing it back can reduce repeated worksheet interaction. However, validate the target range dimensions before assigning the result, and be careful not to overwrite formulas or unrelated data. A workbook copy is prudent before running code that writes to cells.
Common Notation Mistakes
- Using square brackets for an element: use
values(index), notvalues[index], for ordinary VBA array access. - Assuming every array starts at zero: explicit bounds,
Option Base, and the source of the array can affect the lower bound. Ask withLBound. - Forgetting to dimension a dynamic array: declare it with empty parentheses, then use
ReDimbefore accessing an element. - Resizing without preserving data:
ReDimnormally clears the previous contents. UsePreservewhen appropriate, while observing its last-dimension restriction. - Confusing a Variant with an element type: a Variant can hold an array, but a typed declaration such as
Dim counts() As Longspecifies the type of each element. - Mixing worksheet formulas with VBA syntax: formula spill behavior belongs to Excel’s calculation grid; VBA arrays use declarations, subscripts, and bounds in code.
Frequently Asked Questions
Does VBA use square brackets for arrays?
No. Standard VBA array declarations and element references use parentheses, for example Dim a(1 To 4) As Long and a(2). Square brackets have other uses in Excel VBA, but are not the normal array-index notation.
How do I find the size of a VBA array?
Use LBound(array) and UBound(array) to get its first and last indexes. For a multidimensional array, pass a dimension number, such as UBound(array, 2). The count of elements in one dimension is the upper bound minus the lower bound plus one.
What is the difference between Dim and ReDim?Dim declares the array variable and its element type. For a dynamic array, ReDim allocates or changes its bounds at run time. A fixed array usually has its bounds in its declaration instead.
Can a VBA array start at 1?
Yes. Write an explicit lower bound such as Dim a(1 To 5) As Long. Option Base 1 can set a module’s default lower bound for applicable arrays, but explicit bounds make the intended indexes easier to see.
Why does Array return a Variant?
The VBA Array function returns a Variant containing an array, which allows it to create a list without a separate typed-array declaration. Assign it to a Variant and use LBound and UBound to work safely with its indexes.
Related
- Data Validation From a Table in Excel: Setup and Fixes
- Power Query Text to Columns: Split Data Step by Step
- Data Validation Framework: Design, Rules, and Examples
Try it: the Column Letter to Number: aA is 27, XFD is 16,384. Converts both ways.
More in VBA & macros
- VBA Global Dictionary: Scope, Setup, and Code ExamplesVBA has no built-in global dictionary type, but in Excel for Microsoft 365 on Windows you can create…
- Excel VBA: Copy and Paste Values Across a WorksheetTo replace formulas with their current results across a worksheet in Excel for Microsoft 365 on Windows, run…
- Add Controls to a VBA UserForm: Steps and CodeTo add controls to a VBA UserForm, open the form in the Visual Basic Editor and drag a…
- Excel VBA Sub or Function Not Defined? Fix It“Sub or Function not defined” is a VBA compile error: Excel cannot resolve the name of a procedure…
- VBA Code Workbook Open: How to Run Macros at StartupTo run VBA when an Excel workbook opens, put a Workbook_Open event procedure in that workbook’s ThisWorkbook code…
- Excel Macro For Loop: Examples and Common FixesAn Excel macro for loop is a VBA loop that repeats instructions for a set of items or…
Sources: Function behaviour and menu paths: Microsoft Support documentation for Excel. Method & sources
Spotted an error in this guide? Let us know.