Skip to content
Tutorials Center
My learning

Topics

Lesson 10 of 37 ยท Language Basics

Arrays in VBA: Fixed, Dynamic and Multi-Dimensional

Learn VBA arrays: fixed and dynamic arrays, ReDim Preserve, Array(), two-dimensional arrays, LBound/UBound, passing arrays and reading ranges into arrays.

An array holds many values of the same type under one name, accessed by an index. Arrays are essential in VBA for speed: reading a thousand cells into an array and looping over memory is many times faster than touching the sheet in each iteration.

Fixed-size arrays

VB
Dim scores(1 To 5) As Long
scores(1) = 90
scores(2) = 75
scores(3) = 82
Debug.Print scores(2)         ' 75

Dim names(4) As String makes 5 elements, indexes 0 to 4, because VBA counts from 0 unless you give a range (1 To 5) or set Option Base 1. Stating bounds explicitly (1 To 5) is clearest.

Use LBound(arr) and UBound(arr) to get the lowest and highest index, so loops survive size changes:

VB
Dim i As Long, total As Long
For i = LBound(scores) To UBound(scores)
    total = total + scores(i)
Next i
Debug.Print total             ' 247 (one element is still 0)

Reading beyond the bounds raises run-time error 9: Subscript out of range.

Array() for quick lists

VB
Dim days As Variant
days = Array("Mon", "Tue", "Wed")
Debug.Print days(0), UBound(days)    ' Mon   2

Array returns a Variant holding a zero-based array (independent of Option Base). Handy for short lists of names; combine with For Each:

VB
Dim d As Variant
For Each d In Array("Mon", "Tue", "Wed")
    Debug.Print d
Next d

Dynamic arrays and ReDim

When you do not know the size in advance, declare without bounds and size later:

VB
Dim items() As String
ReDim items(1 To 3)
items(1) = "pen": items(2) = "ink": items(3) = "pad"

ReDim Preserve items(1 To 4)     ' grow, keep contents
items(4) = "clip"

ReDim without Preserve empties the array. With Preserve, only the last dimension can change, and copying large arrays repeatedly is slow, so grow in chunks or count first. Release the memory with Erase items.

A common pattern collects matches of unknown number:

VB
Dim result() As Long, n As Long, i As Long
n = 0
For i = 1 To 20
    If i Mod 3 = 0 Then
        n = n + 1
        ReDim Preserve result(1 To n)
        result(n) = i
    End If
Next i
Debug.Print UBound(result)        ' 6

Two-dimensional arrays

VB
Dim grid(1 To 3, 1 To 4) As Long      ' 3 rows, 4 columns
Dim r As Long, c As Long
For r = 1 To 3
    For c = 1 To 4
        grid(r, c) = r * c
    Next c
Next r
Debug.Print grid(2, 3)                ' 6
Debug.Print UBound(grid, 1), UBound(grid, 2)   ' 3  4

Pass the dimension number to UBound/LBound. Up to 60 dimensions exist, but two cover nearly everything.

Ranges and arrays (Excel)

The fastest way to process sheet data is a single read and a single write:

VB
Dim data As Variant
data = Range("A1:C1000").Value        ' 2-D array, 1-based: data(row, col)
' ... loop over data in memory ...
Range("E1:G1000").Value = data        ' write back in one go

A one-cell range returns a single value, not an array, so test with IsArray(data).

Passing arrays to procedures

VB
Function SumArray(values() As Long) As Long
    Dim i As Long
    For i = LBound(values) To UBound(values)
        SumArray = SumArray + values(i)
    Next i
End Function

Arrays are always passed by reference. To copy, assign to another dynamic array: b = a copies all elements.

Stand-in: array patterns in JavaScript

This JavaScript stand-in (not VBA) uses 1-based helper functions so you can see index shifts, the "collect unknown count" pattern, and a two-dimensional grid.

JS Stand-in: grid, collect and bounds

Press Run (or Ctrl + Enter) to see the console output here.

No output.

Common mistakes

  • Forgetting that Dim a(5) has six elements.
  • ReDim Preserve on the first dimension of a 2-D array (not allowed).
  • Looping with a hard-coded size instead of UBound.
  • Declaring Dim a(n) with a variable (needs ReDim).

Check your understanding

Quick check ยท 5 questions

of 5 answered You scored / 5 ยท perfect!

  1. 1. How many elements does Dim a(4) As Long hold by default?

    Indexes run from 0 to 4, so there are five elements.
  2. 2. What does ReDim Preserve do?

    Without Preserve, ReDim clears the contents.
  3. 3. Which error appears when the index is outside the bounds?

    Run-time error 9 means an index outside LBound..UBound.
  4. 4. What does UBound(grid, 2) return for Dim grid(1 To 3, 1 To 4)?

    The second argument selects the dimension; the second dimension runs to 4.
  5. 5. Why read Range values into an array?

    Each sheet access has overhead; one read plus in-memory loops is far faster.

Summary

  • Declare with Dim a(1 To n); use LBound/UBound in loops.
  • ReDim Preserve resizes while keeping data (last dimension only).
  • Array() creates quick zero-based lists; Split returns one too.
  • Read whole ranges into 2-D arrays for speed.

Next: MsgBox and InputBox