I remember when I first dabbled in Excel VBA, it wasn’t just the loops and variables that caught my attention, but the humble comment—it’s the sidekick every Excel user needs but often overlooks. Comments in your VBA scripts are the silent guides that help you keep track of the what, why, and how of your code, especially when you’re revisiting it after a long time or when someone else needs to understand your logic. I’m going to share a few essentials on using comments effectively because, let’s face it, they can make or break the user experience for both you and others.
Now, you might be a beginner, or maybe you’ve been using Excel VBA for a while but haven’t truly appreciated the art of commenting. Whatever your skill level, knowing how to print comments or incorporate them into a loop can simplify your coding journey massively. Stick around as I dive into the specifics of using comments to enhance your coding workflow in Excel VBA—you’ll thank me when you’re able to figure out your own code weeks from now with just a glance.
What is a VBA Comment
In VBA coding within the Visual Basic Editor, comments are my side notes – they’re the text that VBA ignores when the code runs. They appear as a green line of text and are essential for explaining the context of my code to anyone who reads it later. Here’s how I mark a comment:
- Start with an apostrophe
’to initiate a comment line - Write my explanatory text after the apostrophe
- Comments don’t affect code execution
Example:
' This is a VBA comment that will not be executed by the Visual Basic Editor
Why use comments?
- To clarify the purpose of code segments
- Provide context within a module
- For easier maintenance and readability of the code
Remember, it’s not just code; my comments play a pivotal role!
(Video) Understanding VBA Comments
- Step 1: I type an apostrophe
'. - Step 2: I add my commentary text.
| Action | Result |
|---|---|
' precedes text |
Text turns green and becomes a comment |
Advantages of using a Comment
When I’m getting down to coding in VBA, comments are a lifesaver. Not only do they help me remember what on earth I was thinking when I wrote that piece of code, but they also make life easier for anyone else who might pick up my work. Here’s why laying down comments in your VBA code is smart:
-
Documentation: Trust me, scribbling notes alongside your code with comments is a great way to make sure you or a colleague can quickly understand what each part of your program does.
-
Change Tracking: Ever find yourself constantly tinkering with code? Slap on a comment, and you’ve got a handy record of what was changed, and when.
-
Procedure Insights: Before diving into a function, I drop a comment that gives the lowdown on what’s about to happen. It’s like a helpful signpost for anyone reading the code.
-
Variable Clarity: Variables can sometimes have cryptic names. A quick comment can clear up any confusion by describing what they’re there for.
-
Debugging Tool: When things go sideways, and I need to troubleshoot, turning lines of code into comments one at a time can be a real sanity-saver. It’s like having a conversation with the code to see where we misunderstood each other.
Add a Comment in a VBA Code
When I’m writing my VBA code and I need to explain what a section does—maybe for myself later, or for anyone else reading the code—I add a comment. Here’s how I do it:
- I click on the line where my comment should go.
- I simply type an apostrophe ( ‘ ) to start the comment.
- Then, I jot down my comment right there in the same line.
- Once I hit enter, that new comment turns green.
For example, if I’m using loops in my VBA module and I want to remind myself what the loop is for, I add a comment like this:
' This loop goes through all rows in the table
For i = 1 To rowCount
' Do something with each row
Next i
This way, each time anyone looks at my code, they immediately understand the purpose of each part, without the VBA trying to execute my text notes—handy, right? And when my code is cleaner and more understandable, it saves me time when I return to it later on.
Use the Comment/UnComment Button from the Toolbar
I find the comment block button super handy; it adds an apostrophe to start a line of code as a comment. Here’s how I do it:
- Select the line(s) of code.
- Click the comment block button.
- It looks like this:
'.
- It looks like this:
And when I need to bring back that line into action, I:
- Click the ‘Uncomment’ button to remove the apostrophe.
Enter a Multi-Line VBA Comment
When I’m adding explanatory notes or disabling chunks of code in VBA, I often need to comment out multiple lines. To do this efficiently, each line has to begin with an apostrophe.
Here’s how I handle multi-line comments:
- I start by selecting the lines I want to comment out.
- Then I hit the Comment Block button from the VBA editor’s toolbar.
This command swiftly transforms my selection into a commented section. Each line magically begins with an apostrophe, rendering them inactive as far as code execution goes. Here’s an illustration of how it looks before and after:
Recently, I discovered a neat trick for block comments. If my comments stretch continuously over several lines, I can use the line continuation character, an underscore _, right after a space. For instance, I place an apostrophe only at the start of the first line and append an underscore at the end of the first and second lines to continue the comment block without apostrophes on every line.
Check this out for clarity:
This approach keeps my code neat and my comments clear, all while making batch edits to my VBA script a breeze.
Use the “REM” Keyword to Insert a Comment in VBA
When I want to throw in a quick note or explanation within my VBA code, sometimes I skip the apostrophe and go with the REM keyword. It’s just like saying, “Hey, here’s a remark,” or formally, it stands for ‘remarks’.
Here’s how I do it:
- Syntax: Start the comment with
REM, followed by a space - Example:
REM This is a comment
| With Apostrophe | With REM |
|---|---|
' This is a comment |
REM This is a comment |
Remember, there’s no special button for this; I simply type it out. It’s neat, clear, and another proper way to document thoughts for future reference.
Comments while Recording a Macro with Macro Recorder
When I start recording a macro, I often find it super helpful to leave myself some notes about what the macro is supposed to do. Here’s a cool thing: Excel lets me add a description in a special input box right before I hit the record button.
After recording, if I jump into the Visual Basic Editor (VBE) to check out the code, my notes are right there as a comment. It’s pretty handy because it means I’m less likely to forget what each part of the macro is for.
And just to mention, when adding comments directly while recording, I’ve noticed that Excel throws in some apostrophes in there—they’re like saying “Hey, this is a comment” to VBA.
Enter a Comment in the Same Line
Here’s how I quickly add a thought or note right next to my VBA code:
- I finish my line of code.
- Then I hit the apostrophe (
'), which tells VBA, “Hey, ignore this next part.” - I jot down my comment.
It looks like this in action:
| Code with Comments |
|---|
Sub Example() |
Dim i as Integer ' Declaring a variable |
i = 10 ' Setting the variable |
End Sub |
Super handy for inline explanations—everything after the ' stays just between us humans.
Shortcut Key for Add a Comment
Alright, let’s get to it. I found out that adding a comment in VBA doesn’t have a default keyboard shortcut. However, don’t sweat it, because we can make our own. Here’s how I do it:
-
Step 1: I right-click on the toolbar and select Customize…
-
Step 2: In the Customize dialog, I head over to the Commands tab, select the Edit category, grab the Comment Block, and drop it onto the toolbar.
-
Step 3: I then right-click the new comment block icon on the toolbar and slap an “&” in front of the name to make it “&Comment Button”.
-
Step 4: I do one more right-click to pick “Image and Text” for my button’s look, then head back to the editor to bask in the glory of my new power.
And that’s it! Now I can coast through my macros with Alt + C to comment a line of code. By the way, if I need to bring code back to life, I use Alt + U after adding an uncomment button using the same steps. It’s a lifesaver when I’m knee-deep in macro mania and need to organize my thoughts.
Change the Format of the Comment
To tweak the style of comments in VBA:
1. Head over to **Tools ➜ Options ➜ Editor Format**.
2. Click on the **Comment Text** to alter its appearance.
I switched my comment color from the default green to blue. Now, every comment in my code pane shows up in this new blue hue. Check out how this looks:
Interested in more about VBA? Jump to [What is VBA](https://excelchamps.com/vba/).
Related Tutorials
-
Getting Started with VBA
- I’ve learned a lot from the VBA Interview Questions guide.
- The Personal Macro Workbook tutorial really helped me organize my macros.
-
Writing and Managing Code
- Breaking up code lines is super simple with the steps from Add a Line Break in a VBA Code.
- For inserting new lines in strings, check out Add a New Line in VBA.
- VBA Exit Sub Statement has been a lifesaver for exiting routines early.
-
Tools and Tips
- To debug easily, I use the methods from VBA Immediate Window.
- Adding the Developer Tab made accessing VBA features a breeze.
-
Running Macros
- Starting a macro is super easy after I read Run a Macro in Excel.
- Recording actions to automate tasks? The Record a Macro in Excel guide is perfect.
-
Deepening Knowledge
- Ever needed to show a pop-up to users? I got by with the VBA MsgBox article.
- Advanced structuring with VBA With statements has been a game-changer for me.
Remember, if you need support or have feedback on any of these tutorials, most websites have a comment section or contact form. I’ve found that they’re pretty good at getting back to you if you run into trouble or want to give them a thumbs up.
