To add an item to a one-dimensional dynamic VBA array, increase its upper bound with ReDim Preserve, then assign the new value at the new last index. The array must already be initialized; VBA arrays do not grow automatically when you assign an item beyond their current bounds.
What adding an item to an array means
An array stores a sequence of values under one variable name, with each value accessed by an index. For example, an array might hold department names at positions 0, 1, and 2. To add another department, you need to make space for another index and then put the value there. VBA does not provide an array method called Add that handles this automatically.
There are two common kinds of arrays in VBA. A fixed-size array has its bounds set when it is declared, such as Dim names(0 To 2) As String. A dynamic array is declared without bounds, such as Dim names() As String, and its size is set later using ReDim. Dynamic arrays are the usual choice when the number of items is not known in advance.
For a dynamic array, ReDim Preserve changes the bounds while retaining values already stored in the array. The word Preserve matters: plain ReDim can discard the existing contents. In a typical append operation, the upper bound increases by one, and the new value is assigned to that new position.
Basic example: append to an initialized array
This example starts with two values and appends a third. It uses a dynamic, one-dimensional array of Variants, so its elements can hold different kinds of values if needed. The helper procedure expects an array that already contains at least one element.
Sub AppendExample()
Dim items() As Variant
' Start with two allocated positions.
ReDim items(0 To 1)
items(0) = "North"
items(1) = "South"
' Add one value at the end.
AppendItem items, "West"
End Sub
Private Sub AppendItem(ByRef items() As Variant, ByVal newItem As Variant)
Dim nextIndex As Long
' This procedure expects an initialized, one-dimensional array.
nextIndex = UBound(items) + 1
ReDim Preserve items(LBound(items) To nextIndex)
items(nextIndex) = newItem
End Sub
After the call, the array contains “North,” “South,” and “West” at indices 0, 1, and 2. LBound and UBound return the current lowest and highest indices. Using them avoids assuming that an array always begins at zero; its lower bound can depend on how it was created and on the module’s Option Base setting.
Before running a macro, save a copy of the workbook. Macro changes cannot be undone with Ctrl+Z, even though this particular example only changes an in-memory array.
What to do when the array has no items yet
A newly declared dynamic array such as Dim items() As Variant has no allocated bounds. Calling UBound(items) before giving it bounds raises an error, so the append procedure above cannot be used on that uninitialized array. Initialize it first with one position, assign the first item, and use the append procedure only for later items.
For example, the first item can be set with ReDim items(0 To 0) followed by items(0) = "North". The next append then expands the array from index 0 to index 1, preserving the first value. If your code needs to distinguish a genuinely empty list from an array that has not been initialized, track the item count or use a collection instead of relying on an invalid array bound.
A Variant variable can also contain an array, but that does not remove the need to check whether it currently contains an array before calling array-bound functions. In general, the clearest design is to initialize the array explicitly and keep a separate count when the array may contain zero items.
Appending to a multidimensional array
The simple pattern applies to one-dimensional arrays. With a multidimensional dynamic array, VBA’s ReDim Preserve can change only the last dimension. For example, if an array is organized as data(row, column), you can enlarge its column dimension while preserving values, but you cannot use ReDim Preserve to add another row by changing the first dimension.
This limitation matters when storing rows of records. One workaround is to organize the data so that the dimension you need to grow is last, provided that layout suits the rest of your code. Another is to allocate a new array with the desired dimensions and copy the old values across. That takes more code, but it gives you control over both dimensions.
For data that naturally consists of rows and columns, a two-dimensional array may still be appropriate when the final size is known or can be estimated. If rows arrive one at a time and the structure needs frequent changes, a Collection of row objects or another data structure may be easier to maintain. Choose based on how the code reads and updates the data, not just on how to perform one append.
Performance when adding many items
Calling ReDim Preserve for every new value is convenient for a short list, but it can become slow for a large one. Preserving an array may require VBA to allocate a larger block of memory and copy the existing elements. Appending one item at a time can therefore copy the earlier values repeatedly as the array grows.
If you know an approximate maximum size, allocate that many positions once and keep a separate item count. Each new value goes into the next unused position without resizing the array. When you have finished collecting values, you can reduce the array to the actual number of items with one final ReDim Preserve, as long as the array is nonempty and the dimension being changed is its last dimension.
Another option is to grow the capacity in larger steps, for example by doubling it when it fills. That reduces how often values need to be copied, though it requires tracking both the number of stored items and the allocated capacity. For small arrays, this extra machinery is usually unnecessary; for a large number of appends, measuring the whole procedure and choosing a suitable data structure is more useful than assuming one approach is always fastest.
Array versus Collection or worksheet range
A VBA Collection has an Add method, so it is a natural alternative when items arrive one at a time and the final count is unknown. A collection can hold objects or values and does not require you to resize an array manually. Its access pattern differs from an array, however, and it does not offer the same direct array indexing and compact layout.
Arrays are often a good fit when you need indexed access, want to process many values in memory, or plan to pass an array to a procedure. A Collection can be simpler for a changing list. A Scripting.Dictionary is useful when each item needs a unique key for lookup, rather than merely a position in a sequence. These structures are alternatives, not interchangeable versions of the same feature.
If the source or destination is a worksheet, consider reading or writing a whole range in one operation instead of appending worksheet cells individually. For example, a macro can read a contiguous range into a Variant array, process the values in memory, and write the result back as a block. This can reduce repeated communication with the worksheet, which is often more significant than the cost of resizing a modest array.
Common errors and how to avoid them
- Using
UBoundon an uninitialized array: Allocate the first element before asking for the upper bound, or use a collection for a list that may be empty. - Leaving out
Preserve:ReDimwithoutPreservecan erase existing array values. Include it when the old contents must remain. - Changing the wrong dimension: For a multidimensional array, only the last dimension can be resized with
Preserve. Rebuild and copy the array if you need to grow another dimension. - Assuming an index starts at zero: Use
LBoundandUBoundwhen working with arrays whose lower bound is not guaranteed. - Passing an incompatible array type: A helper declared to accept
Variant()is for a dynamic array of Variants. A typed array such asString()needs a helper with a matching parameter type or a different design.
Frequently Asked Questions
Can I use ReDim Preserve to add an item?
Yes. For an initialized one-dimensional dynamic array, increase its upper bound by one with ReDim Preserve, then assign the new value at the new upper bound. It preserves the values already in the array.
Why does UBound give an error?
The array may be declared as dynamic but not yet allocated. Set its bounds with ReDim before calling UBound, or use a different structure if an empty list is a normal state in your program.
Can I add an item to a fixed-size VBA array?
Not without changing its size. A fixed-size array is not resized by assignment. Use a dynamic array declared without bounds, then allocate and resize it with ReDim.
Can I add a row to a two-dimensional array?
Not by preserving and enlarging the first dimension: VBA permits ReDim Preserve to change only the last dimension. To add rows, rebuild and copy the array, change its layout, or use a structure that grows more naturally.
Is a Collection better than an array for appending?
It can be when the list changes often or its size is unknown, because Collection.Add handles insertion without manual resizing. Arrays remain useful for indexed data and bulk processing, so the better choice depends on how the values will be used.
Related
- Excel Solver Syntax: Setup, Constraints, and Examples
- Excel Solver for Transportation Problems: Setup and Tips
- Excel Solver Simplex LP: When to Use It and How It Works
Try it: the Column Letter to Number: aA is 27, XFD is 16,384. Converts both ways.
More in VBA & macros
- Excel VBA Evaluate: How It Works and When to Use ItIn Excel VBA, Application.Evaluate interprets a text expression using Excel’s formula language and returns the result as a…
- Excel VBA Loop Through Used Rows: Safe, Fast MethodsTo loop through rows Excel considers used, set a range to the worksheet’s UsedRange and iterate over its…
- VBA Delete Row If Cell Is Blank: Safe MethodsTo delete rows when a cell in a chosen column is blank, loop through the data from the…
- VBA Loop Through Dictionary Items: Keys, Values, and TipsTo loop through a VBA dictionary’s entries, use For Each on its keys, then retrieve each associated value…
- VBA Code to Link Excel WorksheetsTo link to a worksheet in the same Excel workbook, use VBA’s Hyperlinks.Add method with an empty Address…
- VBA Dictionary Split: Turn Text into Keys and ValuesIn VBA, use the Split function to break a string into an array, then add the pieces to…
Sources: Function behaviour and menu paths: Microsoft Support documentation for Excel. Method & sources
Spotted an error in this guide? Let us know.