Chapter 12. Advanced VBA Techniques

Now that you've learned about the basic building blocks for Ribbon customizations, it's time to delve into some more advanced VBA techniques. We're calling these advanced because they require specialized knowledge of VBA, not necessarily because they are difficult to implement.

In this chapter you learn how to implement custom properties, methods, and events through classes. You also learn how to add custom properties to built-in objects such as ThisWorkbook, Sheet, ThisDocument, Form, and so on. These techniques help you to streamline your code and make it more manageable when implementing the UI across your entire project.

As you are preparing to work through the examples, we encourage you to download the companion files. The source code and files for this chapter can be found on the book's website at

Working with Collections

A Collection object is a set containing various elements. Because it is a set, it can be referred to as a single unit. A Collection object is very handy when it comes to collecting information about related elements — although they do not necessarily need to be related or even have the same data type.

When working with classes, for example, a Collection object can work as a container for elements that you need to add to your custom object.


For a refresher on classes, you might want to review "Document-Level Events" in Chapter 4.

A Collection object can be created as follows:

Dim myCollection As New ...

Get RibbonX: Customizing the Office 2007 Ribbon now with the O’Reilly learning platform.

O’Reilly members experience live online training, plus books, videos, and digital content from nearly 200 publishers.