Identifier under cursor is not recognized- count unique

[vivi218]

Hi dear forum users,
I am trying to find and motify a useful code to count unique row in the excel, here is the code:

#
Public Function CountUnique(rng As Range) As Long
Dim dict As Scripting.Dictionary
Dim cell As Range
Set dict = New Scripting.Dictionary
For Each cell In rng.Cells
If Not dict.Exists(cell.Value) Then
dict.Add cell.Value, 0
End If
Next
CountUnique = dict.count
End Function

#
However, it shows "Identifier under cursor is not recognized" for the first row of the code "Public Function CountUnique(rng As Range) As Long".

Anyone know why this happened and how to solve this? Thank you in advance for any help and suggestions.

 

[Eric W]

Welcome to the forum.

It might be due to the difference between early binding and late binding. With early binding, like your code, you have to have the external reference to the scripting dictionary already included. With late binding, you can define it within your macro. Try this:

Public Function CountUnique(rng As Range) As Long
Dim dict As Object
Dim cell As Range

    Set dict = CreateObject("Scripting.Dictionary")
    For Each cell In rng.Cells
        dict(cell.Value) = 1
    Next
    CountUnique = dict.Count
End Function