VBA Array of Numbers: How to Declare and Work With One

VBA & macros · Updated November 18, 2026

In VBA, an array of numbers stores multiple numeric values under one variable name; declare one with a numeric type, such as Dim values(1 To 5) As Double. Use Long for whole numbers and Double for decimal values, then fill and read the array by referring to each element’s index.

What a VBA numeric array is

A regular variable holds one value at a time. An array groups a set of values of the same declared type, so code can work through them using a loop instead of creating a separate variable for every number. Each value is an element, and its position is identified by an index, also called a subscript.

For example, values(1) means the element at index 1, not necessarily the first element. The first index depends on the array’s lower bound. An array declared as Dim values(1 To 5) As Long has five elements, numbered 1 through 5. An array declared as Dim values(5) As Long normally has six elements, numbered 0 through 5.

Arrays are useful when a macro needs to calculate with a group of measurements, scores, quantities, or other values. They can also reduce repeated worksheet reads: VBA can copy a block of cells into an array, process the values in memory, and write the results back in a block.

Choose a numeric data type

The type after As controls what kind of values an array stores. Choosing a suitable type makes the code clearer and can prevent unexpected conversions. Common choices include:

  • Long for whole numbers. It is a 32-bit signed integer, with a range from -2,147,483,648 to 2,147,483,647. It is usually a better default than Integer for ordinary VBA counters and whole-number data.
  • Double for numbers that may contain decimal places or need a broad range. It is a floating-point type, so some decimal fractions cannot be represented exactly.
  • Single for floating-point numbers when its lower precision and range are sufficient. For most general spreadsheet calculations, Double is the more common choice.
  • Currency for values that need fixed-point storage with four decimal places. It is not a substitute for deciding how a financial calculation should be rounded or displayed.
  • Byte for nonnegative whole numbers from 0 to 255.

Integer holds values from -32,768 to 32,767, but many VBA operations use Long internally, and Long is less likely to overflow for counters or ordinary whole-number inputs. If an array might contain blanks, text, Excel error values, or a mixture of data types, a Variant array may be more appropriate. It is flexible, but that flexibility means code should check what each element contains before treating it as a number.

Declare a fixed-size or dynamic array

A fixed-size array has bounds in its declaration. The following macro creates five decimal-number elements, fills them, and adds them. It displays 150 because the values are 10, 20, 30, 40, and 50. Save a copy of the workbook before running a macro: macro actions cannot be undone with Ctrl+Z.

Sub FillAndTotalNumbers()
    Dim numbers(1 To 5) As Double
    Dim i As Long
    Dim total As Double

    ' Fill each element and add its value to the total.
    For i = LBound(numbers) To UBound(numbers)
        numbers(i) = i * 10
        total = total + numbers(i)
    Next i

    MsgBox total
End Sub

LBound returns the lowest valid index, and UBound returns the highest. Using these functions in a loop is safer than assuming the array starts at 0 or 1. In a standard module, Option Base 1 changes the default lower bound for array declarations that omit an explicit lower bound; without it, the default is 0. Explicit bounds such as (1 To 5) make the intended indexes clear and are unaffected by that default.

A dynamic array is declared without bounds, then sized later. This is useful when the number of values is not known until the macro runs. Save a copy before running this macro as well, because its actions cannot be reversed with Ctrl+Z.

Sub CreateDynamicNumbers()
    Dim numbers() As Long
    Dim i As Long

    ' Set the array bounds before assigning elements.
    ReDim numbers(1 To 5)

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

Declaring numbers() alone does not allocate elements. Trying to use numbers(1) before ReDim sets its bounds causes a subscript error. A dynamic array can also be sized from a value calculated at runtime, provided that the resulting bounds are valid.

Fill, read, and loop through the elements

Assign an individual value by naming its index, as in numbers(2) = 25. Read it the same way: result = numbers(2). The index is a position, not a worksheet row or a number stored inside the array. If an array starts at 1, its second element is numbers(2); if it starts at 0, its second element is numbers(1).

For repeated work, use a For loop over the actual bounds. The loop in the earlier example both assigned values and calculated a total. To inspect values instead, use Debug.Print numbers(i) inside the loop; the output appears in the Immediate window in the Visual Basic Editor. Avoid a hard-coded upper limit if the array may change size, because the loop could then skip elements or request an invalid index.

VBA also has the Array function, which can create a convenient array of values, for example Array(12, 18, 24). Its result is a Variant containing an array, rather than a strongly typed Long() or Double() array. Its lower bound follows the module’s Option Base setting. For a typed array, or when exact bounds matter, declare the array explicitly instead.

Resize an array without losing values

ReDim sets or changes the bounds of a dynamic array. If preserving existing elements is important, use ReDim Preserve. For example, if a one-dimensional array currently holds five values, ReDim Preserve numbers(1 To 10) expands it to ten while retaining the original five. The new elements are initialized to the default value for the type, such as zero for numeric types.

There is an important restriction: with Preserve, VBA lets you change only the upper bound of the last dimension. You cannot use it to change the lower bound, and you cannot resize an earlier dimension of a multidimensional array while preserving its contents. If a two-dimensional array needs a different row count, a common approach is to create a new array, copy the values that should remain, and then use the new array.

Repeatedly expanding an array by one element can also be inefficient for a large collection, because preserving data may require copying it each time. If the final size can be estimated, allocate enough space in one step. Otherwise, consider growing the array in larger increments, or use a collection when the data needs frequent additions and removals.

Use numeric arrays with worksheet data

In Excel VBA, a range’s Value property can transfer worksheet data to an array. A multi-cell range returns a two-dimensional Variant array, even when the range is only one column wide. For example, assigning the values from Range("A1:A5").Value gives an array with row and column indexes; read the first cell using data(1, 1), not data(1).

Here is a complete example that reads five cells, doubles numeric values, and writes the results to the adjacent column. Save a copy of the workbook before running it because macro changes cannot be undone with Ctrl+Z.

Sub DoubleNumbersInColumnA()
    Dim data As Variant
    Dim i As Long

    ' Read A1:A5 as a two-dimensional array.
    data = Range("A1:A5").Value

    For i = LBound(data, 1) To UBound(data, 1)
        If IsNumeric(data(i, 1)) Then
            data(i, 1) = CDbl(data(i, 1)) * 2
        End If
    Next i

    ' Write the results to B1:B5.
    Range("B1:B5").Value = data
End Sub

The example leaves nonnumeric entries unchanged, so a text label or blank does not get multiplied. It assumes the macro is run from the workbook and sheet where the ranges are intended; for reusable code, qualify ranges with a specific worksheet rather than relying on the active sheet. A single-cell range is a special case: its Value is a single value, not a two-dimensional array, so code that handles either one cell or many should account for that difference.

For a one-dimensional numeric array, Excel range assignment is not always the shape a reader expects. A worksheet range is rectangular, so a two-dimensional array is the most predictable structure for writing a vertical or horizontal block. You can also construct a two-dimensional Variant array, then assign it to a range of matching dimensions.

Common errors and how to avoid them

  • Subscript out of range: The code requested an index outside the array’s bounds, or tried to use a dynamic array before sizing it. Check LBound, UBound, and whether ReDim has run.
  • Type mismatch: An element contains text, an error value, or another value that cannot be assigned to the declared numeric type. Validate worksheet inputs with checks such as IsNumeric before conversion, while remembering that a value being numeric does not necessarily make every conversion safe.
  • Unexpected zero-based indexes: An omitted lower bound normally starts at zero, not one. Use explicit bounds or use LBound rather than assuming where the array starts.
  • Overflow: A value is outside the range of the declared type. Check the possible data range and select a type that can hold it; changing an array to Double is not appropriate if exact integer or decimal behavior is required.
  • Wrong range shape: A range returns a two-dimensional array, while the code treats it as one-dimensional, or the array dimensions do not match the destination range. Match the indexes and the row and column counts.

When debugging, inspect the bounds and element values in the Visual Basic Editor, and step through the procedure to find the first incorrect assignment. In Excel for Microsoft 365 on Windows, Alt+F11 opens the Visual Basic Editor; on Mac, Option+F11 may open it, though some keyboards require the Fn key as well. Excel for the web can open workbooks containing macros but cannot create, edit, or run VBA macros.

When an array is the right choice

Use a typed array when the data is a known set of same-type values and the macro needs fast, index-based access or repeated calculations. A fixed array fits a known quantity, such as the seven daily readings in a small calculation. A dynamic array suits a count that varies, such as a list whose size is determined from the worksheet at runtime.

Use a Variant array when receiving values from a worksheet range or when the values may not all have the same type. Consider a Collection or a Scripting.Dictionary when the code needs to add or remove items flexibly, or associate keys with values rather than access items by numeric position. For work that can be done directly with worksheet formulas or built-in Excel features, VBA may not be necessary at all. The appropriate choice depends on whether the task is about a fixed numeric sequence, mixed worksheet input, or a changing set of records.

Frequently Asked Questions

How do I create an array of numbers in VBA?
Declare a typed array, for example Dim numbers(1 To 5) As Long for five whole numbers. Assign values using indexes such as numbers(1) = 12. For a size decided later, declare Dim numbers() As Long and set its bounds with ReDim.

Does a VBA array start at 0 or 1?
By default, an array declaration without an explicit lower bound starts at 0. A module-level Option Base 1 changes that default for applicable declarations. Explicit bounds, such as (1 To 5), specify the indexes directly and avoid ambiguity.

What is the best type for an array of whole numbers?
Long is a practical default for most whole-number arrays because its range is much larger than Integer. Use a different type when the data range or required representation calls for one, such as Byte for values from 0 to 255 or Double for floating-point decimals.

How do I find the size of a VBA array?
Use LBound(arrayName) and UBound(arrayName) to get the lowest and highest indexes. For a multidimensional array, include the dimension number, such as UBound(data, 1) for the upper bound of its first dimension.

Can I put an array of numbers into Excel cells?
Yes. A two-dimensional array whose dimensions match the target range can be assigned to the range’s Value property. Data read from a multi-cell range is also returned as a two-dimensional array, including for a single-column range.

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.