MS-Excel / General Formatting

---

Advantage of VBA IntelliSense

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

List Properties/Methods

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 (.). 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.

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.) For example, type the following in a module:

Application.ActiveWindow.WindowState=

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

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 VBA's 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) VBA's Pmt function. 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 will explain data types in the next tutorial but, for now, think of them as defining what kind of data is associated with each argument (text, numeric, and so on).
• To remove the banner, press Esc.

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

Complete Word

The last of the IntelliSense features that I will discuss is Complete Word. You use this feature to get VBA to complete a keyword that you've started typing, and thus save some wearandtear 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.