Dick Kusleika - Excel VBA Programming For Dummies

Auto List Members option: If the Auto List Members option is set, the VBE provides some help when you’re entering your VBA code. It displays a list that would logically complete the statement you’re typing. This bit of magic is sometimes called IntelliSense. It helps you write code more quickly and accurately, making it one of the best features of the VBE. Figure 3-6 shows an example (which will make lots more sense when you start writing VBA code). FIGURE 3-6:An example of Auto List Members.

Auto Quick Info option: If the Auto Quick Info option is set, the VBE displays information about functions and their arguments as you type, which can be very helpful. Figure 3-7 shows this feature in action, telling you about the arguments for the MsgBox function. FIGURE 3-7:Auto Quick Info offers help about the MsgBox function.

Auto Data Tips option: If the Auto Data Tips option is set, the VBE displays the value of the variable over which your cursor is placed when you’re debugging code. When you enter the wonderful world of debugging, as described in Chapter 13, you’ll appreciate this option.

Auto Indent setting: The Auto Indent setting determines whether the VBE automatically indents each new line of code the same as the previous line. Use the Tab key to indent your code, not the spacebar. Also, you can press Shift+Tab to “unindent” a line of code. If you want to indent more than just one line, select all the lines you want to indent. Then press the Tab key. The VBE’s Edit toolbar (which is hidden by default) contains two useful buttons: Indent and Outdent. These buttons let you quickly indent or “unindent” a block of code. Select the code and click one of these buttons to change the block’s indenting.

Tab Width: Determines how wide your tab characters are in the equivalent number of spaces.

Drag-and-Drop Text Editing option: The Drag-and-Drop Text Editing option, when enabled, lets you move or copy and paste text by dragging and dropping with your mouse.

Default to Full Module View option: The Default to Full Module View option sets the default state for new modules. (It doesn’t affect existing modules.) If this option is set, procedures in the code pane appear as a single scrollable list. If this option is turned off, you can see only one procedure at a time.

Procedure Separator option: When the Procedure Separator option is turned on, separator bars appear between procedures in a code pane.

Figure 3-8 shows the Editor Format tab of the Options dialog box. With this tab, you can customize the way the VBE looks.

Code Colors option: The Code Colors option lets you set the text color and background color displayed for various elements of VBA code. This is largely a matter of personal preference.

Font option: The Font option lets you select the font that’s used in your VBA modules. For best results, stick with a fixed-width font such as Courier New. In a fixed-width font, all characters are exactly the same width. This makes your code more readable because the characters are nicely aligned vertically, and you can easily distinguish multiple spaces (which is sometimes useful).

Size setting: The Size setting specifies the point size of the font in the VBA modules. This setting is a matter of personal preference determined by your video display resolution and how many carrots you’ve been eating.

Margin Indicator Bar option: This option controls the display of the vertical margin indicator bar in your modules. You should keep this turned on; otherwise, you won’t be able to see the helpful graphical indicators when you’re debugging your code.

FIGURE 3-8:Change how the VBE looks with the Editor Format tab.

Figure 3-9 shows the options available on the General tab of the Options dialog box. In almost every case, the default settings are just fine.

FIGURE 3-9:The General tab of the Options dialog box.

The most important setting is Error Trapping. It’s considered a best practice not to use the Break on All Errors setting. If you use this setting, your error-handling code won’t work. You can read more about this in Chapter 12.

If you’re really interested in these options, click the Help button for details.

Figure 3-10 shows the Docking tab. These options determine how the various windows in the VBE behave. When a window is docked, it’s fixed in place along one of the edges of the VBE program window. This makes it much easier to identify and locate a particular window. If you turn off all docking, you have a big, confusing mess of windows. Generally, the default settings work fine.

Sometimes, the VBE seems to have a mind of its own when you’re trying to dock a window. If docking doesn’t seem to work correctly, try double-clicking the title bar of the window.

FIGURE 3-10:The Docking tab of the Options dialog box.

Chapter 4

IN THIS CHAPTER

Learning the Excel Object Model and its hierarchy

Understanding how to access properties and methods

Discovering resources to help you code

The purpose of Excel VBA is to manipulate Excel. Some of the code you write will be VBA language constructs — aspects of the language that are common to all programming languages. The rest of the code you write will be specific to Excel. In this chapter, you learn about the Excel Object Model. The object model is the gateway into all the objects that make up Excel. Manipulating these objects is what Excel VBA programming is all about.

The Excel Object Model is computer code that Microsoft wrote that defines how VBA interacts with Excel. In it, Microsoft has defined hundreds of objects whose names you would recognize as elements of the Excel user interface, like Workbook, Worksheet, or PivotTable. An object is nothing more than computer code that defines properties, methods, and events. It is the unique combination of properties, methods, and events that defines an object. Properties and methods are discussed later in this chapter and events are discussed in Chapter 11.

A car is an object. It has properties like color and number of pistons. If you change the color or the number of pistons, you have a different car, but it’s still a car. Cars have methods like accelerate and brake. Excel objects are a little more abstract. For example, a Worksheet is an object. It has properties like Name and Visible that you can change to rename or hide the worksheet. It has methods like Activate that you can invoke to make it the active worksheet. You can change the properties of a Worksheet object to make it the Worksheet you want, but it’s still a Worksheet because it has that unique set of properties and methods.