Collection does not work with strings?
 

Hi all coders out there!

I wanted a function that returned the number of unique elements in a
range.
And I thought that the best and easyest solution would be using the
Collection object.
But to my big surprice the solution I came up with only worked with
numbers!

Option Explicit

' Returns the number of unique values in the given range.
' Only works with numbers. why not strings?
Public Function uniqueCountInt(r As Range)
Dim tmpR As Range
Dim tmpColl As New Collection
On Error Resume Next

For Each tmpR In r.Cells
tmpColl.Item tmpR.Cells.Value

If Err.Number < 0 Then
tmpColl.Add tmpR.Cells.Value
End If
Err.Clear
Next

uniqueCountInt = tmpColl.Count
End Function


Does any one out there have a tip on the easyest way of making the
function work with strings (and numbersc as well if possible)?
Is Collection the right way to do it?

best regards/
Olaf

Collection does not work with strings?
 

Sub AAAA()
Dim rng As Range
Set rng = Range("A1:a15")
Debug.Print uniqueCountInt(rng)
End Sub

Public Function uniqueCountInt(r As Range)
Dim tmpR As Range
Dim tmpColl As New Collection
On Error Resume Next

For Each tmpR In r.Cells
tmpColl.Add tmpR.Value, CStr(tmpR.Value)
Next
On Error GoTo 0
uniqueCountInt = tmpColl.Count
End Function


Worked fine for me with strings. Note that is was case insensitive.

--
Regards,
Tom Ogilvy

"Olaf" wrote in message
om...
Hi all coders out there!

I wanted a function that returned the number of unique elements in a
range.
And I thought that the best and easyest solution would be using the
Collection object.
But to my big surprice the solution I came up with only worked with
numbers!

Option Explicit

' Returns the number of unique values in the given range.
' Only works with numbers. why not strings?
Public Function uniqueCountInt(r As Range)
Dim tmpR As Range
Dim tmpColl As New Collection
On Error Resume Next

For Each tmpR In r.Cells
tmpColl.Item tmpR.Cells.Value

If Err.Number < 0 Then
tmpColl.Add tmpR.Cells.Value
End If
Err.Clear
Next

uniqueCountInt = tmpColl.Count
End Function


Does any one out there have a tip on the easyest way of making the
function work with strings (and numbersc as well if possible)?
Is Collection the right way to do it?

best regards/
Olaf

Collection does not work with strings?
 

Olaf,
You need to add the string value as a key. Indeed the key only accepts
string, not numeric variable (unless you CStr first)

From the help file:
MyClasses.Add item := Inst, key := CStr(Num)

NickHK

"Olaf" wrote in message
om...
Hi all coders out there!

I wanted a function that returned the number of unique elements in a
range.
And I thought that the best and easyest solution would be using the
Collection object.
But to my big surprice the solution I came up with only worked with
numbers!

Option Explicit

' Returns the number of unique values in the given range.
' Only works with numbers. why not strings?
Public Function uniqueCountInt(r As Range)
Dim tmpR As Range
Dim tmpColl As New Collection
On Error Resume Next

For Each tmpR In r.Cells
tmpColl.Item tmpR.Cells.Value

If Err.Number < 0 Then
tmpColl.Add tmpR.Cells.Value
End If
Err.Clear
Next

uniqueCountInt = tmpColl.Count
End Function


Does any one out there have a tip on the easyest way of making the
function work with strings (and numbersc as well if possible)?
Is Collection the right way to do it?

best regards/
Olaf