Transcript Class_12_Excel_CA202.ppt

CA202
Spreadsheet Application
Automating Repetitive
Tasks with Macros
Lecture # 12
Dammam Community College
1
Objectives
In this chapter you will learn to:
✔ Open and run macros.
✔ Create and modify macros.
✔ Create toolbars and menus to hold
macros.
✔ Run a macro when a workbook is opened.
2
Introducing Macros
• Some sequences involve many steps and
take time to accomplish by hand.
• Rather than doing by hand every time, you
can create a macro, to do repeated things.
• Alt+F8 to bring Macro dialog box or click
Tools  Macro  Macros…
3
Introducing Macros
• If you have trouble
running macros, you
can click Tools 
Macro  Security…
• Click on Medium
option button and
click OK
4
To See How Macro Works
• To see how the macro
works, you can open
the Macro dialog box,
click the name of the
macro you want to
examine, and then
click Step Into.
5
To See How Macro Works
• To execute an
instruction, you press
F8. The highlight
moves to the next
instruction, and your
worksheet changes to
reflect the action
ExerciseMacrosView
6
Creating and Modifying Macros
• Once you have planned
your process, you can
create a macro by
Clicking:
– Tools  Macro Record
New Macro.
• Type the name of macro
• click OK.
• The Stop Recording
toolbar appears.
ExerciseRecordMacros
7
Creating a Toolbar to Hold Macros
• Run a macro from
toolbar makes your
worksheets much
easier to use,
especially for people
with less experience.
• To create a toolbar
Click Tools 
Customize.
8
Creating a Toolbar to Hold Macros
• Once you have created
the new toolbar, you can
assign your macros to it.
• Click Commands tab
– Two items will appear in
the Commands list:
• Custom Menu Item.
• Custom Button.
– To add a button to your
new toolbar, drag the
Custom Button item to the
toolbar.
9
Creating a Toolbar to Hold Macros
• Right Click on toolbar
and assign the Macro
10
Creating a Toolbar to Hold Macros
• To Delete a toolbar click
Tool  Customize
• Click on Toolbar tab
• Select the Toolbar
• Click on Delete
• Click OK to confirm
deletion
11
Creating a Toolbar to Hold Macros
• You can change the
image.
• Right Click on Image
• Select Change Image
Button
12
Creating a Menu to Hold Macros
• Click Tool 
Customize
• Click Command 
New Menu
• Drag New Menu to
desired spot
• Right click and
change the Name of
menu
ExerciseCustomMenu
13
Running a Macro When a
Workbook Is Opened
• You can run your macro by hand, or from
a toolbar button, or a menu.
• You can run a macro automatically even
when you are opening a work book.
• Whenever Excel finds a macro with a
name starting with Auto_ (Auto followed by
an underscore), it runs the macro when
the workbook to which it is attached is
opened.
ExerciseRunOnOpen
14
Chapter 12 Key Points
•
•
•
•
•
•
Macros are handy tools you can use to perform repetitive
tasks quickly, such as inserting blocks of text.
You don’t have to be a programmer to use macros; you can
record your actions and have Excel save them as a macro.
Always keep control over whether you want to enable
macros in a particular workbook by setting your security
level to Medium or High.
If you’re curious about what a macro looks like, you can
display it in the Visual Basic Editor. If you know a little VBA,
or if you just want to experiment, feel free to modify the
macro code and see what happens.
You can create toolbar buttons or menu items that, when
clicked, will run a macro.
Starting a macro name with Auto_ tells Excel to run the
macro when it opens the workbook to which the macro is
attached.
15