Transcript Excel Lesson 14

Excel Lesson 14
Creating and Using Macros
Microsoft Office 2010
Advanced
1
Cable / Morrison
Objectives

Excel Lesson 14

Understand macros.
Review macro security settings.
Record a macro.
Save a macro as a macro-enabled
workbook.
Run a macro.

2
Cable / Morrison


Microsoft Office 2010 Advanced
Objectives (continued)

Excel Lesson 14

Edit a macro.
Create a button.
Align and format a button.
Open a macro-enabled workbook.

3
Cable / Morrison

Microsoft Office 2010 Advanced
Vocabulary

Excel Lesson 14

button
code
macro
virus

4
Cable / Morrison

Microsoft Office 2010 Advanced
Introduction
Excel Lesson 14

5

A macro automates a common, repetitive
task you perform in Excel.
In this lesson, students will learn how to:
–
–
–
–
Create macros
Run a macro
Make changes to a macro
Add a macro to a button
Cable / Morrison
Microsoft Office 2010 Advanced
Understanding Macros
Excel Lesson 14

6


A macro records a series of steps that you do
frequently, such as formatting text.
Visual Basic for Applications (or VBA) is the
program used to create macros.
Code refers to the macro actions formatted
in easy-to-read sentences.
Cable / Morrison
Microsoft Office 2010 Advanced
Reviewing Macro Security Settings

Excel Lesson 14

7
Macros are susceptible to a virus attack.
A virus is a computer program that is
designed to reproduce by copying itself and
attaching to other programs in a computer.
–

Can cause extreme damage to data
You can set one of four macro security levels
in Excel.
Cable / Morrison
Microsoft Office 2010 Advanced
Reviewing Macro Security Settings
(continued)
Macro security level options
Excel Lesson 14

8
Cable / Morrison
Microsoft Office 2010 Advanced
Reviewing Macro Security Settings
(continued)
Trust Center
dialog box
Excel Lesson 14

9
Cable / Morrison
Microsoft Office 2010 Advanced
Recording a Macro

To begin recording a macro:
Excel Lesson 14
–
10
–
–
Click the Record Macro button in the Code group
on the Developer tab.
Fill out the options in the Record Macro dialog
box.
Click OK to close the dialog box; the macro will
start recording.
Cable / Morrison
Microsoft Office 2010 Advanced
Recording a Macro (continued)
Record Macro dialog box
Excel Lesson 14

11
Cable / Morrison
Microsoft Office 2010 Advanced
Saving a Workbook as a MacroEnabled Workbook
Excel Lesson 14

12


The file extension .xlsm is used for a macroenabled workbook.
A macro-enabled workbook lets you save a
macro in the workbook and run the macro.
You can save an ordinary Excel workbook as
a macro-enabled workbook using the Save
As command.
Cable / Morrison
Microsoft Office 2010 Advanced
Saving a Workbook as a MacroEnabled Workbook (continued)
Save As
dialog box
Excel Lesson 14

13
Cable / Morrison
Microsoft Office 2010 Advanced
Running a Macro

Excel Lesson 14

14
After a macro is created, it is ready to use.
Excel provides different ways to run a macro:
–
–
Use a shortcut key combination if one was
created in the Record Macro dialog box.
On the Developer tab in the Code group, select
the Macros button.


The Macro dialog box appears.
Click the macro you want and click the Run button.
Cable / Morrison
Microsoft Office 2010 Advanced
Editing a Macro

Excel Lesson 14

15


Macros are easy to edit.
To edit a macro, you need to make changes
to the VBA code.
The code word Sub indicates the start of the
macro.
The code End Sub signals the end of the
macro.
Cable / Morrison
Microsoft Office 2010 Advanced
Editing a Macro (continued)
Example of a macro in VBA code
Excel Lesson 14

16
Cable / Morrison
Microsoft Office 2010 Advanced
Editing a Macro (continued)
Excel Lesson 14

17
Making edits to
the Formats
macro
Cable / Morrison
Microsoft Office 2010 Advanced
Creating a Button

A button is called a control.
Excel Lesson 14
–
18


Controls the actions that are assigned to it
When you create a button, you are asked to
assign a macro to it.
When you click the button, the macro will run.
Cable / Morrison
Microsoft Office 2010 Advanced
Creating a Button (continued)
Worksheet with
both buttons added
Excel Lesson 14

19
Cable / Morrison
Microsoft Office 2010 Advanced
Aligning and Formatting Buttons
Excel Lesson 14

20
To select a button with an assigned macro,
right-click the button.
–

A shortcut menu appears.
With more than one button selected, the
Format tab appears on the Ribbon.
–
Format tab has options to make changes to the
buttons, such as aligning buttons.
Cable / Morrison
Microsoft Office 2010 Advanced
Aligning and Formatting Buttons
(continued)
Alignment options
Excel Lesson 14

21
Cable / Morrison
Microsoft Office 2010 Advanced
Aligning and Formatting Buttons
(continued)
Excel Lesson 14

You can right-click a
button and choose
Format Control from
the shortcut menu.
Format Control dialog box
22
Cable / Morrison
Microsoft Office 2010 Advanced
Opening a Macro-Enabled
Workbook
Excel Lesson 14

23
When a workbook with macros is opened, a
security warning appears on the Message
Bar.
–

The warning is displayed whenever the Disable
all macros with notification option is chosen.
To enable the macros, click the Enable
Content button on the Message Bar.
Cable / Morrison
Microsoft Office 2010 Advanced
Opening a Macro-Enabled
Workbook (continued)
Message Bar with security warning
Excel Lesson 14

24
Cable / Morrison
Microsoft Office 2010 Advanced
Excel Lesson 14
Summary
25
In this lesson, you learned:
 Macros can automate frequently used tasks.
 You can review and change macro security
settings.
 You can record a macro.
 A workbook can be saved as a macroenabled workbook.
Cable / Morrison
Microsoft Office 2010 Advanced
Summary (continued)
Excel Lesson 14

26


After a macro is created, you can run the
macro.
If changes need to be made to a macro, you
can edit the macro.
Buttons are a type of form control that have a
macro assigned to them.
Cable / Morrison
Microsoft Office 2010 Advanced
Summary (continued)

Excel Lesson 14

27
Buttons can be aligned and formatted.
When you open a macro-enabled workbook,
you can enable the workbook contents and
run any macros in the workbook.
Cable / Morrison
Microsoft Office 2010 Advanced