Coding in VBA can be a pretty snug fit for automating tasks in Excel, but it’s not just about writing code that works. It’s also about making that code readable for future me or anyone who might pick it up after me. That’s where comments come into play. They’re like little notes to self or to the next person, explaining what’s going on, or maybe why I made a certain coding choice.
I find that adding comments in VBA is a piece of cake. Just a quick tap of the apostrophe key and I’m off, typing away explanations or context right in the code. It doesn’t just help with readability; it’s also key to maintaining the code later on. The trick, though, isn’t just in using comments—it’s in using them well, making sure they make things clearer and not more confusing. Let me share some insights on how to ace using comments.
What is a VBA Comment
In VBA, I make sure to use comments to clarify my code. These are lines that VBA skips over during execution, marked green for easy identification. Here’s what I keep in mind:
- Purpose: To explain and improve the readability of my code.
- Appearance: Green text within the Visual Basic Editor.
- Syntax: Begin with an apostrophe (
').
Example:
' This is a comment explaining the next line of code
Range("A1").Value = "Hello"
(Video) Understanding VBA Comments
- Step 1: Click the code line.
- Step 2: Type
'(apostrophe). - Remember: Everything after
'is a comment.
| Action | Result |
|---|---|
' + Text | Comment appears |
| Enter | Comment finalized |
Advantages of using a Comment
Commenting in VBA code is like leaving breadcrumbs on a trail; it guides anyone who follows. Let me break down why I find comments absolutely crucial:
Documenting Work: It’s like scribbling notes in the margin of a book. I jot down what a chunk of code is up to. Super handy when I revisit it later, or when sharing with others.
Keeping Tabs: Much like post-its on a calendar for myself, comments are perfect for tracking changes. Especially true for those bits I tweak often.
Clarifying Procedures: At the outset of a procedure, I’ll drop a comment. It’s essentially a brief for what’s coming up – the what and the why.
Elucidating Variables: Since variables are the backbone of my VBA story, I use comments to detail what role they play in the narrative.
Debugging Aid: Sometimes, to isolate troublemakers in my code, I transform lines into comments. It’s like switching off puzzle pieces to see where the picture changes.
Comments are time-savers – they prevent a lot of future head-scratching.
Add a Comment in a VBA Code
In working with the VBA editor, I sometimes find it necessary to explain the purpose of a line of code or to disable it temporarily without deletion. Here’s how I add comments:
- I click on the line I’m interested in commenting.
- Then, I type an apostrophe (
'). This simple character is the starting point for a comment in VBA. - Right after the apostrophe, I type my comment. Anything I type after the apostrophe won’t affect the code execution.
- When I’m done, I press Enter. The text of my comment appears in green, confirming it’s a non-executable comment.
The Visual Basic Editor, or VBA editor, instinctively turns the comment text green to distinguish it from the executable code, ensuring clarity. This is a swift way to annotate my work directly in the code without altering the program’s behavior. Using the AddComment method, I could also dynamically insert comments into Excel cells via a macro, which is especially handy for documenting complex worksheets.
Use the Comment/UnComment Button from the Toolbar
I find that sometimes the quickest way to alter blocks of code is right from the toolbar. When I need to comment out parts of my VBA code, I select the lines I’m working on and hit the Comment Block button, a handy shortcut that adds an apostrophe to the beginning of each line.
To keep things tidy, especially when debugging or revising code, I use the adjacent Uncomment Block button to remove the apostrophes. This feature keeps me from manually deleting each apostrophe, saving me a ton of time. Just click the button and I’m back to executing those lines of code.
In case the Comment and Uncomment buttons aren’t already on your toolbar, you’ll need to:
- Right-click on the toolbar and pick the Customize option.
- Go to the Commands tab, look for Edit under Categories.
- Drag the Comment Block and Uncomment Block buttons onto the toolbar for easy access.
Here’s a little pro tip: once you’ve got those buttons up there, you can quickly Modify Selection to create keyboard shortcuts or alter the button image.
Enter a Multi-Line VBA Comment
When I’m working on tidying up my VBA code, sometimes I need to group a bunch of lines together with comments for better clarity. Here’s the deal: each line I want to comment out has to kick off with an apostrophe. It’s like raising a little flag that says, “Hey, this is a comment, not code.”
If I’m dealing with several lines, I’d rather not add an apostrophe to the beginning of each line manually. That’s where the comment button comes into play. Once I select all the lines and hit that button, it works like magic—each line gets its own apostrophe, transforming the whole selection into a comment block. It looks something like this:
The moment I click, boom—my code is now sporting a sleek multi-line comment block.
Learned something awesome recently: if my comments are flowing, one after another, I can use a line continuation character, an underscore. Just pop that underscore at the end of a line, and it’s like a bridge to the next. Check this out:
Here, the first line begins with an apostrophe, and the underscore at the end lets the comment spill over to the next line—no extra apostrophes needed. Pretty neat, right? Helps keep things tidy and connected.
Use the “REM” Keyword to Insert a Comment in VBA
In VBA, comments are typically added with an apostrophe, but I sometimes use the REM keyword. Here’s what I do:
- Begin the line with REM, followed by a space
- Write my comment text directly after REM
| Method | Syntax Example |
|---|---|
| Apostrophe | ‘ This is a comment |
| REM | REM This is a comment |
I remember that REM is short for remarks, which helps remind me that it’s another way to add comments. Just keep in mind, unlike the apostrophe that can be used mid-line, REM must be at the start. There’s no toolbar button for it—I type it out myself.
Here’s an example:
Comments while Recording a Macro with Macro Recorder
Before I start recording a macro, there’s a neat feature that allows me to add a description. In the record macro dialog box, I simply type in what I want in the “Description” input box.
Here’s how this works:
- Open the Record Macro dialog.
- Type your comment in the Description box.
- Start recording your actions.
After recording, when I jump to the VBE (Visual Basic Editor) to check out my code, my comments are right there at the top. By the way, VBA does this cool thing by adding apostrophes (') turning regular text into comments.
The result? Those descriptions become non-executable code, which means they’re just there for my reference and don’t affect the macro performance.
Additionally, here’s VBA automatically adding those apostrophes in recorded macros:
So whenever I need to detail my actions or give myself future reminders, the description box is my friend.
Enter a Comment in the Same Line
Sub AddInlineComment()
Dim exampleVar As Integer ' Initialize variable
exampleVar = 5 ' Assign value to variable
MsgBox exampleVar ' Display variable value
End Sub
- To include a comment directly next to code, type an apostrophe
'followed by your text. - The comment will not affect your code execution and provides clarity on what the line does.
- Notice how the comments turn green in VBA, signaling they’re non-executable text.
Shortcut Key for Add a Comment
Okay, so you’re coding away in VBA and you want to drop a quick comment without touching your mouse. Looks like you’re out of luck because there isn’t a built-in keyboard shortcut… but hang on, I’ve got a nifty workaround.
Here’s what I do:
First, I right-click on the toolbar and then hit the customize option.
In the dialog that pops up, I go to Command-Tab ➜ Edit ➜ and drag the comment block over to my toolbar.
Next, I tweak the name of the comment block icon by tacking on an “&” before it, so it reads “&Comment Button”.
I right-click that button again, pick “Image and Text”, then scoot back to the visual basic editor.
Now I can hit Alt + C and bam, any line I’m on turns into a comment! And guess what? If I repeat these steps for the uncomment button, I just press Alt + U to bring the code back to life. Isn’t that snazzy?
Change the Format of the Comment
When customizing VBA code, I often tweak the comment format for better readability. To change the default green text, follow these steps:
- Navigate to Tools ➜ Options ➜ Editor Format.
- Select Comment Text to edit.
Here’s a table of options I usually consider:
| Action | Result |
|---|---|
| Edit Foreground Color | Changes the text color of the comment. |
| Edit Background Color | Alters the background color of the comment. |
Right-click to access these settings. After editing, the comment’s appearance in the code window updates instantly.
For example, I changed my comment text color from green to blue, and now all comments in my code are blue.
Quick Tips for using VBA Comments
- Style Matters: I always make sure my comments have a uniform style. It keeps things clear, especially for VBA beginners.
- Stay Current: It’s crucial for me to update comments as my code evolves. Outdated notes can confuse more than help.
- Smart Debugging: For complex code, I use conditional compilation to manage which parts run during debugging. It’s a handy trick that goes beyond basic commenting.
- Simplicity Is Key: Even with minimal coding knowledge, following best practices makes VBA coding easier to manage and understand.
Wrap Up
In managing my Excel spreadsheets, I’ve learned that judicious use of comments in VBA can be a real game-changer. Here’s a snapshot of what I keep in mind:
- Comments: They’re not just notes; they’re my roadmap for future tweaks.
- Clarity: Simple, clear comments save me headaches down the road.
- Visibility: Each comment indicator is a signpost, guiding the way through complex functions.
For more on Excel VBA, check out What is VBA. Adding comments is like leaving breadcrumbs for myself and others—it just makes sense.
Related Tutorials
Function & Loops: For cracking those tricky interview rounds with VBA questions, I found VBA Interview Questions.
Text Manipulation: Here’s how you add line breaks and new lines in strings: Add a Line Break in VBA and Carriage Return in VBA.
Macros & Procedures: Wondering how to run or record a macro? Check my bookmarks: Run a VBA Code and Record a Macro in Excel.
Coding Essentials: Been using these a lot – VBA Exit Sub Statement and VBA Immediate Window for Debugging.
VBA Tools: The best guides for the Visual Basic Editor and adding that nifty Developer Tab are right here: Excel Visual Basic Editor and Add Developer Tab.
