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.
Produkt-Info
WebSite Associated w/Book
Auflage
Sprache
Verlagsort
Verlagsgruppe
Produkt-Hinweis
Broschur/Paperback
Klebebindung
Maße
Höhe: 236 mm
Breite: 185 mm
Dicke: 23 mm
Gewicht
ISBN-13
978-1-118-99137-4 (9781118991374)
Schweitzer Klassifikation
Tom Urtis is an Excel Microsoft Excel MVP, developer, and programmer with 30 years of experience in business management and developing spreadsheet and database applications for companies of all sizes. He is an Excel instructor and frequent contributor to newsgroups in the Excel community.
Wrox guides are crafted to make learning programming languages and technologies easier than you think. Written by programmers for programmers, they provide a structured, tutorial format that will guide you through all the techniques involved.
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
What Is Debugging? 251
What Causes Errors? 252
Weapons of Mass Debugging 254
Trapping Errors 264
Try It 266
PART IV: ADVANCED PROGRAMMING TECHNIQUES
LESSON 21: CREATING USERFORMS 271
What Is a UserForm? 271
Creating a UserForm 272
Designing a UserForm 273
Adding Controls to a UserForm 274
Showing a UserForm 280
Where Does the UserForm's Code Go? 281
Closing a UserForm 281
Try It 283
LESSON 22: USERFORM CONTROLS AND THEIR FUNCTIONS 285
Understanding the Frequently Used UserForm Controls 285
Try It 301
LESSON 23: ADVANCED USERFORMS 305
The UserForm Toolbar 305
Modal versus Modeless 306
Disabling the UserForm's Close Button 307
Maximizing Your UserForm's Size 308
Selecting and Displaying Photographs on a UserForm 308
Unloading a UserForm Automatically 309
Pre-sorting the ListBox and ComboBox Items 310
Populating ListBoxes and ComboBoxes with Unique Items 312
Displaying a Real-Time Chart in a UserForm 314
Try It 315
LESSON 24: CLASS MODULES 321
What Is a Class? 321
What Is a Class Module? 322
Creating Your Own Objects 323
An Important Benefi t of Class Modules 323
Creating Collections 326
Class Modules for Embedded Objects 326
Try It 330
LESSON 25: ADD-INS 335
What Is an Excel Add-In? 335
Creating an Add-In 336
Converting a File to an Add-In 341
Installing an Add-In 342
Creating a User Interface for Your Add-In 346
Closing Add-Ins 349
Removing an Add-In from the Add-Ins List 349
Try It 350
LESSON 26: MANAGING EXTERNAL DATA 353
Creating QueryTables from Web Queries 353
Creating a QueryTable for Access 356
Using Text Files to Store External Data 359
Try It 361
LESSON 27: DATA ACCESS WITH ACTIVEX DATA OBJECTS 365
Introducing ADO 365
An Introduction to Structured Query Language (SQL) 368
Try It 371
LESSON 28: IMPRESSING YOUR BOSS (OR AT LEAST YOUR FRIENDS) 373
Selecting Cells and Ranges 373
Filtering Dates 376
Setting Page Breaks for Specifi ed Areas 379
Using a Comment to Log Changes in a Cell 380
Using the Windows API with VBA 381
Scheduling Your Workbook for Suicide 382
Try It 382
PART V: INTERACTING WITH OTHER OFFICE APPLICATIONS
LESSON 29: OVERVIEW OF OFFICE AUTOMATION FROM EXCEL 391
Why Automate Another Application? 391
Understanding Offi ce Automation 392
Try It 395
LESSON 30: WORKING WITH WORD FROM EXCEL 399
Activating a Word Document 399
Creating a New Word Document 402
Copying an Excel Range to a Word Document 402
Printing a Word Document from Excel 403
Importing a Word Document to Excel 404
Try It 405
LESSON 31: WORKING WITH OUTLOOK FROM EXCEL 409
Opening Outlook 409
Composing an E-mail in Outlook from Excel 410
Putting It All Together 413
E-mailing a Single Worksheet 415
Try It 415
LESSON 32: WORKING WITH ACCESS FROM EXCEL 419
Adding a Record to an Access Table 419
Exporting an Access Table to an Excel Spreadsheet 423
Creating a New Table in Access 426
Try It 427
LESSON 33: WORKING WITH POWERPOINT FROM EXCEL 431
Creating a New PowerPoint Presentation 431
Copying a Worksheet Range to a PowerPoint Slide 432
Copying Chart Sheets to PowerPoint Slides 433
Running a PowerPoint Presentation from Excel 435
Try It 436
INDEX 441
