Excel 2019 Bible

Chapter 42: Introducing Visual Basic for Applications

42

FIGURE 42.12

Adding a button to a worksheet so that it can be used to execute a macro

After you perform these steps, clicking the button executes the assigned macro.

Adding a macro to your Quick Access toolbar

You can also assign a macro to a button on your Quick Access toolbar:

1. Right-click the Quick Access toolbar, and choose Customize Quick Access
   Toolbar from the shortcut menu. The Quick Access Toolbar tab of the Excel
   Options dialog box appears.


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


3. At the top of the list on the right, choose For All Documents, or For xxx (where
   xxx is the active workbook’s name). This step determines whether the macro will
   be available for all workbooks or just the workbook that contains the macro.


4. Select your macro and click the Add button.


5. To change the icon or displayed text, click the Modify button, make a selec-
   tion, and click OK.


6. Click OK to close the Excel Options dialog box.

After performing these steps, your Quick Access toolbar will display a button that executes

your macro.

Writing VBA code

As demonstrated in the preceding sections, the easiest way to create a simple macro is to

record your actions. To develop more complex macros, however, you have to enter the VBA

code manually—in other words, write a program. To save time (and assist in the learning

process), you can often combine recording with manual code entry.

Before you can begin writing VBA code, you must have a good understanding of such topics

as objects, properties, and methods. Also, you must be familiar with common programming

constructs, such as looping and If-Then statements.