Read more
Master VBA automation quickly and easily to get more out of Excel
Excel VBA 24-Hour Trainer, 2nd Edition is the quick-start guide to getting more out of Excel, using Visual Basic for Applications. This unique book/video package has been updated with fifteen new advanced video lessons, providing a total of eleven hours of video training and 45 total lessons to teach you the basics and beyond. This self-paced tutorial explains Excel VBA from the ground up, demonstrating with each advancing lesson how you can increase your productivity. Clear, concise, step-by-step instructions are combined with illustrations, code examples, and downloadable workbooks to give you a practical, in-depth learning experience and results that apply to real-world scenarios.
This is your comprehensive guide to becoming a true Excel power user, with multimedia instruction and plenty of hands-on practice.
* Program Excel's newest chart and pivot table object models
* Manipulate the user interface to customize the look and feel of a project
* Utilize message boxes, input boxes, and loops to yield customized logical results
* Interact with and manipulate Word, Access, PowerPoint, and Outlook from Excel
If you're ready to get more out of this incredibly functional program, Excel VBA 24-Hour Trainer, 2nd Edition provides the expert instruction and fast, hands-on learning you need.
List of contents
INTRODUCTION xxvii
PART I: UNDERSTANDING THE BASICS
LESSON 1: INTRODUCING VBA 3
What Is VBA? 3
A Brief History of VBA 4
What VBA Can Do for You 5
Liabilities of VBA 8
Try It 9
LESSON 2: GETTING STARTED WITH MACROS 11
Composing Your First Macro 11
Running a Macro 21
Try It 22
LESSON 3: INTRODUCING THE VISUAL BASIC EDITOR 25
What Is the VBE? 25
Try It 30
LESSON 4: WORKING IN THE VBE 33
Toolbars in the VBE 33
Macros and Modules 33
Understanding the Code 36
Editing a Macro with Comments and Improvements to the Code 37
Try It 44
PART II: DIVING DEEPER INTO VBA
LESSON 5: OBJECT-ORIENTED PROGRAMMING: AN OVERVIEW 49
What "Object-Oriented Programming" Means 49
The Object Model 50
Try It 53
LESSON 6: VARIABLES, DATA TYPES, AND CONSTANTS 55
What Is a Variable? 55
Assigning Values to Variables 56
Why You Need Variables 56
Data Types 57
Forcing Variable Declaration 59
Understanding a Variable's Scope 61
Try It 64
LESSON 7: UNDERSTANDING OBJECTS AND COLLECTIONS 67
Workbooks 67
Cells and Ranges 69
Try It 71
LESSON 8: WORKING WITH RANGES 75
Working with Noncontiguously Populated Ranges 77
Try It 82
LESSON 9: MAKING DECISIONS WITH VBA 85
Understanding Logical Operators 85
Choosing Between This or That 88
Getting Users to Make Decisions 92
Try It 94
PART III: BEYOND THE MACRO RECORDER: WRITING YOUR OWN CODE
LESSON 10: REPEATING ACTIONS WITH LOOPS 101
What Is a Loop? 101
Nesting Loops 110
Try It 111
LESSON 11: PROGRAMMING FORMULAS 113
Understanding A1 and R1C1 References 113
Programming Your Formula Solutions with VBA 118
Try It 124
LESSON 12: WORKING WITH ARRAYS 127
What Is an Array? 127
The Option Base Statement 130
Boundaries in Arrays 132
Declaring Arrays with Fixed Elements 132
Declaring Dynamic Arrays with ReDim and Preserve 133
Try It 134
LESSON 13: AUTOMATING PROCEDURES WITH WORKSHEET EVENTS 137
What Is an Event? 137
Worksheet Events: An Overview 138
Examples of Common Worksheet Events 141
Try It 144
LESSON 14: AUTOMATING PROCEDURES WITH WORKBOOK EVENTS 149
Workbook Events: An Overview 149
Examples of Common Workbook Events 153
Try It 158
LESSON 15: HANDLING DUPLICATE ITEMS AND RECORDS 161
Deleting Rows Containing Duplicate Entries 161
Working with Duplicate Data 167
Try It 173
LESSON 16: USING EMBEDDED CONTROLS 181
Working with Form Controls and
ActiveX Controls 181
Try It 191
LESSON 17: PROGRAMMING CHARTS 199
Try It 208
LESSON 18: PROGRAMMING PIVOTTABLES AND PIVOTCHARTS 213
Creating a PivotTable Report 213
Understanding PivotCaches 226
Manipulating PivotFields in VBA 230
Manipulating PivotItems with VBA 231
Creating a PivotTables Collection 231
Try It 232
LESSON 19: USER-DEFINED FUNCTIONS 237
What Is a User-Defi ned Function? 237
UDF Examples That Solve Common Tasks 239
Volatile Functions 243
Try It 248
LESSON 20: DEBUGGING YOUR CODE 251
