Classes and class collections in Excel VBA

All the stuff about classes and class modules may sound awfully complicated, but in fact it isn't that difficult. Once you grasp the concept, you'll see that they can make programming so much easier, and you will use classes whenever you get the chance.

What is a class or a class module?

You insert a class module the same way you insert a standard module, but with a class you can define your own properties, methods and events.

As an example a class could represent an item/product in a supermarket. A supermarket would probably not control its stock using Excel, but it could, and anyway it is just an example!

The class could then have several properties, such as: supplier, purchase price, sales price, number supplied, number sold, stock, group, danger class etc.

The class can also have some methods and events. For instance it can automatically calculate the stock, when there is a new supply delivered, or a single bottle is sold. When the stock gets to a defined minimum, it can automatically trigger an order to the supplier.

Imagination is the only limitation.

Class collections

The beauty is that this product class works for all products - it is a kind of "cookie cutter".

You write the code once and for all, and for each new product you just make a new instance (copy) of the class, which is "stored" in a class collection - a collection of all the product classes. If you drop a product, you just remove its class from the collection.

All information is safely encapsuled in the class, and you don't need a lot of public variables. No, you get the stock of Champagne by simply saying something like:

lStock = Products.Item("champagne").lStock

if Champagne is in a class collection called "Products".

You can build a hierarchy of classes and class collections, that lie within one another like chinese boxes. If we stick to the supermarket example it could look like this:

  • ProductGroups (class collection of Product groups)
  • You have X product groups such as "Detergents", "Wine", "Frost" and "Meats"
  • At the same time every product group is a class collection of specific product classes.
  • The product class is a class for a single product e.g. citric acid, Amarone red wine and legs of lamb.

The spreadsheet How to use classes and class collections is a fairly simple example. It also shows, how you can use a Userform to add and remove product items and manipulate data.