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.
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.
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:
- How to Insert / Delete Comments in Excel (including Shortcuts)
- How to Print Comments in Excel
- Get a List of All the Comments in a Worksheet in Excel
- Working with Worksheets using Excel VBA
- Using Workbook Object in Excel VBA (Open, Close, Save, Set)
- Useful Excel Macro Examples for VBA Beginners (Ready-to-use)