.CompareMode with Dictionary

[gmaxey]

I have the following two procedures in an Excel workbook. Can someone offer and explanation why the first runs as expected, but the second will not compile or run:


Sub CompareModeDemoI()
Dim oKey
'Compiles and runs without error.
With CreateObject("scripting.dictionary")
.CompareMode = 1
For Each oKey In Array("X", "Y", "Z", "x", "y", "z")
On Error Resume Next
.Item(oKey) = oKey
Next oKey
Debug.Print "Item Count: " & .Count
End With
End Sub

Sub CompareModeDemoII()
'This version will not compile or run.
Dim oDic As Object
Dim oKey
Set oDic = CreateObject("scripting.dictionary")
.CompareMode = 1 'Compile error invalid or unqualified reference.
For Each oKey In Array("X", "Y", "Z", "x", "y", "z")
On Error Resume Next
.Item(oKey) = oKey 'Here upper and lower case are not unique. Item content defiend same as key.
Next oKey
Debug.Print "Item Count: " & .Count
End With
End Sub

The second won't run with or without a reference scripting.runtime

Thanks

[Kenneth Hobs]

Set oDic = CreateObject("scripting.dictionary")
With oDic

[gmaxey]

Doh!!!

Kenneth, I feel pretty stupid about that!! Actually, I was seeing that error as part of a larger process and I see now the cause is I wasn't setting the .CompareMode as the first step:

Sorry for wasting your time. Thanks.


Sub CompareModeWorking()
'This version will not compile or run.
Dim oDic As Object
Dim oKey
Set oDic = CreateObject("scripting.dictionary")
With oDic
.CompareMode = 1
.Add 123, "ABCDE"
For Each oKey In Array("X", "Y", "Z", "x", "y", "z")
On Error Resume Next
.Item(oKey) = oKey 'Here upper and lower case are not unique. Item content defiend same as key.
Next oKey
Debug.Print "Item Count: " & .Count
End With
End Sub

Sub CompareModeBroke()
'This version will not compile or run.
Dim oDic As Object
Dim oKey
Set oDic = CreateObject("scripting.dictionary")
With oDic
.Add 123, "ABCDE"
.CompareMode = 1
For Each oKey In Array("X", "Y", "Z", "x", "y", "z")
On Error Resume Next
.Item(oKey) = oKey 'Here upper and lower case are not unique. Item content defiend same as key.
Next oKey
Debug.Print "Item Count: " & .Count
End With
End Sub

[snb]

Sub M_snb()
With CreateObject("scripting.dictionary")
.CompareMode = 0
.Add "L_" & .Count, "A B C D E"
For Each it In Array("X", "Y", "Z", "x", "y", "z")
.Item(it) = it
Next

MsgBox Join(.keys, vbLf)
End With

With CreateObject("scripting.dictionary")
.CompareMode = 1
.Add "L_" & .Count, "A B C D E"
For Each it In Split("X Y Z x y z")
.Item(it) = it
Next

MsgBox Join(.keys, vbLf)
End With

With CreateObject("scripting.dictionary")
.CompareMode = 1
.Add "L_" & .Count, "A B C D E"
For Each it In Split("X Y Z x y z")
.Item(it) = it
Next

MsgBox Join(.keys, vbLf)
End With

With CreateObject("scripting.dictionary")
.CompareMode = 1
.Add "L_" & .Count, "A B C D E a b c d e"

For Each it In Split(.Item("L_0"))
.Item(it) = it
Next

MsgBox Join(.keys, vbLf)
End With

With CreateObject("scripting.dictionary")
.CompareMode = 0
.Add "L_" & .Count, "ABCDEabcde"

For Each it In Split(Format(.Item("L_0"), Replace(Space(Len(.Item("L_0"))), " ", "@ ")))
.Item(it) = it
Next

MsgBox Join(.keys, vbLf)
End With
End Sub

More on dictionaries: http://www.snb-vba.eu/VBA_Dictionary_en.html