How to format Sub procedures in Visual Basic for Applications

This article describes how to format Sub procedures in Microsoft Visual Basic for Applications. The topics that are covered include general information about Sub procedures, Sub procedure naming conventions, and components of Sub procedures. This article is helpful for users who are getting familiar with Visual Basic for Applications.

Each macro has the following Sub procedure structure: You can use any name for your Sub procedure, but you will want to use the following guidelines:

• The first character must be a letter.
• The name cannot exceed 255 characters.
• You cannot use a character space, a period (.), an exclamation point (!), an ampersand (&), an "at" sign (@), a dollar sign ($), or a number sign (#) in the name.
• You cannot use restricted keywords.

Note For a complete list of restricted keywords, click Content and Index on the Help menu in the Visual Basic Editor, and type keywords.

You can have two macros in the same project with the same name, but you must apply the following rules:

• The two macros with the same name must be in different modules.
• To call a macro where there are two macros in the same project with the same name, you must add the module name before the macro name.

For example, to call the Test macro in a module that is named Module2, use the following syntax:

Components of Sub procedures

Comments

Comments can explain a procedure or a particular instruction in your code. They can be invaluable when you or someone else reads your code at a later date. Comments are made up of whole lines or endings to lines that start either with an apostrophe (') or with a REM statement that is followed by a space. They will automatically turn green in a module. When you run a macro, Visual Basic ignores any comments in your code.

The following three lines are comments: You can also use comments as a debugging tool when you have problems with a macro. Instead of cutting out a problematic line of code, you can just insert an apostrophe (') at the beginning of the line of code, and then the next time that you run your macro, that line of code will not be executed.

Line continuation

Lines of code can cause very long macros. You can break your lines of code up into smaller lines by using the line continuation format. The line continuation format is a space followed by an underscore ( _ ). For example, the following two lines of code are treated as single executable lines of code because the first line ends with a space and is followed by an underscore: When you use this line continuation format, you make your code easier to read because you do not have to scroll left or right to read long lines of code.

Note You cannot use the line continuation format in the middle of a string expression. The following line of code will generate a compile error: Instead of breaking the string with line continuation, break the string into two or more strings and concatenate them with an ampersand (&). Therefore, to prevent an error with the code used in the earlier example, the code will appear as follows:

Code alignment, capitalization, and colors

When you type statements into a module, there are some automatic features that are built into the Visual Basic Editor that will change the appearance of your text.

Alignment

To automatically indent your code, do the following:

1. In the Visual Basic Editor, on the Tools menu, click Options.
2. Click the Editor tab, and then click to select Auto Indent.

By default, this check box is selected and it works in conjunction with the tab width. The Auto Indent feature is very useful for formatting your macros. We recommend that you leave this feature on. Auto Indent operates according to the following rule:

To see how the Auto Indent feature works, do the following:

1. In the Visual Basic Editor, insert a new module in a project.
2. Type Sub Test, and then press ENTER.
3. Press the TAB key one time. The pointer will indent the code to the right.
4. Type the following line:

   MsgBox "hello"
   

Note Save this module for the next section.

Capitalization

Another built-in feature is the auto-recognition feature. What this feature does is let you not have to worry about whether a particular letter in a macro command must be upper-case or lower-case letters. If you spell the command correctly, the Visual Basic Editor will automatically change the letters appropriately.

To see how this feature works, do the following:

1. Using the macro that you created in the "Alignment" section, move your pointer to the blank line below the following command:

   MsgBox "hello"
   

2. Type the following command in lower-case letters:

   msgbox activeworkbook.name
   

3. Press ENTER.
   
   This line of code will automatically change to:

   MsgBox ActiveWorkbook.Name
   

The auto-recognition feature also works with variables. Any dimmed variable is automatically recognized. The case of the letters in the variable is changed to match the Dim line.

Note This feature helps reduce typographical errors in your code.

Auto quick info

You may notice that as soon as you complete a recognized Visual Basic statement, a tip that contains the syntax and argument list for the statement appears just under the statement. This is named the Auto Quick Info feature. By default, the feature is turned on. You can turn this feature off by locating the Editor tab in the Options dialog box, and then selecting to clear the Auto Quick Info check box.

Auto list members

You may have also noticed that a list appears when you type a period (.) following a valid Visual Basic object. This list displays all the valid properties and methods for the object. You can either manually type a method or property, or you can select one from the list and then press TAB to have that item appended to the object. By default, this feature is turned on, but it can be turned off by locating the Editor tab in the Options dialog box, and then by selecting to clear the Auto List Members check box.

To see an example of how the Auto List Members feature works, do the following:

1. On the module that is created in the "Alignment" section, put your pointer on a blank line inside the Test macro.
2. Type the following line of text. Do not press ENTER.

   MyVar = activeworkbook.
   

   A list with all the methods and properties appears.
3. Use the UP and DOWN arrows on your keyboard to scroll to the Path property.
4. Press TAB when Path is selected. The line of code will change to:

   MyVar = activeworkbook.Path

5. Press ENTER. The line of code will then change to:

   MyVar = ActiveWorkbook.Path
   

6. In the Project Explorer window, right-click the module that contains the macro code in step 5, and then click Remove Module.

When you receive the prompt that prompts you to export the module, click No. This will remove the module from your project.

Colors

You may already have noticed that words in your macro display as blue, black, green, or red. These colors increase the readability of your macro so that you can pick out items like comments, keywords, and syntax errors. Be default, the colors of the code appear as follows:

• Keyword text: blue
• Normal text: black
• Comment text: green
• Syntax error text: red

You can change the colors that are assigned to certain types of macro code on the Editor Format tab of the Options dialog box.

For more information about Sub procedures in Visual Basic for Applications, click the following article numbers to view the articles in the Microsoft Knowledge Base: