Lesson Objective: To introduce the strategic use of Visual Basic for Applications (VBA) to automate repetitive tasks, enforce formatting standards, and create custom functions that reduce manual workload and eliminate user-induced errors in high-stakes models.

In-Depth Notes:

1. The “Automation vs. Transparency” Trade-Off:
Global best practice dictates that VBA should be used to automate workflow, not calculations. Core calculations must remain on the worksheet with visible formulas so they can be audited by a human. VBA is utilized for tasks that are mechanical and non-quantitative, such as:

  • Formatting reports for printing.

  • Sorting data tables.

  • Unhiding all hidden sheets for a full audit.

  • Refreshing all external data connections and pivot tables with a single click.

2. Recording vs. Writing Code:
While the macro recorder is a useful starting point, recorded code is often inefficient (containing unnecessary Select and Activate commands). A globally compliant VBA script must be written or cleaned to use direct references, which speeds up execution.

  • Best Practice: Instead of Range("A1").Select and Selection.Copy, the code should use Range("A1").Copy Destination:=Range("B1"). This prevents the screen from flickering and runs faster.

  • Error Handling: Every macro must include On Error Resume Next or On Error GoTo ErrorHandler to prevent the model from crashing mid-execution. The macro should always include an Application.ScreenUpdating = False command at the start and Application.ScreenUpdating = True at the end to prevent visual lag.

3. Custom Functions (UDFs – User Defined Functions):
When built-in functions are insufficient, VBA allows the creation of custom functions.

  • Application: Creating a function that calculates the “Modified Dietz” return for a portfolio with uneven cash flows, or a function that automatically generates a fiscal quarter label based on a specific fiscal year-end.

  • Critical Regulatory Constraint: UDFs are generally prohibited in models that are submitted to US regulators (like the SEC) because they are not transparent; the underlying logic is hidden in the VBA editor. Therefore, UDFs should only be used for formatting purposes (e.g., a function that color-codes a cell based on a value) and never for core financial calculations like free cash flow or tax provisioning.

4. The “Model Reset” Macro – A Global Standard:
Every institutional model must contain a “Reset” macro assigned to a large, prominent button on the Control Panel. This macro performs the following critical actions:

  • Sets the Scenario toggle back to “Base Case.”

  • Clears all manual overrides (checks if cells were input manually and reverts them to the formula link).

  • Sets the Excel calculation mode to “Automatic” (if a previous user changed it to manual).

  • Re-applies the global print area to ensure all output sheets print correctly.

  • Logs the date and time of the reset in a hidden “Audit Log” sheet.

5. Password Protection and Distribution:
When distributing the model to a client or investment committee (especially in Europe under GDPR), the VBA project must be password-protected to prevent unauthorized tampering. However, the macro must not hide the workbook’s formulas (e.g., using xlSheetVeryHidden), as this violates the transparency standards required for a fair valuation. The model must remain “open code” – all cell formulas visible – while only the VBA workflow engine is secured.

PreviousNext