Finding Duplicates in VBA Array (1 Viewer)


New member
Local time
Today, 12:49
Nov 26, 2023
Trying to identify duplicates in an array of numbers. I found some variations of this on the Internet - seems like a practical option.
The value of i and j in line 8 is 0 and 1 respectively - this makes sense with a zero-based array. Unsure what's out of range.

Public Function GetDuplicate() As Boolean
    Dim arr()
    Dim i As Long
    Dim j As Long
    arr = Array(123, 345, 654, 235, 94, 123)
    For i = LBound(arr) To UBound(arr)
        For j = i + 1 To UBound(arr)
            If arr(i, 1) = arr(j, 1) Then 'error is "Subscript out of range"
                GetDuplicate = True
                Exit Function
            End If
    GetDuplicate = False
End Function
You only have a one dimensional array not a two dimensional array
 If arr(i, 1) = arr(j, 1)
If arr(i) = arr(j)
Outstanding! thanks for the reply. it worked perfectly.
Your code works (after removing the second dimension), but it doesn't report the duplicate. It would be more efficient with a sorted array, assuming you need to re-use the array for some other reason.
As a database developer, you can/should work with tables and queries instead of first creating objects like arrays, which are not really suitable for the actual task.
Excel developers also use dictionaries in this context.

Users who are viewing this thread

Top Bottom