Techniques to Organize a VBA Project

While most modern programming environments provide robust tools to organize complex projects and separate procedures, the VBA editor’s organization features are limited. While procedures can be separated into different modules, not many options exist in terms of organizing modules into subfolders.

Overview

This post within the Getting Started with VBA series continues the focus on Functions and Subroutines in VBA.

There are many approaches to organizing code within VBA’s limited project management capabilities — this post discussed two approaches. First, we review an often-overlooked concept by beginner programmers — the idea of creating modules to manage the scope of module components. Second, we look at a technique for mimicking subfolder structures commonly found in modern programming environments.

In terms of organizing code, the VBA editor permits code to be separated into a declarations area, subroutines, functions, or properties. Each of these elements must exist within a module. Three distinct types of modules exist: Microsoft Excel Objects (including Workbook and Worksheet modules), Basic Modules, and Class Modules. Form Modules are a 4th type of module, but will not be discussed in this post. In other situations, additional libraries may introduce new module types — those will not be discussed in this post.

Outside of the basic hierarchical/subfolder structure with the module types, the VBA editor does not permit individual modules to be placed in subfolders for organization purposes. This may be hindrance with complex projects, as some may contain dozens (or in some cases, over a hundred) of modules.

The following sections discuss techniques to help organize code within the limitations of the VBA editor’s features.

Key Terminology

Module

Encapsulates code into related procedures. In VBA, modules can contain declarations, subroutines, and functions.

Subroutine

A type of procedure which performs one or more actions.

Function

Similar to its Subroutine counterpart, a function is a procedure that can perform one or more actions. Functions, however, have an advantage in that they can return a value, which can then be treated like a standard variable. Functions can only be called from within existing functions or subroutines.

Procedure

In VBA, modules can contain any number of procedures, which refer to all functions and subroutines.

Why Not Use a Single Module?

VBA is often used to accomplish minor tasks. In most cases, simple procedures will not call another procedure, or will not use any globally declared variables. In these situations, using a single module may be the best approach.

However, when engaging with complex projects, it may be worthwhile to consider separating procedures into different modules. While there is not a clear-cut rule in how to seperate code into different modules or procedures, the principle of Separation of Concerns provides some guidance.

Use modules to organize related procedures.

Let’s say you have to build a somewhat complex dashboard for accounting reports in Excel. The dashboard requires user inputs, back-end calculations, and complex output displays. In this case, it may make sense to separate procedures into three different modules:

  1. One module containing the input procedures. This includes all procedures to store inputs entered by the user, any validation functions, and any data cleaning procedures.
  2. Another module containing all back-end calculation procedures. This includes any procedures and functions required to manage accepted user-inputs, run any mathematical calculations, and prepare calculated data for display.
  3. A final module containing procedures which display the data. This includes procedures that handle data passed from calculation procedures and display them on the spreadsheet with formatting and placement.

While this may be an oversimplification—complex dashboards may require even more modules—it’s a good starting point for a dashboard.

So why bother delegating these procedures to their own modules? For one, while working on code, it can vastly improve the ease of finding specific subroutines. Unless you plan your project well in advance, you may quickly realize that the project may require dozens of procedures for each task. Separating these procedures into modules can make them easier to find and pinpoint, especially when using the VBA procedures list.

Modules can help manage variable scope.

When projects become more complex, you will undoubtedly encounter a need to establish global variables – variables which store values outside of individual script runs. It’s worthwhile to read this post on variable scope for a detailed overview, but in summary, variables declared within procedures only exist within that procedure while it is run, while globally declared variables can be stored to memory even after a procedure ends.

If a global variable should exist only for procedures within a specific module, it’s worthwhile to declare the variable as Private within that module. Assuming no other procedures outside of the module require the use of that variable, this will help keep code clean. Not only will it help avoid ambiguity errors if a variable of the same name is declared in a different module (with a different, unrelated purpose), it will prevent intellisense from accessing that variable name when working outside of its host module.

It’s also important to note that a global variable will not be properly initialized unless a procedure within the module containing the global variable is run. Going back on the dashboard example, let’s say that global variable setData exists as a Long variable in the module containing all back-end calculations. However, a procedure within the first data-input module requires that variable for a data input operation. Unless a procedure within the calculation module is called, the setData variable will be cast as a default variant when it is used in the data-input module.

Modules can separate Subroutines and Functions.

In some cases, it may be worthwhile to separate subroutines and functions into separate modules. If your otherwise complex project only requires a few calculations driven by functions, it may be worthwhile to keep them all functions in a central location. Additionally, these functions may share certain global variables.

Modules can separate general spreadsheet operations.

For complex projects, it may be worthwhile to include a module for general worksheet and workbook operations. This can be used to store values that are not essential to the data being processed, but are essential for the code which processes that data. For example, you may want to include a global variable which manages certain hidden or unhidden rows to help let the user know where to look.

Mimic a Folder Structure with Naming Conventions

As stated in the introduction, the VBA editor does not permit and subfolders outside of its basic module type hierarchical structure. Subfolders are helpful to organize large complex projects that contain numerous modules. Despite the absence of this feature, there are some techniques for mimicking a folder structure with naming conventions.

Going back to the previous dashboard example, let’s drill down to some details to begin building out a project structure. The dashboard displays financial data for an entire organization, requiring numerous inputs, calculations, and displays for different transaction types, such as expenses, revenue, prepaid expenses, and accrued expenses. Ideally, we would want to establish a folder structure for each action, but VBA does not permit this. However, using naming conventions, we can mimic a hierarchical folder structure and ensure that all related modules are grouped together.

By including a consistent word for the first part of each module within a “folder”, the group of modules will always be grouped together as modules are listed in alphabetical order. Here’s a simple naming convention for a single level folder structure

[Folder Name]_[Description]

In our dashboard example, the project structure may look as follows:

Inputs_Accrued
Inputs_Expenses
Inputs_Prepaid
Inputs_Revenue
Calculations_Accrued
Calculations_Expenses
Calculations_Prepaid
Calculations_Revenue
Outputs_Accrued
Outputs_Expenses
Outputs_Prepaid
Outputs_Revenue

We could even take it a step folder an include subfolders. In the following example, the Revenue and Expense operations are included in the “Inc” subfolder (short for Income Statement), while the Prepaid and Accrued operations are included in the “Bal” subfolder (short for Balance Sheet).

[Folder Name]_[Subfolder Name]_[Description]

Using this example, all balance sheet and income statement modules can now be grouped together.

Bal_Calculations_Accrued
Bal_Calculations_Prepaid
Bal_Inputs_Accrued
Bal_Inputs_Prepaid
Bal_Outputs_Accrued
Bal_Outputs_Prepaid
Inc_Calculations_Expenses
Inc_Calculations_Revenue
Inc_Inputs_Expenses
Inc_Inputs_Revenue
Inc_Outputs_Expenses
Inc_Outputs_Revenue

Example of a VBA project subfolder structure.
Example of a VBA project subfolder structure.

If future modifications expanded the list of modules even further, it will still be easy to find modules given the alphabetic structure. Module names are also “smart coded” in a way which provides a quick description of the encapsulated code.

Other Notes

  • If a variable is declared as a global variable in one module, but declared as a procedure level variable in a subroutine, the procedure level variable will superseded the global variable within its own procedure. Likewise, the procedure level variable will not override the global variable — VBA will treat them as two different variables, even if they have the same name.
  • Global variables follow the same control flow rules as any other VBA element. If a global variable exists in a subroutine which is called later in a procedure, that global variable will not be initialized (can not be used) until that subroutined is accessed.

Leave a Reply