An “array error” in Excel VBA is not one specific error: it usually means code has requested an array element outside the array’s bounds, or has mishandled an array’s type or size. The most common case is runtime error 9, “Subscript out of range”; check the array’s actual lower and upper bounds with LBound and UBound, then make the loop or index fit those bounds.
What an array error means
An array stores multiple values under one variable name, with a numbered position, or index, for each value. For example, a list of monthly totals might be held in one array rather than in twelve separate variables. VBA reports an array-related problem when code uses an invalid index, tries to use an array in an incompatible way, or changes its dimensions in a way VBA does not allow.
The phrase “array error” is informal, so the exact error message matters. Runtime error 9 points toward an invalid index or an array that is not available as expected. A type mismatch (error 13) can occur when a value or array is assigned to an incompatible variable. Other messages relate to resizing or changing arrays. Read the full message and identify the highlighted line before changing code; the line that fails is often more useful than the general description.
Arrays appear in ordinary VBA tasks, including reading a block of worksheet cells, processing the results in memory, and writing results back. A mistake in the assumed starting index or in the shape of the data can therefore look like a worksheet problem even when the underlying issue is the array.
How VBA array indexes work
Do not assume every VBA array starts at zero or one. An array’s lower bound depends on how it was created. Explicit bounds make the intended positions clear: Dim scores(1 To 5) As Long creates five elements, numbered 1 through 5. By contrast, Dim scores(5) As Long has five as its upper bound; with the default lower bound of zero, that means six elements, numbered 0 through 5. An Option Base 1 statement can change the default lower bound for declarations in that module that omit an explicit lower bound, so explicit bounds are often easier to reason about.
Use LBound(arrayName) and UBound(arrayName) to ask VBA for the actual limits. For a multidimensional array, include the dimension number: LBound(values, 1) checks the first dimension, while UBound(values, 2) checks the second. A loop written from zero to a hard-coded number can fail if the array is later created with different bounds.
For a simple example, this macro loops over the bounds of the array rather than guessing where it starts or ends. Save a copy of the workbook before running a macro; VBA changes cannot be undone with Ctrl+Z.
Sub PrintArrayItems()
Dim items As Variant
Dim i As Long
' Array creates a Variant containing multiple values.
items = Array("North", "South", "West")
For i = LBound(items) To UBound(items)
Debug.Print items(i)
Next i
End Sub
The Immediate window in the VBA editor displays the printed values. The key point is not to assume the array’s first index: use its bounds, especially when code may receive arrays from a different source or module.
Common messages and their likely causes
These are common situations behind array-related errors. They are clues, not substitutes for checking the failing line and the values involved.
- Runtime error 9, “Subscript out of range”: Code requested an element outside the array’s bounds, used the wrong dimension, or referred to a collection item that does not exist. An array index of 5 fails if the valid indexes are 0 through 4.
- “Subscript out of range” while reading worksheet data: A loop may use worksheet row numbers as array indexes. If a range starts on worksheet row 2, its in-memory array is not indexed by worksheet row number; it has its own bounds.
- Type mismatch: The receiving variable may not match the value being assigned. A multi-cell range returns a two-dimensional Variant array, which does not fit a single numeric variable. A cell containing text also cannot always be placed directly into a variable declared as a number.
- “This array is fixed or temporarily locked”: Code may be trying to resize an array whose size cannot be changed at that point. Check whether the array is fixed-size or whether a procedure is using it in a way that prevents resizing.
- An error on
LBoundorUBound: A dynamic array may not yet have been allocated withReDim, or a routine may have received an uninitialized array. Establish that the array has been initialized before asking for its bounds.
Another frequent source of confusion is mixing array indexes with worksheet coordinates. A value in the first position of an array taken from a range is not necessarily worksheet row 1. Keep track of what each index represents: an array position, a worksheet row, or a worksheet column.
Reading a worksheet range into an array
In Excel VBA, assigning a multi-cell range’s Value to a Variant gives you a two-dimensional array. Its first dimension represents rows and its second dimension represents columns. For a rectangular range such as A2:C5, loop through dimension 1 for rows and dimension 2 for columns. Use bounds from the array instead of hard-coding its dimensions.
This example reads a four-row by three-column range and prints each value. The worksheet named Data must exist in the workbook, and the range must be available. Save a copy of the workbook before running the macro; macros cannot be undone with Ctrl+Z.
Sub PrintRangeValues()
Dim data As Variant
Dim rowIndex As Long
Dim colIndex As Long
' A multi-cell range is read as a two-dimensional array.
data = ThisWorkbook.Worksheets("Data").Range("A2:C5").Value
For rowIndex = LBound(data, 1) To UBound(data, 1)
For colIndex = LBound(data, 2) To UBound(data, 2)
Debug.Print data(rowIndex, colIndex)
Next colIndex
Next rowIndex
End Sub
A single-cell range is a notable exception: its Value is a scalar value rather than a two-dimensional array. Code that sometimes reads one cell and sometimes reads multiple cells should account for that difference instead of blindly using two indexes. One straightforward approach is to ensure the requested range contains multiple cells; another is to branch on the range size and handle the single value separately.
Resizing dynamic arrays safely
A dynamic array is declared without fixed bounds, using empty parentheses, and then allocated with ReDim. This is useful when the number of items is not known until the code runs. The new bounds must make sense for the data you intend to store; resizing does not automatically correct an incorrect loop or index.
Use ReDim Preserve when you need to resize an array and keep its existing values. In a multidimensional array, VBA only permits the final dimension’s upper bound to change while preserving data. Trying to change a different dimension with Preserve can produce an error. If the required change is to another dimension, use a different storage strategy or create a new array and copy the values deliberately.
For example, this one-dimensional array grows from three positions to five while retaining its first three items. Save a copy before running any macro that writes or changes workbook data; Ctrl+Z does not reverse VBA actions.
Sub ResizeItems()
Dim items() As String
' Allocate three positions, numbered 0 through 2.
ReDim items(0 To 2)
items(0) = "Red"
items(1) = "Green"
items(2) = "Blue"
' Preserve the existing values while extending the upper bound.
ReDim Preserve items(0 To 4)
End Sub
After resizing, positions 3 and 4 exist but are not populated by this example. For a numeric array they would contain the numeric default value; for a string array they would be empty strings. New array space is not automatically filled with meaningful data, so code should assign values before relying on them.
A practical debugging process
When an array error appears, work from the failing statement outward. Avoid changing several bounds or declarations at once, because that can hide the cause and create a second problem.
- Record the exact error and line. Click Debug when the runtime error appears. The highlighted line shows which operation failed, though the incorrect value may have been set earlier.
- Inspect the index values. In the VBA editor, hover over variables or use the Immediate window to check the index and the array bounds. Confirm that the index is within the actual lower and upper limits.
- Check every dimension. For a two-dimensional array, confirm both row and column indexes, and pass the correct dimension number to
LBoundandUBound. - Confirm how the array was created. Look for explicit bounds,
Option Base,Array,Split,ReDim, or an assignment from a worksheet range. Different sources can have different shapes or bounds. - Check the data type and shape. Determine whether the variable holds one value or an array, and whether the array is one-dimensional or two-dimensional. A Variant can hold different kinds of values, but it does not remove the need to use the right indexing pattern.
- Compile after edits. In the VBA editor, use Debug > Compile VBAProject to catch compile-time problems. Compilation does not prove that every runtime index is valid, but it can identify separate issues before you run the macro again.
For a temporary diagnostic, print bounds to the Immediate window with Debug.Print LBound(data, 1), UBound(data, 1). For a two-dimensional array, print the bounds of the second dimension as well. Remove or keep these diagnostics as appropriate once the cause is understood.
Choosing an array approach
Use a fixed-size array when the number of elements is known and stable, such as a small set of weekday values. Explicit bounds make its valid indexes visible in the declaration. Use a dynamic array when the size depends on the data or changes while the macro runs. Remember to allocate it before using it, and decide whether a resize should preserve old values.
A Variant is often practical for worksheet data because a range can contain numbers, text, dates, blanks, or error values. A strongly typed array, such as Long() or String(), is useful when values have a consistent type and you want the declaration to express that expectation. However, assigning mixed worksheet contents to a strongly typed array can fail or require explicit conversion and validation.
For a rectangular block of cells, a two-dimensional Variant is usually a natural fit. For a simple list created in code, a one-dimensional array is easier to navigate. If you need a collection that grows and shrinks frequently, a VBA Collection or a suitable worksheet table may be easier to manage than repeatedly resizing a multidimensional array. The best choice depends on the data’s shape and how the code needs to change it.
Frequently Asked Questions
What does “Subscript out of range” mean in VBA?
It means code requested an item using an index or key that is not valid for the array or collection. Check the index against LBound and UBound, and verify that you are using the right dimension.
Do VBA arrays always start at zero?
No. Bounds depend on how an array is declared or created, and Option Base 1 can affect declarations that omit explicit lower bounds in its module. Use explicit bounds where practical and inspect them with LBound and UBound.
Why does a range value cause a type mismatch?
A multi-cell range returns a two-dimensional array when assigned to a Variant, while a single-cell range returns one value. Code expecting a single number or a one-dimensional array may not match what the range actually returns.
Why does ReDim Preserve fail for my array?
With ReDim Preserve, VBA allows you to change only the upper bound of the final dimension while retaining existing values. For a multidimensional array, changing another dimension requires a different approach, such as copying values to a newly allocated array.
How can I check whether a dynamic array has been initialized?
Do not assume LBound or UBound will work on an unallocated dynamic array. Track initialization with a Boolean flag in your code, set it when you allocate the array, and check the flag before accessing the array or asking for its bounds.
Can I undo a VBA macro with Ctrl+Z?
No. Excel’s normal Undo command does not reverse changes made by a VBA macro. Save a separate copy of the workbook before running a macro that changes data, formulas, formatting, or workbook structure.
Related
- Excel Not Responding and Unsaved? Recover Your Work
- Excel Insert Slicer: Why No Connections Are Found?
- Excel Insert X Rows: Fast Methods for Any Worksheet
Try it: the Column Letter to Number: aA is 27, XFD is 16,384. Converts both ways.
More in VBA & macros
- Excel Macro Refresh All: How to Update Workbook DataIn Excel for Microsoft 365 on Windows, use the VBA method ThisWorkbook.RefreshAll to request a refresh of the…
- Add Worksheet VBA: Excel Methods and ExamplesTo add a worksheet with VBA in Excel, use Worksheets.Add, then assign the new sheet a name. For…
- Excel VBA MsgBox: Syntax, Buttons, and ExamplesExcel VBA’s MsgBox function displays a dialog box with a message, optional buttons and icon, and an optional…
- Excel VBA: Loop Through Files in a Folder with DirIn Excel VBA, use the Dir function to find files that match a folder path and filename pattern,…
- VBA Print Variable in a MsgBox: Syntax and ExamplesTo display a VBA variable in a message box, use MsgBox variableName, or combine a label and the…
- Excel VBA UserForm Tab Order: Set It CorrectlyTo set an Excel VBA UserForm’s tab order, open the form in the Visual Basic Editor, choose View…
Sources: Function behaviour and menu paths: Microsoft Support documentation for Excel. Method & sources
Spotted an error in this guide? Let us know.