Onthefly Syntax and Programming Assistance

The Edit toolbar in the Visual Basic Editor window contains several buttons that let you enter correctly formatted VBA instructions with speed and ease. If the Edit toolbar isn't currently docked in the Visual Basic Editor window, you can turn it on by choosing View | Toolbars.

Figure 2-11:

Buttons located on the Edit toolbar make it easy to write and format VBA instructions.

Figure 2-11:

Buttons located on the Edit toolbar make it easy to write and format VBA instructions.

Writing procedures in Visual Basic requires that you use hundreds of built-in instructions and functions. Because most people cannot learn the correct syntax of all the instructions that are available in VBA, the IntelliSense technology provides you with syntax and programming assistance on demand. While working in the Code window, you can have special windows pop up and guide you through the process of creating correct VBA code.

List Properties/Methods

Each object can contain a number of properties and methods. When you enter the name of the object and a period that separates the name of the object from its property or method in the Code window, a pop-up menu may appear. This menu lists the properties and methods available for the object that precedes the period (Figure 2-12). To turn this automated feature on, choose Tools | Options. In the Options dialog box, click the Editor tab, and make sure the Auto List Members check box is selected.

Figure 2-12:

While entering VBA instructions, Visual Basic suggests properties and methods that can be used with the particular object.

Figure 2-12:

While entering VBA instructions, Visual Basic suggests properties and methods that can be used with the particular object.

To choose an item from the pop-up menu (Figure 2-12), start typing the name of the property or method that you want to select. When Excel highlights the correct item name, press Enter to insert the item into your code and start a new line. Or, if you want to continue writing instructions on the same line, press the Tab key instead. You can also double-click the item to insert it in your code. To close the pop-up menu without inserting an item, simply press Esc.

When you press Esc to remove the pop-up menu, Visual Basic will not display it again for the same object. To display the properties/methods pop-up menu again, you can:

■ Use the backspace key to delete the period and type the period again

■ Right-click in the Code window and select List Properties/Methods from the shortcut menu

■ Choose Edit | List Properties/Methods

■ Click the List Properties/Methods button ^=1 on the Edit toolbar List Constants

Earlier in this chapter, you learned that to assign a value to a property, you need to use the following rule: Object.Property = Value. If the Options dialog box (Editor tab) has a check mark next to the Auto List Members setting, Excel displays a pop-up menu listing the constants that are valid for the property that precedes the equal sign. A constant is a value that indicates a specific state or result. Excel and other applications in the Microsoft Office Suite have a number of predefined, built-in constants. You will learn about constants, their types, and usage in Chapter 3.

Suppose you want your program to turn on the Page Break Preview for your worksheet. The Edit menu has two options: Normal View, which is the default view for most tasks in Excel, and Page Break Preview, which is the editing view that displays the worksheet as it prints. Both of these options are represented by a built-in constant. Microsoft Excel constant names begin with the "xl" characters. As soon as you enter in the Code window the instruction:

ActiveWindow.View =

a pop-up menu will appear with the names of valid constants for the property.

Figure 2-13:

The List Constants pop-up menu displays a list of constants that are valid for the property typed.

Figure 2-13:

The List Constants pop-up menu displays a list of constants that are valid for the property typed.

To work with the List Constants pop-up menu, use the same techniques as the List Properties/Methods pop-up menu outlined in the previous section. The List Constants menu can be activated by pressing Ctrl+Shift+J or clicking the List Constants button =4 on the Edit toolbar.

Parameter Info

If you've had a chance to work with Excel functions, you already know that many functions require one or more arguments (or parameters). If a Visual Basic function requires an argument, you can see the names of required and optional arguments in a tip box that appears just below the cursor as soon as you type the left parenthesis (Figure 2-14). The Parameter Info feature makes it easy for you to supply correct arguments to a VBA function. In addition, it reminds you of two other things that are very important for the function to work correctly: the order of the arguments and the required data type of each argument. You will learn about data types in the next chapter.

To see how this works, enter the following in the Code window:

ActiveWorkbook.SaveAs(

As soon as you enter the beginning parenthesis, a tip window appears just below the cursor. The current argument is displayed in bold. When you supply the first argument and enter the comma, Visual Basic displays the next argument in bold. Optional arguments are surrounded by square brackets [ ].

Figure 2-14: A tip window displays a list of arguments utilized by a VBA function or instruction.

To close the Parameter Info window, press Esc. To open the tip window using the keyboard, enter the instruction or function, follow it with the left parenthesis, and press Ctrl+Shift+1. You can also click the Parameter Info button % on the Edit toolbar or choose Edit | Parameter Info.

Quick Info

When you select an instruction, function, method, procedure name, or constant in the Code window and then click the Quick Info button on the Edit toolbar (or press Ctrl+I), Visual Basic will display the syntax of the highlighted item, as well as the value of a constant. The Quick Info feature can be turned on or off using the Options dialog box. To use the feature, click the Editor tab and choose the Auto Quick Info option.

Figure 2-15:

The Quick Info feature provides a list of function paramenters, as well as constant values and VBA statement syntax.

Figure 2-15:

The Quick Info feature provides a list of function paramenters, as well as constant values and VBA statement syntax.

Complete Word

Another way to increase the speed of writing VBA procedures in the Code window is with the Complete Word feature. As you enter the first few letters of a keyword and press Ctrl+Spacebar, or click the Complete Word button ^ on the Edit toolbar, Visual Basic will save you time entering the remaining letters by completing the keyword entry for you.

For example, enter the first four letters of the keyword Application in the Code window and press Ctrl+Spacebar:

Appl

Visual Basic will complete the rest of the word, and in the place of Appl, you will see the entire word Application.

If there are several VBA keywords that begin with the same letters, when you press Ctrl+Spacebar, Visual Basic will display a pop-up menu listing all the keywords. To try out this example, enter only the first three letters of the word Application, press the Complete Word button on the toolbar, and select the appropriate word from the pop-up menu.

Indent/Outdent

As you have seen, the Editor tab in the Options dialog box contains a number of settings that you can turn on to make many automated features available in the Code window. If the option Auto Indent is turned on, you can automatically indent the selected lines of code the number of characters specified in the Tab Width text box. The default entry for Auto Indent is four characters. You can easily change the Tab Width by typing a new value in the text box.

Why would you want to use indentation in your code? When you indent certain lines in your VBA procedures, you make them more readable and easier to understand. Indenting is especially recommended for entering lines of code that make decisions or repeat actions. You will learn how to create these kinds of Visual Basic instructions in Chapters 5 and 6. For now, let's practice indenting and outdenting lines of code using the WhatsInACell macro that you recorded in Chapter 1.

1. In the Project Explorer window, select the FirstSteps (Chap01.xls) project and activate the WorksheetFormatting module that contains the code of the WhatsInACell macro.

2. Select any block of code beginning with the keyword With and ending with the keywords End With.

3. Click the Indent button iw on the Edit toolbar, or press Tab on the keyboard.

4. The selected block of instructions will move four spaces to the right if you are using the default setting in the Tab Width box in the Options dialog box (Editor tab).

5. Click the Outdent button on the Edit toolbar, or press Shift+Tab to return the selected lines of code to the previous location in the Code window.

The Indent and Outdent options are also available from the Edit menu. Comment Block/Uncomment Block

In the first chapter you learned that an apostrophe placed at the beginning of a line of code denotes a comment. Not only do comments make it easier to understand what the procedure does, but they are also very useful in testing and troubleshooting VBA procedures. For example, when you execute a procedure, it may not run as expected. Instead of deleting the lines that may be responsible for the problems, you may want to skip these lines of code for now and return to them later. By placing an apostrophe at the beginning of the line you want to avoid, you can continue checking the other parts of your procedure. While commenting one line of code by typing an apostrophe works fine for most people, when it comes to turning entire blocks of code into comments, you'll find the Comment Block and Uncomment Block buttons on the Edit toolbar very handy and easy to use. To comment a few lines of code, simply select the lines and click the Comment Block button . To turn the commented code back into VBA instructions, click the Uncomment Block button •

If you don't select text and click the Comment Block button, the apostrophe is added only to the line of code where the cursor is currently located.

Was this article helpful?

0 0

Post a comment