Training videos for learning VBA - CLASSES AND STRUCTURES

Excel VBA Part 34 - Type Declarations

Posted by Andrew Gould on 13 October 2014

The Type statement in VBA allows you to define your own static data structures which you can then use in your variable declarations. This video explains how to declare a type, how to employ the type in variable declarations, how to read to and write from the variable, as well as a couple of fun features such as using enumerations within a type declaration and nesting types.

Excel VBA Part 35 - Class Modules

Posted by Andrew Gould on 13 October 2014

Class Modules in VBA allow you to define your own classes of objects. This video explains why that's a useful and shows you how to go about creating a class. You'll learn about the two class events: initialise and terminate, you'll see how to define properties, including how to create read-only properties and set default values. You'll also find out how easy it is to create methods for your class and how to use your class in your regular code.

Excel VBA Part 36 - Class Modules in Other Projects

Posted by Andrew Gould on 27 October 2014

One small limitation of VBA Class Modules is that, by default, you can only use them in the VBA Project in which they were created. This video explains how you can make your class modules available to other VBA projects. You'll learn about the Instancing property of a class, how to set references to VBA projects, how to write a function to return a new instance of a class and how to create an Excel Add-In as a convenient way to distribute your class modules.

Excel VBA Part 37 - Custom Collections

Posted by Andrew Gould on 27 October 2014

Excel VBA is full of collections and you can even create your own custom collections using the Collection object. This video teaches you how to create a new custom collection, how to add items to it, how to reference those items individually and how to loop over all of the items in the collection. You'll also see how you can add items based on a custom class module to a collection.

Excel VBA Part 38 - Typed Collections

Posted by Andrew Gould on 27 October 2014

One of the issues with the Collection class in VBA is that it's untyped. This means you can add any kind of object to the collection which can lead to issues later on. This video explains how to use class modules to create strongly typed collections. You'll learn how to create a private collection variable in a class module, how to create a custom Add method to restrict the type of item that can be added to the collection and how to use a custom collection class in your other procedures.

Excel VBA Part 39 - Dictionaries

Posted by Andrew Gould on 27 October 2014

The Dictionary class is similar to the Collection class in that it allows you to store a custom collection of objects. This video teaches you how to use dictionaries, including how to reference the Scripting Runtime Object Library, how to create a new Dictionary and add, remove, count and reference the items in it. You'll also learn about the Items and Keys arrays contained within a Dictionary and how to loop over these arrays in order to process all of the items that the Dictionary contains.