VBA Array Set: Assigning and Initializing Arrays in Excel

In VBA, you do not use Set to assign an array: use ordinary assignment for array values, and use Set only when assigning an object reference. For example, assign Array("North", "South") to a Variant, but use Set for a worksheet variable or for an individual object stored in an array.

What Set means in VBA

VBA distinguishes between a value and an object reference. A number, text string, date, or array of values is assigned with the equals sign. An object, such as a Worksheet, Range, or Collection, is assigned with Set. The keyword tells VBA that the variable should refer to an existing object rather than receive a value.

These two assignments therefore have different purposes:

count = 12 assigns a value.
Set ws = ThisWorkbook.Worksheets("Sheet1") assigns an object reference.
items = Array("A", "B") assigns an array held in a Variant.

An array is a group of values accessed by index; it is not itself an object that you can assign with Set. Trying Set items = Array("A", "B") is a common mistake because Array returns an array value, not an object reference. The fact that a variable is declared as a Variant does not change this rule: use ordinary assignment when the Variant contains an array.

Choose the right kind of array

The declaration determines how an array is used. A fixed-size array has its bounds in the declaration, while a dynamic array is declared without bounds and allocated later with ReDim. A Variant can also hold an array returned by the Array function or by reading a range.

Fixed-size, typed array: Choose this when the number of elements is known and they all have a particular type, such as Long.
Dynamic, typed array: Choose this when the number of elements is determined while the procedure runs, but the element type is known.
Variant containing an array: Choose this for convenient initialization with Array or when working with data returned from an Excel range.

For example, Dim scores(1 To 3) As Long creates three Long elements, indexed 1, 2, and 3. By contrast, Dim scores() As Long declares a dynamic array but does not allocate any elements yet. Knowing which kind you have helps explain errors: an unallocated dynamic array cannot be safely read, and a typed Long array cannot receive text values.

Initialize an array with Array

The Array function is a compact way to create a Variant that contains an array. Declare the receiving variable as Variant, then assign the result with an ordinary equals sign. The following example stores two text values and displays the first one:

Sub ShowArrayValue()
    Dim regions As Variant

    ' Array returns an array held inside a Variant.
    regions = Array("North", "South")
    MsgBox regions(LBound(regions))
End Sub

By default, an array created with Array is commonly zero-based, so its first index is 0. However, the lower bound can be affected by the module’s Option Base setting. Do not assume an index when code may run in different modules or projects: use LBound(regions) for the first valid index and UBound(regions) for the last. These functions also make loops safer when bounds are not obvious.

The Array function is convenient for short lists, but it produces a Variant array. If you need a strongly typed array, declare one and assign each element instead. This also makes the intended bounds and allowed data type explicit.

Assign values to a typed array

A typed array receives values by assigning to individual indexed elements. For a known-size array, you can initialize its elements directly. The example uses one-based bounds on purpose, so the index labels match the three positions in this small list.

Sub FillScores()
    Dim scores(1 To 3) As Long

    ' Assign a numeric value to each typed element.
    scores(1) = 82
    scores(2) = 91
    scores(3) = 76
    MsgBox scores(2)
End Sub

For a longer list, use a loop or copy values from a worksheet rather than writing a separate assignment for every element. If you need to copy one typed array into another, a loop over their valid indexes is a dependable approach; ordinary array-to-array assignment is not supported for ordinary typed array variables. Make sure the source and destination bounds line up before copying. If they do not, translate the index deliberately rather than assuming both arrays start at zero or one.

Allocate and resize a dynamic array

Use ReDim to give a dynamic array bounds. In this example, the array can hold four Long values indexed from 1 through 4. A later ReDim without Preserve allocates it again and discards its existing contents, so use that form only when replacing the data is intended.

Sub CreateDynamicScores()
    Dim scores() As Long

    ' Allocate four elements before using the array.
    ReDim scores(1 To 4)
    scores(1) = 82
    scores(2) = 91
End Sub

To expand an array while retaining its current values, use ReDim Preserve. In a one-dimensional array, the upper bound is the bound you can change while preserving data. For a multidimensional array, VBA only allows the last dimension’s upper bound to change with Preserve; changing another dimension or a lower bound is not permitted. Plan the array layout with that restriction in mind.

For example, if a procedure continually adds items, resizing by one element on every addition can be inefficient for large datasets because VBA may need to copy existing contents repeatedly. A common alternative is to allocate extra capacity in larger increments and track how many entries are actually in use. For modest arrays, straightforward resizing is often easier to read and maintain.

Store objects in an array

An array can contain object references, but the array itself still is not assigned with Set. Instead, use Set for each element that refers to an object. This example creates two Collection objects and places a reference to each in a fixed-size array.

Sub CreateObjectArray()
    Dim groups(0 To 1) As Collection

    ' Set assigns each Collection object reference.
    Set groups(0) = New Collection
    Set groups(1) = New Collection
    groups(0).Add "Apples"
    groups(1).Add "Pears"
End Sub

Notice the difference: Set groups(0) = New Collection is valid because that element is an object variable. Writing Set groups = ... is not how you initialize the array as a whole. When working with object arrays, each element begins without an object reference until you assign one. Check that an element has been set before calling a method on it; otherwise, VBA can raise an Object variable or With block variable not set error.

Use arrays with Excel ranges

Arrays are useful for moving worksheet data in batches. In Excel for Microsoft 365 on Windows, assigning a multi-cell range’s Value to a Variant normally returns a two-dimensional array: one dimension represents rows and the other columns. The bounds are generally one-based. You can then write the array to a same-sized destination range with a regular assignment, not Set.

Sub CopyRangeThroughArray()
    Dim ws As Worksheet
    Dim data As Variant

    ' Save a copy of the workbook before running a macro.
    Set ws = ThisWorkbook.Worksheets("Sheet1")
    data = ws.Range("A1:C3").Value
    ws.Range("E1:G3").Value = data
End Sub

The destination range in this example is also three rows by three columns, matching the source array. If the dimensions do not match, the result may not be what you expect, so check the source and destination sizes. A one-cell range is a special case: its Value is a scalar value, not a two-dimensional array. Code that handles both one cell and multiple cells may need to treat those cases separately.

The workbook-copy reminder matters because Excel macros cannot be undone with Ctrl+Z after they run. On Windows, open the Visual Basic Editor with Alt+F11; on Mac, the equivalent is generally Option+F11, sometimes with the Fn key depending on the keyboard. Macro availability and keyboard behavior can vary with Excel settings, but the VBA assignment rules are the same.

Common array errors and how to avoid them

Many array problems are caused by a mismatch between the declaration and the assignment, rather than by the Set keyword alone. When an error appears, check what type of variable is on the left side, what the expression on the right returns, and whether the array has been allocated.

Using Set with values: Remove Set when assigning a number, string, array value, or range’s cell contents. Keep it when assigning an object reference such as a Worksheet.
Using an array before ReDim: Allocate a dynamic array before reading or writing its elements. Check bounds with LBound and UBound once it has been allocated.
Assuming every array starts at zero: Bounds depend on how the array was created and, in some cases, on Option Base. Use explicit bounds or query them.
Putting the wrong data type in a typed array: A Long array is for whole-number values, not text or object references. Choose a suitable type or use a Variant when mixed values are genuinely needed.
Resizing and losing data: Plain ReDim does not preserve existing elements. Use Preserve when appropriate and respect its dimension limits.

If an error mentions an object variable, inspect object assignments and confirm each object array element was set. If it mentions a subscript being out of range, inspect the actual lower and upper bounds instead of guessing an index. These checks usually identify the issue faster than changing array declarations at random.

Frequently Asked Questions

Can I use Set to assign an array in VBA?
No. Use ordinary assignment for arrays. Use Set for object references, including an individual object stored in an array.

How do I assign Array values to a VBA variable?
Declare the receiving variable as Variant, then write an assignment such as values = Array(10, 20, 30). Use LBound and UBound to find the valid indexes.

Can I assign one VBA array directly to another?
Not when both are ordinary typed array variables. Copy their elements with a loop. Variants can hold arrays and can be assigned as Variants, but that is different from assigning one typed array variable directly to another.

How do I add items to a dynamic array?
Allocate it with ReDim. To enlarge a one-dimensional array without discarding its current elements, use ReDim Preserve and change its upper bound.

Why does an array from a worksheet start at 1?
A multi-cell range’s Value is returned as a two-dimensional Variant array whose indexes are typically one-based. A single-cell range instead returns a scalar, so check whether the range contains one cell or several.

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.