Excel VBA 24-Hour Trainer

by Urtis, Tom

• ISBN13: 9780470890691
• ISBN10: 047089069X
• Edition: DVD
• Format: Paperback
• Copyright: 2011-05-03
• Publisher: Wrox

Summary

This unique book-and-DVD package helps even non-programmers extend Excel with VBAVirtually every manual task in Excel can be automated with VBA, which increases your productivity and saves enormous amounts of time. This unique book-and-DVD package prepares you to get more out of Excel by using Visual Basic for Applications (VBA) to automate many routine or labor-intensive Excel tasks. Microsoft Excel MVP and author Tom Urtis walks through a series of lessons and illustrations, while the accompanying DVD provides demos and screencasts to complement each lesson. Introduces you to programming with Visual Basic for Applications (VBA), macro composition, and the programming environment Explains events programming, embedded controls, user forms, message boxes, input boxes, looping, and more Shows you how to control other Office applications from Excel, such as Word, Outlook, Access, and PowerPoint Includes enhanced coverage of each lesson on the DVD, which also offers detailed examples Provides ideas for applying VBA to everyday tasks in a way that's both practical and funUse this book-and-DVD package to get the total learning experience of VBA for Excel!

Author Biography

Tom Urtis is a Microsoft Office developer, programmer, instructor, and Microsoft Excel MVP. Tom is founder of Atlas Programming Management (www.atlaspm.com), a Silicon Valley-based Office business solutions company specializing in Excel, providing consulting, development, training, and support of fully customized Office programs for a diverse international clientele.

Table of Contents

Introduction	p. xxvii
Understanding the BASI Cs
Introduc ing VBA	p. 3
What Is VBA?	p. 3
A Brief History of VBA	p. 4
What VBA Can Do for You	p. 5
Automating a Recurring Task	p. 5
Automating a Repetitive Task	p. 5
Running a Macro Automatically if Another Action Takes Place	p. 5
Creating Your Own Worksheet Functions	p. 5
Simplifying the Workbook's Look and Feel for Other Users	p. 5
Controlling Other Office Applications from Excel	p. 6
Liabilities of VBA	p. 7
Try It	p. 8
Getting Started with Macros	p. 9
Composing Your First Macro	p. 9
Accessing the VBA Environment	p. 9
Using the Macro Recorder	p. 12
Running a Macro	p. 16
The Macro Dialog Box	p. 16
Shortcut Key	p. 17
Try It	p. 17
Lesson Requirements	p. 18
Step-by-Step	p. 18
Introduc ing the Visual Basic Editor	p. 19
What Is the VBE?	p. 19
How To Get Into the VBE	p. 20
Understanding the VBE	p. 20
The Project Explorer Window	p. 21
The Code Window	p. 21
The Properties Window	p. 22
The Immediate Window	p. 22
Understanding Modules	p. 22
Using the Object Browser	p. 23
Exiting the VBE	p. 24
Try It	p. 25
Working in the VBE	p. 27
Toolbars in the VBE	p. 27
Macros and Modules	p. 28
Locating Your Macros	p. 28
Understanding the Code	p. 29
Editing a Macro with Comments and Improvements to the Code	p. 30
Deleting a Macro	p. 33
Inserting a Module	p. 33
Renaming a Module	p. 34
Deleting a Module	p. 36
Locking and Protecting the VBE	p. 36
Try It	p. 37
Lesson Requirements	p. 37
Step-by-Step	p. 37
: Diving Deeper into VBA
Ob ject-oriented Programm ing - A n Ov erview	p. 43
What "Object-Oriented Programming" Means	p. 43
The Object Model	p. 44
Properties	p. 45
Methods	p. 46
Collections	p. 46
Try It	p. 47
Variabl es, Data Types, and Constants	p. 49
What Is a Variable?	p. 49
Assigning Values to Variables	p. 50
Why You Need Variables	p. 50
Data Types	p. 51
Understanding the Different Data Types	p. 51
Declaring a Variable for Dates and Times	p. 53
Declaring a Variable with the Proper Data Type	p. 53
Forcing Variable Declaration	p. 54
Understanding a Variable's Scope	p. 56
Local Macro Level Only	p. 56
Module Level	p. 56
Application Level	p. 57
Constants	p. 57
Choosing the Scope and Lifetime of Your Constants	p. 58
Try It	p. 58
Lesson Requirements	p. 58
Step-by-Step	p. 58
Understanding
Ob jects and Coll ections	p. 61
Workbooks	p. 61
Worksheets	p. 62
Cells and Ranges	p. 63
SpecialCells	p. 64
Try It	p. 65
Lesson Requirements	p. 65
Step-by-Step	p. 65
Making Decisions with VBA	p. 69
Understanding Logical Operators	p. 69
AND	p. 70
OR	p. 70
NOT	p. 71
Choosing Between This or That	p. 72
If…Then	p. 72
If…Then…Else	p. 73
If…Then…ElseIf	p. 74
Select Case	p. 74
Getting Users to Make Decisions	p. 76
Message Boxes	p. 76
Input Boxes	p. 77
Try It	p. 78
Lesson Requirements	p. 78
Step-by-Step	p. 78
: the macro recorder: writing your Own Code
Repeating Ac tions with Loops	p. 85
What Is a Loop?	p. 85
Types of Loops	p. 86
For…Next	p. 87
For…Each…Next	p. 88
Exiting a For… Loop	p. 89
Looping In Reverse with Step	p. 90
Do…While	p. 91
Do…Until	p. 91
Do…Loop…While	p. 93
Do…Loop…Until	p. 94
While…Wend	p. 94
Nesting Loops	p. 94
Try It	p. 95
Lesson Requirements	p. 96
Step-by-Step	p. 96
Working with Arrays	p. 99
What Is an Array?	p. 99
What Arrays Can Do for You	p. 101
Declaring Arrays	p. 102
The Option Base Statement	p. 103
Boundaries in Arrays	p. 104
Declaring Arrays with Fixed Elements	p. 104
Declaring Dynamic Arrays with ReDim and Preserve	p. 105
Try It	p. 107
Lesson Requirements	p. 107
Step-by-Step	p. 107
Au tomating Procedures with Worksheet Ev ents	p. 111
What Is an "Event"?	p. 111
Worksheet Events - an Overview	p. 112
Where Does the Worksheet Event Code Go?	p. 112
Enabling and Disabling Events	p. 114
Examples of Common Worksheet Events	p. 115
Worksheet_Change Event	p. 115
Worksheet_SelectionChange Event	p. 116
Worksheet_BeforeDoubleClick Event	p. 116
Worksheet_BeforeRightClick Event	p. 117
Worksheet_FollowHyperlink Event	p. 117
Worksheet_Activate Event	p. 117
Worksheet_Deactivate Event	p. 118
Worksheet_Calculate Event	p. 118
Worksheet_PivotTableUpdate Event	p. 119
Try It	p. 119
Lesson Requirements	p. 119
Step-by-Step	p. 119
Au tomating Procedures
with Workbook Ev ents	p. 123
Workbook Events - An Overview	p. 123
Where Does the Workbook Event Code Go?	p. 123
Entering Workbook Event Code	p. 125
Examples of Common Workbook Events	p. 126
Workbook_Open Event	p. 126
Workbook_BeforeClose Event	p. 127
Workbook_Activate Event	p. 127
Workbook_Deactivate Event	p. 128
Workbook_SheetChange Event	p. 128
Workbook_SheetSelectionChange Event	p. 128
Workbook_SheetBeforeDoubleClick Event	p. 129
Workbook_SheetBeforeRightClick Event	p. 129
Workbook_SheetPivotTableUpdate Event	p. 130
Workbook_NewSheet Event	p. 130
Workbook_BeforePrint Event	p. 130
Workbook_SheetActivate Event	p. 131
Workbook_SheetDeactivate Event	p. 131
Workbook_BeforeSave Event	p. 131
Try It	p. 132
Lesson Requirements	p. 132
Step-by-Step	p. 132
Emb edded Controls	p. 135
Working with Forms Controls and ActiveX Controls	p. 135
The Forms Toolbar	p. 136
The Control Toolbox	p. 140
Try It	p. 144
Lesson Requirements	p. 144
Step-by-Step	p. 144
Programm ing Charts	p. 151
Adding a Chart to a Chart Sheet	p. 152
Adding an Embedded Chart to a Worksheet	p. 154
Moving a Chart	p. 155
Looping Through All Embedded Charts	p. 157
Deleting Charts	p. 158
Renaming a Chart	p. 159
Try It	p. 160
Lesson Requirements	p. 160
Step-by-Step	p. 160
Programm ing PivotTabl es and PivotCharts	p. 163
Creating a PivotTable Report	p. 163
Hiding the PivotTable Field List	p. 167
Using the Report Filter Area	p. 167
Formatting Numbers in the Values Area	p. 168
Why It's Called a PivotTable	p. 170
Creating a PivotChart	p. 171
Understanding PivotCaches	p. 173
Manipulating PivotFields in VBA	p. 176
Manipulating PivotItems with VBA	p. 177
Creating a PivotTables Collection	p. 177
Try It	p. 178
Lesson Requirements	p. 178
Step-by-Step	p. 179
User Defined Fu nctions	p. 183
What Is a User Defined Function?	p. 183
Characteristics of User Defined Functions	p. 184
Anatomy of a UDF	p. 184
UDF Examples That Solve Common Tasks	p. 185
Volatile Functions	p. 188
The Name of the Active Worksheet and Workbook	p. 189
UDFs with Conditional Formatting	p. 190
Calling Your Function from a Macro	p. 190
Adding a Description to the Insert Function Dialog	p. 191
Try It	p. 193
Lesson Requirements	p. 193
Step-by-Step	p. 193
Debu gging Your Code	p. 195
What Is Debugging?	p. 195
What Causes Errors?	p. 196
Weapons of Mass Debugging	p. 198
The Debugging Toolbar	p. 198
Trapping Errors	p. 207
Error Handler	p. 207
Bypassing Errors	p. 208
Try It	p. 210
Lesson Requirements	p. 210
Step-by-Step	p. 210
Advanced Programm ing Techniques
Creating UserForms	p. 215
What Is a UserForm?	p. 215
Creating a UserForm	p. 216
Designing a UserForm	p. 218
Showing a UserForm	p. 225
Where Does the UserForm's Code Go?	p. 225
Closing a UserForm	p. 226
Unloading a UserForm	p. 226
Hiding a UserForm	p. 227
Try It	p. 228
Lesson Requirements	p. 228
Step-by-Step	p. 228
nd Their Fu nctions	p. 231
Understanding the Frequently Used UserForm Controls	p. 231
CommandButtons	p. 232
Labels	p. 232
TextBoxes	p. 234
ListBoxes	p. 236
ComboBoxes	p. 238
CheckBoxes	p. 240
OptionButtons	p. 241
Frames	p. 243
MultiPages	p. 245
Try It	p. 246
Lesson Requirements	p. 246
Step-by-Step	p. 246
Advanced UserForms	p. 249
The UserForm Toolbar	p. 249
Modal versus Modeless	p. 250
Disabling the UserForm's Close Button	p. 250
Maximizing Your UserForm's Size	p. 252
Selecting and Displaying Photographs on a UserForm	p. 252
Unloading a UserForm Automatically	p. 253
Pre-Sorting the ListBox and ComboBox Items	p. 253
Populating ListBoxes and ComboBoxes with Unique Items	p. 255
Display a Real-Time Chart in a UserForm	p. 258
Try It	p. 259
Lesson Requirements	p. 259
Step-by-Step	p. 259
Class Modul es	p. 263
What Is a Class?	p. 263
What Is a Class Module?	p. 264
Creating Your Own Objects	p. 265
An Important Benefit of Class Modules	p. 266
Creating Collections	p. 268
Class Modules for Embedded Objects	p. 269
Try It	p. 272
Lesson Requirements	p. 272
Step-by-Step	p. 272
Add-Ins	p. 279
What Is an Excel Add-In?	p. 279
Creating an Add-In	p. 280
Converting a File to an Add-In	p. 284
Installing an Add-In	p. 286
Creating a User Interface for Your Add-In	p. 288
Changing the Add-In's Code	p. 290
Closing Add-Ins	p. 290
Removing an Add-In from the Add-Ins List	p. 291
Try It	p. 291
Lesson Requirements	p. 291
Step-by-Step	p. 291
Managing External Data	p. 295
Creating QueryTables from Web Queries	p. 295
Creating a QueryTable for Access	p. 299
Using Text Files to Store External Data	p. 301
Try It	p. 304
Lesson Requirements	p. 304
Step-by-Step	p. 304
Data Acc ess with Ac tiveX Data Ob jects	p. 307
Introducing ADO	p. 307
The Connection Object	p. 309
The Recordset Object	p. 309
The Command Object	p. 310
An Introduction to Structured Query Language (SQL)	p. 310
The SELECT Statement	p. 311
The INSERT Statement	p. 311
The UPDATE Statement	p. 312
The DELETE Statement	p. 312
Try It	p. 313
Not Gone, Not Forgotten	p. 315
Using Dialog Sheets	p. 315
What Does a Dialog Sheet Look Like?	p. 316
Option to Show Message Only Once	p. 318
Using XLM Get.Cell Functions	p. 321
Using the SendKeys Method	p. 322
Try It	p. 323
Lesson Requirements	p. 323
Step-by-Step	p. 323
Interacting with Other
Office Applications
Ov erview of Office Au tomation from Excel	p. 327
Why Automate Another Application?	p. 327
Understanding Office Automation	p. 328
Early Binding	p. 328
Late Binding	p. 329
Which One Is Better?	p. 330
Try It	p. 330
Lesson Requirements	p. 330
Step-by-Step	p. 330
Working with Word from Excel	p. 333
Activating a Word Document	p. 333
Activating the Word Application	p. 334
Opening and Activating a Word Document	p. 334
Creating a New Word Document	p. 336
Copying an Excel Range to a Word Document	p. 337
Printing a Word Document from Excel	p. 337
Importing a Word Document to Excel	p. 338
Try It	p. 339
Lesson Requirements	p. 339
Step-by-Step	p. 339
Working with Ou tlook from Excel	p. 343
Opening Outlook	p. 343
Composing an E-mail in Outlook from Excel	p. 344
Creating a MailItem Object	p. 344
Transferring an Excel Range to the Body of Your E-mail	p. 345
Putting It All Together	p. 346
E-mailing a Single Worksheet	p. 348
Try It	p. 348
Lesson Requirements	p. 348
Step-by-Step	p. 348
ith Acc ess from Excel	p. 353
Adding a Record to an Access Table	p. 353
Exporting an Access Table to an Excel Spreadsheet	p. 356
Creating a New Table in Access	p. 358
Try It	p. 359
Lesson Requirements	p. 359
Step-by-Step	p. 360
Working with PowerPoint from Excel	p. 363
Creating a New PowerPoint Presentation	p. 363
Copying a Worksheet Range to a PowerPoint Slide	p. 364
Copying Chart Sheets to PowerPoint Slides	p. 365
Running a PowerPoint Presentation from Excel	p. 367
Try It	p. 368
Lesson Requirements	p. 368
Step-by-Step	p. 368
Appendix: What's on the DVD?	p. 371
Index	p. 375
Table of Contents provided by Publisher. All Rights Reserved.