Tom Urtis
Excel VBA 24-Hour Trainer
Tom Urtis
Excel VBA 24-Hour Trainer
- Broschiertes Buch
- Merkliste
- Auf die Merkliste
- Bewerten Bewerten
- Teilen
- Produkt teilen
- Produkterinnerung
- Produkterinnerung
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…mehr
Andere Kunden interessierten sich auch für
- Amanda PerranBeginning SharePoint 201343,99 €
- Richard ShepherdExcel VBA Macro Programming37,99 €
- Lois PattersonTeach Yourself Microsoft Excel 97 in 24 Hours17,99 €
- John P. MuellerMicrosoft Office VISIO 2007 for Dummies24,99 €
- Joel ScottMicrosoft Dynamics Crm 4 for Dummies24,99 €
- Kim HeldmanMicrosoft Office Excel 2007 for Project Managers34,99 €
- Michael AlexanderExcel Dashboards and Reports, 2nd Edition33,99 €
-
-
-
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.
Hinweis: Dieser Artikel kann nur an eine deutsche Lieferadresse ausgeliefert werden.
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.
Hinweis: Dieser Artikel kann nur an eine deutsche Lieferadresse ausgeliefert werden.
Produktdetails
- Produktdetails
- Verlag: Wiley & Sons
- 2. Aufl.
- Seitenzahl: 496
- Erscheinungstermin: 30. März 2015
- Englisch
- Abmessung: 236mm x 185mm x 23mm
- Gewicht: 816g
- ISBN-13: 9781118991374
- ISBN-10: 1118991370
- Artikelnr.: 41416834
- Verlag: Wiley & Sons
- 2. Aufl.
- Seitenzahl: 496
- Erscheinungstermin: 30. März 2015
- Englisch
- Abmessung: 236mm x 185mm x 23mm
- Gewicht: 816g
- ISBN-13: 9781118991374
- ISBN-10: 1118991370
- Artikelnr.: 41416834
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
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
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
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