[RESOLVED] A Scripting.Dictionary of arrays of collections

[caffeine]

I would like to have a scripting.dictionary.

    Dim sqlArray As Scripting.Dictionary
    Set sqlArray = New Scripting.Dictionary

Each element of the scripting dictionary is an array of 2 Collections.

            Dim tableName as string
            tableName = "foo"

            Dim ar(0 To 1) As Collection
            Set ar(0) = New Collection
            Set ar(1) = New Collection
            sqlArray.Add tableName, ar

Is there anything wrong with this code? Intellisense isn't working on it, and in my experience, if intellisense doesn't work with early binding, it usually means my code is wrong. This is the full routine:

Private Function createInsertsArray() As Scripting.Dictionary
    Dim sqlArray As Scripting.Dictionary
    Set sqlArray = New Scripting.Dictionary

    Dim ptr As Range, table As String
    Set ptr = Worksheets("fields").Range("A1")

    Do While ptr.Value <> vbNullString
        table = accessTable(ptr.Value)

        If Not sqlArray.Exists(table) Then
            Dim ar(0 To 1) As Collection
            Set ar(0) = New Collection
            Set ar(1) = New Collection
            sqlArray.Add table, ar
        End If

        sqlArray(table)(0).Add ptr.Offset(0, 4).Value
        sqlArray(table)(1).Add ptr.Offset(0, 5).Value
        debug.Print sqlArray(table)(0).  <---- intellisense stops working

        Set ptr = ptr.Offset(1, 0)
    Loop

    Set createInsertsArray = sqlArray
End Function

I'm really confused. This should be simple:

• sqlArray is a Scripting.Dictionary
• sqlArray(table) is an array of 2 Collections
• sqlArray(table)(0) is a Collection

Therefore, when I type the last "." in sqlArray(table)(0). I expect Collection methods to appear, but they don't. My syntax is probably wrong. Can someone please help?

Thanks!

[TnTinMN]

VBA Intellisense is not the most intelligent, but in this case there is not much that it could do.

The Scripting Dictionary is not a typed Dictionary like is available in other languages. The stored item is a Variant and each Dictionary entry could have a different type item. Hence there is nothing to give Intellisense a clue that the item you retrieved is an array and much less so the each array element is a Collection.

[caffeine]

VBA Intellisense is not the most intelligent, but in this case there is not much that it could do.

The Scripting Dictionary is not a typed Dictionary like is available in other languages. The stored item is a Variant and each Dictionary entry could have a different type item. Hence there is nothing to give Intellisense a clue that the item you retrieved is an array and much less so the each array element is a Collection.

Sorry for the tardy reply. New father trying to get a grip on this new phase of life.

Now that I think of it, you're probably right -- except -- it should be *possible* for intellisense to figure out what a Variant points to.

I've done some COM programming in the past, and am somewhat familiar with what Variants look like from a C++ perspective. I'm pretty sure I recall that there's some kind of "isArray()" method that returns a boolean. For the dictionary, since it's a COM object, it should have a class ID which is known to all COM aware DLLs.

So in principle, intellisense should know better, as long as you use early binding. But I guess you're right -- in practice, intellisense just isn't very intelligent. It's a shame. The design of COM is very beautiful. I feel like this could've been implemented had VBA's VBE been a priority....