Best Practices in Writing VBA Code for Developer Teams

Are you part of a development team? Are you writing VBA code for your team project? Are other team members working on the same project? What are the best practices in writing VBA codes so other team members can understand your code efficiently and continue the job smoothly?

As Excel Consultant in Vancouver, we share our experiences within our own group.

Define Constants

A sheet name might be used all over the code. Similarly, a range name is most often used. What if a constant is defined for these names? A team member might have to change a sheet or a range name. With defining constants, they don’t have to look for it all over the code just because they want to change a sheet name. They can simply, change the value of the constant.

A Module for Constants

You can create a module called “Constants” and place all your constants in that module. Make sure you categorize your constants, for example, place constants for each sheet names below each another. Create another section for range names. Make sure you write a comment for each section, and explain what this section is about. Reading comments is easier for your team member than reading codes. You can see a screenshot of a module that I created for constants below.

Declare Variables

In VBA you don’t get an error if you don’t declare variables. However, this is a must, especially, if you’re programming in a team based environment. Use “Option Explicit” on top of your code. Using Option Explicit forces you to declare all your variables. Define your variables at the beginning of a function because team members who are reading the code are probably looking for the list of variables at the beginning of a function.

Declare Variables with Specific Data Type

Make sure to declare the variable with a specific data type to give others a clue of what the purpose is. For example, “Dim c” might have multiple meanings. Some might interpret variable “c” as a counter (integer type) but others as a column name (string type).

Comments and Proper Naming for Variables

In addition, include comments for variable and write the purpose, if the variable is for specific use. Try proper naming. Which variable is more meaningful for declaring a report workbook, the one called “rep”or “reportwb”?

This is a sample of how we declared a variable:

' Master workbook
Dim masterwb as Workbook

Function Names & Commenting

Descriptive Function Names & Commenting in Calling Functions

Functions are for specific use. Why not use a specific descriptive name for them? When you find a function called in the middle of another function and you want to know what it is for, what would be the easiest way? I would say the answer is, proper function naming and commenting. If the function name is descriptive, you wouldn’t need to scroll all the way down to find the function and study its structure. A descriptive function name saves a lot of time in scrolling. However, often a descriptive function name is not enough. Make sure to add comments when calling a function, explain what the function does and possibly what it returns.

Descriptive Commenting in Defining Functions

So, another programmer is reading your code and is wondering why you created a function called “adjust_columns”. They get a clue from the function name that it has something to do with fixing rows, but what exactly does it do? They could study the function code or you could save their time by writing some comments about what the function does. For instance, you can explain that this function adjusts the width of all filled columns in a given sheet to make sure the values in the columns are visible.

Other than the purpose of the function in the comment area, it’s a good idea to explain:

• the inputs,
• the outputs, if any,
• and requirements, i.e. a requirement of a function before it’s called that a specific sheet should be active.

Practices for Functions

Short Functions

It’s a good practice to make functions short. Going through a long function might be frustrating for others. Break your long functions into smaller ones. It is easier to understand three small functions rather than a long one, right? I, at least, prefer reading smaller functions.

Repetitive Code in a Function

Avoid repetitive code. If your team member wants to change something in a repetitive code, they must change all of the repetitions. Define a function and add the repetitive code in that function.

Main Function

In some projects, there is a main function which is called first and calls other functions. In C or C++, this function is called main(). In VBA, we could follow the same practice and call the main function as main() and place it above all other functions. Consequently, other programmers can easily identify it.

Indent Style & Next Line

Indent your code specially inside loops, “if” conditions, and other blocks. Additionally, if a line gets too long, use “_” to break it into multiple lines. This helps programmers visually picture the code block in their mind.

Commenting

Commenting a source code could lift pressure! Reading comments instead of a code reduces frustrations and helps other team members understand what your code does. Place comments for,

• Functions: explain what a function does, the inputs, outputs and requirements.
• Specific variables: what a variable is specifically used for.
• At the top of a module: write the purpose of the module.
• Unified block of code: for example, include comments for a loop, or a block of a code used for opening a workbook or a specific goal.

Finally, it’s a good practice to time stamp each comment so other programmers know when the related code is written or updated.

If you’re looking for a team of Excel development services, please feel free to contact us for a free quote.