Michael Alexander - Excel 2019 Power Programming with VBA

If you store macros in the Personal Macro Workbook, you don't have to remember to open the Personal Macro Workbook when you load a workbook that uses macros. When you want to exit, Excel asks whether you want to save changes to the Personal Macro Workbook.

The Personal Macro Workbook normally is in a hidden window to keep it out of the way.

When you create macros, you may want to have a clear and easy way to run each macro. A basic button can provide a simple but effective user interface.

As luck would have it, Excel offers a set of form controls designed specifically for creating user interfaces directly on spreadsheets. There are several different types of form controls, from buttons (the most commonly used control) to scrollbars.

The idea behind using a form control is simple. You place a form control on a spreadsheet and then assign a macro to it—that is, a macro you've already recorded. When a macro is assigned to the control, that macro is executed, or played, when the control is clicked.

Take a moment to create a button for the AddTotalRelativemacro you created earlier. Here's how:

1 Click the Insert button on the Developer tab (see Figure 2.7). FIGURE 2.7 You can find the form controls on the Developer tab.

2 Select the Button control from the drop-down list that appears.

3 Click the location where you want to place your button.When you drop the button control onto your spreadsheet, the Assign Macro dialog box, as shown in Figure 2.8, activates and asks you to assign a macro to this button. FIGURE 2.8 Assign a macro to the newly added button.

4 Select the macro that you want to assign to the button and then click OK.

At this point, you have a button that runs your macro when you click it. Keep in mind that all the controls in the Form Controls group (shown in Figure 2.7) work in the same way as the command button in that you right-click and choose Assign Macro to specify a macro to trigger with the control.

Notice the form controls and ActiveX controls in Figure 2.7. Although they look similar, they're quite different. Form controls are designed specifically for use on a spreadsheet, and ActiveX controls are typically used on Excel user forms. As a general rule, you should always use form controls when working on a spreadsheet. Why? Form controls need less overhead, so they perform better, and configuring form controls is far easier than configuring their ActiveX counterparts.

You can also assign a macro to a button in Excel's Quick Access toolbar. The Quick Access toolbar sits either above or below the Ribbon. You can add a custom button that will run your macro by following these steps:

1 Right-click your Quick Access toolbar and select Customize Quick Access Toolbar. This will open the dialog box illustrated in Figure 2.9. FIGURE 2.9 Adding a macro to the Quick Access toolbar

2 Select Macros from the Choose Commands From drop-down list on the left.

3 Select the macro that you want to add and click the Add button.

4 Click the Modify button to choose an icon for your macro and provide a friendly display name.

5 Click the OK button.

The Visual Basic Editor is a separate application that runs when you open Excel. To see this hidden VBE environment, you'll need to activate it. The quickest way to activate the VBE is to press Alt+F11 when Excel is active. To return to Excel, press Alt+F11 again.

You can also activate the VBE by using the Visual Basic command on Excel's Developer tab.

Figure 2.10shows the VBE program with some of the key parts identified. Chances are that your VBE program window won't look exactly like what you see in Figure 2.10. The VBE contains several windows and is highly customizable. You can hide windows, rearrange windows, dock windows, and so on.

FIGURE 2.10 The VBE with significant elements identified

The VBE menu bar works just like every other menu bar you've encountered. It contains commands that you use to do things with the various components in the VBE. You will also find that 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 under the menu bar by default, is one of four VBE toolbars available. You can customize the toolbars, move them around, display other toolbars, and so on. If you're so inclined, use the View ➪ Toolbars command to work with VBE toolbars. Most people just leave them as they are.

The Project window 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. You'll explore this window in more detail in the “Working with the Project Window” section later in this chapter.

If the Project window is not visible, press Ctrl+R or use the View ➪ Project Explorer command. To hide the Project window, click the Close button in its title bar. Alternatively, right-click anywhere in the Project window and select Hide from the shortcut menu.

A Code window contains VBA code. Every object in a project has an associated Code window. To view an object's Code window, double-click the object in the Project window. For example, to view the Code window for the Sheet1object, double-click Sheet1in the Project window. Unless you've added some VBA code, the Code window will be empty.

You find out more about Code windows later in this chapter's “Working with a Code Window” section.

The Immediate window may or may not be visible. If it isn't visible, press Ctrl+G or use the View ➪ Immediate Window command. To close the Immediate window, click the Close button in its title bar (or right-click anywhere in the Immediate window and select Hide from the shortcut menu).

The Immediate window is most useful for executing individual VBA statements and for debugging your code. If you're just starting out with VBA, this window won't be all that useful, so feel free to hide it and free up some screen space for other things.

When you're working in the VBE, each Excel workbook and add-in that's open is a project. You can think of a project as a collection of objects arranged as an outline. You can expand a project by clicking the plus sign ( +) at the left of the project's name in the Project window. Contract a project by clicking the minus sign ( -) to the left of a project's name. Or, you can double-click the items to expand and contract them.

Figure 2.11shows a Project window with two projects listed: a workbook named Book1and a workbook named Book2.