Skip to content
Tutorials Center
My learning

Topics

Lesson 76 of 93 ยท Automation and Collaboration

VBA Arrays, Collections and Strings

Learn VBA arrays (static, dynamic, ReDim Preserve, 2D), Collection and Dictionary objects, removing duplicates, string and date functions, and constants for fast macros.

A macro that reads and writes one cell at a time spends most of its life waiting on Excel. A macro that loads a whole range into memory, works on it there and writes the result back in one step can be hundreds of times faster. That is what arrays are for. Collections and Dictionaries give you lookup tables, and VBA's string and date functions clean and reshape the text you find in cells.

Excel and VBA cannot run on this site, so the VBA listings are read-only. The TryIts re-create the same algorithms in JavaScript (arrays, ReDim Preserve, a Dictionary-style tally, Split/Mid) so you can see exactly what the code does. The stand-ins are not VBA.

Arrays

An array is a numbered row of variables of one type. By default VBA arrays start at index 0; Option Base 1 at the top of a module changes that, and LBound/UBound tell you the real limits so your loops do not depend on it.

VB
Dim months(1 To 3) As String          ' static: size fixed, explicit bounds
months(1) = "Jan": months(2) = "Feb": months(3) = "Mar"

Dim i As Long
For i = LBound(months) To UBound(months)
    Debug.Print i, months(i)
Next i

Dynamic arrays and ReDim Preserve

When you do not know the size in advance, declare an empty array and size it later with ReDim. Plain ReDim erases the contents; ReDim Preserve keeps them, but only the last dimension can be resized.

VB
Dim names() As String
Dim n As Long
For Each cell In ws.Range("A2:A100")
    If cell.Value <> "" Then
        ReDim Preserve names(0 To n)   ' grow by one, keep old items
        names(n) = cell.Value
        n = n + 1
    End If
Next cell

Growing by one each time copies the array every time, so for big data grow in chunks or count first. Split and Array are shortcuts: parts = Split("a,b,c", ",") builds a zero-based array of strings, and Array(1, 2, 3) builds a Variant array.

Two dimensions: the range-to-array trick

Assigning a range's .Value to a Variant gives you a two-dimensional, one-based array (rows first, then columns), even for a single column. This is the single most useful speed-up in VBA.

VB
Dim data As Variant, r As Long
data = ws.Range("A2:C5001").Value            ' one read, 15,000 cells

For r = 1 To UBound(data, 1)                 ' dimension 1 = rows
    data(r, 3) = data(r, 1) * data(r, 2)     ' work in memory
Next r

ws.Range("A2:C5001").Value = data            ' one write

Note that UBound(data, 1) is the row count and UBound(data, 2) the column count. You can also declare multi-dimensional arrays yourself: Dim grid(1 To 3, 1 To 4) As Double.

Run it: ReDim Preserve and the read-compute-write pattern

JS Growing an array and working on a 2D block in memory

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

No output.

Collection and Dictionary

A Collection is a built-in ordered list of items that you can Add, look up by position or by a text key, and Remove. A key that is already there raises an error, which makes Collections a classic (if clunky) way to get unique values.

VB
Dim seen As New Collection, cell As Range
On Error Resume Next                         ' Add fails on a duplicate key
For Each cell In ws.Range("A2:A100")
    seen.Add cell.Value, CStr(cell.Value)    ' item, key
Next cell
On Error GoTo 0
Debug.Print seen.Count & " unique values"

A Collection also lets you control where an item goes and how it is removed. Items are numbered from 1, and Add item, key, Before, After inserts at a position. Remove takes an index or a key, and later items shift down to close the gap, so when you delete in a loop, go from the end:

VB
Dim c As New Collection
c.Add "Item1", "k1"
c.Add "Item2", "k2"
c.Add "Item0", , 1            ' Before:=1, so it becomes the first item
c.Remove 2                    ' by index: removes Item1
c.Remove "k2"                 ' by key: removes Item2
Debug.Print c.Count           ' 1 (only Item0 is left)

Dim i As Long
For i = c.Count To 1 Step -1  ' back to front while deleting
    If c(i) = "Item0" Then c.Remove i
Next i

A function can build a Collection and return it with Set PopulateCollection = result, because a Collection is an object. There is no way to update an item in place; remove it and add the new value at the same position.

A Dictionary is the better tool. It stores key and item pairs, tests a key with .Exists and has no need for error tricks. It lives in the Scripting Runtime library, so create it with late binding (works on Windows; not available in Excel for Mac):

VB
Dim d As Object
Set d = CreateObject("Scripting.Dictionary")
d.CompareMode = vbTextCompare                ' keys ignore case

For Each cell In ws.Range("A2:A100")
    If d.Exists(cell.Value) Then
        d(cell.Value) = d(cell.Value) + 1    ' count occurrences
    Else
        d.Add cell.Value, 1
    End If
Next cell

For Each k In d.Keys
    Debug.Print k, d(k)
Next k

Removing duplicates from an array

Keys in a Dictionary are unique by definition, so adding every value as a key and then reading d.Keys gives the distinct values in first-seen order (assigning d(value) = 1 works even for repeats, since it overwrites rather than adds):

VB
For Each v In data                           ' data is any array of values
    d(v) = 1
Next v
unique = d.Keys                              ' a zero-based array

Excel's own Remove Duplicates (Removing Duplicates) and UNIQUE (Filter, Sort and Unique) do the same without code, and Range.RemoveDuplicates is the macro-recorder equivalent.

JS A Dictionary-style tally and distinct values

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

No output.

Strings

Text from cells is rarely clean. These functions cover most jobs; positions are 1-based.

Function Result for s = " Report-2025-Q3 "
Len(s) 18
Trim(s) "Report-2025-Q3" (also LTrim, RTrim)
UCase(s), LCase(s), StrConv(s, vbProperCase) case conversion
Left(t, 6), Right(t, 2), Mid(t, 8, 4) "Report", "Q3", "2025" where t = Trim(s)
InStr(t, "-") 7 (position of the first dash; 0 if none)
InStrRev(t, "-") 12 (search from the end)
Replace(t, "-", "/") "Report/2025/Q3"
Split(t, "-") array: Report, 2025, Q3
Join(parts, " ") the reverse of Split
Format(1234.5, "#,##0.00") "1,234.50"
StrReverse, Space(n), String(n, "*") reverse, padding

Use & to join text and vbCrLf or vbLf for line breaks (Excel cells use vbLf, i.e. Chr(10)). Compare text with StrComp(a, b, vbTextCompare) when case should not matter.

Dates and times

Date returns today, Now the current date and time, Time the time. DateAdd("m", 1, d) adds a month, DateDiff("d", a, b) counts days, DatePart("q", d) returns the quarter, and Year, Month, Day, Weekday take dates apart. DateSerial(2025, 3, 31) builds a date safely from numbers, and IsDate(x) tests whether text is a date. They match the worksheet functions in Dates and Times and Date Calculations, except that VBA works in your system's regional settings for date text, so build dates with DateSerial.

Constants

A constant is a named value that cannot change. Use them for rates, sheet names and column numbers so a change needs one edit.

VB
Const TAX_RATE As Double = 0.2
Const SHEET_DATA As String = "Sales"
Public Const COL_AMOUNT As Long = 3         ' visible to all modules

VBA also ships ready-made constants: vbCrLf, vbYes, xlUp, vbRed.

JS Split, Mid, InStr and Join on a messy label

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

No output.

Check your understanding

Quick check ยท 4 questions

of 4 answered You scored / 4 ยท perfect!

  1. 1. What does ReDim Preserve do compared with plain ReDim?

    Plain ReDim clears the array. ReDim Preserve keeps the old values, but only the last dimension may change size.
  2. 2. You write data = Range("A2:A100").Value. What kind of array is data?

    A range's Value always comes back as a 1-based 2D Variant array, even for a single column, so you read it as data(row, 1).
  3. 3. Why is a Dictionary often better than a Collection for unique values?

    Dictionary.Exists tests a key directly. A Collection raises an error on a duplicate key, so you must trap it. The Scripting.Dictionary is Windows only.
  4. 4. What does InStr("Report-2025", "-") return?

    VBA positions start at 1, and the dash is the 7th character. InStr returns 0 when the text is not found.

Summary

  • Arrays are 0-based by default (Option Base 1 or 1 To n bounds change that); use LBound and UBound in loops.
  • ReDim Preserve grows only the last dimension; a range's .Value gives a 1-based 2D array, and writing it back is the big speed-up.
  • A Collection holds items with optional unique keys; a Scripting.Dictionary adds Exists, counts and Keys (Windows only).
  • Dictionary keys give you distinct values; Excel's Remove Duplicates and UNIQUE do the same without code.
  • Trim, Left, Mid, InStr, Replace, Split and Join are 1-based string tools; use DateSerial and DateAdd for dates.
  • Const names fixed values such as rates and sheet names.

Next: VBA Events, UserForms and Controls.