VBA arrays

An array is many values under one name. Instead of twelve variables, you declare one array indexed by position.

Dim months(1 To 12) As Double   ' static: size fixed at declaration
months(1) = 125000

That's a static array. Its size is baked in. Read its edges with LBound(months) and UBound(months) instead of hardcoding 1 and 12, so a resize never orphans a loop. If you won't know the size until run time, declare a dynamic array with empty parentheses and size it later with ReDim:

Dim vals() As Double
ReDim vals(1 To n)          ' size now known

ReDim wipes the array's contents. To grow an array and keep what's already in it, add Preserve:

ReDim Preserve vals(1 To UBound(vals) + 1)

One rule trips everyone up. ReDim Preserve can only change the last dimension of a multi-dimensional array, and you can't change the lower bound either. For one-dimensional arrays that's no constraint. For 2-D arrays, plan your growth along the last axis.

Touching cells one at a time is slow. The professional move, the fast-array idiom, is to slurp a whole range into a Variant in a single assignment, work on it in memory, then write it back once:

Dim data As Variant
data = Range("A2:C100").Value        ' one hit: a 2-D, 1-based array (99 x 3)
' ... loop over data(i, 1) etc. in memory ...
Range("A2:C100").Value = data        ' one write-back

That array is always 1-based and two-dimensional (data(row, col)), even for a single column. It catches a lot of people out. VBA III makes this idiom the core of its performance lesson.

What is a Dictionary in VBA?

The Dictionary gives you keyed memory. An array is indexed by number. A Scripting.Dictionary is indexed by any key (a string, a code, a name), and it answers "have I seen this before?" in near-constant time. That makes it the right tool for de-duplication, counting, and fast lookups inside a loop. There are two ways to create one:

' Early binding — add reference first: Tools > References > Microsoft Scripting Runtime
Dim dict As Scripting.Dictionary
Set dict = New Scripting.Dictionary

' Late binding — no reference needed, works on any machine
Dim dict As Object
Set dict = CreateObject("Scripting.Dictionary")

Notice the Set. A Dictionary is an object, and object variables have to be assigned with Set, never with a plain =. Now its workhorse members. .Add key, item stores a pair, and .Exists(key) tests membership without creating the key. .Item(key) reads the item, or creates it without warning if the key is absent. .Keys and .Items return arrays, .Count is the number of pairs and .Remove key deletes one. Here's the canonical de-dup / count pattern:

Dim c As Range
For Each c In Range("A2:A100")
    If dict.Exists(c.Value) Then
        dict(c.Value) = dict(c.Value) + 1     ' seen before: increment its tally
    Else
        dict.Add c.Value, 1                   ' first sighting
    End If
Next c
' dict.Count is now the number of DISTINCT values; dict.Items holds their counts

What are the common Dictionary gotchas?

Case-sensitivity is the classic gotcha. By default a Dictionary treats "West" and "west" as different keys, so a group you meant as one gets split, and nothing warns you. Set dict.CompareMode = vbTextCompare before adding any keys to make matching case-insensitive. On a Mac, where Scripting.Dictionary is unavailable, a Collection gives you keyed storage but no .Exists. You test membership by trapping the error instead. It's uglier, so prefer the Dictionary on Windows.