Dick Kusleika - Excel VBA Programming For Dummies

Figure 2-6 shows the Macro Settings section of the Trust Center dialog box. To display this dialog box, choose Developer ⇒ Code ⇒ Macro Security.

FIGURE 2-6:The Macro Settings section of the Trust Center dialog box.

By default, Excel uses the Disable All Macros with Notification option. With this setting in effect, if you open a workbook that contains macros (and the file is not digitally “signed” or stored in a trusted location), Excel displays a warning like the one shown in Figure 2-7. If you are certain that the workbook comes from a trusted source, click Enable Macros, and Excel enables the macros.

FIGURE 2-7:Excel’s warning that the file to be opened contains macros.

You see the pop-up box in Figure 2-7 only when the VBE is open. Otherwise, Excel displays an eye-catching Security Warning above the Formula bar, as shown in Figure 2-8. If you know the workbook is safe, click the Enable Content button to enable the macros. To use the workbook without macros, click the X to dismiss the warning.

Excel remembers when you designate a workbook to be safe. So the next time you open the workbook, you won’t see the Security Warning.

FIGURE 2-8:Excel's warning that the workbook just opened contains macros. You see this warning when the VBE isn’t open.

Perhaps the best way to handle macro security is to designate one or more folders as trusted locations. All the workbooks in a trusted location are opened without a macro warning. You designate trusted folders in the Trusted Locations section of the Trust Center dialog box.

If you want to find out what the other macro security settings mean, press F1 while the Macro Settings section of the Trust Center dialog box is in view and review the Help screen that appears.

Part 2

IN THIS PART …

See how to access the important parts of the Visual Basic Editor.

Discover VBA code modules (where you store your VBA code).

Obtain an overview of the Excel object model.

Get a crash course in using the Excel macro recorder.

Chapter 3

IN THIS CHAPTER

Accessing the Visual Basic Editor

Discovering the Visual Basic Editor parts

Knowing what goes into a VBA module

Getting VBA code into a module

Customizing the VBA environment

The Visual Basic Editor (VBE) is the main tool for programming in VBA. It’s where you view and edit recorded code and write code from scratch. In this chapter, you find out how to work with the VBE, and you get down to the nitty-gritty of writing some VBA code.

The Visual Basic Editor (often referred to as the VBE) is a separate application where you write and edit your VBA macros. Beginning with Excel 2013, when you open multiple workbooks, each opens in a separate window. However, the VBE only ever has one window, and from this window you can write, edit, and view macros for any open workbook.

You can’t run the VBE separately; Excel must be running for the VBE to run.

The quickest way to activate the VBE is to press Alt+F11 from an Excel window. To return to Excel, press Alt+F11 again. Or you can simply click the Close button on the VBE’s title bar. When the VBE window closes, Excel becomes the active window.

You can also activate the VBE by choosing Developer ⇒ Code ⇒ Visual Basic. If you don’t have a Developer tab at the top of your Excel window, refer to Chapter 2, which shows how to get that handy Developer tab to show up.

The VBE contains several windows and is highly customizable. You can hide windows, rearrange windows, dock windows, and more. Figure 3-1 shows the VBE program, with some key parts identified. Your VBE program window probably won’t look exactly like what is shown in Figure 3-1, but from the figure, you can at least get a feel for the basic appearance of the VBE.

FIGURE 3-1:The VBE is your customizable friend.

The VBE menu bar contains commands you can use to do things with the various components in the VBE. Many of the menu commands have shortcut keys associated with them.

The VBE also features shortcut menus. You can right-click virtually anything in the VBE and get a shortcut menu of common commands.

The Standard toolbar, which is directly below the menu bar by default (refer to Figure 3-1), is one of the four VBE toolbars. You can customize the toolbars, move them around, display other toolbars, and more. To do so, choose View ⇒ Toolbars, and customize as you see fit. Most people just leave them as they are.

The Project Explorer displays a tree diagram that shows every workbook currently open in Excel (including add-ins and hidden workbooks). Double-click items to expand or contract them within the outline. See the upcoming “ Working with the Project Explorer” section for more detail.

If the Project Explorer is not visible, press Ctrl+R or choose View ⇒ Project Explorer. To hide the Project Explorer, click the Close button on its title bar. Or right-click anywhere in the Project Explorer and choose Hide from the shortcut menu.

A Code pane is where you put your VBA code. Every object in a project has an associated Code pane. To view an object’s Code pane, double-click the object in the Project Explorer. For example, to view the Code pane for the Sheet1 object in Book1, double-click Sheet1 in the VBAProject for Book1. Unless you’ve added some VBA code, the Code pane is empty.