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

M

Metamexcel

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
 
R

RB Smissaert

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

RBS


Metamexcel said:
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
Click to expand...
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Similar Threads

Drop down list control value in vba 2
Closing PDF file from Excel VBA (Adobe Reader, Adobe Acrobat, Browser) 0
Cannot iterate through the items in custom class collection (NewEnum()) 8
How to create portable Excel applications? 2
Autonumber field in Excel that behaves the same as Access 0
ItemAdd and FolderAdd events problem with Public Folders 0
Array of Collections 1
Property Get NewEnum in VBA 6

Top