Collections Can Be So Insensitive

I’ve never noticed this before, but the Key argument of the Add method for a collection is not case sensitive. That is, this throws an error:

Sub testcollection()

Dim col As Collection

Set col = New Collection

col.Add “me”, “me”
col.Add “Me”, “Me”

End Sub

Because it considers “me” and “Me” to be the same. I was adding some characters to a collection, then looping through that collection and using its elements in a Replace function. It was replacing lower case ‘i’, but not capital ‘I’. I thought I had a problem with my vbCompareMethod (vbBinaryCompare is case-sensitive and vbTextCompare is not). But whatever I tried didn’t work. Finally I looked at the collection and noted that the capital ‘I’ wasn’t being added.

I made this function to use as my key

Public Function ConvertStringToCodes(sInput As String) As String

Dim sReturn As String
Dim i As Long

For i = 1 To Len(sInput)
sReturn = sReturn & Format(Asc(Mid$(sInput, i, 1)), “000”)
Next i

ConvertStringToCodes = sReturn

End Function

So instead of

On Error Resume Next
mcolDelims.Add sDelim, CStr(sDelim)
On Error GoTo 0

I use

On Error Resume Next
mcolDelims.Add sDelim, ConvertStringToCodes(sDelim)
On Error GoTo 0

It seems to work. Am I missing a simpler solution?