Microsoft KB Archive/843621

= How to format Sub procedures in Visual Basic for Applications =

Article ID: 843621

Article Last Modified on 2/6/2007

-

APPLIES TO


 * Microsoft Office Excel 2003
 * Microsoft Excel 2002 Standard Edition
 * Microsoft Excel 2000 Standard Edition
 * Microsoft Excel 97 Standard Edition

-



SUMMARY


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.



IN THIS TASK

 * INTRODUCTION
 * Sub procedure naming conventions
 * Components of Sub procedures
 * Comments
 * Line continuation
 * Code alignment, capitalization, and colors
 * Alignment
 * Capitalization
 * Auto quick info
 * Auto list members
 * Colors
 * REFERENCES



INTRODUCTION


A Sub procedure is a series of Visual Basic statements that are enclosed in the Sub and End Sub statements. In Visual Basic for Applications, macros perform actions. They are made up of Sub procedures that take arguments, such as constants, variables, or expressions, but they do not return values. Arguments are passed by a calling procedure. If a Sub procedure does not have arguments, the Sub statement must include an empty set of parentheses.

There are many components of a Sub procedure. In this article, we will discuss the following components:
 * Comments
 * Line continuation
 * Code alignment, capitalization, and colors

back to top



Sub procedure naming conventions
Each macro has the following Sub procedure structure: Sub MacroName lines of macro code End Sub 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 &quot;at&quot; 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: Module2.Test

back to top

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: 'this is a comment MsgBox &quot;text in a message box&quot; 'comment after the apostrophe REM this whole line is a comment 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.

back to top

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: ActiveWorkbook.SaveAs FileName:=&quot;myfile&quot;, _ Password:=&quot;pwd&quot;, CreateBackup:=True 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: ActiveWorkbook.SaveAs FileName:=&quot;my _ file&quot;, Password:=&quot;pwd&quot;, CreateBackup:=True 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: ActiveWorkbook.SaveAs FileName:=&quot;my &quot; & _ &quot;file&quot;, Password:=&quot;pwd&quot;, CreateBackup:=True

back to top

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:

If you indent a line of code by pressing the Tab key, after you type the code on this line, and then you press ENTER, the next line of code will automatically be tabbed the same amount.

To see how the Auto Indent feature works, do the following:  In the Visual Basic Editor, insert a new module in a project. Type Sub Test, and then press ENTER. Press the TAB key one time. The pointer will indent the code to the right.  Type the following line: MsgBox &quot;hello&quot; 

Note Save this module for the next section.

back to top

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:   Using the macro that you created in the &quot;Alignment&quot; section, move your pointer to the blank line below the following command: MsgBox &quot;hello&quot; </li>  Type the following command in lower-case letters: msgbox activeworkbook.name </li>  Press ENTER.

This line of code will automatically change to: MsgBox ActiveWorkbook.Name </li></ol>

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.

back to top

=
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.

back to top

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:  On the module that is created in the &quot;Alignment&quot; section, put your pointer on a blank line inside the Test macro.</li>  Type the following line of text. Do not press ENTER. MyVar = activeworkbook. A list with all the methods and properties appears. </li> Use the UP and DOWN arrows on your keyboard to scroll to the Path property.</li>  Press TAB when Path is selected. The line of code will change to: MyVar = activeworkbook.Path </li>  Press ENTER. The line of code will then change to: MyVar = ActiveWorkbook.Path </li> In the Project Explorer window, right-click the module that contains the macro code in step 5, and then click Remove Module.</li></ol>

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

back to top

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.

back to top

<div class="references_section">