VBA: New collection -> A module is not a valid type

Question

I'm trying to use a collection as part of a function, however I keep getting the error: "A module is not a valid type" on compile.

Even if the function simply defines a collection, I get the same:

Function CountUniqueTags()
    Dim table As Collection
    Set table = New Collection
End Function

This code is in a standard module, but the error implies I should be writing this in a class module, but Collection is a built-in class so I don't see the issue?

Answer 1

This was making me go crazy for a while, too. I had code in one file that worked fine. Copied the code, went to a different file, added and renamed the module, pasted in the code, and suddenly the code will not compile! I read several unhelpful answers before I saw one that gave me the clue I needed.

The error message states "A module is not a valid type" (note the emphasis on the word "module"). That means the Type you are specifying (in your case, "Collection") is also the name of a Module.

If you rename the Module "Collection" to some other name, the error will go away.