<

[Product update] Infrastructure Analysis Tool is now available with Business Accounts.Learn More

x

Parent Class Builder Add-In for Microsoft Excel

Published on
29,260 Points
10,360 Views
24 Endorsements
Last Modified:
Awarded
Editor's Choice
Community Pick

Introduction



Visual Basic for Applications (VBA) developers of intermediate or higher skill level may often find it useful to implement custom classes in their applications.  For some tasks, a single class instance may be all that is needed.  However, there may often be a need to create and persist multiple instances of a class, and to manage these class instances through a "parent" or "collection class".

While the particular application for a parent class may vary according to the task at hand, the core functionality required for any parent class is fairly stable, and thus developers who frequently use parent classes may find it useful to automate part of the set-up work, thus leaving more time and attention for the more subtle aspects of the project.

This article introduces and describes a new add-in built for this purpose: the Parent Class Builder Add-In for Excel 2000 and higher.

Note: While the Parent Class Builder Add-In is an Excel component, you can still use it to create class modules in other Office applications.  The technique for doing this is explained in the section Using the Parent Class Builder Add-In.


About Custom Classes and Parent Classes in VBA



This article is not intended as a general introduction to custom classes in VBA; that is a very rich subject deserving an article (or series of articles!) in its own right.  For any readers who may need a refresher on class modules, you may want to start with this introduction by Chip Pearson.  Rather, this article describes a very specific tool used as an aid and time-saver for developers who want to create their own parent classes.

In simple terms, what I refer to as a "custom class" is a user-defined object type.  A custom class is defined by a class module in the VBA project, and the code in that class module defines the class’s properties and methods.

In a recent EE question, the Asker needed to find the information associated with the most recent update of a contract:

I have an Excel file that has 10K contract records.  Each contract will have a name, a contract version, contract effective date, contract expiration date.  For the purpose of reporting I only want the last version of any contract.  How do I find the last version of a contract record, if there are multiple contract versions?

In this case, we can use a custom class, "clsContract", to model an individual contract as an object with three properties: version, effective date, and expiry date.  Further, to gather up and manage the individual contracts, it is useful to create a "parent", or "collection class".  Continuing with the example, using this add-in I created a parent class, "clsContracts", to form a collection of the individual contracts.

clsContracts Object Model
That is a relatively simple object model; complex data sets may require several nested levels.  For example, in a past project, to model weekly work requirements, by activity, week, and branch, I need something like this:

Workload Object Model
Note how in the second example even the “child” nodes spun off additional parent/child pairs.  For example, the clsBranch object has a property, Weeks, that is itself a clsWeeks collection class.

Note also that in both examples, the properties and methods of the parent classes are very similar.  Indeed, it is intrinsic to the nature of parent classes that they will have the following properties and methods:
Add;
Clear;
Count;
Item; and
Remove.

Indeed, it is that degree of commonality that makes the process of creating parent and child classes susceptible to automation.


Installing the Parent Class Builder Add-In



To install the Parent Class Builder Add-In, please download the file below:

Parent-Class-Builder.xla

It does not matter where you save the file to.  It is slightly more convenient if you save the file to Excel’s default add-in directory; this can vary a bit depending on your Office and Windows versions, but it will the same directory with the standard add-ins SUMIF.XLAM, EUROTOOL.XLAM, and LOOKUP.XLAM (in Excel 2003 and earlier, those extensions will be *.XLA).

Once you have downloaded the file, launch Excel, and enable the add-in:
In Excel 2003 and earlier, select Tools / Add-Ins from the menu.  In the Add-Ins form, check the box for Parent Class Builder.  If you do not see Parent Class Builder in the listed add-ins, then click the Browse button, and navigate to where you saved the file;
In Excel 2007, click the Office button, and then Excel Options.  Click Add-Ins, and then in the Manage drop-down select Excel Add-Ins, then click the Go button.  Then follow the directions above for Excel 2003 and earlier;
In Excel 2010, click the File menu and then Excel Options, and then follow the directions above for Excel 2007.

Note: If you wish to use the Parent Class Builder Add-In to actually create the class modules for you, then you must enable trusted access to the Visual Basic Project.  Without this trusted access, the Parent Class Builder Add-In can still create *.cls files that you can import to create classes, but it cannot actually create the classes for you.


Using the Parent Class Builder Add-In



When the Parent Class Builder Add-In is enabled, you will see a new menu item for it:
In Excel 2003 and earlier, there will be a new item in the Worksheet Menu Bar, "Parent Class Builder", with one item below it, "Create Parent Class"
In Excel 2007 and later, in the Ribbon’s Add-Ins tab there will be a new item, "Parent Class Builder", with one item below it, "Create Parent Class"

To create your classes, use the following work steps:
Select Create Parent Class from the menu.  Excel will then show you the following form:

Add-In Form
Enter a name for the parent class (1)
Enter a name for the child class (2)
Indicate the child class property that you want to use as the key in the parent collection (3)
Use the checkbox to indicate whether you also want to create a child class (4)
If you want to create a child class, then indicate whether that child class will itself spawn another collection class (5)
If for (5) above you are including a "next level", then indicate the name to use for that "next level" class (6)
If for (5) above you are including a "next level", then indicate the property name you will use in the child class to refer to that "next level" class (7)
If you want to import the new classes into a workbook, check the appropriate checkbox, and use the drop down list to select the workbook to add the class(es) to; the list includes an option to import the classes into a new workbook (8)
If you wish to write *.cls files to a directory either instead of or in addition to importing the classes into a workbook, check the appropriate checkbox, and indicate which directory to save the *.cls file(s) to (9)
Indicate whether the class(es) should have case sensitive or case insensitive text comparisons by default.  These defaults will use Option Compare Binary|Text, and as such can be overridden on a statement-by-statement level (10)
Indicate whether to include a comment for the module creation date (11)
If desired, populate a "byline" comment for the module (12)

Note: The Parent Class Builder Add-In cannot directly add classes to the VBA projects for other Office applications, such as Word, Access, or PowerPoint.  However, if you need to create classes for these applications, you can run the Parent Class Builder Add-In to create the necessary *.cls files, and then import those *.cls files into your application’s VBA project.

In the VBA IDE, to import a *.cls file, you can use Ctrl+M, select File / Import File from the menu, or right click the project you want to import the file into in the Project Explorer.

You can use the Parent Class Builder Add-In in an iterative fashion to build up complicated object models.  For example, to create the "Contracts" object model described in the section About Custom Classes and Parent Classes in VBA:
I used a single run of the add-in, with "clsContracts" as the parent class, "clsContract" as the child class, "Name" as the child key property, and no "next level".
After running the add-in, I fleshed out the object model by adding Import and Export methods to clsContracts, and EffectiveDate, ExpiryDate, and Version properties to clsContract.

Owing to its more complex structure, creating my "Workload" object model would require three steps:
First, run the add-in, with "clsBranches" as the parent class, "clsBranch" as the child class, and "BranchCode" as the child key property.  Include a "next level", with clsWeeks as the next level class and Weeks as the next level property
Next, run the add-in again, with "clsWeeks" as the parent class, "clsWeek" as the child class, and "StartDate" as the child key property.  Include a "next level", with clsActGroups as the next level class and ActGroups as the next level property
Last, run the add-in again, with "clsActGroups" as the parent class, "clsActGroup" as the child class, and "Name" as the child key property.  For this last step, do not include a "next level"
As above, I would then have to flesh out the object model with any additional methods and/or properties my project required


How the Parent Class Build Add-In Does Its Work



The basic logical flow for the Parent Class Build Add-In is as follows:

Flowchart
Note that the Parent Class Builder Add-In always creates *.cls files.  For example, if the user elects to have the *.cls files saved to a specified directory, then the files are saved there.  However, if this option is not selected, the Parent Class Builder Add-In creates "temporary" *.cls files that are then deleted once they are imported.

The add-in does this because it is the only way to enable a default property for the collection class (e.g., the Item property), and it is the only way to enable For Each…Next enumerations of the parent class.  Enabling both of these require using attribute modifiers, which is not supported in the VBA IDE.  However, if you import *.cls files with the appropriate attribute modifiers into a VBA project, then VBA will honor the settings.

Internally, the add-in uses worksheets to store three code templates:
Parent Class
Child Class with Next Level
Child Class No Next Level

Each of these templates use tokens as placeholders for various user defined code elements.  The add-in uses the following tokens:

Token            Usage
-------------------------------------------------------------
<<clsLevel1>>    Parent class name
<<clsLevel2>>    Child class name
<<clsLevel3>>    "Next level" class name
<<Name>>         Child property used as key in collection
<<Level3>>       Child property used to refer to "next level"
<<Byline>>       Comment with customizable byline
<<CreatedDate>>  Comment containing current date
<<Text>>         Used to set Option Compare Text|Binary

Open in new window


If desired, you can modify the code templates.  To get to them, please use the following work steps:
If it is not already, open the Parent Class Builder Add-In, and go to the VBA IDE
In the Project Explorer, find the project ParentClassBuilder, and expand its node for Microsoft Excel Objects
Click ThisWorkbook, and in the Properties window set the IsAddin property to False
Switch back to Excel, and the worksheets for the Parent Class Builder Add-In should be visible
After making any desired modifications, be sure to go back to the VBA IDE, set IsAddin back to true, and save the file


Finishing the Code Generated by the Parent Class Build Add-In



The Parent Class Build Add-In may be useful to you in setting up the structure of your parent/child class modules, but it cannot do all of your work for you.  The add-in will create the elements that are common to almost all such parent/child classes, but you will still have to add the elements required for your specific application.

For example, the add-in will create a parent class that looks similar to this result:

' Patrick Matthews
' Created 2012-10-28

' Parent collection class of clsChild

Option Explicit
Option Compare Text

' Container for all clsChild objects in the parent collection class
Private coll As Collection

Private Sub Class_Initialize()
    
    Set coll = New Collection
    
End Sub

Private Sub Class_Terminate()
    
    Set coll = Nothing
    
End Sub

Public Function Add(Name As String) As clsChild
    
    ' Adds a new item to the collection.  Causes an error if an item with the same key already exists
    ' or if you pass a zero length string for the Name argument
    
    If Name = "" Then
        Err.Raise vbObjectError + 1002, , "Name property of clsChild object cannot be zero length string"
    End If
    
    Set Add = New clsChild
    Add.Name = Name

    ' Raise an error and set return value to Nothing if we fail to add item to collection (most likely
    ' because an item already exists with the same key
    
    On Error GoTo ErrHandler
    coll.Add Add, Name

    Exit Function
    
ErrHandler:

    Set Add = Nothing
    Err.Raise vbObjectError + 1003, , "Could not add item '" & Name & "' to clsParent collection"
    
End Function

Public Sub Clear()
    
    ' Recreates (and thus clears) collection
    
    Set coll = New Collection
    
End Sub

Property Get Count() As Long
    
    ' Returns number of items in the collection
    
    ' Read-only
    
    Count = coll.Count
    
End Property

Function Exists(Name As String) As Boolean

    ' Returns True if a clsChild member specified by the Name exists in clsParent parent collection

    Dim TempItem As clsChild

    On Error GoTo CleanUp

    ' Default return is False
    Exists = False

    ' If item exists, then the Set operation completes without error
    Set TempItem = coll(Name)
    Exists = True

CleanUp:

    Set TempItem = Nothing

End Function

Property Get Item(Index As Variant) As clsChild
    
    ' Default property.  Returns an item from the collection.  Index may be either ordinal position (Long) or Name (String)
    
    ' Read-only
    
    On Error GoTo ErrHandler
    Set Item = coll(Index)

    Exit Property

ErrHandler:

    Set Item = Nothing
    Err.Raise vbObjectError + 1004, , "Item does not exist in clsParent collection"
    
End Property

Function Keys() As Variant

    ' Returns a 1-based array of the various strings used as Name key values for the clsChild items
    ' in the clsParent collection"

    Dim Counter As Long
    Dim Results() As String

    ' If there are no items in the clsParent collection then raise an error
    If Me.Count > 0 Then

        ' Redimension array so there is one member per clsChild item in clsParent collection
        ReDim Results(1 To Me.Count) As String

        ' Loop through clsParent collection and grab Name values for each clsChild item
        For Counter = 1 To Me.Count
            Results(Counter) = Me(Counter).Name
        Next

        ' Set return value
        Keys = Results

    Else

        ' Raise error for no items
        Err.Raise vbObjectError + 1005, , "Keys method failed: no clsChild items exist in clsParent collection"

    End If

End Function

Public Sub Remove(Index As Variant)
    
    ' Removes an item from the collection.  Index may be either ordinal position (Long) or Name (String)
    
    coll.Remove Index
    
End Sub

Function NewEnum() As IUnknown

    ' Enables enumeration of the clsParent parent collection, i.e.:
    '
    ' For Each Child In Parent...Next
    
    Set NewEnum = coll.[_NewEnum]

End Function

Open in new window


While the new parent class will have such familiar properties and methods as Add, Count, Exists, Item, etc., at a minimum you will have to add procedures for populating your parent collection, and performing whatever analysis and/or manipulations your application requires.

In addition, if you indicated that you also want to create a child class, then you will end up with a class module similar to this:

' Patrick Matthews
' Created 2012-10-28

Option Explicit
Option Compare Binary

' Container for read-only property
Private Safe_NextLevel As clsNextLevel

' Container for "write-once read-many" property
Private Safe_Name As String

Private Sub Class_Initialize()
    
    Set Safe_NextLevel = New clsNextLevel
    
End Sub

Private Sub Class_Terminate()
    
    Set Safe_NextLevel = Nothing
    
End Sub

Property Get NextLevel() As clsNextLevel
    
    ' Returns reference to item's clsNextLevel collection
    
    ' Read-only
    
    Set NextLevel = Safe_NextLevel
    
End Property

Property Get Name() As String
    
    ' Returns item's Name value
    
    Name = Safe_Name
    
End Property

Property Let Name(NameString As String)
    
    ' Sets Name value for item
    
    ' This makes the Name property "write once, read many".  If the Name is a zero length string,
    ' the Property Let allows you to change it; if not, the procedure raises a user defined
    ' error.  Basically, we cannot allow changes because we want this property to match the
    ' item's true key used when it was added to the parent clsParent collection
    
    If Safe_Name = "" Then
        Safe_Name = NameString
    Else
        Err.Raise vbObjectError + 1001, , "Cannot change Name property of clsChild object"
    End If
    
End Property

Open in new window


The child class will look like this if you did not want to include a reference to a “next level”:

' Patrick Matthews
' Created 2012-10-28

Option Explicit
Option Compare Text

' Container for "write-once read-many" property
Private Safe_Name As String

Property Get Name() As String
    
    ' Returns item's Name value
    
    Name = Safe_Name
    
End Property

Property Let Name(NameString As String)
    
    ' Sets Name value for item
    
    ' This makes the Name property "write once, read many".  If the Name is a zero length string,
    ' the Property Let allows you to change it; if not, the procedure raises a user defined
    ' error.  Basically, we cannot allow changes because we want this property to match the
    ' item's true key used when it was added to the parent clsParent collection
    
    If Safe_Name = "" Then
        Safe_Name = NameString
    Else
        Err.Raise vbObjectError + 1001, , "Cannot change Name property of clsChild object"
    End If
    
End Property

Open in new window


As above with the parent class, you will almost certainly have to add other properties and/or methods to your child class to support your application’s needs.

Please note the following characteristics of the class modules the add-in creates, which reflect my own preferences in using classes.  If your preferences vary, please feel free to modify my templates to better reflect your own coding practices.
The parent class’s Add method returns an object of type clsChild.  Thus, creating a new child instance and setting it to a variable can be done in a single statement:
Set MyObjectVariable = ParentClass.Add("Unique Key Value")
The parent class’s Add method takes at least one argument, which is used as the key value when adding the child instance to the collection.  Passing a zero-length string or a duplicate value raises an error.  If desired, you can embellish the Add method to include other properties for the newly created child class instance
The Item property is set as the default property of the parent class.  Thus, the following statements are equivalent:
Set MyObjectVariable = ParentClass.Item(42) Set MyObjectVariable = ParentClass(42)
If you pass the Item property an index that does not correspond to an item in the collection, the Item property will raise an error
The parent class includes an Exists method, which you can use to see whether the class collection contains a member with the indicated index value.  The method returns Boolean True if such a member exists, and False if not
The parent class supports enumeration using For Each…Next.  Thus the following are equivalent:
For Each Child In clsParent Debug.Print Child.Name Next For Counter = 1 To clsParent.Count Debug.Print Parent(Counter).Name Next
The child class’s key property is set up as "write once, read many": the Property Let procedure for the key property raises an error if the key property has already been set.  I do this to ensure that the child key property always matches up to the key value used when the child instance is added to the parent collection


Further Reading and Acknowledgments



I owe a large debt of gratitude to EE Member and fellow Microsoft Excel MVP zorvek for introducing me to the world of custom classes in VBA.  A few years back I had a project that I knew would be much easier if I used classes, but I had no idea where to start.  zorvek showed tremendous patience in explaining some key concepts to me.  I still learned some of my lessons the hard way, but his help made the learning curve much shorter and much less painful to traverse.

One of the very best Excel blogs out there is Daily Dose of Excel, by Excel MVP Dick Kusleika.  In a recent series that started with this entry on collection classes and continues on for several more posts, Dick and co-contributor Rob van Gelder write some terrific stuff regarding collection classes.  Indeed, it was Dick’s statement that a lot of the donkey work associated with setting up parent and child classes could be automated that inspired me to create this add-in.

Yet another Excel MVP, Chip Pearson, whose web site is a treasure trove of Excel information, also covers collection classes in this article.

Lastly, in creating my own collection class template, I was substantially influenced by Francesco Ballena’s collection class module template.

Some of the functionality in this add-in can be obtained through the free, and indispensable, MZ-Tools add-in for the various flavors of VB, VBA, and VB.Net.  For example, users can create code templates that correspond to the parent and child templates I used for the Parent Class Builder Add-In.  However, if you wish to set the Item property as the default property of the parent class, you would still have to export the code module to a *.cls file, edit the file, and re-import.  The Parent Class Builder Add-In automates that process for you.


=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=
If you liked this article and want to see more from this author,  please click here.

If you found this article helpful, please click the Yes button near the:

      Was this article helpful?

label that is just below and to the right of this text.   Thanks!
=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=
24
Comment
  • 9
  • 8
  • 6
  • +2
26 Comments
LVL 42

Expert Comment

by:dlmille
Patrick - What a GREAT tool!  I'm a "way back" programmer coming back up to speed on the latest and after creating a farily complex data structure (using Type), I threw down the gauntlet today to learn more about class modules.

I've just created my structure - one more level deep than your example and haven't tested anything as yet, but I feel like I'm almost there.

By the way, at least one link is broken in your article:

Here:

>>In a recent EE question, the Asker needed to find the information associated with the most recent update of a contract:


Dave

0
LVL 93

Author Comment

by:Patrick Matthews
Thanks, Dave!  The broken link was the result of an errant space; it should be working now.

Let me know how the project works out.

Patrick
0
LVL 42

Expert Comment

by:dlmille
Well, I'm loading data into the structure, no problem.  A bit strange the naming convention.  

Please see Question post:  http:/Q_26990058.html

Dave
0
Learn SQL Server Core 2016

This course will introduce you to SQL Server Core 2016, as well as teach you about SSMS, data tools, installation, server configuration, using Management Studio, and writing and executing queries.

LVL 42

Expert Comment

by:dlmille
I'm a believer! http:/Q_27244336.html

:)

Dave
0
LVL 93

Author Comment

by:Patrick Matthews
Glad to see that, Dave!

BTW, based on your recent question about class modules generally, we got some great feedback from rspahitz and Rory.

For example, a better way to set up the default Add method would be something like this:

Public Function Add(Name As String) As clsChild
    
    ' Adds a new item to the collection.  Causes an error if an item with the same key already exists
    ' or if you pass a zero length string for the Name argument
    
    If Name = "" Then
        Err.Raise vbObjectError + 1002, , "Name property of clsChild object cannot be zero length string"
    End If
    
    Set Add = New clsChild
    Add.Name = Name
    
    ' Raise an error and set return value to Nothing if we fail to add item to collection (most likely
    ' because an item already exists with the same key
    
    On Error GoTo ErrHandler
    coll.Add Add, Name
    
    Exit Function
    
ErrHandler:

    Set Add = Nothing
    Err.Raise vbObjectError + 1003, , "Could not add item '" & Name & "' to clsParent collection"
    
End Function

Open in new window


Right now, the Item method returns Nothing if no such Item exists, so effectively this also becomes an "exists" method.

Any Experts who may be looking on, does this behavior make sense?  Or would it be better to have a real Exists method to test for existence, and then have the Item method raise an error if the item you are looking for does not exist?

Patrick
0
LVL 85

Expert Comment

by:Rory Archibald
My preference would be to have an Exists method as well.
0
LVL 93

Author Comment

by:Patrick Matthews
Thanks Rory!  I'll wait a bit to see if we get other comments, but unless someone comes up with a good reason not to have an Exists method, I will update the article and the add-in to make the changes above to the Add and Item methods, and to put in an Exists method.
0
LVL 48

Expert Comment

by:aikimark
* the add-in restores a functionality missing since the VB-classic environments (class builder)

* Why is a key needed?  Collection items do not require a key.
0
LVL 93

Author Comment

by:Patrick Matthews
Thanks aikimark!

I am aware that VBA and VB6 collections make the key optional.

Making the key mandatory was a matter of my personal preference: in the many times I have used this approach, I cannot remember any instances in which I did not need some sort of key value.  That is not to say that I will always need a key value, but I am having a really difficult time envisioning an implementation where I wouldn't want a key to be mandatory.

If there turns out to be a lot of demand for it, I could put in an option to remove the mandate for a key.

In your experience, how often would you NOT want a key to be mandatory?

Patrick
0
LVL 48

Expert Comment

by:aikimark
>>...how often would you NOT want a key to be mandatory?

When I was using the parent as strictly a collection container.  If I were going for keyed linked list/hash table/name-value pair array, I would most likely use a dictionary object for both its speed and its greater flexibility in its (accepted) key values (numeric or text).

I thought you were using that (collection container only) type of parent class for your article's example, which is why I commented.
0
LVL 42

Expert Comment

by:dlmille
I like having the Exists method, and also a Key method.  Regardless of whether the developer NEEDS it, its available - what's wrong with having the stub, otherwise?  In the solution I developed, I had to create an Exists and a Key method, as I wanted to "think" about my class as if it were a dictionary.  Also, for the "just" initiated, can you create an Exists method that can be used both from within and without the class?  When I created those methods, intellisense didn't popup on these methods when I was writing functions inside the class module.


Patrick, what if there were a checkbox to include these methods, then the developer has the option?

Another potential add, is the developer needing additional properties inside the class - would it be a good add to have fields for that in the Parent Class builder?

Finally (not to hijack this thread, but :), is the way I went about providing for sorting the class the best way to do this?  Is adding a sort method going to far with the "builder"?

Cheers,

Dave
0
LVL 42

Expert Comment

by:dlmille
@aikmark - I tried initially to create a dictionary where the object being added was the class I created with the PC builder, but it wouldn't let me do that.  I couldn't figure out how to create a collection of "home-made" class objects, hence I built it all into the class, instead.

Dave
0
LVL 93

Author Comment

by:Patrick Matthews
Interesting.  I'm on record as being a proponent of the dictionary, but I never gave much thought to using it here as the container object.

It would definitely work, and would bring the benefits you mention.  The downsides, as I see them:
For...Each...Next becomes clumsier: enumerating a dictionary loops through its keys, not its members.  Of course, that is assuming that NewEnum on a dictionary object would enable enumeration, which I haven't tested
You lose the ability to retrieve an item based on ordinal position.  Of course, if you're already in a key-mandatory situation, this might be something that won't be missed very much
0
LVL 48

Expert Comment

by:aikimark
* return the dicThing(keyvalue) instead of the key

* Since the dictionary object's keys are an array, I assumed that you would index the keys array and return the value of the item with that key.

    Dim dicThing As New Scripting.Dictionary
    Dim varItem As Variant
    Dim lngLoop As Long
    
    dicThing.Add "A", 123
    dicThing.Add "B", 456
    dicThing.Add "C", 789

    For Each varItem In dicThing
        Debug.Print varItem, dicThing(varItem)
    Next
    For lngLoop = 0 To dicThing.Count - 1
        Debug.Print lngLoop, dicThing.Keys(lngLoop)
    Next

Open in new window


Immediate Window results
A              123 
B              456 
C              789 
 0            A
 1            B
 2            C

Open in new window


If you need sorting, I'd almost consider using a dynamic (non-persisted) ADO recordset object.  I'm still thinking about that idea and don't know if it meets all the criteria or if it is too much trouble/overhead.  It just seemed like a simpler library that is almost universally present, giving you both filtering and sorting capability.
0
LVL 48

Expert Comment

by:aikimark
Oops.  I clicked the submit button too soon.  I meant to show both iterations of the item values.

    Dim dicThing As New Scripting.Dictionary
    Dim varItem As Variant
    Dim lngLoop As Long
    
    dicThing.Add "A", 123
    dicThing.Add "B", 456
    dicThing.Add "C", 789
    For Each varItem In dicThing
        Debug.Print varItem, dicThing(varItem)
    Next
    For lngLoop = 0 To dicThing.Count - 1
        Debug.Print lngLoop, dicThing(dicThing.Keys(lngLoop))
    Next
    For lngLoop = 0 To dicThing.Count - 1
        Debug.Print lngLoop, dicThing.Items(lngLoop)
    Next

Open in new window

0
LVL 42

Expert Comment

by:dlmille
@aikmark - were you responding to my comment?  I'm trying to follow the thread to understand the context of your comment...

Dave
0
LVL 48

Expert Comment

by:aikimark
@Dave

No.  I was responding to Patrick's comment.
0
LVL 85

Expert Comment

by:Rory Archibald
Of course, the Dictionary won't work on a Mac, if that's relevant to anyone.
0
LVL 48

Expert Comment

by:aikimark
@Patrick

I had forgotten about this article when I wrote my Fast Concatenate article.  I just posted a comment with a link to this article.  It is most excellent.

I had toyed with the idea of writing such an article.  I'm really glad to have found this one (again) before I wasted my time.
0
LVL 48

Expert Comment

by:aikimark
Please read my new article:
http://www.experts-exchange.com/A_8450.html

=============
@Patrick

In addition to filtering, I thought it might be a good idea to have your parent class builder create a properties collection for every class.

Each item in the collection would only need three attributes (name, data type, r/w status).  I would recommend using the VB intrinsic constants for data types, rather than the data types from one of the database environments.  The r/w status would probably be an integer value with bits indicating
(read-only | updatable), write-only conditions on the property.  

I'm not sure how best to represent a class/object data type or if such properties should be included.
0
LVL 93

Author Comment

by:Patrick Matthews
All,

Based on feedback, I have posted an updated version of the add-in with the following changes:
The parent class template now includes an Exists method
Passing a non-existent index value to the Item method now raises an error

Thanks!

Patrick
0
LVL 48

Expert Comment

by:aikimark
@Patrick

Do the class description graphics need to reflect the new Exists method?
0
LVL 93

Author Comment

by:Patrick Matthews
Good question!  I thought about updating them, but then the graphics would not accurately reflect the solutions I offered in the questions I referred to...
0
LVL 48

Expert Comment

by:aikimark
@Patrick

As long as the graphics are meaningful and in-sync with the original question, then there shouldn't be any need to change them.
0
LVL 56

Expert Comment

by:Martin Liss
Patrick, great article. Do you possibly after all this time have a workbook that illustrates your 'Workload Object Module' or something similar?
0
LVL 93

Author Comment

by:Patrick Matthews
Thanks for the compliment!

Technically, that "Workload Object Model" was from an Access project.  Work for hire, so I can't post the original here (it would be a royal pain to obfuscate it enough to post).

But my approach would have worked :)
0

Featured Post

Learn SQL Server Core 2016

This course will introduce you to SQL Server Core 2016, as well as teach you about SSMS, data tools, installation, server configuration, using Management Studio, and writing and executing queries.

Join & Write a Comment

Enter Foreign and Special Characters Enter characters you can't find on a keyboard using its ASCII code ... and learn how to make a handy reference for yourself using Excel ~ Use these codes in any Windows application! ... whether it is a Micr…
A query can call a function, and a function can call Excel, even though we are in Access. This is Part 2, and steps you through the VBA that "wraps" Excel functionality so we can use its worksheet functions in Access. The declaration statement de…

Keep in touch with Experts Exchange

Tech news and trends delivered to your inbox every month