How to Use Comments in Excel’s VBA Editor

Written by co-founder Kasper Langmann, Microsoft Office Specialist.

Like any scripts, Excel’s VBA modules can be very long and complicated to read.

Basic VBA scripts are easy to work your way through.

But once you start adding more variables and functions, it gets very difficult to figure out what’s going on.

So, how do you keep your VBA code organized?

And make it clear what the various parts of the file actually do?

With comments!

Let’s take a look at how to insert comments, and then talk about what you should use them for.

Kasper Langmann, Co-founder of Spreadsheeto

How to insert comments in VBA code

Inserting comments in Excel’s VBA code is as simple as inserting an apostrophe.

If there’s an apostrophe at the beginning of a line, it will be “commented out.”

excel-vba-comment

What happens when a line is commented out?

Excel completely ignores it when it’s running the VBA code. So you can type whatever you want.

We’ll talk about what sort of information you may want to put in comments in a moment.

You’ll know that a line has been commented out when the text is green.

Open up the Visual Basic Editor in Excel (click the Visual Basic button in the Developer tab) and try it yourself.

excel-funcres-vba

You can add a new line and prepend an apostrophe, or just add an apostrophe to an existing line.

Adding an apostrophe to an existing line might change how the VBA runs—or even break it completely.

We’ll talk about why you might want to do that in a moment.

Kasper Langmann, Co-founder of Spreadsheeto

What to use VBA comments for

Now that we’ve seen how to add comments to Visual Basic in Excel, let’s talk about why you’d use them.

As I mentioned, VBA code can get very complicated very quickly.

If you’re working on a script over a long period of time (or even coming back later to tweak it), you might find that it’s very unclear.

excel-vba-macro-example

Comments can solve that problem.

Many programmers recommend adding comments before each section of the code to remind yourself (or whoever’s editing the code) of what the next block does.

excel-vba-comment-explanation

This makes editing—no matter who’s doing it—significantly easier.

That isn’t to say that you should leave a comment before every line. In many cases, it’s clear what the code is doing.

Kasper Langmann, Co-founder of Spreadsheeto

You can also use comments to prevent Excel from executing certain lines without deleting them.

This is great for when you’re testing different scripts to see if they get you the result you want.

In this example, I’ve commented out a line of viable code:

excel-vba-comment-out-code

This lets me run the script and see what happens if that line isn’t included.

If I decide that I want to reinsert that line, all I have to do is delete the apostrophe at the beginning of the line. It’s much easier than trying to remember what was there in the first place.

When you comment out code, it’s a good idea to leave a comment before it saying why you did so you don’t forget later.

Kasper Langmann, Co-founder of Spreadsheeto

When in doubt, leave a comment

It’s not always clear exactly when you should leave a comment in your code. But in general, it’s better to leave one than not.

I don’t recommend going crazy and leaving a comment on everything.

It’s better to include more information than less, but too many comments can make a file harder to work with than no comments at all.

Think about it from the perspective of someone coming back to this file a year from now. What will they need to know?

Kasper Langmann, Co-founder of Spreadsheeto