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?

Posted in Uncategorized

3 thoughts on “Collections Can Be So Insensitive

  1. Nitpicking suggestion…use ascw instead of asc and use hex(ascw(…)) rather than format(asc(…)). The keys will be shorter w/o any loss of information.

    Or use a Scripting.Dictionary object. The keys appear to be case sensitive.

Posting code? Use <pre> tags for VBA and <code> tags for inline.

Leave a Reply

Your email address will not be published.