Are you a team member? Writing VBA code for your team project? Are other team members working on the same project? What are best practices in writing VBA code so other team members can understand your code efficiently and continue your job smoothly?
As a team of Excel VBA Developers, we share our experiences within our own group.
A sheet name might be used all over the code. Similarly, a range name is often used. It’s a good practice to define a constant 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 all over the code to change a sheet name. Simply, they can change the value of the constant.
A Module for Constants
You can create a module called “Constants” and place all your constants in the module. Make sure categorize your constants. For example, place constants for sheet names one below each other. And another section for range names. For each section, make sure to write a comment what this section is about. Reading comments is easier for your team member than codes. A screenshot of a module that I created for constants is shown.
In VBA you don’t get an error if you don’t declare variables. However, it’s 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 in the beginning of a function because any one else reading the code, they probably look for the list of variables at the start of a function.
Declare Variables with Specific Data Type
And make sure to declare the variable with a specific data type so others have a clue what could be the purpose of it. 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, add comments for variable and write the purpose of them, if the variable is for specific use. And use proper naming. Is a variable called “rep” more meaningful or “reportwb” for declaring a report workbook?
This is a sample of how I 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 to use a specific descriptive name for them? When you find a function called in a middle of another function and want to know what it’s 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 have 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, many times a descriptive function name is not enough. Make sure to add some comments when calling a function and explain what the function does and possibly what it returns.
Descriptive Commenting in Defining Functions
Another programmer is reading your code and finding out why you created a function called “adjust_columns”. They get a clue from the function name that this is for fixing rows but what exactly it does? They could study the function code or you could save their time by writing some comments what the function does. For example you can explain that this function adjust 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 practice to explain
- the inputs
- the outputs, if any
- and requirements. For example a requirement of a function before it’s called is that a specific sheet should be active.
Practices for Functions
It’s a good practice to make function shorts. Study your long function might be frustrating for others. Break your long functions into smaller ones. Is it easier to understand three small function rather than a long one? For me, I prefer reading smaller functions.
Put Repetitive Code in a Function
Avoid repetitive code. If your team-mate wants to change something in a repetitive code, they have to change all of repetitions. Define a function and add the repetitive code in that 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. This way, any other programmer 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 a source code could save some lives! Reading comments instead of a code reduces frustrations in understanding 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?
- In the top of a module: write the purpose of the module.
- For unified block of code: For example, put comments for a loop, or a block of a code used for opening a workbook or a specific goal.
Please note, it’s a good practice to time stamp each comment so other programmers know when the related code is written or updated.