Unleashing the power of Excel with VBA

Knowing your tools: The Developer tab

Let’s start with a basic overview of the tools needed to use VBA. By default, Microsoft hides the VBA tools. You need to complete the following steps to change a setting to access the Developer tab:

  1. Right-click the ribbon and choose Customize The Ribbon.

  2. In the right list box, select the Developer check box, which is the tenth item.

  3. Click OK to return to Excel.

Excel displays the Developer tab, as shown in Figure 1-1.


FIGURE 1-1 The Developer tab provides an interface for running and recording macros.

The Code group on the Developer tab contains the icons used for recording and playing back VBA macros, as listed here:

  • Visual Basic—Opens the Visual Basic Editor.

  • Macros—Displays the Macro dialog box, where you can choose to run or edit a macro from the list of macros.

  • Record Macro—Begins the process of recording a macro.

  • Use Relative References—Toggles between using relative or absolute recording. With relative recording, Excel records that you move down three cells. With absolute recording, Excel records that you selected cell A4.

  • Macro Security—Accesses the Trust Center, where you can choose to allow or disallow macros to run on this computer.

The Add-ins group provides icons for managing regular add-ins and COM add-ins.

The Controls group of the Developer tab contains an Insert menu where you can access a variety of programming controls that can be placed on the worksheet. See “Assigning a macro to a form control, text box, or shape,” later in this chapter. Other icons in this group enable you to work with the on-sheet controls. The Run Dialog button enables you to display a custom dialog box or userform that you designed in VBA. For more on userforms, see Chapter 10, “Userforms: An introduction.”

The XML group of the Developer tab contains tools for importing and exporting XML documents.

The Modify group enables you to specify whether the Document Panel is always displayed for new documents. Users can enter keywords and a document description in the Document Panel. If you have SharePoint and InfoPath, you can define custom fields to appear in the Document Panel.