VBA, Is there a way to check if a value is equal to any value inside array?

Question

I'm using an array as a list of values that are not present in a table. However, sometimes an equal value is inputed twice.

I have used a simple condition to avoid the current value being equal to the prior, but I can't seem to find a solution for duplicate values overall.

        If k > 0 Then
            If arrA(k) = arrA(k - 1) Then
                arrA(k) = ""
                k = k - 1
            End If
        End If

You can take `IsInArray` from https://stackoverflow.com/a/3244429/11683, or just [switch to a `Collection`](https://stackoverflow.com/a/3017973/11683) which makes duplicate tracking much easier. — GSerg, Jan 04 '19 at 18:27
Perhaps a dictionary or collection would be a better choice than an array. It's tough to say since it's not clear what you are using the array for (like if you are making use of the order of the elements downstream). — JNevill, Jan 04 '19 at 18:30
Also, if the array is reasonably small you could do an ugly version of `IsInArray()` in a one-liner `If InStr("|" & Join(arrA, "|") & "|", "|" & arrA(K) & "|") Then` — JNevill, Jan 04 '19 at 18:35

T.M. · Accepted Answer · 2019-01-06T20:37:49.990

Repeated filtering method

Just for the sake of the art, I demonstrate an approach via ► repeated filtering (i.e. without using a dictionary or collection - you'll find numerous dict/coll examples at SO :-):

Example code

This example assumes string values to be checked for duplicates (using case sensitivity in repeated filtering in a tricky way - cf. argument vbBinaryCompare in Filter function) and matching the current index position (idx) of each search term.

Option Explicit                     ' declaration head of code module
Sub DupEx()
' Purpose: delete subsequent string duplicates in array
' Method:  repeated filter function using match to get index position
' Site:    https://stackoverflow.com/questions/54044235/vba-is-there-a-way-to-check-if-a-value-is-equal-to-any-value-inside-array
' Author:  T.M. (https://stackoverflow.com/users/6460297/t-m)
  Dim arrA(), i&, idx&, flt, searched
' example string values (change to wanted values)
  arrA = Array("zero", "one", "two", "two", "three", "two", "four", "zero", "four", "three", "five", "five")
  flt = arrA
  Debug.Print "Original array counts " & UBound(flt) + 1 & " elements: " & Chr(34) & Join(flt, ", ") & Chr(34)
  For i = LBound(arrA) To UBound(arrA)
      searched = arrA(i)                                      ' define search term
      If UBound(Filter(flt, searched, True, vbBinaryCompare)) > 0 Then
         '[1] change first occurrence of search term to temporary dummy (to avoid later deletion)
          On Error Resume Next
          idx = Application.Match(searched, flt, False) - 1    ' deduct 1 as match result is one based
          flt(idx) = ChrW(&H2999)                              ' change to temporary dummy value
         '[2] execute filter (3rd argument=False DELETES each subsequent search value in flt array)
         '    [Caveat: this example deletes partial string findings as well !]
          flt = Filter(flt, searched, False, vbBinaryCompare)
         '[3] restore first occurrence back to old value
          flt(idx) = searched
      End If
  Next i
  Debug.Print "Filtered array counts " & UBound(flt) + 1 & " elements: " & Chr(34) & Join(flt, ", ") & Chr(34)
  'arrA = flt                                                   ' overwrite original array
End Sub

Debug.Print result as shown in the immediate window of the Visual Basic Editor (VBE)

  Original array counts 12 elements: "zero, one, two, two, three, two, four, zero, four, three, five, five"
  Filtered array counts 6 elements:  "zero, one, two, three, four, five"

VBA, Is there a way to check if a value is equal to any value inside array?

1 Answers1