Consulting

Results 1 to 4 of 4

Thread: .CompareMode with Dictionary

  1. #1
    Microsoft Word MVP 2003-2009 VBAX Guru gmaxey's Avatar
    Joined
    Sep 2005
    Posts
    3,043
    Location

    .CompareMode with Dictionary

    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
    Greg

    Visit my website: http://gregmaxey.com

  2. #2
    VBAX Guru Kenneth Hobs's Avatar
    Joined
    Nov 2005
    Location
    Tecumseh, OK
    Posts
    4,846
    Location
    Set oDic = CreateObject("scripting.dictionary")
    With oDic

  3. #3
    Microsoft Word MVP 2003-2009 VBAX Guru gmaxey's Avatar
    Joined
    Sep 2005
    Posts
    3,043
    Location
    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
    Greg

    Visit my website: http://gregmaxey.com

  4. #4
    Knowledge Base Approver VBAX Guru
    Joined
    Apr 2012
    Posts
    4,519
    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
    Last edited by snb; 08-21-2019 at 01:27 AM.

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •