Object not defined

Question

"channel", "facebook", "instagram" and "twitter" are "Object" data types

"i", "j" and "k" are "integer" data types

"ws" is "Woksheet" data type

Set channel = CreateObject("Scripting.Dictionary")

With channel

    .Add "facebook", facebook
    .Add "instagram", instagram
    .Add "twitter", twitter

End With

Set facebook = CreateObject("Scripting.Dictionary")

With facebook

    .Add "brand", 0
    .Add "post", 0
    .Add "likes", 0
    .Add "comments", 0
    .Add "boosted", 0
    .Add "shares", 0

End With

Set instagram = CreateObject("Scripting.Dictionary")

With instagram

    .Add "brand", 0
    .Add "post", 0
    .Add "likes", 0
    .Add "comments", 0

End With

Set twitter = CreateObject("Scripting.Dictionary")

With twitter

    .Add "brand", 0
    .Add "post", 0
    .Add "likes", 0
    .Add "retweets", 0
    .Add "is retweet", 0

End With

For Each ws In Sheets

    For i = 0 To channel.count - 1

        If channel.Keys()(i) = ws.name Then

            Sheets(ws.name).Activate
            emptyCol = WorksheetFunction.CountA(Range("1:1"))
            emptyRow = WorksheetFunction.CountA(Range("A:A"))

            For j = 1 To emptyCol

                For k = 0 To channel(channel.Keys()(i)).count - 1

                    If InStr(Cells(1, j), channel(channel.Keys()(i)).Keys()(k)) <> 0 Then

                        channel(channel.Keys()(i))(channel(channel.Keys()(i))) = j
                        MsgBox channel(channel.Keys()(i)) & "." & channel(channel.Keys()(i)) & "=" & j

                    End If

                Next k

            Next j

        End If

    Next i

Next ws

The error which is coming up is:

It is highlighting the following code: For k = 0 To channel(channel.Keys()(i)).count - 1

Hi, Yes. Iterating, checking them with the column headers and storing the column number as their item. — Vaibhav Miglani, Sep 01 '16 at 13:41
Since you only have 3 items in the dictionary, it might be easier to just use an Array. See this answer for an example to iterate the keys of a dictionary. http://stackoverflow.com/questions/39271503/type-mismatch-error-array-parsing-json-array-of-strings-in-vba — Ryan Wildry, Sep 01 '16 at 13:52
You are saying to use an array instead of the "channel" dictionary? — Vaibhav Miglani, Sep 01 '16 at 13:55
Yeah, I mean dictionary are typically employed for fast lookups when you have a lot of items. You have 3 items. You can do it this way, however an Array or even a Collection might be a better fit. — Ryan Wildry, Sep 01 '16 at 13:59

score 0 · Accepted Answer · edited Sep 21 '17 at 17:27

You're assembling your Dictionary objects in the wrong order. When this code runs...

With channel
    .Add "facebook", facebook
    .Add "instagram", instagram
    .Add "twitter", twitter
End With

...facebook, instagram, and twitter aren't set to anything. I'm guessing they aren't declared either, so you're storing empty Variant's instead of objects. When you eventually create the actual object, it's too late because they were stored in the Dictionary by value, so the underlying variable just gets changed to point at the new object. See Why should I dimension my variables in VBA really?.

The immediate fix is to just reorder the top section of code like this...

Set facebook = CreateObject("Scripting.Dictionary")
With facebook
    .Add "brand", 0
    .Add "post", 0
    .Add "likes", 0
    .Add "comments", 0
    .Add "boosted", 0
    .Add "shares", 0
End With

Set instagram = CreateObject("Scripting.Dictionary")
With instagram
    .Add "brand", 0
    .Add "post", 0
    .Add "likes", 0
    .Add "comments", 0
End With

Set twitter = CreateObject("Scripting.Dictionary")
With twitter
    .Add "brand", 0
    .Add "post", 0
    .Add "likes", 0
    .Add "retweets", 0
    .Add "is retweet", 0
End With

Set channel = CreateObject("Scripting.Dictionary")
With channel
    .Add "facebook", facebook
    .Add "instagram", instagram
    .Add "twitter", twitter
End With

The solution is to add Option Explicit which ensures that your variables are properly declared.

Object not defined

1 Answers1