Strange ByRef Error in Array Duplicate Removal VBA

[btorrean]

Dear MrExcel Users,

I am trying to remove duplicate values from an array that I am creating from a range. The range is a named range in my workbook, and is only one column of values. I found some code on-line to help me do this, but when I put it in my code and run it, I get a 'ByRef argument type mismatch' error at the following line:

If Not IsInArray(tempArray, arr(i)) Then ' not in destination array

Does anyone have an idea of why this is happening? I have been unable to contact the code creator.

Public Sub TestRemoveDupesFromArray()Dim rngProcSheets As Range
Dim arrProcSheets() As Variant
Dim strProcSheetsOut As String
Dim R As Long
Dim C As Long
Set rngProcSheets = Range("rngProcSheets")
strProcSheetsOut = ""
' Turn the range into an array.
arrProcSheets = rngProcSheets
' Remove the duplicates from the array
arrProcSheets = Array_Unique(arrProcSheets)
For R = 1 To UBound(arrProcSheets, 1) ' First array dimension is rows.
    For C = 1 To UBound(arrProcSheets, 2) ' Second array dimension is columns.
        strProcSheetsOut = strProcSheetsOut & vbCrLf & arrProcSheets(R, C)
    Next C
Next R
MsgBox strProcSheetsOut
End Sub

Function Array_Unique(arr As Variant) As Variant
 ' Purpose: Re-creates an array to only include unique items.  Returns 
 '   the new array.
 ' Created by: Kannan Chandrasekaran
 ' URL: [URL="http://ckannan.blogspot.com/2012/09/vba-distinct-array-items-remove.html"]Kannan's Tech Blog..: VBA: Distinct Array items - Remove duplicates[/URL]
 ' Download date: 03/22/2013
 ' Downloaded by: BT 03/22/2013
 Dim tempArray As Variant
 Dim i As Long
 
 ' start the temp array with one element and
 ' populate with first value
 ReDim tempArray(0)
 tempArray(0) = arr(LBound(arr))
 
 For i = LBound(arr) To UBound(arr)
  If Not IsInArray(tempArray, arr(i)) Then  ' not in destination array
   ReDim Preserve tempArray(UBound(tempArray) + 1)
   tempArray(UBound(tempArray)) = arr(i)
  End If
 Next i
 
 Array_Unique = tempArray
  
End Function

Function IsInArray(arr As Variant, valueToCheck As String, _
   Optional exactMatch As Boolean = True) As Boolean
 ' Purpose: Checks to see if a value is in an array.  Returns True
 '   if the value is in the array, False if not.
 ' Created by: Kannan Chandrasekaran
 ' URL: [URL="http://ckannan.blogspot.in/2012/09/vba-isinarray-find-value.html"]Kannan's Tech Blog..: VBA: IsInArray - Find a value in array items[/URL]
 ' Download date: 03/22/2013
 ' Downloaded by: BT 03/22/2013
   Dim wordList As String
   Dim startPosition As Long
   Dim nextCommaPosition As Long
   Dim matchedTerm As String    
   
   If UBound(Filter(arr, valueToCheck)) > -1 Then
  wordList = Join(arr, ",")
  ' start from the allegedly matched term ....
  startPosition = InStr(wordList, valueToCheck)
  ' get position of the comma after the allegedly matched term ...
  nextCommaPosition = InStr(startPosition + 1, wordList, ",")
  ' the alleged "match" is in between
  matchedTerm = Mid$(wordList, startPosition, _
   nextCommaPosition - startPosition)
  If exactMatch Then
   IsInArray = (StrComp(valueToCheck, matchedTerm) = 0)
  Else
   IsInArray = (StrComp(valueToCheck, matchedTerm) <> 0)
  End If
   End If
End Function

 

[CodeNinja]

When you create the array by making it from a range, it is a multidimensional array (rows, columns) so, when you try to say is not in arr(i) it does not have enough arguments... You are probably going to have to do something like arr(i,1)

 

[btorrean]

CodeNinja, Thanks for the reply. Unfortunately, that didn't work. I still get the same error.

Brian

 

[Firefly2012]

Hi Brian

If you change that line to this do you still get the error?

If Not IsInArray(tempArray, CStr(arr(i))) Then

 

[btorrean]

Firefly2012,

Thanks! That may have solved that problem, but now I'm getting a 'Subscript out of range' error at the following line:

tempArray(0) = arr(LBound(arr))

Brian

 

[CodeNinja]

Heh, not to beat my point in too much, but that is the error I got from the multidimensional array issue... It might be looking for 2 dimensions... or I could be totally wrong...

 

[Firefly2012]

I think CodeNinja is right there - in your Sub change the array assignment line to:

arrProcSheets = Application.Transpose(rngProcSheets)

 

[CodeNinja]

Heh... YAY I got something right!!!

 

[btorrean]

Dear Firefly2012 and CodeNinja,

Sorry for the delay in reply. The fixes to the code didn't work and my supervisor had me take another direction with the programming. That's why it took me so long to get back to you. I'm sorry that I had to abandon that code. I think it would have been really useful. Thanks again for the help, though. I wish it would have worked!

Brian