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.
Last month, I posted a new financial model presenting a bond ladder portfolio. The model calculates a portfolio’s average weighted yield, trends, and income.
This post will look at the various VBA techniques used to create a user-friendly interface and handle complex calculations.
Financial Model File
You can download the financial model directly through this link. Macros must be enabled upon opening.
VBA Design Elements
While the most common elements of design do not have much to do with VBA—colors, fonts, spacing, etc.—VBA can play a major role in handling dynamic design, responsiveness, and the overall user experience.
The financial model uses a VBA-based navigation feature, eliminating the need to use spreadsheet tabs. Users often feel intimidated viewing multiple tab workbooks and may not necessarily know where to start. By using a custom tab feature, users will be more familiar with navigation. Likewise, the approach implemented in this financial model provides intuitive navigation and placement cues (the tab format changes for the active page) for the user.
This VBA approach to navigation permits full customization. In the bond ladder model, the bottom border line will disappear for the active tab, mimicking a file-folder appearance of the active tab being in front of the other tabs. Within the model, multiple procedures manage the navigation, starting with a
Worksheet_SelectionChange event procedure to call a subroutine unique to the tab pressed. For example, the
View_Dashboard_Detail subroutine in the Interface_Operations module will be called when a user clicks on the “Dashboard” tab.
Excel does offer listbox ActiveX and Form Controls, but these are feature-limited and contain numerous glitches. One way around this is to build a custom listbox using a scrollbar ActiveX Control. The custom VBA listbox solution allows full customization of the listbox. In addition to its core functionality (scrolling through data), the listbox assigns alternating colors to rows, contains buttons, and can contain conditional formatting. Additionally, we do not run into any inconsistent size and alignment issues when viewing the financial model across many different monitor resolutions (this is a common issue with the ActiveX listbox).
The custom VBA listbox prints a section of a dataset on a spreadsheet. The section printed is reliant on the values returned from the scrollbar when it was clicked. This gives the appearance of a listbox window. The bulk of custom listbox operations are managed within the class module: Scrolltable.
Modifying Chart Properties
The first chart on the dashboard (Portfolio Average YTM Over Time) presents a year-over-year comparison of the average YTM for the bond ladder. Given that the spread between years can be a very small amount, it might be difficult to see the magnitude of difference. The financial model uses VBA to change the properties of the charts Y axis, acting as a zoom-in feature.
A snapshot of the zoomed-out chart:
And a snapshot of the zoomed-in chart (using the exact same data):
While the first chart shows the 2017 and 2016 YTM compared, it’s difficult to see the difference between the two year as the large scale narrows the visual distance. The second chart shrinks the scale to give the viewer a clearer view of the comparison over the course of the year. The second image shows a narrowed y-axis, where it is adjusted to be 10% from the maximum and minimum values in the chart. The following procedure accomplishes both the zoom-in and zoom-out functions whenever a user clicks the “Zoom In” or “Zoom Out” radio buttons at the top-right of the chart.
Dim chartMax As Double
Dim chartMin As Double
Dim chartSpread As Double
Dim axisPadding As Double
Dim primaryYear As Integer
Dim secondaryYear As Integer
primaryYear = sheetDashboard.Range("Input_Primary_Year").Value
secondaryYear = sheetDashboard.Range("Input_Secondary_Year").Value
If sheetDashboard.OLEObjects("Option_Chart_Zoom").Object.Value = True Then ' If chart is currently zoomed out, will zoom in.
chartMax = rungPortfolio.Portfolio_Max_Yield
chartMin = rungPortfolio.Portfolio_Min_Yield
chartSpread = chartMax - chartMin
axisPadding = chartSpread * 0.1 ' Create axis padding so lines do not press up against min and max values.
chartMax = Round(chartMax + axisPadding, 4)
chartMin = Round(chartMin - axisPadding, 4)
.MaximumScale = chartMax
.MinimumScale = chartMin
Else ' If chart is currently zoomed in, will zoom out.
.MinimumScaleIsAuto = True ' Revert back to auto-scale when zooming out.
.MaximumScaleIsAuto = True
VBA Classes and Calculations
In most cases, spreadsheet-level Excel functions provide everything required to support calculations in financial models. This includes basic arithmetic, calculating simple probabilities, and calculating aggregate data. However, as financial models grow more complex, you may begin to rely on a large number of volatile formulas (formulas which recalculate after an unrelated change in the spreadsheet) and massive datasets. The constant recalculations can make the model operate extremely slowly as more data is added.
One way around time-consuming formula recalculations is to have VBA to handle calculations. This has two main advantages:
- VBA can calculate data much more efficiently than spreadsheet-level calculations.
- VBA gives you full control over when calculations will process.
Storing Arrays in Classes
Given that we include a filter to calculate cash flow and yield over specific years, we need to store this data as an array within a class. Within the RungCollection collection class, this data is stored under the array properties: rungCollectionMonthlyIncome, rungCollectionMonthlyYield, rungCollectionPriorMonthlyIncome, and rungCollectionPriorMonthlyYield.
These arrays store monthly cumulative calculations derived from transaction data. Whenever a user switches the comparison years on the dashboard (using the form pictured below this paragraph), the RungCollection collection class cycles through all transaction data and runs the required calculation if a transaction meets various criteria.
One alternative to this would be storing aggregate data for all years. However, given that the model can store an unlimited number of years worth of transaction data, this could potentially add a processing burden whenever a transaction occurs (new transactions impact aggregate data and require recalculations). In short, the arrays within the collection ensure us that only two years of data are recalculated, as opposed to 30 or 40.
The financial model relies heavily on collection classes to store transaction and rung data. Transactions are held within TransCollection for easy access to methods which sort, calculate totals, manage printing, and to support saving. Rungs are held within RungCollection for calculating aggregate bond and portfolio data.
Storing Properties in Collection Classes
Collection classes are known to have a generic set of properties and methods to add, remove, and count values. It’s also possible to encapsulate unique properties within collection classes. This may seem counter-intuitive, as a properties are known to store a specific detail/attribute of a single element (not multiple elements), but they can be used within collection classes to present aggregate data about the entire collection.
Within the financial model, properties are used within collection classes to calculate and present totals, averages, and present arrays about the entire portfolio or individual rungs.
Making Object Data Persist After Spreadsheet Close
One of the major drawbacks of relying heavily on VBA classes is that the objects will terminate whenever a process is interrupted, or a spreadsheet is closed. To get around this, class data must be saved somewhere on a spreadsheet.
Within the Bond Ladder Financial Model, all custom object data is saved (printed) within the “Rungs” and “Transactions” sheets. Within the RungCollection and TransCollection collection classes, the
Class_Initialize subroutines work to load all saved data into their respective collection classes when a worksheet is opened. Data is automatically saved to these pages whenever a transaction is created or a major change takes place.