How To Get Intellisense For Custom Collection?

[johnywhy]

how can i get Intellisense for collection items? example:

MyCollection(Key).SomeProperty

Where MyCollection(Key) returns a custom object (a custom class instance)

Here's how to make a Custom Collection:
http://www.dailydoseofexcel.com/archives/2010/07/04/custom-collection-class/

cheers!
johny

 

[Norie]

Johny

How have you created your custom class?

 

[RoryA]

You would have to assign MyCollection(key) to a variable declared as your class, and then use that variable.

 

[johnywhy]

by gosh, you're right. i AM getting intellisense. Here's the Item property from my custom collection class:

Public Property Get Item(Index As Variant) As Thing
    Set Item = objThings.Item(Index)
End Property

SO, my question is different: How can i pass a custom collection to a function that expects a collection? (that was my issue).

Only solution i can think of is to make the Col parameter a Variant, which is undesirable for the standard reasons. If i wrap it in parens in the Caller (to force conversion), i get "Argument not optional".

sub Test()
         Dim MyThings As Things
         ProcessCollection (MyThings)    ' gives "Argument not optional"
end sub

Sub ProcessCollection (Col As Collection)
          ' ... do stuff
end Sub

A related question is, how can I configure my custom collection so that VBA will treat it like any other collection? (maybe has something to do with Inheritance, which they say Excel VBA doesn't support). Can my collection class return something that looks like Things or a generic Collection, depending on who's asking?

Thanks!

 

[RoryA]

You can't. Since a Collection isn't typed anyway, why not just pass it as Object?

 

[johnywhy]

Hi Rory

Not sure if we mean the same thing. I am getting apparently strongly-typed objects, with intellisense, from this:

Things(2).Item.

Meaning, I get the members of my single Thing class, which is great. And since I made .Item the default property of the collection, I get intellisense with:

Things(2).

Can you explain what you mean by "collections are not typed"?

At least if I pass it "As Collection" I get the standard collection members (.Add, .Count, etc). But if I pass "As Object", as you recommend, then I'll lose even that. To what advantage?

Thanks

 

[RoryA]

Your Things class is not a Collection (as in VBA.Collection) - it's a collection of Thing objects. The VBA.Collection object is not typed - in other words it can contain anything.
If all you want to do is pass a Things collection to your sub, why not specify the sub's argument as Things, rather than Collection?

 

[johnywhy]

I think it would be correct to say that:
-my Things class contains a collection (in the Vba sense) of my Thing objects.
-My Things collection, like the vba Collection, is a Class, which presents a programming interface to the outside world, including .Add, .Count, .Remove, .Item, etc.

Seems to me the only difference between the Vba collection and my Things collection, from the point of view of external code, is the Vba's .Item is an Object, and mine is a Thing.

how are they different Otherwise?

 

[RoryA]

That's not really relevant though. You are not passing the Collection to the sub, you are passing the whole class instance (and you cannot implement the Collection interface in your class).
If your called sub expects a Collection, that's all you can pass it, but what I am asking is why you can't specify that it takes an Object (or Variant as you said initially) instead? Or failing that, pass it the Collection contained in your Things class.

 

[johnywhy]

My first goal is to get the intellisense.

Passing my class "As Things" between procedures, and returning my Things.Item "As Thing" from the Things class achieves that.

My second goal, to pass a Things object into a param that expects a collection, might just be solved by Your suggestion to pass the internal collection from Things.

(Because sometimes I want to use Collection utility functions on my custom collections.)

Thanks!