Excel VBA: Initialize Arrays, Set Values, and Resize Them

VBA & macros · Updated November 21, 2026

To initialize an array in Excel VBA, declare it and assign values: use Dim items As Variant: items = Array("red", "green", "blue") for a quick list, or declare a typed array and fill its elements individually. For an array whose size is determined while the macro runs, declare it dynamically and use ReDim before assigning values.

What it means to initialize an array

An array is a variable that holds a series of values under one name. Initializing can mean two different things: setting up the array’s storage and bounds, or putting useful values into its elements. VBA sometimes does the first automatically but does not fill the array with your intended data unless you assign it.

For example, a numeric array declared as Dim totals(1 To 3) As Long has room for three values, and its elements initially contain zero. That is allocated storage, not a populated list of meaningful totals. You still need statements such as totals(1) = 12 to set the values you need.

Deciding which meaning you need helps avoid a common misunderstanding: a declaration can create an array, but it does not automatically copy values from worksheet cells or assign your own starting values. You must write those assignments or load the data from a range.

Initialize a fixed-size array

A fixed-size array has bounds specified in its declaration. Use explicit lower and upper bounds when you want indexing to be clear and independent of a module’s Option Base setting.

Sub InitializeFixedArray()
    Dim months(1 To 3) As String

    ' Assign a value to each element.
    months(1) = "January"
    months(2) = "February"
    months(3) = "March"

    Debug.Print months(1)
End Sub

In this example, the valid indexes are 1, 2, and 3. A declaration such as Dim months(3) As String does not mean the same thing: without Option Base 1, its indexes are normally 0 through 3, so it has four elements. Explicit bounds make the element count and index range visible.

VBA supplies default values based on the element type. Numeric elements start at zero, Boolean elements at False, strings at an empty string, and object references at Nothing. These defaults do not substitute for meaningful initialization; for example, zero might be a valid total as well as an indication that no value has been assigned.

Use Array() for a short list

The Array() function is convenient when the values are known in advance. It returns a Variant containing an array, so declare the receiving variable as Variant rather than as a typed array.

Sub InitializeWithArrayFunction()
    Dim colors As Variant

    ' Array returns a Variant containing an array.
    colors = Array("red", "green", "blue")

    Debug.Print colors(0)
End Sub

By default, the first element in this example is at index 0, so the indexes are 0 through 2. The lower bound used by Array() can be affected by the module’s Option Base statement. If a particular lower bound matters, check it with LBound rather than assuming it.

A frequent error is trying to assign the result of Array() directly to a typed array, such as Dim colors() As String followed by colors = Array("red", "green"). That assignment is not the normal way to populate a typed VBA array. Use a Variant to receive Array(), or allocate the typed array and assign its elements individually.

Initialize a dynamic array with ReDim

A dynamic array is declared without bounds, then allocated with ReDim when its required size is known. This is useful when a macro reads a variable number of records or calculates the number of elements at runtime.

Sub InitializeDynamicArray()
    Dim scores() As Long

    ' Allocate three elements with indexes 1 through 3.
    ReDim scores(1 To 3)

    scores(1) = 85
    scores(2) = 92
    scores(3) = 78

    Debug.Print scores(2)
End Sub

Before the ReDim statement, scores has no allocated elements, so attempting to read scores(1) will fail. Allocate it first, and make sure every index used falls within its declared bounds. ReDim can also resize a dynamic array later, but resizing it without Preserve clears its existing contents.

Use ReDim Preserve when you need to keep existing values while expanding a one-dimensional array. For example, after declaring Dim scores() As Long, you can allocate indexes 1 to 3 and later resize to 1 to 5 with ReDim Preserve scores(1 To 5). In a multidimensional array, VBA permits Preserve to change only the upper bound of the last dimension. If your design needs to grow another dimension, consider a different storage approach or copy the data into a newly allocated array.

Initialize a multidimensional array

Multidimensional arrays are useful for data arranged in rows and columns. Declare each dimension’s bounds, then assign values using a comma-separated index for each dimension.

Sub InitializeGrid()
    Dim grid(1 To 2, 1 To 3) As Variant

    ' First index is the row; second index is the column.
    grid(1, 1) = "Item"
    grid(1, 2) = "Quantity"
    grid(1, 3) = "Price"
    grid(2, 1) = "Notebook"
    grid(2, 2) = 4
    grid(2, 3) = 2.5
End Sub

This array has two rows and three columns. VBA does not provide a simple literal syntax for assigning a complete two-dimensional typed array in one statement, so assigning individual elements or reading a two-dimensional range into a Variant is often more practical. Keep the row and column order consistent: grid(2, 1) means the second row and first column in this example.

Fill an array with a loop or worksheet data

When values follow a pattern, a loop avoids repetitive assignment statements. For instance, the following code initializes a one-dimensional array with the squares of the numbers from 1 to 5.

Sub FillArrayWithLoop()
    Dim squares(1 To 5) As Long
    Dim i As Long

    For i = LBound(squares) To UBound(squares)
        squares(i) = i * i
    Next i
End Sub

For worksheet data, a multi-cell range can be assigned to a Variant, which receives a two-dimensional array of values. This is a common way to transfer data for processing in memory instead of reading one cell at a time.

Sub LoadRangeIntoArray()
    Dim data As Variant

    ' Assumes Sheet1 contains data in A1:C10.
    data = ThisWorkbook.Worksheets("Sheet1").Range("A1:C10").Value

    Debug.Print data(1, 1)
End Sub

For a range containing multiple cells, the first dimension corresponds to worksheet rows and the second to columns. A range containing just one cell is a special case: its Value is a single value rather than a two-dimensional array. If your code must handle both one-cell and multi-cell selections, account for that difference before indexing the result as an array.

Check bounds and avoid common errors

Array indexes are not always zero-based or one-based. The declared bounds, or the way the array was created, determine which indexes are valid. Use LBound(arrayName) and UBound(arrayName) to find the lower and upper bounds of a one-dimensional array. For a multidimensional array, provide the dimension number, such as UBound(grid, 2) for the upper bound of its second dimension.

  • Subscript out of range: The code used an index outside the array’s current bounds, or tried to access a dynamic array before allocating it.
  • Assuming the first index is zero: An array declared with explicit bounds such as 1 To 5 starts at 1. Use the actual declared range or the bound functions.
  • Resizing and losing values: A plain ReDim discards the existing contents. Use Preserve where its dimension restriction fits your design.
  • Trying to assign a list to a typed array: Array() returns a Variant containing an array. Receive it in a Variant or set each typed-array element separately.

An unallocated dynamic array has no usable elements. Calling LBound or UBound on it can raise an error, so do not use those functions as a substitute for checking whether your code has already allocated the array. A practical approach is to control allocation in one place and call the code that reads the array only after allocation succeeds.

Choose the right initialization method

The best approach depends on where the values come from and whether the array needs to change size.

  • Choose a fixed-size typed array when the number of elements is known and the data has a consistent type, such as dates or whole-number counts.
  • Choose Array() assigned to a Variant for a short, fixed list of mixed or simple values where concise code is more useful than a specific element type.
  • Choose a dynamic array with ReDim when the count is not known until the macro runs.
  • Choose a Variant loaded from a range when processing a block of worksheet cells in memory.

For small arrays, assigning each element directly is easy to read. For larger arrays, loops or range assignment are more maintainable. If an array must grow repeatedly as records arrive, resizing on every item can be inefficient; collect a known batch, expand capacity in larger steps, or use a collection when its add-and-remove behavior better fits the task.

Run VBA safely in Excel

In Excel for Microsoft 365 on Windows, open the Visual Basic Editor with Alt+F11, insert a standard module with Insert > Module, and place the procedure there. On Mac, the editor shortcut is commonly Option+F11; some keyboards require Fn as well. The VBA editor and available shortcut can vary with the Mac keyboard and Excel setup.

Save a copy of the workbook before running a macro. VBA changes cannot be undone with Ctrl+Z, and a macro can alter worksheet data or other workbook content. If the workbook contains macros, save it in a macro-enabled format such as .xlsm. Macros run in desktop Excel; Excel for the web can open macro-enabled workbooks but cannot create, run, or edit VBA macros.

Frequently Asked Questions

How do I initialize an array with values in VBA?
For a short list, assign Array("A", "B", "C") to a Variant. For a typed array, declare its bounds and assign values to its elements, either individually or in a loop.

Does VBA array indexing start at 0 or 1?
It depends on how the array was declared or created. Explicit bounds such as Dim values(1 To 4) As Long settle the matter directly. Use LBound to check the lower bound of an allocated array.

How do I create an empty dynamic array?
Declare it with empty parentheses, such as Dim values() As Long. It is not allocated for indexing until you use ReDim. Do not try to read an element or assume it has a usable upper bound before allocation.

Can I initialize a VBA array from an Excel range?
Yes. Assign a multi-cell range’s Value to a Variant, for example data = Sheet1.Range("A1:C10").Value. The resulting array is indexed by row and column; handle a single-cell range separately if your code expects an array.

What does Erase do to an array?
Erase resets a fixed-size array’s elements to their default values. For a dynamic array, it releases the allocated storage, so the array must be allocated again with ReDim before its elements can be used.

Related

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.