How To: Collections in VBA in Excel and probably other MS Office 2003 applications.

Hello Newsgroup,

Went searching for this and haven't found it anywhere so I thought I'd post
this for the benefit of all Excel developers.

Coming from a VB (for Windows), C++ background and now C# (and Excel
developer for many years and moons now), I've always loved the use of
collections. The main (and only) thing I had against VBA (for Excel) is that
I haven't ever been able to create my own custom collections for (obviously)
my custom classes. For all those Excel-VBA developers out there who would
very much like to make your own custom collections, here's a work around.

From the VBA IDE in Excel make shore that OLE Automation is selected in
references, it is by default anyway, it's just that paramount. Create a
class for instance called

CMyClasses, and obviously the object class for this collection
CMyClass

Open up your object browser, right click somewhere and show hidden members.

Construct CMyClasses (very) basically as follows:

=====The Collection Class==========================
Private mcolYetAnotherCollection As VBA.Collection

'**************************************************
Public Property Get NewEnum() As stdole.IUnknown

Set NewEnum = mcolYetAnotherCollection.[_NewEnum]
End Property
'**************************************************

Private Sub Class_Initialize()

Set mcolYetAnotherCollection = New VBA.Collection
End Sub

Private Sub Class_Terminate()

Set mcolYetAnotherCollection = Nothing
End Sub

'And the rest of the Add, Item, Remove, Count functions-properties...
=============================================

Take note of **Public Property Get NewEnum() As stdole.IUnknown** property.

Export your collection class(es) as typical *.cls files. Open these *.cls
files in a text file editor that will easily open them, Locate your NewEnum
properties and right under the function's name type the following;

Attribute NewEnum.VB_UserMemId = -4

so it all looks something like this;

Public Property Get NewEnum() As stdole.IUnknown
Attribute NewEnum.VB_UserMemId = -4

Set NewEnum = mcolYetAnotherCollection.[_NewEnum]
End Property

Exactly as it is. Save the file and import it back into your Workbook
development projects. For those of you from VB 6 backgrounds will know
what's going on as the Object Browser in VB 6.0 allows the setting of the
attribute for this function contained in custom collection classes. You'll
quickly notice that you have a custom collection of your very own and, such
things like...

For Each myClass In myClasses

MsgBox myClass.TheRestOfIt
Next myClass

....are indeed possible.

For anyone out there that knows VBA better than they know themselves I'd
like to ask;

1) Is there anything I haven't considered or more realistically, out right
don't know why I should not develop with this in mind given any limitation
in VBA for the reason attribute settings for class members isn't possible?

2) Would this be a legal infringement to Microsoft's disfavour since I have
made an alteration to it's development language? As I'm about to take this
to work and my boss will kiss me 6x10^56 times as where looking at
developing the backend to our Excel applications in a different language in
order to achieve collection classes in MS Excel 2003.

Thanks you very much Newsgroup(s) hope this helps some of you and I'm able
to do this.

Regards,
- Metamexcel

If I remember well this has been covered in Stephen Bullen et al's book
Professional Excel Development.

RBS


"Metamexcel" <[email protected]> wrote in message
news:[email protected]...
>
> Hello Newsgroup,
>
> Went searching for this and haven't found it anywhere so I thought I'd
> post
> this for the benefit of all Excel developers.
>
> Coming from a VB (for Windows), C++ background and now C# (and Excel
> developer for many years and moons now), I've always loved the use of
> collections. The main (and only) thing I had against VBA (for Excel) is
> that
> I haven't ever been able to create my own custom collections for
> (obviously)
> my custom classes. For all those Excel-VBA developers out there who would
> very much like to make your own custom collections, here's a work around.
>
> From the VBA IDE in Excel make shore that OLE Automation is selected in
> references, it is by default anyway, it's just that paramount. Create a
> class for instance called
>
> CMyClasses, and obviously the object class for this collection
> CMyClass
>
> Open up your object browser, right click somewhere and show hidden
> members.
>
> Construct CMyClasses (very) basically as follows:
>
> =====The Collection Class==========================
> Private mcolYetAnotherCollection As VBA.Collection
>
> '**************************************************
> Public Property Get NewEnum() As stdole.IUnknown
>
> Set NewEnum = mcolYetAnotherCollection.[_NewEnum]
> End Property
> '**************************************************
>
> Private Sub Class_Initialize()
>
> Set mcolYetAnotherCollection = New VBA.Collection
> End Sub
>
> Private Sub Class_Terminate()
>
> Set mcolYetAnotherCollection = Nothing
> End Sub
>
> 'And the rest of the Add, Item, Remove, Count functions-properties...
> =============================================
>
> Take note of **Public Property Get NewEnum() As stdole.IUnknown**
> property.
>
> Export your collection class(es) as typical *.cls files. Open these *.cls
> files in a text file editor that will easily open them, Locate your
> NewEnum
> properties and right under the function's name type the following;
>
> Attribute NewEnum.VB_UserMemId = -4
>
> so it all looks something like this;
>
> Public Property Get NewEnum() As stdole.IUnknown
> Attribute NewEnum.VB_UserMemId = -4
>
> Set NewEnum = mcolYetAnotherCollection.[_NewEnum]
> End Property
>
> Exactly as it is. Save the file and import it back into your Workbook
> development projects. For those of you from VB 6 backgrounds will know
> what's going on as the Object Browser in VB 6.0 allows the setting of the
> attribute for this function contained in custom collection classes. You'll
> quickly notice that you have a custom collection of your very own and,
> such
> things like...
>
> For Each myClass In myClasses
>
> MsgBox myClass.TheRestOfIt
> Next myClass
>
> ...are indeed possible.
>
> For anyone out there that knows VBA better than they know themselves I'd
> like to ask;
>
> 1) Is there anything I haven't considered or more realistically, out right
> don't know why I should not develop with this in mind given any limitation
> in VBA for the reason attribute settings for class members isn't possible?
>
> 2) Would this be a legal infringement to Microsoft's disfavour since I
> have
> made an alteration to it's development language? As I'm about to take this
> to work and my boss will kiss me 6x10^56 times as where looking at
> developing the backend to our Excel applications in a different language
> in
> order to achieve collection classes in MS Excel 2003.
>
> Thanks you very much Newsgroup(s) hope this helps some of you and I'm able
> to do this.
>
> Regards,
> - Metamexcel
>
>
>