Excel Macros
ii
Table of Contents
About the Tutorial ............................................................................................................................................ i
Audience ........................................................................................................................................................... i
Prerequisites ..................................................................................................................................................... i
Copyright & Disclaimer ..................................................................................................................................... i
Table of Contents ............................................................................................................................................ ii
1. Excel Macros – Overview .......................................................................................................................... 1
2. Excel Macros – Creation ............................................................................................................................ 4
Recording a Macro .......................................................................................................................................... 4
Running a Macro ............................................................................................................................................. 6
Storing a Macro ............................................................................................................................................... 7
Saving a Macro Enabled File ............................................................................................................................ 8
3. Excel Macros – Macros in a Single Workbook ......................................................................................... 10
Saving Macros in Personal Macro Workbook ................................................................................................ 10
Hiding / Unhiding Personal Macro Workbook ............................................................................................... 13
Adding / Deleting Macros in Personal Macro Workbook .............................................................................. 14
4. Excel Macros – Security ........................................................................................................................... 16
What are Macro Viruses? .............................................................................................................................. 16
Macro Enabled Excel Workbooks .................................................................................................................. 16
Ways of Trusting Macro Enabled Workbook ................................................................................................. 16
Macro Security Settings in Trust Center ........................................................................................................ 17
Macro Settings ............................................................................................................................................... 19
Defining a Trusted Location ........................................................................................................................... 20
Digitally Signed Macros from Reliable Sources ............................................................................................. 21
Using Warning Messages............................................................................................................................... 22
Enabling / Disabling Security Alerts on the Message Bar .............................................................................. 22
5. Excel Macros – Absolute References ....................................................................................................... 25
Absolute References ...................................................................................................................................... 25
Ensuring Absolute References ....................................................................................................................... 27
Recording a Macro ........................................................................................................................................ 28
Running a Macro ........................................................................................................................................... 30
6. Excel Macros – Relative References ........................................................................................................ 31
Relative References ....................................................................................................................................... 31
Using Relative References ............................................................................................................................. 32
Recording a Macro ........................................................................................................................................ 33
Running a Macro ........................................................................................................................................... 35
7. Excel Macros – VBA ................................................................................................................................. 37
Developer Tab on the Ribbon ........................................................................................................................ 37
Developer Commands for Macros ................................................................................................................. 38
VBA Editor ..................................................................................................................................................... 39
Projects Explorer ........................................................................................................................................... 41