In Microsoft Access VBA, an array is a variable that holds multiple values of the same declared type, or—when declared as a Variant—values that can have different types. Declare an array with parentheses, use LBound and UBound to find its index limits, and use ReDim when a dynamic array needs to grow.
What an array does in Access VBA
An array keeps a sequence of related values together under one variable name. Instead of declaring separate variables such as item1, item2, and item3, you can store those values in an array and refer to each by its index. Arrays are part of VBA, so the same core syntax is available in Access modules, forms, and reports.
Arrays are useful for temporary in-memory work: collecting values from a recordset, processing a known set of options, or passing several values to a procedure. They do not automatically save data in an Access table. If values must persist between sessions or be queried and related to other data, store them in suitable table rows instead of relying on an array.
VBA arrays can be fixed-size or dynamic, one-dimensional or multidimensional, and typed or stored in a Variant. Choosing deliberately makes bounds easier to manage and helps prevent errors such as reading an index that does not exist.
Declare arrays and choose their bounds
A fixed-size array declares its index range in advance. For example, Dim scores(1 To 5) As Long creates five Long values indexed from 1 through 5. The lower and upper bounds are part of the declaration; the number in parentheses is not necessarily the number of elements. For instance, Dim scores(5) As Long normally has six elements when the lower bound is zero.
VBA’s Option Base statement can change the default lower bound for declarations that omit an explicit lower bound. Because that can make code harder to read across modules, specify both bounds when the range matters, such as Dim names(0 To 2) As String. With explicit bounds, readers do not need to infer whether the first valid index is zero or one.
A dynamic array leaves its size undecided until the program runs. Declare it with empty parentheses, then allocate it with ReDim. In Access code, save a copy of the database before running a macro that changes data or other important state: VBA procedures cannot be reversed with Ctrl+Z.
Option Explicit
Public Sub BuildScoreList()
Dim scores() As Long
Dim i As Long
' Allocate five elements, indexed from 1 through 5.
ReDim scores(1 To 5)
For i = LBound(scores) To UBound(scores)
scores(i) = i * 10
Next i
End Sub
For this example, the array contains 10, 20, 30, 40, and 50 at indexes 1 through 5. LBound and UBound keep the loop aligned with the actual allocation, so the same loop pattern works if the bounds change later.
Read, write, and loop through values
An array element is addressed by its index in parentheses: scores(3) means the value at index 3. Assign a value to an element just as you would to a regular variable, and read it by using that same indexed expression. Indexes are not automatically converted to positions: in an array declared 1 To 5, scores(1) is the first element, not the second.
A For loop is a common way to visit every element. Use the array’s bounds rather than hard-coding the first or last index. This avoids a mismatch if the array changes size and helps prevent “Subscript out of range,” which occurs when code refers to an index outside the allocated bounds.
Public Sub PrintNames()
Dim names(0 To 2) As String
Dim i As Long
' Fill the three available positions.
names(0) = "Ada"
names(1) = "Grace"
names(2) = "Katherine"
For i = LBound(names) To UBound(names)
Debug.Print names(i)
Next i
End Sub
This procedure writes each name to the Immediate window, which is useful for debugging VBA. In the Visual Basic Editor, open that window with Ctrl+G on Windows. On a Mac, shortcut availability can vary by keyboard and Office version; use the editor’s View menu if the shortcut differs.
Resize dynamic arrays safely
ReDim allocates or reallocates a dynamic array. If you use it again without Preserve, existing values are discarded. Add Preserve to retain existing values while changing the upper bound of a one-dimensional array:
Public Sub AddAnotherValue()
Dim values() As String
' Start with two elements indexed from zero.
ReDim values(0 To 1)
values(0) = "North"
values(1) = "South"
' Keep existing contents and add a third element.
ReDim Preserve values(0 To 2)
values(2) = "West"
End Sub
In this example, the original two strings remain and the new element is added at index 2. With Preserve, VBA permits changing only the upper bound of the last dimension. In a multidimensional array, that means you cannot use ReDim Preserve to change the number of rows in the first dimension. If you need frequent growth, consider collecting values in a Collection or another suitable structure, then copying them to an array when needed.
You can use Erase to clear array contents. For a fixed-size array, it resets the elements to their default values; for a dynamic array, it releases the allocated storage. If a dynamic array is erased, allocate it again before accessing its elements.
Use arrays returned by built-in functions
Some VBA functions produce arrays, which can save you from building one element at a time. Split, for example, divides a string at a delimiter and returns an array of substrings. This is handy for straightforward text such as a comma-separated list, but it does not handle quoted commas or other CSV rules by itself.
Public Sub ShowSplitValues()
Dim parts() As String
Dim i As Long
' Split returns items separated by commas.
parts = Split("red,green,blue", ",")
For i = LBound(parts) To UBound(parts)
Debug.Print parts(i)
Next i
End Sub
The output consists of the three strings “red,” “green,” and “blue.” The result of Split is zero-based, so its first item is at index 0. Other array-producing expressions can have different lower-bound behavior, so checking LBound is safer than assuming all arrays start at the same index.
The Array function also creates an array, but returns a Variant containing an array. A Variant-based array can be convenient when values have mixed types or when calling code that expects a Variant. For a stable collection of values with a known type, a typed array is usually clearer and allows VBA to catch some type mismatches earlier.
Pass arrays to procedures
To reuse array-processing logic, pass an array to a procedure or function. Declare the receiving parameter as an array of the appropriate type. A procedure that receives an array can then use its bounds rather than requiring a separate element count. Keep the parameter type consistent with the array being passed.
Public Function TotalValues(ByRef numbers() As Long) As Long
Dim i As Long
Dim total As Long
' Sum every element in the supplied array.
For i = LBound(numbers) To UBound(numbers)
total = total + numbers(i)
Next i
TotalValues = total
End Function
The function returns the sum of the supplied Long values. ByRef passes the array variable by reference, which is the usual approach for VBA array parameters. If a procedure changes an element, the caller can see that change. Use ByVal or a separate copy only when the intended behavior and VBA’s parameter rules support it; do not assume passing an array automatically makes a protective copy.
When a procedure accepts an optional number of separate arguments, VBA also provides ParamArray, which gathers those arguments into a Variant array. It is a different interface from passing one already-created typed array, and a procedure using ParamArray has restrictions on its argument list. Choose it for flexible call signatures, not as a substitute for a clearly typed array parameter.
Work with multidimensional arrays
A multidimensional array uses more than one index, often to represent rows and columns in a small in-memory grid. For example, Dim grid(1 To 3, 1 To 2) As String creates three row indexes and two column indexes. Refer to an item with both indexes, such as grid(2, 1).
Nested loops can visit every cell. The outer loop can iterate through the first dimension and the inner loop through the second. Label the dimensions clearly in your code, because VBA does not give them names such as “row” and “column.” A two-dimensional array is not an Access table: it has no field names, keys, relationships, or query capabilities.
For larger or relational data, a DAO Recordset or a table is often more suitable. A recordset can move through database records without loading every value into a manually managed array. Arrays remain useful when a bounded set of values must be processed in memory, but they are not automatically the fastest or simplest option for every Access task.
Common errors and practical choices
“Subscript out of range” usually means an index is outside the array’s current bounds, or that a dynamic array has not been allocated as expected. Check the declaration, each ReDim, and any index calculation. A frequent cause is mixing a zero-based result from Split with code that assumes indexing starts at one.
- Bounds: Use
LBoundandUBoundin loops, and declare explicit bounds when the starting index matters. - Resizing: Allocate a dynamic array before use. Remember that
ReDimwithoutPreservediscards existing values. - Types: Use a typed array when the values are consistently strings, numbers, or another known type. Use Variant arrays when flexibility is genuinely needed.
- Storage: Use a table for persistent, searchable, relational data; consider a recordset for database iteration; use an array for temporary in-memory values.
Another common misconception is that an array is a special kind of Access field. It is not: it is a VBA data structure held in memory. Access has features such as multivalued fields, but those are database-field behavior, not ordinary VBA arrays, and they do not make an array assignment a way to save a list into a table field. For durable data, design rows and relationships around the information you need to store and query.
Frequently Asked Questions
Do Access VBA arrays start at zero or one?
They can start at either, depending on the declaration and, for bounds that are not explicit, the module’s Option Base setting. State the bounds directly when predictable indexing matters, and use LBound to check the actual lower limit.
How do I add an item to a VBA array?
For a dynamic one-dimensional array, increase its upper bound with ReDim Preserve, then assign the new item at the new index. This retains the existing elements. For frequent additions, a Collection may be more convenient.
Why does ReDim Preserve give an error?
With a multidimensional array, ReDim Preserve can change only the upper bound of the last dimension. It also requires a dynamic array. If you need to resize a different dimension, redesign the layout or copy values into a newly allocated array.
Can I save a VBA array directly in an Access table?
No. An ordinary VBA array exists in memory and is not a table field. To retain values, store them in table records with an appropriate structure, or use another deliberate serialization approach when the data requirements call for it.
Should I use an array or a recordset in Access?
Use an array for temporary values that are convenient to process by index. Use a DAO recordset when you need to work with database records and fields, and tables when data must be stored, queried, or related. The choice depends on whether the values are transient or database data.
Related
- Protect Sheet in Excel: Allow Editing Specific Cells
- Excel Conditional Formatting Icon Sets Explained
- Markdown Table Merge Cells? What You Can Do
Try it: the Column Letter to Number: aA is 27, XFD is 16,384. Converts both ways.
More in VBA & macros
- Gorilla Copy and Paste VBA: Excel Methods and ExamplesThere is no standard Excel VBA command or feature called Gorilla; if you mean copying and pasting cells…
- Excel VBA Copy Worksheet: Code, Methods, and SafetyTo copy a worksheet with Excel VBA, call the worksheet’s Copy method and specify where the duplicate should…
- VBA AutoFilter Criteria Not Equal: How to Use ItTo filter out a value with VBA AutoFilter, pass a not-equal criterion such as "<>Closed" to the field…
- Excel VBA Find Cell: Code, Options, and ExamplesIn Excel VBA, use the Range.Find method to locate a cell containing specified text or a value. Set…
- Excel VBA UserForm Examples: Forms, Code, and Setup GuideCommon Excel VBA UserForm examples include a contact-entry form, a search box, a data-editing dialog, and a task-specific…
- Excel Macro Recorder: Can It Repeat Without a Limit?Excel’s Macro Recorder does not have a built-in “repeat forever” setting: it records a set of actions once.…
Sources: Function behaviour and menu paths: Microsoft Support documentation for Excel. Method & sources
Spotted an error in this guide? Let us know.