Wiley.com
Print this page Share

Excel VBA 24-Hour Trainer, 2nd Edition

ISBN: 978-1-118-99137-4
504 pages
March 2015
Excel VBA 24-Hour Trainer, 2nd Edition (1118991370) cover image

Description

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.

See More

Table 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

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

See More

Author Information

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.

See More

Downloads

Download TitleSizeDownload
All Files 6.76 MB Click to Download
Advanced Lesson 1 Workbook 82.02 KB Click to Download
Advanced Lesson 2 Workbook 40.83 KB Click to Download
Advanced Lesson 3 Workbook 54.38 KB Click to Download
Advanced Lesson 4 Workbook 26.47 KB Click to Download
Advanced Lesson 5 Workbook 297.36 KB Click to Download
Advanced Lesson 6 Workbook 37.92 KB Click to Download
Advanced Lesson 7 Workbook 26.25 KB Click to Download
Advanced Lesson 8 Workbook 22.66 KB Click to Download
Advanced Lesson 9 Workbook 24.52 KB Click to Download
Advanced Lesson 10 Workbook 25.78 KB Click to Download
Advanced Lesson 11 Workbook 38.95 KB Click to Download
Advanced Lesson 12 Workbook 20.05 KB Click to Download
Advanced Lesson 13 Workbook 18.93 KB Click to Download
Advanced Lesson 14 Workbook 18.68 KB Click to Download
Advanced Lesson 15 Workbook 31.71 KB Click to Download
ReadMe 7.39 KB Click to Download
Try It Lesson 2 11.04 KB Click to Download
Try It Lesson 4 14.31 KB Click to Download
Try It Lesson 6 14.05 KB Click to Download
Try It Lesson 8 19.97 KB Click to Download
Try It Lesson 9 16.69 KB Click to Download
Try It Lesson 10 14.37 KB Click to Download
Try It Lesson 11 19.14 KB Click to Download
Try It Lesson 12 16.67 KB Click to Download
Try It Lesson 13 17.09 KB Click to Download
Try It Lesson 14 19.49 KB Click to Download
Try It Lesson 15 25.67 KB Click to Download
Try It Lesson 16 19.65 KB Click to Download
Try It Lesson 17 19.83 KB Click to Download
Try It Lesson 18 26.09 KB Click to Download
Try It Lesson 19 16.07 KB Click to Download
Try It Lesson 20 22.34 KB Click to Download
Try It Lesson 21 19.18 KB Click to Download
Try It Lesson 22 30.84 KB Click to Download
Try It Lesson 23 21.38 KB Click to Download
Try It Lesson 24 23.44 KB Click to Download
Try It Lesson 25 11.38 KB Click to Download
Try It Lesson 25 Comment Text 13.20 KB Click to Download
Try It Lesson 26 22.24 KB Click to Download
Try It Lesson 29 18.35 KB Click to Download
Try It Lesson 29 PowerPoint Example 1 2.30 MB Click to Download
Try It Lesson 30 16.93 KB Click to Download
Try It Lesson 31 17.32 KB Click to Download
Try It Lesson 32 17.86 KB Click to Download
Try It Lesson 32 Database 444.00 KB Click to Download
Try It Lesson 33 23.20 KB Click to Download
Code Download Lesson 4 22.89 KB Click to Download
Code Download Lesson 7 17.69 KB Click to Download
Code Download Lesson 8 28.99 KB Click to Download
Code Download Lesson 9 36.94 KB Click to Download
Code Download Lesson 10 81.71 KB Click to Download
Code Download Lesson 11 54.51 KB Click to Download
Code Download Lesson 12 67.78 KB Click to Download
Code Download Lesson 13 50.40 KB Click to Download
Code Download Lesson 14 29.91 KB Click to Download
Code Download Lesson 15 79.20 KB Click to Download
Code Download Lesson 16 41.44 KB Click to Download
Code Download Lesson 17 41.21 KB Click to Download
Code Download Lesson 18 56.43 KB Click to Download
Code Download Lesson 19 44.47 KB Click to Download
Code Download Lesson 20 27.02 KB Click to Download
Code Download Lesson 22 93.79 KB Click to Download
Code Download Lesson 23 150.98 KB Click to Download
Code Download Lesson 24 69.19 KB Click to Download
Code Download Lesson 26 37.65 KB Click to Download
Code Download Lesson 28 61.84 KB Click to Download
Code Download Lesson 30 41.55 KB Click to Download
Code Download Lesson 31 26.47 KB Click to Download
Code Download Lesson 32 40.72 KB Click to Download
Code Download Lesson 33 68.92 KB Click to Download
Database2 444.00 KB Click to Download
PowerPointExample1 2.30 MB Click to Download
SheetManager 15.96 KB Click to Download
See More

Errata

Do you think you've discovered an error in this book? Please check the list of errata below to see if we've already addressed the error. If not, please submit the error via our Errata Form. We will attempt to verify your error; if you're right, we will post a correction below.

ChapterPageDetailsDatePrint Run
22 286 Error in Code
currently reads:
Landscape
should be:
xlLandscape
3 june 2015
13 142 Error in Text
Lesson 13 on page 142, the heading currently reads:

Worksheet_Before RightClick Event

Should Read:

Worksheet_BeforeRightClick Event

(All of the other instances of this item in the book and code are correct as is.)
07-Oct-15
21 275 Error in Text
Lesson 21, Figure 21-6 on page 275 currently shows:
An arrow pointing from the title bar?s caption in a UserForm (in design mode) to the BorderStyle property in the Properties window

Should Show:
An arrow pointing to the Caption property that is listed just below the BorderStyle property.
07-Oct-15
72 Errata in Text
The first sentence in Item 7 of the Try It section starts as:

With the Name property item selected, either double-click it or press the Alt key..


Should read:

With the Name property item selected, either double-click it or press the Tab key..
02-Feb-16
See More

Related Titles

Back to Top