Excel VBA classes - where have you used them?

Murray Brown
Murray Brown used Ask the Experts™

I recent;y asked for practical examples of using Excel VBA classes and was directed to a few sites that explained classes, but I still have not been given examples in simple words of where the answerer has actually used them. Please could someone give me a sentence ot two on where they have used Excel VBA classes in the real world and why they made a big difference over standard modules.

Watch Question

Do more with

Expert Office
EXPERT OFFICE® is a registered trademark of EXPERTS EXCHANGE®
Most Valuable Expert 2012
Top Expert 2012
Well, my first foray into classes was not too long ago, and it was pretty much a copy/paste, as it dealt with events:

Here are a few resources where I used them and they come in handy and are easily convertible for use with add-ins:

1.  Application Events - http://www.cpearson.com/excel/appevent.aspx
Usage: knowing when workbooks were being opened, so I could keep track for my backtrack tool (I had an array of counters keeping track of sheet activations so my spinner could move back or forwards between tabs like IE moves between pages - e.g., each workbook was like an IE tab, and each sheet tab was like an IE page on a tab).

But, its easy enough once you get the hang of it to take someone's class modules for Application Events, or particular Active X events (e.g., knowing which checkbox was being clicked, or option button, etc.) or even chart events and use them - most of which help avoid writing a bunch of object-related stubs.

So, let's get into a few more (I haven't done a lot, but starting to get the hang of building them):

2.  Tracking bookmarks in my recent ExcelToWord! utility (article here: http://www.experts-exchange.com/Software/Office_Productivity/Office_Suites/MS_Office/Excel/A_8933-How-to-quickly-and-accurately-populate-Word-documents-with-Excel-data-charts-and-images-including-Automated-Bookmark-generation.html

I used matthewspatrick's parent-class-builder utility http://www.experts-exchange.com/Software/Office_Productivity/Office_Suites/MS_Office/Excel/A_3802-Parent-Class-Builder-Add-In-for-Microsoft-Excel.html to construct a collection of bookmark indicators that held not only the bookmark object I wanted to store, but the type of object being stored.  So, basically a simple collection that had a key value and a type associated with it.

3. My first foray into using the parent class builder needed some help: http://www.experts-exchange.com/Software/Office_Productivity/Office_Suites/MS_Office/Q_26990058.html and there's a good example there as well.  I used classes to create quite a sophisticated (to me) data structure that I was having a problem defining using the old TYPE statement with.  And after having read Patrick's article, I threw down the gauntlet and decided I was going to build my first custom class.  What seemed remarkable to me (still is) is that once I had defined the structure, writing the code to read in the reports I was getting, and generate the database I wanted so I could pivot the results was almost trivial!

This is a great example of the (now seeming) simplicity of creating data structures using class modules to instantiate what you need, rather than the complexity (now seeming and then seeming) of having multiple TYPE structures and arrays of types to achieve the same thing.

4.  Not to mention the added capability to sort your collections as items are added (re: add insertion sort routine to the Add method in your class module), create enumerations allowing For-Each, etc.  Here's my first foray into that which is not quite how I would do it today, but to share the evolution of my learnings:

5. I mentioned insertion sort on Add.  Here's a bit of play I had with classes and collections, so I could take a list and create a unique sorted list in adjacent column along with the first instance's original location.  The goal was not a work problem, but a learning exercise, where I added Exists, an Insert on Add, and other functions to my collection.

Attached, you can also download and enjoy that fun, lol.

There's other stuff you can do, as well, such as data validation, create events when certain data meets certain criteria as you're processing (e.g., from printing reports to turning off the lights, lol;) .  When you start doing it, and build on your experience, you'll find you want to do it more, build more into your custom class, add more features, etc., because on the OUTSIDE where you're writing the workhorse code, it looks and feels, and works so much simpler!

But you've probably heard all that and said, "so what?" as I did and DO, until I actually have to do it myself, the next time, and that will be a learning experience and I'll be the one saying, "Yea, I used a class module to do that, and it was much more easy/elegant than what I would have done, otherwise".

Its pretty darn easy to start substituting class modules for any type structure you might have previously defined, and its simpler than what I started out doing.  Today I hesitate and sometimes I create the TYPE structure, others I use class modules - again, its all about building on successes.  Sure, the TYPE structure was easy to define, but with the class module, I can add additional CAPABILITY to the structure I defined - capability that's easier to use and replicate than dedicated subs/functions than one would have originally thought.

That's about it.  Its another tool in the bag.  I guess you have to just use it in substitution (as I did with the parent class builder) when you have a problem, and success adds to experiences which will draw you to use the right data structures at the right time - just like with Excel formulas.


Murray BrownASP.net/VBA/VSTO Developer


Great answer! Much appreciated

Do more with

Expert Office
Submit tech questions to Ask the Experts™ at any time to receive solutions, advice, and new ideas from leading industry professionals.

Start 7-Day Free Trial