Series Overview: Tips and Tricks with VBA Classes

In their simplest form, VBA classes can create basic objects. By diving into advanced concepts, classes introduce some common features present in traditional object oriented programming languages.

Overview

This is the fourteenth and final post of the Tips and Tricks with VBA Classes series. This post acts as a summary of all topics discussed over the series. Within the outline below, brief explanations and snippets are provided for each topic discussed during the series. For more information on each topic, click on the links provided to see the full post and example file.

Useful Concepts

Custom Keys

General guidelines and information on assigning custom keys to elements in a collection class.

Full Post: Using Custom Keys in Collection Classes

Assign key in collection class’ Add procedure

Extract element using string key in object class

Within the post, also learn about
  • Rules and requirements when working with custom keys.
  • Creating an add procedure to handle custom keys.
  • Accessing elements through a standard module.

Saving (Persisting) Object Data

Techniques to save object data after workbook close.

Full Post: Saving Custom Object Data in a Workbook

Worksheet Cells

Save and load object data by storing data points in individual cells.

Worksheet Strings

Save and load object data by storing data points in a delimited string.

Named Constant String

Save and load object data by storing data points as a named reference.

Within the post, also learn about
  • Deleting previously saved elements.
  • Details associated with each technique.

Class Initialized in a Collection Class

Details on how to incorporate Class Initialize and Class Terminate events in a collection class.

Full Post: Using Class Initialize in Collection Classes

Use class initialize to load saved data

Within the post, also learn about
  • Using the class initialize event within objects that exist within a collection class.
  • Using the class terminate event within a collection class, both for the collection and individual objects.

Sorting Collection Class Objects

Sort elements of a collection class by a property value.

Full Post: Sorting Objects of a Collection Class

Build sort procedure as member subroutine within collection class

Within the post, also learn about
  • Discussion of mechanics and CallByName function.
  • Review of implementation techniques.

Complex Class Structures

Collection Class within a Collection Class

Use multiple collection classes to create a node-based data structure.

Full Post: Creating a Collection Class within a Collection Class

General Concept
Schema of a basic collection of a collection class structure.
Schema of a basic collection of a collection class structure.
Within the post, also learn about
  • Calling specific objects within the data structure.
  • Review scripting in each level of collection class.

Multiple Object Collections within a Collection Class

Similar to a collection within a collection, but uses many different custom class (data) types.

Full Post: Storing Multiple Object Collections in a Collection Class

General Concept
Two semi-related collections of custom objects are now linked together through a single collection class.
Two semi-related collections of custom objects are now linked together through a single collection class.
Collection Class Contains Two Collections and Two “Add” Procedures

Within the post, also learn about
  • How to access elements in different collections.
  • Implementation in a standard module.
  • Working with multiple instances of the class structure.

Advanced Concepts with Class Properties

Advanced Use of Class Properties

Learn how to include procedure-like scripts within class properties.

Full Post: Forcing Class Properties to do More Work

Simple validation in a Let Property

Calculations in the Get Property

Within the post, also learn about
  • Using the Variant data type with built-in property validation.

Advanced Use of Properties within a Collection Class

Learn how to expand the functionality of a collection class by including properties which provide aggregate information about the collection.

Full Post: Using Methods and Properties within a Collection Class

Extract aggregate data

Run a bulk calculation

Within the post, also learn about
  • Properties that return the minimum and maximum values of a collection.
  • Printing all collection data as a collection class method.

Storing Arrays as Class Properties

Use class properties to store a list of data.

Full Post: Storing Arrays as Class Properties

Directly assign array to class property

Property argument must be a Variant data type. Array within standard module must also be declared as a variant.

Build array within property using the Split function

Build array incrementally within property

Within the post, also learn about
  • Implementation within a standard module.
  • Why the Variant data type is required for this technique.

Storing Excel Objects as Class Properties

Use class properties to Excel Objects

Full Post: Storing Objects as Class Properties

Use Set property to assign Excel shape

Use Set property to assign ActiveX Control (scrollbar)

Within the post, also learn about
  • Interacting with objects set within the class.
  • Build class members to affect objects set within the class.

Storing Custom Objects as Class Properties

Mimic inheritance by accessing custom objects through another object’s properties.

Full Post: VBA Inheritance: Custom Objects as Class Properties

General Concept
Schematic showing how data flows with object inheritance. This post will focus on components within the red dotted line.
Schematic showing how data flows with object inheritance. This post will focus on components within the red dotted line.
Within the post, learn about
  • Building both a base and derived class.
  • Interacting with the structure in a standard module.
  • Linking objects through composition.

Using the Implements Statement

Use class properties to Excel Objects

Full Post: VBA Interfaces: The Implements Statement

General Concept
Schematic showing a basic VBA Interface and how it sits between classes and procedures.
Schematic showing a basic VBA Interface and how it sits between classes and procedures.
Declare an Interface

Make interface-compatible property

Property name must be preceded by the interface name and an underscore.

Implementation in a standard module

Object must initially be declared as the interface data type, then Set as the actual object data type.

Within the post, also learn about
  • How interfaces relate to polymorphism.

Leave a Reply