Taking Advantage of Intelli Sense

VBA's IntelliSense feature is like a mini version of the VBA Help system. It offers you assistance with VBA syntax, either on the fly or on demand. You should find this an incredibly useful tool because, as you'll see as you work through this book, VBA contains dozens of statements and functions and VBA-enabled programs offer hundreds of objects to work with. Few people are capable of committing all this to memory, and it's a pain to be constantly looking up the correct syntax. IntelliSense helps by giving you hints and alternatives as you type. To see what I mean, let's look at the four most useful types of IntelliSense help available.

List Properties/Methods

In Chapter 4, you'll learn how to work with the objects that each VBA-enabled application makes available. In particular, you'll learn about properties and methods which, put simply, define the characteristics of each object. (In broad terms, properties describe an object's appearance or behavior and methods describe what you can do with an object.)

As you'll see, however, each object can have dozens of properties and methods. To help you code your procedures correctly, IntelliSense can display a list of the available properties and methods as you type your VBA statements. To try this out, activate a module in the Visual

Basic Editor and type application followed by a period (.). As shown in Figure 2.11, VBA displays a pop-up menu. The items on this menu are the properties and methods that are available for the Application object. Use the following methods to work with this menu:

■ Keep typing to display different items in the list. In Excel, for example, if you type cap, VBA highlights Caption in the list.

■ Double-click an item to insert it in your code.

■ Highlight an item (by clicking it or by using the up and down arrow keys) and then press Tab to insert the item and continue working on the same statement.

■ Highlight an item and then press Enter to insert the item and start a new line.

■ Press Esc to remove the menu without inserting an item.

Figure 2.11

IntelliSense displays the available properties and methods as you type.

Figure 2.11

IntelliSense displays the available properties and methods as you type.

Note that if you press Esc to remove the pop-up menu, VBA won't display it again for the same object. If you would like to display the menu again, choose Edit, List Properties/ Methods (or press Ctrl+J).

List Constants

IntelliSense has a List Constants feature that's similar to List Properties/Methods. In this case, you get a pop-up menu that displays a list of the available constants for a property or method. (A constant is a fixed value that corresponds to a specific state or result. See Chapter 3 to learn more about them.) For example, type the following in a module: Application.ActiveWindow.WindowState=

Figure 2.12 shows the pop-up menu that appears in Excel. This is a list of constants that correspond to the various settings for a window's WindowState property. For example, you would use the xlMaximized constant to maximize a window. You work with this list using the same techniques that I outlined for List Properties/Methods.

If you need to display this list by hand, choose Edit, List Constants (or press Ctrl+Shift+J).

Figure 2.12

The List Constants feature in action.

Figure 2.12

The List Constants feature in action.

Parameter Info

You learned earlier that a user-defined function typically takes one or more arguments (or parameters) to use in its internal calculations. Many of the functions and statements built into VBA also use parameters, and some have as many as a dozen separate arguments! The syntax of such statements is obviously very complex, so it's easy to make mistakes. To help you out when entering a user-defined function or one of VBAs built-in functions or statements, IntelliSense provides the Parameter Info feature. As its name implies, this feature displays information on the parameters that you can utilize in a function. To see an example, enter the following text in any Excel module: activecell.formula=pmt(

As soon as you type the left parenthesis, a banner pops up that tells you the available arguments for (in this case) VBAs Pmt function (see Figure 2.13). Here are the features of this banner:

■ The current argument is displayed in boldface. When you enter an argument and then type a comma, VBA displays the next argument in boldface.

■ Arguments that are optional are surrounded by square brackets ([ ]).

■ The various As statements (for example, As Double) tell you the data type of each argument. I'll explain data types in the next chapter but, for now, think of them as defining what kind of data is associated with each argument (text, numeric, and so on).

As usual, IntelliSense also enables you to display this information by hand by choosing Edit, Parameter Info (or pressing Ctrl+Shift+I).

Figure 2.13

The Parameter Info feature shows you the defined arguments for the current function or statement.

Figure 2.13

The Parameter Info feature shows you the defined arguments for the current function or statement.

Complete Word

The last of the IntelliSense features that I'll discuss is Complete Word. You use this feature to get VBA to complete a keyword that you've started typing, and thus save some wear-and-tear on your typing fingers. To use Complete Word, type in the first few letters of a keyword and then choose Edit, Complete Word (or press Ctrl+Space).

If the letters you typed are enough to define a unique keyword, IntelliSense fills in the rest of the word. For example, if you type appl and run Complete Word, IntelliSense changes your typing to Application. However, if there are multiple keywords that begin with the letters you typed, IntelliSense displays a pop-up menu that you can use to select the word you want.

0 0

Post a comment