An array holds many values under one name, indexed by number. Reading a whole range into an array and looping in memory is the single biggest speedup in VBA.

Code

Dim data As Variant
data = Range("A1:C100").Value
MsgBox data(5, 2)

• Read a range

  Dim data As Variant data = Range("A1:C100").Value MsgBox data(5, 2) → The value from row 5, column 2 of the range

  Range arrays are 1-based and two-dimensional.

• Fixed array

  Dim names(1 To 3) As String names(1) = "West" → A fixed-size typed array

  Known sizes.

• Split into an array

  Dim parts() As String parts = Split("a,b,c", ",") MsgBox UBound(parts) → 2, because Split returns a 0-based array

  Split is 0-based.

• Range arrays are 1-based and two-dimensional even for a single column.
• UBound and LBound give the bounds; never assume 0 or 1.
• Even one column read from a range is a 2D array: data(i, 1).
• Write back with Range.Value = data after resizing the target range.

• Off-by-one errors between 0-based and 1-based arrays.
• A single cell read into a Variant is a value, not an array.