Introduction

Getting unique values from an array is a common VBA task in reporting, import cleanup, and worksheet automation. The most practical solution is usually Scripting.Dictionary, because it gives fast existence checks and a simple way to preserve first-seen order. Other patterns exist, but most of them are either slower or more awkward than a dictionary-based approach.

The standard solution: Scripting.Dictionary

The basic idea is straightforward: loop through the array, add each value as a dictionary key, and ignore keys that already exist.

1Option Explicit
2
3Public Function UniqueValues(values As Variant) As Variant
4    Dim dict As Object
5    Dim i As Long
6
7    Set dict = CreateObject("Scripting.Dictionary")
8
9    For i = LBound(values) To UBound(values)
10        If Not dict.Exists(values(i)) Then
11            dict.Add values(i), True
12        End If
13    Next i
14
15    UniqueValues = dict.Keys
16End Function
17
18Public Sub DemoUniqueValues()
19    Dim items As Variant
20    Dim uniqueItems As Variant
21    Dim i As Long
22
23    items = Array("A", "B", "A", "C", "B")
24    uniqueItems = UniqueValues(items)
25
26    For i = LBound(uniqueItems) To UBound(uniqueItems)
27        Debug.Print uniqueItems(i)
28    Next i
29End Sub

This returns A, B, C in the order they first appeared. That ordering is often exactly what you want in Excel automation.

Case-insensitive uniqueness for text

If your array contains strings and "A" should be treated the same as "a", set the dictionary compare mode before you start inserting values.

1Option Explicit
2
3Public Function UniqueValuesIgnoreCase(values As Variant) As Variant
4    Dim dict As Object
5    Dim i As Long
6
7    Set dict = CreateObject("Scripting.Dictionary")
8    dict.CompareMode = vbTextCompare
9
10    For i = LBound(values) To UBound(values)
11        If Not dict.Exists(values(i)) Then
12            dict.Add values(i), True
13        End If
14    Next i
15
16    UniqueValuesIgnoreCase = dict.Keys
17End Function

Without CompareMode, VBA treats differently cased strings as distinct keys. That may be correct for IDs, but usually not for user-entered text.

Handle arrays coming from worksheet ranges

One subtle detail is that arrays built with Array(...) are one-dimensional, but values read from a worksheet range are usually two-dimensional and one-based. If your source is a single worksheet column, loop over the first dimension explicitly.

1Option Explicit
2
3Public Function UniqueFromRangeColumn(data As Variant) As Variant
4    Dim dict As Object
5    Dim r As Long
6
7    Set dict = CreateObject("Scripting.Dictionary")
8
9    For r = LBound(data, 1) To UBound(data, 1)
10        If Not IsEmpty(data(r, 1)) Then
11            If Not dict.Exists(CStr(data(r, 1))) Then
12                dict.Add CStr(data(r, 1)), True
13            End If
14        End If
15    Next r
16
17    UniqueFromRangeColumn = dict.Keys
18End Function
19
20Public Sub DemoRangeValues()
21    Dim source As Variant
22    Dim uniqueItems As Variant
23    Dim i As Long
24
25    source = Sheet1.Range("A1:A10").Value
26    uniqueItems = UniqueFromRangeColumn(source)
27
28    For i = LBound(uniqueItems) To UBound(uniqueItems)
29        Debug.Print uniqueItems(i)
30    Next i
31End Sub

This matters because code written for a one-dimensional array will fail if you feed it a range value directly.

Why Collection is a weaker fallback

You may also see a Collection pattern that uses On Error Resume Next to ignore duplicates. It works, but it is less explicit and harder to maintain.

1Public Function UniqueWithCollection(values As Variant) As Collection
2    Dim result As New Collection
3    Dim i As Long
4
5    On Error Resume Next
6    For i = LBound(values) To UBound(values)
7        result.Add values(i), CStr(values(i))
8    Next i
9    On Error GoTo 0
10
11    Set UniqueWithCollection = result
12End Function

That style depends on duplicate-key errors for normal control flow. A dictionary is clearer because duplicate detection is an ordinary Exists check.

Common Pitfalls

The most common mistake is forgetting what kind of array you have. A worksheet range usually gives you a two-dimensional variant array, not the one-dimensional shape returned by Array(...).

Another issue is ignoring case-sensitivity requirements. If the business rule says "NY" and "ny" are the same value, set CompareMode before adding keys.

Mixed data types can also surprise you. Numeric 1 and string "1" are not necessarily the same key unless you normalize the values yourself.

Finally, if you use Collection plus On Error Resume Next, always restore normal error handling afterward.

Summary

• In VBA, Scripting.Dictionary is usually the cleanest way to get unique values from an array.
• Add each unseen value as a dictionary key and return dict.Keys.
• Set CompareMode when text uniqueness should ignore case.
• Treat worksheet range arrays differently from one-dimensional arrays built in code.
• Prefer explicit dictionary logic over Collection plus error-driven duplicate detection.