Finding Duplicates in VBA Array (1 Viewer)

john843

New member
Local time
Today, 12:49
Joined
Nov 26, 2023
Messages
5
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.

Code:
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
 
You only have a one dimensional array not a two dimensional array
Code:
 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

Back
Top Bottom