Excel 2007 Unit I

These Slides are shared for Education purposes only,

1. Microsoft Excel 2007 Microsoft Excel 2007
- Illustrated- Illustrated
Automating Worksheet Automating Worksheet
TasksTasks

2. 2Microsoft Office Excel 2007 - Illustrated
•Plan a macroPlan a macro
•Enable a macroEnable a macro
•Record a macroRecord a macro
•Run a macroRun a macro
•Edit a macroEdit a macro
ObjectivesObjectives

3. 3Microsoft Office Excel 2007 - Illustrated
•Use shortcut keys with macrosUse shortcut keys with macros
•Use the Personal Macro WorkbookUse the Personal Macro Workbook
•Assign a macro to a buttonAssign a macro to a button
ObjectivesObjectives

4. 4Microsoft Office Excel 2007 - Illustrated
Unit IntroductionUnit Introduction
•A A macromacro is a set of instructions that is a set of instructions that
performs tasks in the order you performs tasks in the order you
specifyspecify
•Create macros to automate frequently Create macros to automate frequently
performed tasks that require a series of performed tasks that require a series of
stepssteps

5. 5Microsoft Office Excel 2007 - Illustrated
Plan a MacroPlan a Macro
•Create macros for tasks that you Create macros for tasks that you
perform on a regular basisperform on a regular basis
•To create a macro, you record the To create a macro, you record the
series of actions in a special series of actions in a special
programming languageprogramming language
•The sequence of actions is important, The sequence of actions is important,
so a macro needs to be carefully so a macro needs to be carefully
plannedplanned

6. 6Microsoft Office Excel 2007 - Illustrated
Plan a Macro (cont.)Plan a Macro (cont.)
•Macro planning guidelinesMacro planning guidelines
•Assign the macro a nameAssign the macro a name
•Write out the steps the macro will Write out the steps the macro will
performperform
•Decide how you will perform the actionsDecide how you will perform the actions
•Practice the steps you want Excel to Practice the steps you want Excel to
record and write them downrecord and write them down
•Decide where to locate the description Decide where to locate the description
of the macro and the macro itselfof the macro and the macro itself

7. 7Microsoft Office Excel 2007 - Illustrated
Enable a Macro (cont.)Enable a Macro (cont.)
•Macro and virusesMacro and viruses
•Macros can contain viruses which can Macros can contain viruses which can
damage your computerdamage your computer
•Always disable macros if you are not Always disable macros if you are not
working with them.working with them.

8. 8Microsoft Office Excel 2007 - Illustrated
Recording a MacroRecording a Macro
•Use the Excel Macro Recorder to Use the Excel Macro Recorder to
create a macrocreate a macro
•Click the Record Macro button Click the Record Macro button
•Name the macroName the macro
•Enter the keystrokes and select the Enter the keystrokes and select the
commands you wantcommands you want
•Click the Stop Recording buttonClick the Stop Recording button
•Each action you record is translated Each action you record is translated
into program codeinto program code

9. 9Microsoft Office Excel 2007 - Illustrated
Recording a Macro (cont.)Recording a Macro (cont.)
Enter macro
name here

10. 10Microsoft Office Excel 2007 - Illustrated
Running a MacroRunning a Macro
•Once you record a macro, you should test Once you record a macro, you should test
it to make sure it runs properlyit to make sure it runs properly
•Select the macro in the Macro dialog box, then Select the macro in the Macro dialog box, then
click Run to test itclick Run to test it
Listed macros
appear here

11. 11Microsoft Office Excel 2007 - Illustrated
Editing a MacroEditing a Macro
•The program instructions of a macro, The program instructions of a macro,
called called program codeprogram code, are , are
automatically recorded in the Visual automatically recorded in the Visual
Basic for Applications (VBA) Basic for Applications (VBA)
programming languageprogramming language
•Each macro is stored as a Each macro is stored as a modulemodule, or , or
program code container attached to the program code container attached to the
workbookworkbook
•Edit a macro with the Visual Basic Edit a macro with the Visual Basic
EditorEditor

12. 12Microsoft Office Excel 2007 - Illustrated
Editing a Macro (cont.)Editing a Macro (cont.)
Comment
Code window
Project
Explorer with
open module
Properties
window
Macro program
code

13. 13Microsoft Office Excel 2007 - Illustrated
Editing a Macro (cont.)Editing a Macro (cont.)
•Adding comments to Visual Basic Adding comments to Visual Basic
codecode
•You can explain macro code by adding You can explain macro code by adding
commentscomments
•Comments are explanatory text added Comments are explanatory text added
to the lines of codeto the lines of code
•A comment must be preceded by an A comment must be preceded by an
apostropheapostrophe

14. 14Microsoft Office Excel 2007 - Illustrated
Using Shortcut Keys Using Shortcut Keys
with Macroswith Macros
•Assign a shortcut key combination to Assign a shortcut key combination to
run a macrorun a macro
•Reduces the number of steps it takes to Reduces the number of steps it takes to
run a macrorun a macro
•Assign a shortcut key combination in Assign a shortcut key combination in
the Record Macro dialog boxthe Record Macro dialog box

15. 15Microsoft Office Excel 2007 - Illustrated
Using Shortcut Keys with Using Shortcut Keys with
Macros (cont.)Macros (cont.)
Shortcut to
run macro

16. 16Microsoft Office Excel 2007 - Illustrated
Using the Personal Using the Personal
Macro WorkbookMacro Workbook
•Store commonly used macros in a Store commonly used macros in a
Personal Macro WorkbookPersonal Macro Workbook
•The Personal Macro Workbook is The Personal Macro Workbook is
always available no matter which always available no matter which
workbook is openworkbook is open
•Created the first time you store a macroCreated the first time you store a macro

17. 17Microsoft Office Excel 2007 - Illustrated
Using the Personal Using the Personal
Macro Workbook (cont.)Macro Workbook (cont.)
Click to store
in new blank
workbook
Stores macro in
active workbook
Click to store in Personal
Macro Workbook

18. 18Microsoft Office Excel 2007 - Illustrated
Using the Personal Using the Personal
Macro Workbook (cont.)Macro Workbook (cont.)
•Working with the Personal Macro Working with the Personal Macro
WorkbookWorkbook
•By default the Personal Macro By default the Personal Macro
Workbook is hidden as a precautionary Workbook is hidden as a precautionary
measure so you don’t accidentally measure so you don’t accidentally
delete anything from itdelete anything from it

19. 19Microsoft Office Excel 2007 - Illustrated
Assign a Macro to a buttonAssign a Macro to a button
•You can run a macro by assigning it You can run a macro by assigning it
to a button on your worksheet. to a button on your worksheet.
•When you click the button the macro When you click the button the macro
will run.will run.

20. 20Microsoft Office Excel 2007 - Illustrated
Assigning a Macro to a button Assigning a Macro to a button
(cont.)(cont.)
Add text to
label the
button

21. 21Microsoft Office Excel 2007 - Illustrated
SummarySummary
•Use Excel Macro Recorder to create Use Excel Macro Recorder to create
a macroa macro
•Run a macro to test itRun a macro to test it
•Use the Visual Basic Editor to edit a Use the Visual Basic Editor to edit a
macromacro
•Use shortcut keys with macrosUse shortcut keys with macros
•Use the Personal Macro WorkbookUse the Personal Macro Workbook