Finding Duplicates in VBA Array (1 Viewer)

[john843]

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
        Next
    Next
    GetDuplicate = False
End Function

 

[MajP]

You only have a one dimensional array not a two dimensional array

 If arr(i, 1) = arr(j, 1)

If arr(i) = arr(j)

Array Dimensions - Visual Basic

Learn more about: Array Dimensions in Visual Basic

learn.microsoft.com

 

[john843]

Outstanding! thanks for the reply. it worked perfectly.

 

[gemma-the-husky]

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.

 

[ebs17]

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.