Copied straight out of the VB module:
Function UniqueCount(rngCells As Range) As Integer

Dim vCell As Variant

' Setup dictionary
Dim unique  As Object
Set Unique = CreateObject("Scripting. Dictionary")

' Populate dictionary
For Each vCell In rngCells
If Not Unique.exists(vCell.Value) Then
Unique.Add vCell.Value, vCell.Value
End If
Next vCell

' Count unique items
UniqueCount = Unique.Count

' Clean up
Set Unique = Nothing

End Function


Calling the function in cell B4 with a named range (MyRange) of text values;
=UniqueCount(MyRange)
also tried manually setting a range of cells:
UniqueCount(A2:A7)