Adding and Managing VBA Comment – Step by Step Excel VBA Guide

When working with Visual Basic for Applications (VBA) in Excel, commenting on your code might seem like a small detail. However, it is one of the most valuable habits you can develop as a programmer. Comments help clarify your code, making it easier to read, understand, and maintain. Whether you’re returning to your own code after some time or someone else needs to work with it, well-commented code can be a lifesaver.

In this article, I’ll walk you through everything you need to know about VBA comment, from the basics to more advanced practices.

Key Takeaways:

• VBA comment enhance readability, making code easier to understand and maintain.
• The apostrophe (') is the primary method for adding VBA comment.
• Using the Comment Block button in the VBA editor simplifies adding comments to multiple lines.
• Comments should be concise, explaining the “why” behind complex code, not the “what.”
• Keeping comments updated with code changes ensures they remain accurate and helpful.

 

Introduction to the Art of VBA Comment

The Role of Comments in VBA Scripting

In my explorations of Excel VBA, I’ve come to appreciate the subtle art of commenting. Comments in VBA scripting serve as silent narrators within our VBA modules, providing context, rationale, and human understanding to the otherwise mechanical script.

They’re the annotations in the margins—non-executable strands of text—that allow us to document what our code is intended to do. Consider it much like keeping a journal within our code, ensuring that the intent behind our programming isn’t lost over time.

Understanding the Syntax and Methodology

As I delve deeper into comment syntax, let me share the simplicity that makes VBA accessible for even the novice coder. A comment is initiated with a single apostrophe (‘). Anything following this apostrophe on the same line becomes a part of the comment and is thus ignored by the VBA engine during code execution.

Methodologically, commenting should never be an afterthought; it should be integrated into the scripting process, paving the way for clear communication to anyone who might work with the code in the future. It’s also essential when revisiting scripts – because, let’s face it, even we can forget the why and how of our own code over time.

By ensuring that each significant or complex line of VBA is accompanied by a clear comment, we foster an environment of transparency and easier maintainability within our programming endeavors.

 

Key Techniques for Adding VBA Comment

Single Line Comment

Inline commenting is a swift, straightforward method to elucidate the purpose of a particular line of code. It is inherently useful when I want to explain a variable, reveal the reasoning behind a certain operation, or even to denote a provisionary fix.

To implement an inline comment, I simply type an apostrophe followed by my descriptive note at the end of a code line. This approach keeps the explanation in proximity to the action, ensuring clarity. While inline comments are valuable, I’m cautious not to overuse them, as a surfeit of commentary on simple operations can clutter the code more than elucidate it.

It’s like seasoning a meal; just enough enhances the flavor, too much, and we’re overwhelmed.

For example:

In these lines, comments are added succinctly right after the code, illustrating each line’s intent without obscuring the core script.

Multi-Line Comment

When it comes to explaining larger blocks of logic, setting the stage for complex functions, or providing extensive documentation within the VBA script, multi-line comments become indispensable.

Unlike some programming languages where block comments are wrapped within a specific set of characters, in VBA, every line needs to be prefaced with an apostrophe. For example, I might detail the strategy behind a selection sort algorithm as follows:

These lines work together to give a comprehensive view of a section’s purpose. Although it might appear cumbersome to add an apostrophe to each line, it encourages me to be succinct yet descriptive. This makes my code more approachable for future readers, a virtue that cannot be overstated.

Using Comment Block

To add a comment using the Comment Block option in VBA, follow these simple steps. I’ll walk you through the process step by step using the built-in tools in the VBA editor. This is a handy method when you want to comment out multiple lines of code quickly.

STEP 1: In the VBA Editor, right-click anywhere on the toolbar, and from the dropdown menu, select Customize.

STEP 2: In the Customize window, check the Edit toolbar. Drag the ‘Comment Block’ and ‘Uncomment Block’ commands onto the toolbar.

You should now see the Comment Block button (a green rectangle with a comment bubble) and the Uncomment Block button (a green rectangle with a crossed-out comment bubble) on your toolbar.

STEP 3: Highlight the lines of code you want to comment by clicking and dragging your mouse over them, or using Shift + Arrow Keys to select them.

STEP 4: Click on the Comment Block button. All the selected lines will be commented out, with an apostrophe (‘) added to the beginning of each line.

You can repeat this process as needed for multiple blocks of code. If you want to remove the comments, simply select the lines and click the Uncomment Block button.

 

Advanced Commenting Features

Personalizing Your VBA Environment with Color-Coded Comments

Personalizing my VBA environment by color-coding comments has proven to be beneficial, especially in enhancing readability and code segmentation. This simple customization allows me to visually categorize comments and swiftly navigate complex macros.

For others like me, who might be visually impaired or have specific preferences for on-screen colors, this flexibility is more than an aesthetic choice — it can be a necessity.

Changing comment color is a simple process:

STEP 1: Open the Visual Basic Editor (VBE) and navigate to ‘Tools’ > ‘Options’.

STEP 2: Click on the ‘Editor Format’ tab.

STEP 3: From here, select ‘Comment Text’ in the ‘Code Colors’ section. Adjust the ‘Foreground’ and, if required, the ‘Background’ colors to the preferred shades.

By choosing a color that stands out to them, they could navigate the code more effectively, illustrating the importance of inclusiveness in software design practices.

However, this customization should be approached with a certain level of restraint. Too many colors might create a visual cacophony that can have an off-putting effect rather than providing clarity. I recommend a few, meaningful choices that align with individual preferences — akin to an artist selecting a palette that reflects their vision yet respects the viewer’s experience.

Leveraging the “REM” Keyword for Readability

Leveraging the ‘REM’ keyword can be an alternate commenting style in VBA that some users find enhances readability. ‘REM’ stands for ‘Remark’ and functions exactly like an apostrophe in that it remarks out the rest of the line.

It spells out its purpose forthrightly and can be particularly useful for making certain comments stand out or when creating a template or instructional code where ‘REM’ might be more visibly recognizable and explanatory for beginners.

Usage is simple: just as I would with the apostrophe, I place ‘REM’ at the beginning of a line to comment on everything that follows. For example:

However, I must type the ‘REM’ keyword manually as there’s no dedicated button for it in the VBA editor, and it’s generally less popular than the simple apostrophe due to requiring more typing. Despite this, I find ‘REM’ to be a clearly defined indicator that makes certain comments stand out, which can be valuable for delineating sections of code or for training materials where clarity is paramount.

 

Best Practices for Writing VBA Comment

Simply adding comments isn’t enough—you need to ensure they are helpful. Here are some best practices I’ve learned over time for writing good comments in VBA:

• Be Clear and Concise – Your comments should explain the “why” behind the code, not the “what.” The code itself shows what is happening, but a comment should help clarify why a particular approach was taken. Avoid stating the obvious.
• Use Comments to Explain Complex Logic – If you’re using complex formulas or algorithms, comments are crucial. Write a brief explanation of what the code is doing so someone unfamiliar with the project can easily follow along.
• Update Comments When You Update Code – One of the most common problems with comments is that they get outdated. If you change your code, make sure to update your comments too, or they can become misleading. An outdated comment is worse than no comment at all.
• Avoid Over-Commenting – Adding too many comments can make the code harder to read. Not every single line needs a comment—focus on the parts of your code that require clarification. For instance, if you have a function that calls several smaller functions, it’s usually unnecessary to comment every line inside those smaller functions unless they contain tricky logic.

 

Frequently Asked Questions (FAQs)

How can I quickly add VBA comment?

To quickly add a comment in VBA, click at the beginning of the line where the comment should go, type an apostrophe (‘), then type your comment. The comment will turn green, indicating it’s non-executable text.

How do you comment out multiple lines in Excel VBA?

In Excel VBA, comment out multiple lines by manually placing an apostrophe (‘) at the start of each line or by using a pre-set shortcut key if you’ve customized your VBA editor with a ‘Comment Block’ function.

What is the purpose of adding VBA comment?

Comments in VBA code serve to document the purpose and functionality of the code, making it easier to understand, maintain, and modify in the future, either by the original author or by others.

Is it possible to create a custom toolbar button for commenting in VBA?

Yes, it’s possible to create a custom toolbar button for commenting in VBA. Customize the toolbar and drag the ‘Comment Block’ and ‘Uncomment Block’ commands onto it, then set a key for easy access.

How do I add a new line to a comment in Excel VBA?

To add a new line to a comment in Excel VBA without starting a new comment, end your line with a space followed by an underscore (_), then press Enter to continue on the next line. Remember to start with an apostrophe on the following line.