Comments in Excel VBA (Add, Remove, Block Commenting)

-- By Sumit Bansal

When working with VBA coding in Excel, you can easily add comments while writing the code.

Comments in VBA could be really useful for beginners, where you can add a comment to a line of code (or a block of code) that explains what it does. So the next time you come back to the code, you’ll not be completely lost and would have some context because of the comments.

Even for advanced Excel VBA programmers, once the code starts to get beyond a few lines, it’s a good idea to add context using comments (especially if there is a chance that someone else may have to work on the code in the future)

And since it’s a comment, VBA ignores it while executing the code.

In this short Excel tutorial, I will cover how to add comments in VBA and all the best practices around it.

This Tutorial Covers:

Adding Comments in VBA in Excel

To add a comment in VBA, simply add an apostrophe sign before the line that you want to be marked as a comment.

Anything after the apostrophe sign in that line would be considered a comment and VBA would turn it into green color (to visually differentiate it from regular code)

There are two ways you can add a comment in VBA:

Have a comment in a separate line, where this line starts with an apostrophe and then has the comment text after it
Have a comment as a part of the regular code line, where after the code you have a space character followed by an apostrophe, and then the comment(as shown below)

While I’ve seen both of these being used by the VBA programmers, I prefer the first method where a comment has a separate line altogether.

Another (old school) method of adding a comment is to have the word ‘Rem’ followed by the comment.

Here Rem is short for Remark.

Rem was used in the days of BASIC and has been kept in the current versions of VBA. While it’s good to know that it exists, I recommend you only use the apostrophe method while adding comments in VBA.

Converting a Line of Code (or Block of Code) into Comment

Sometimes, you may have a need to convert an existing line of code (or a block of code) into comments.

Programmers often do this when they’re working on a code and they want to quickly try out something else, while still keeping the already written code.

So you can quickly comment out a line, try a new one, and if you want to get the earlier code back, just remove the apostrophe and convert that comment back into a normal line of code.

For a line (or even a few lines), it’s best to manually add the apostrophe before these lines.

But if you have a large block of code, use the below steps to add the option to convert an entire block of code into a comment:

Click the View tab
Go to the Toolbar option.
When you hover your cursor over it, you’ll see more options
Click on the Edit option. This will make the edit toolbar appear somewhere on your screen.
Drag the Edit toolbars towards the toolbar area so that it would dock itself there (in case it’s not docked already)
Select the block of code that you want to comment out
Click on the ‘Comment Block’ option in the toolbar

The above steps would instantly convert a block of code into comments by adding an apostrophe in front of every line in that code.

In case you want to remove the comment and convert it back into regular code lines, select that block of code again and click on the ‘Uncomment block’ option in the Edit toolbar

Changing the Color of the Comment in Excel VBA

While VB doesn’t allow a lot of formatting, it does allow you to change the color of the comment if you want to.

One of my VBA course students emailed me and told me that the ability to change the color of comments in VBA was really useful for people suffering from color blindness.

Below are the steps to change the color of the comment in Excel VBA:

Open the Visual Basic Editor
Click the Tools option in the menu
Click on Options
In the Options dialog box, click on the ‘Editor Format’ tab
In the Code colors options, select Comment Text
Change the Foreground and/or the background color
Close the dialog box

When you change the comment color, it would also change the color for all the existing comments in your code.

Some Best Practices when Working with Comments in VBA

Here are some of the best practices to keep in mind when using comments in the VBA code.

Keep the comment meaningful and add context. When adding a comment, consider what would be helpful for a new user who has never seen this code and is trying to make sense of it.
Avoid excessive commenting as it would make your code look a bit cluttered. While it’s alright to add more comments when you are a beginner, as you gain more experience in VBA coding, you would anyway not need to add a lot of comments.
For every new Subroutine or Function, it’s a good idea to add a comment that explains what it does.
When working with complex code, it’s a good idea to add comments before conditions and loops, so that it’s easier for you to get a handle on what you had done when you revisit the code (or when someone else goes through the code)

In this tutorial, I covered how you can add comments in VBA and some best practices to use it.

I hope you found this tutorial useful.

Other Excel tutorials you may also like: