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
|
Similar Threads
