API reference · Arrays

Unique

Return distinct values from the first column in first-appearance order.

FunctionArrays→VariantSource L558

Signature#

Function Unique(ByVal vArr As Variant) As Variant

Parameters#

Parameter	VBA type	Default	Description
`vArr`	`Variant`ByVal	Required	Allocated 1D array or 2D array. A 1D input is expanded; a multi-column 2D input contributes only its lowest-indexed column.

Returns#

Variant containing a 2D column with bounds (1 To distinctCount, 1 To 1). Returns Empty for unsupported rank, non-array input, or no retained values, such as an input consisting only of Excel errors.

Example#

SEE THE TRANSFORMATIONKeep the first occurrence of each value

Before

categories1D · (4,)

Indices 0..3 · one dimension

0	1	2	3
“Sales”	“Fees”	“Sales”	“sales”

After

distinct2D · 3 × 1

Rows 1..3 · Columns 1..1

row / col	1
1	“Sales”
2	“Fees”
3	“sales”

Sales and sales are distinct under binary text comparison. The result is a 2D column.

Shaded headers are array indices, not data. Quoted cells are text. Values shown are derived from the source, not an Excel execution.

Sub ExampleUnique()
    Dim a As New Arrays
    Dim categories As Variant, distinct As Variant

    categories = VBA.Array("Sales", "Fees", "Sales", "sales")
    distinct = a.Unique(categories)
    Debug.Print distinct(1, 1) ' Expected: Sales
    Debug.Print distinct(2, 1) ' Expected: Fees
    Debug.Print distinct(3, 1) ' Expected: sales; binary text comparison
End Sub

Behavior and edge cases#

Uses an early-bound Dictionary with CompareMode=vbBinaryCompare. Text keys are case-sensitive, so 'Sales' and 'sales' remain distinct.
The source records keys explicitly as they are first encountered. It does not sort the result or return entire distinct rows.
Excel error values are skipped. One Null and one Empty are retained through separate flags, each at its first occurrence. A zero-length string follows the dictionary path and is distinct from Empty.
Other key equivalence follows Scripting.Dictionary's key behavior; the function does not normalize numeric strings with CDbl or text with UCase.
The Microsoft Scripting Runtime reference is required for the Dictionary declaration. Unsupported dictionary keys can raise runtime errors. The input array is not modified.

Performance#

View implementation

Function Unique(ByVal vArr As Variant) As Variant
    'Returns the unique values from a column vector in first-appearance order

    Dim oDict                   As New Dictionary
    Dim vArr_Out                As Variant
    Dim vKeys()                 As Variant
    Dim vKey                    As Variant
    Dim lRow                    As Long
    Dim lCol                    As Long
    Dim lCount                  As Long
    Dim bBlank                  As Boolean
    Dim bNull                   As Boolean

    If Not IsArray(vArr) Then Exit Function
    If Me.DimCounter(vArr) = 1 Then vArr = Me.DimExpand(vArr)
    If Me.DimCounter(vArr) <> 2 Then Exit Function

    lCol = LBound(vArr, 2)
    ReDim vKeys(1 To UBound(vArr, 1) - LBound(vArr, 1) + 1)
    oDict.CompareMode = vbBinaryCompare

    For lRow = LBound(vArr, 1) To UBound(vArr, 1)
        vKey = vArr(lRow, lCol)
        If IsError(vKey) Then
            'Excel errors are skipped because they cannot be dictionary keys
        ElseIf IsNull(vKey) Then
            If Not bNull Then
                lCount = lCount + 1
                vKeys(lCount) = Null
                bNull = True
            End If
        ElseIf IsEmpty(vKey) Then
            If Not bBlank Then
                lCount = lCount + 1
                vKeys(lCount) = Empty
                bBlank = True
            End If
        ElseIf Not oDict.Exists(vKey) Then
            lCount = lCount + 1
            oDict.Add vKey, lCount
            vKeys(lCount) = vKey
        End If
    Next lRow

    If lCount = 0 Then Exit Function

    ReDim vArr_Out(1 To lCount, 1 To 1)
    For lRow = 1 To lCount
        vArr_Out(lRow, 1) = vKeys(lRow)
    Next lRow

    Unique = vArr_Out
End Function