Excel 2019 Power Programming with VBA. Michael Alexander

If the Require Variable Declaration option is set, the VBE inserts the following statement at the beginning of each new VBA module you insert: Option Explicit. Changing this setting affects only new modules, not existing modules. If this statement appears in your module, you must explicitly define each variable you use. Using a Dim statement is one way to declare variables.

       The 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 is one of the best features of the VBE.

       The Auto Quick Info option If the Auto Quick Info option is selected, the VBE displays information about functions and their arguments as you type. This is similar to the way Excel lists the arguments for a function as you start typing a new formula.

       The Auto Data Tips option If the Auto Data Tips option is set, VBE displays the value of the variable over which your cursor is placed when you're debugging code. This is turned on by default and often quite useful. There is no reason to turn this option off.

       The Auto Indent setting The Auto Indent setting determines whether the VBE automatically indents each new line of code the same as the previous line. Most Excel developers are keen on using indentations in their code, so this option is typically kept on.

       The Tab Width setting The Tab Width setting is used to increase or decrease the number of spaces used when indenting code or pressing the Tab key on the keyboard.

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

       The 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 set, procedures in the Code window appear as a single scrollable list. If this option is turned off, you can see only one procedure at a time.

       The Procedure Separator option When the Procedure Separator option is turned on, separator bars appear at the end of each procedure in a Code window. Separator bars provide a nice visual line between procedures, making it easy to see where one piece of code ends and where another starts.

      The Editor Format tab

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

FIGURE 2.14 Change the VBE's looks with the Editor Format tab.

       The 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. Most Excel developers stick with the default colors, but if you like to change things up, you can play around with these settings.

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

       The 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 good your eyesight is.

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

      The General tab

Figure 2.15 shows the options available on the General tab in the Options dialog box. In almost every case, the default settings are just fine. The most important setting on the General tab is Error Trapping. If you are just starting your Excel macro writing career, it's best to leave the Error Trapping set to Break on Unhandled Errors. This ensures that Excel warns you of errors as you type your code—as opposed to waiting until you try to run your macro.

FIGURE 2.15 The General tab of the Options dialog box

      The Docking tab

Figure 2.16 shows the Docking tab. These options determine how the various windows in the VBE behave. When a window is docked, it is 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.

FIGURE 2.16 The Docking tab of the Options dialog box

VBA Fundamentals

      VBA is an object-oriented programming language. The basic concept of object-oriented programming is that a software application (Excel in this case) consists of various individual objects. An Excel application contains workbooks, worksheets, cells, charts, pivot tables, shapes, and the list goes on. Each object has its own set of attributes, which are called properties, and its own set of actions, called methods.

      You can think of this concept just as you would of the objects you encounter every day, such as your computer or your car. Each of those objects has attributes, such as height, weight, and color. They also have their own distinct actions. For instance, your computer boots up, and your program starts.

      VBA objects also have their identifiable attributes and actions. A workbook, for example, is an object with attributes (properties), such as its name, the number of worksheets it contains, and the date it was created. A workbook object also has actions (methods) such as Open, Close, and Save.

      In Excel, you deal with objects such as workbooks, worksheets, and ranges on a daily basis. You likely think of each of these “objects” as all part of Excel, not really separating them in your mind. However, Excel thinks about these internally as all part of a hierarchical model called the Excel Object Model. The Excel Object Model is a clearly defined set of objects that are structured according to the relationships among them.

      Understanding objects

      In the real world, you can describe everything you see as an object. When you look at your house, it is an object. Your house has rooms; those rooms are also separate objects. Those rooms may have closets. Those closets are likewise objects. As you think about your house, the rooms, and the closets, you may see a hierarchical relationship among them. Excel works in the same way.

      In Excel, the Application object is the all-encompassing object—similar to your house. Inside the Application object, Excel has a workbook. Inside a workbook is