Excel VBA does not support dictionary with key as String and Item as System.Collections.ArrayList RRS feed

  • Question

  • Hello,

    Created a Dictionary with Key as String and Item as ArrayList. When key exists I try to retieve the Item by sending key it fails with  Invalid procedure call or argument

         Set testCaseVal = CreateObject("System.Collections.ArrayList")
        Set testCase = CreateObject("Scripting.Dictionary")      

        Set spRange = Range("B2:B17")

        For Each cell In spRange
            ValueItem = Range(Cells(cell.Row, 1), Cells(cell.Row, 1)).Text + "~" + Range(Cells(cell.Row, 3), Cells(cell.Row, 3)).Text
            Key = Range(Cells(cell.Row, 2), Cells(cell.Row, 2)).Text
            If Not testCase.Exists(Key) Then
                Set testCaseVal = CreateObject("System.Collections.ArrayList")
                testCaseVal.Add (ValueItem)
                testCase.Add Key, testCaseVal
               testCaseVal = testCase.Item(Key)
                testCaseVal.Add (ValueItem)
                testCase.Item(Key) = testCaseVal
            End If

        Next cell

        For Each x In testCase
            testCase.Item (x)
        Next x

    If I change the same Dictionary to use String as Item then it works without a glitch



    Monday, January 28, 2013 11:37 PM