Case Study: VBA Implementation of a Bond Ladder

This is a short post showcasing some of the VBA procedures, classes, and coding techniques in last month’s bond ladder financial model. The model relies heavily on VBA, using it to manage displays, calculations, and data storage.

The Boss Macro

We’ll make this an April Fools’ Day tradition. Last year, I created The Garbage Financial Statement — a prank financial statement that would flip data whenever entered into a cell, and subsequently move the data to an adjacent cell whenever the user attempted to delete it. I wouldn’t recommend sending …

VBA Inheritance: Custom Objects as Class Properties

Inheritance is a concept that permits the transfer of data from one class to another class. While common in many programming languages, it is not native to VBA. In the absence of true inheritance, effective coding can help mimic its behavior.

Storing Objects as Class Properties

In most situations, Excel objects, such as shapes and buttons, perform simple functions and stand on their own. However, when stored within a class, the opportunity exists to supplement the functionality of custom objects.

Storing Arrays as Class Properties

There may be situations where a single class property is needed to store multiple values. This can support a list of data or provide the foundation for a loop. VBA classes provide the framework to store arrays as class properties. Overview This is the ninth post in the Tips and …

Storing Multiple Object Collections in a Collection Class

While VBA collection classes are best known for storing collections of single custom object types, they can also be built into much more complex data structures. This includes storing multiple object collections within a single collection class, permitting a node-like structure to store any number of related items.

Creating a Collection Class within a Collection Class

A common object structure in VBA is the collection class, where many custom objects are grouped together. VBA also permits additional layers above the collection class, resulting in a collection within a collection. This is a useful technique for organizing hierarchical data or grouping certain objects.

Bubble Sort for Arrays and Other VBA Data Structures

The bubble sort is a commonly used technique for sorting simple arrays and collections. It can also be used to sort through other common VBA data structures, such as two-dimensional arrays, collections of custom objects, and dictionaries.

Sorting Objects of a Collection Class

One feature noticeably absent from VBA is a built-in function to sort arrays and collections. The bubble sort is a well known technique for sorting groups of elements when no native function exists. With a few minor adjustments, a bubble sort can also be used to sort collections of custom …

Using Class Initialize in Collection Classes

Class Initialize is known as an intrinsic (native) procedure that triggers when a custom object is initialized. Did you know that this procedure will also work within collection classes? When used properly, class initialize can help set up collections by using default data or loading from saved data.