Using Comments in Your VBA Code

A comment is the simplest type of VBA statement. Because VBA ignores these statements, they can consist of anything you want. You can insert a comment to remind yourself why you did something or to clarify some particularly elegant code you wrote. Use comments liberally to describe what the code does (which isn't always obvious by reading the code itself). Often, code that makes perfect sense today mystifies you tomorrow. Been there. Done that.

You begin a comment with an apostrophe (' ). VBA ignores any text that follows an apostrophe in a line of code. You can use a complete line for your comment or insert your comment at the end of a line of code. The following example shows a VBA procedure with three comments, though they're not necessarily good comments:

Sub CommentsDemo()

' This procedure does nothing of value x = 0 'x represents nothingness 'Display the result MsgBox x End Sub

¿¡tîMj^ The "apostrophe indicates a comment" rule has one exception: VBA doesn't interpret an apostrophe inside a set of quotation marks as a comment indicator. For example, the following statement doesn't contain a comment, even though it has an apostrophe:

Msg = "Can't continue"

When you're writing code, you may want to test a procedure without a particular statement or group of statements. Rather than delete the statements, simply turn them into comments by inserting apostrophes. VBA ignores statements enclosed in apostrophes when executing a routine. Simply remove the apostrophes to convert the comments back to statements.

When testing a procedure you may want to remove some statements temporarily. Rather than delete the statements, you can convert them to comments. Then, when testing is completed, convert the comments back to statements. In the VBE, choose ViewOToolbarsOEdit to display the Edit toolbar that you see in Figure 7-1. To convert a block of statements to comments, select the statements and click the Comment Block button. To remove the apostrophes, select the statements and click the Uncomment Block button.

Figure 7-1:

The VBE Edit toolbar contains several useful buttons.

Comment block fffliil

Uncomment block

Although comments can be helpful, not all comments are created equal. For example, the following procedure uses lots of comments, but they add nothing of value. The code is clear enough without the comments.




Declare variables

Dim x As Integer

Dim y As Integer

Dim z As Integer


Start the routine

x = 100 ' Assign 100 to x

y = 200 ' Assign 200 to y


Add x and y and store in z

z = x + y


Show the result

MsgBox z



Everyone develops his or her own style of commenting. To be useful, however, comments should convey information that's not immediately obvious from reading the code. Otherwise, comments just chew up bytes and make files larger than necessary.

The following tips can help you make effective use of comments:

1 Briefly describe the purpose of each Sub or Function procedure you write.

1 Use comments to keep track of changes you make to a procedure.

1 Use a comment to indicate that you're using a function or a construct in an unusual or nonstandard manner.

1 Use comments to describe the variables you use, especially if you don't use meaningful variable names.

1 Use a comment to describe any workarounds you develop to overcome bugs in Excel.

1 Write comments as you develop code, rather than saving the task for a final step.

0 0


  • Isembold
    Why don't i see the comment for the vba code?
    7 years ago

Post a comment