About this Course Material
This course material is developed for Microsoft Excel 2010 training and will
teach y o u the subject step-by-step. With comprehensive instructions and
objectives checklists, it provides easy-to-follow hands-on exercises and a
glossary of Excel-specific terms.
When you return to your home or office, you will find this book to be a valuable
resource for reviewing exercises and applying the skills you have learned. Each
lesson concludes with review questions that review whatever you have learnt from
the lesson. Lesson review questions are provided as a study resource only.
They do not guarantee a passing score on any certification exam(s).
Course Prerequisites
This Course Material is designed for participants who possess Intermediate Level
knowledge in Microsoft Excel 2010.
, Acknowledgements and Disclaimer
This course material is a compilation of internal knowledge base and content from various
sources, websites, booklets, documents, etc. Same goes with various screen shots used in it,
and the accompanying exercise files. Most of the content and exercises have been taken from
websites like microsoft.com, excelexperts.com, excel-formulas.com, gcflearnfree.org, and
homeandlearn.co.uk. Yashwantrao Chavan Academy of Development Administration
(YASHADA) duly and thankfully acknowledges these sources.
The aim of this course material is to provide a preliminary understanding on the subjects and
areas covered as part of YASHADA's training programs. While working on Microsoft Excel, this
document should be treated only as a quick reference on the topics, and should not be treated as a
guideline and / or instructions.
The document by no means has any commercial intention and is solely compiled for the
purpose of knowledge sharing. It is meant for free of cost distribution amongst participants of
YASHADA’s training programs.
YASHADA shall have no liability for errors, omissions or inadequacies in the
information contained herein or for interpretation thereof. The reader is solely responsible for the
selection of this material to achieve its intended results.
The content provided herein is subject to change without prior notice.
,C o n t e n t s a t a G l a n c e
1 Unleash the Power of Excel with VBA
2 This Sounds Like BASIC, So Why Doesn’t It Look Familiar?
3 Referring to Ranges
4 User-Defined Functions
5 Looping and Flow Control
6 R1C1-Style Formulas
7 What Is New in Excel 2010 and What Has Changed
8 Create and Manipulate Names in VBA
9 Event Programming
10 Userforms—An Introduction
11 Creating Charts
12 Data Mining with Advanced Filter
13 Using VBA to Create Pivot Tables
14 Excel Power
15 Data Visualizations and Conditional Formatting
16 Reading from and Writing to the Web
17 Dashboarding with Sparklines in Excel 2010
18 Automating Word
19 Arrays
20 Text File Processing
21 Using Access as a Back End to Enhance Multiuser Access to Data
22 Creating Classes, Records, and Collections
23 Advanced Userform Techniques
24 Windows API
25 Handling Errors
26 Customizing the Ribbon to Run Macros
27 Creating Add-Ins
Index
, Contents
1 Unleash the Power of Excel with VBA . ......................................................................................... 7
The Power of Excel .....................................................................................................................................................................7
Barriers to Entry ..............................................................................................................................................................7
The Macro Recorder Doesn’t Work!......................................................................................................................................7
Visual Basic Is Not Like BASIC ..............................................................................................................................................8
Good News: Climbing the Learning Curve Is Easy ................................................................................................................8
Great News: Excel with VBA Is Worth the Effort . .........................................................................................................8
Knowing Your Tools: The Developer Tab ....................................................................................................................................9
Macro Security .........................................................................................................................................................................10
Adding a Trusted Location .................................................................................................................................................10
Using Macro Settings to Enable Macros in Workbooks Outside of Trusted Locations.........................................................11
Using Disable All Macros with Notification ........................................................................................................................12
Overview of Recording, Storing, and Running a Macro............................................................................................................12
Filling Out the Record Macro Dialog ..................................................................................................................................13
Running a Macro......................................................................................................................................................................14
Creating a Macro Button on the Ribbon ............................................................................................................................14
Creating a Macro Button on the Quick Access Toolbar .......................................................................................................15
Assigning a Macro to a Form Control, Text Box, or Shape..................................................................................................16
Using New File Types in Excel 2010 .........................................................................................................................................18
Understanding the VB Editor ...................................................................................................................................................19
VB Editor Settings ..............................................................................................................................................................19
The Project Explorer...........................................................................................................................................................20
The Properties Window .....................................................................................................................................................21
Understanding Shortcomings of the Macro Recorder ..............................................................................................................21
Examining Code in the Programming Window .................................................................................................................23
Running the Macro on Another Day Produces Undesired Results ......................................................................................25
This course material is developed for Microsoft Excel 2010 training and will
teach y o u the subject step-by-step. With comprehensive instructions and
objectives checklists, it provides easy-to-follow hands-on exercises and a
glossary of Excel-specific terms.
When you return to your home or office, you will find this book to be a valuable
resource for reviewing exercises and applying the skills you have learned. Each
lesson concludes with review questions that review whatever you have learnt from
the lesson. Lesson review questions are provided as a study resource only.
They do not guarantee a passing score on any certification exam(s).
Course Prerequisites
This Course Material is designed for participants who possess Intermediate Level
knowledge in Microsoft Excel 2010.
, Acknowledgements and Disclaimer
This course material is a compilation of internal knowledge base and content from various
sources, websites, booklets, documents, etc. Same goes with various screen shots used in it,
and the accompanying exercise files. Most of the content and exercises have been taken from
websites like microsoft.com, excelexperts.com, excel-formulas.com, gcflearnfree.org, and
homeandlearn.co.uk. Yashwantrao Chavan Academy of Development Administration
(YASHADA) duly and thankfully acknowledges these sources.
The aim of this course material is to provide a preliminary understanding on the subjects and
areas covered as part of YASHADA's training programs. While working on Microsoft Excel, this
document should be treated only as a quick reference on the topics, and should not be treated as a
guideline and / or instructions.
The document by no means has any commercial intention and is solely compiled for the
purpose of knowledge sharing. It is meant for free of cost distribution amongst participants of
YASHADA’s training programs.
YASHADA shall have no liability for errors, omissions or inadequacies in the
information contained herein or for interpretation thereof. The reader is solely responsible for the
selection of this material to achieve its intended results.
The content provided herein is subject to change without prior notice.
,C o n t e n t s a t a G l a n c e
1 Unleash the Power of Excel with VBA
2 This Sounds Like BASIC, So Why Doesn’t It Look Familiar?
3 Referring to Ranges
4 User-Defined Functions
5 Looping and Flow Control
6 R1C1-Style Formulas
7 What Is New in Excel 2010 and What Has Changed
8 Create and Manipulate Names in VBA
9 Event Programming
10 Userforms—An Introduction
11 Creating Charts
12 Data Mining with Advanced Filter
13 Using VBA to Create Pivot Tables
14 Excel Power
15 Data Visualizations and Conditional Formatting
16 Reading from and Writing to the Web
17 Dashboarding with Sparklines in Excel 2010
18 Automating Word
19 Arrays
20 Text File Processing
21 Using Access as a Back End to Enhance Multiuser Access to Data
22 Creating Classes, Records, and Collections
23 Advanced Userform Techniques
24 Windows API
25 Handling Errors
26 Customizing the Ribbon to Run Macros
27 Creating Add-Ins
Index
, Contents
1 Unleash the Power of Excel with VBA . ......................................................................................... 7
The Power of Excel .....................................................................................................................................................................7
Barriers to Entry ..............................................................................................................................................................7
The Macro Recorder Doesn’t Work!......................................................................................................................................7
Visual Basic Is Not Like BASIC ..............................................................................................................................................8
Good News: Climbing the Learning Curve Is Easy ................................................................................................................8
Great News: Excel with VBA Is Worth the Effort . .........................................................................................................8
Knowing Your Tools: The Developer Tab ....................................................................................................................................9
Macro Security .........................................................................................................................................................................10
Adding a Trusted Location .................................................................................................................................................10
Using Macro Settings to Enable Macros in Workbooks Outside of Trusted Locations.........................................................11
Using Disable All Macros with Notification ........................................................................................................................12
Overview of Recording, Storing, and Running a Macro............................................................................................................12
Filling Out the Record Macro Dialog ..................................................................................................................................13
Running a Macro......................................................................................................................................................................14
Creating a Macro Button on the Ribbon ............................................................................................................................14
Creating a Macro Button on the Quick Access Toolbar .......................................................................................................15
Assigning a Macro to a Form Control, Text Box, or Shape..................................................................................................16
Using New File Types in Excel 2010 .........................................................................................................................................18
Understanding the VB Editor ...................................................................................................................................................19
VB Editor Settings ..............................................................................................................................................................19
The Project Explorer...........................................................................................................................................................20
The Properties Window .....................................................................................................................................................21
Understanding Shortcomings of the Macro Recorder ..............................................................................................................21
Examining Code in the Programming Window .................................................................................................................23
Running the Macro on Another Day Produces Undesired Results ......................................................................................25
