Excel VBA 24-Hour Trainer

Оглавление

Tom Urtis. Excel VBA 24-Hour Trainer

Introduction

Who This Book Is For

What This Book Covers

How This Book Is Structured

What You Need to Use This Book

Conventions

Errata

Part I. Understanding the BASICs

Lesson 1. Introducing VBA

What is VBA?

A Brief History of VBA

What VBA Can Do for You

Liabilities of VBA

Try It

Lesson 2. Getting Started with Macros

Composing Your First Macro

Running A Macro

Try It

Lesson 3. Introducing the Visual Basic Editor

What is the VBE?

Try It

Lesson 4. Working in the VBE

Toolbars in the VBE

Macros and Modules

Understanding the Code

Editing a Macro with Comments and Improvements to the Code

Try It

Part II. Diving Deeper into VBA

Lesson 5. Object-Oriented Programming: An Overview

What “Object-Oriented Programming” Means

The Object Model

Try It

Lesson 6. Variables, Data Types, and Constants

What is a Variable?

Assigning Values to Variables

Why You Need Variables

Data Types

Forcing Variable Declaration

Understanding a Variable's Scope

Try It

Lesson 7. Understanding Objects and Collections

Workbooks

Cells and Ranges

Try It

Lesson 8. Working with Ranges

Working with Contiguously Populated Ranges

Working with Noncontiguously Populated Ranges

Try It

Lesson 9. Making Decisions with VBA

Understanding Logical Operators

Choosing between this or that

Getting Users to Make Decisions

Try It

Part III. Beyond the Macro Recorder: Writing Your Own Code

Lesson 10. Repeating Actions with Loops

What is a Loop?

Nesting Loops

Try It

Lesson 11. Programming Formulas

Understanding A1 and R1C1 References

Programming Your Formula Solutions with VBA

Try It

Lesson 12. Working with Arrays

What is an Array?

The Option Base Statement

Boundaries in Arrays

Declaring Arrays with Fixed Elements

Declaring Dynamic Arrays with Redim and Preserve

Try It

Lesson 13. Automating Procedures with Worksheet Events

What Is an Event?

Worksheet Events: An Overview

Examples of Common Worksheet Events

Try It

Lesson 14. Automating Procedures with Workbook Events

Workbook Events: An Overview

Examples of Common Workbook Events

Try It

Lesson 15. Handling Duplicate Items and Records

Deleting Rows Containing Duplicate Entries

Working with Duplicate Data

Try It

Lesson 16. Using Embedded Controls

Working with Form Controls and ActiveX Controls

Try It

Lesson 17. Programming Charts

Adding a Chart to a Chart Sheet

Adding an Embedded Chart to a Worksheet

Moving a Chart

Looping Through All Embedded Charts

Try It

Lesson 18. Programming PivotTables and PivotCharts

Creating a PivotTable Report

Understanding PivotCaches

Manipulating PivotFields in VBA

Manipulating PivotItems with VBA

Creating a PivotTables Collection

Try It

Lesson 19. User-Defined Functions

What Is a User-Defined Function?

UDF Examples That Solve Common Tasks

Volatile Functions

Try It

Lesson 20. Debugging Your Code

What Is Debugging?

What Causes Errors?

Weapons of Mass Debugging

Trapping Errors

Try It

Part IV. Advanced Programming Techniques

Lesson 21. Creating UserForms

What Is a UserForm?

Creating a UserForm

Designing a UserForm

Adding Controls to a UserForm

Showing a UserForm

Where Does the UserForm's Code Go?

Closing a UserForm

Try It

Lesson 22. UserForm Controls and Their Functions

Understanding the Frequently Used UserForm Controls

Try It

Lesson 23. Advanced UserForms

The UserForm Toolbar

Modal versus Modeless

Disabling the UserForm's Close Button

Maximizing Your UserForm's Size

Selecting and Displaying Photographs on a UserForm

Unloading a UserForm Automatically

Pre-sorting the ListBox and ComboBox Items

Populating ListBoxes and ComboBoxes with Unique Items

Displaying a Real-Time Chart in a UserForm

Try It

Lesson 24. Class Modules

What Is a Class?

What Is a Class Module?

Creating Your Own Objects

An Important Benefit of Class Modules

Creating Collections

Class Modules for Embedded Objects

Try It

Lesson 25. Add-Ins

What Is an Excel Add-In?

Creating an Add-In

Converting a File to an Add-In

Installing an Add-In

Creating a User Interface for Your Add-In

Closing Add-Ins

Removing an Add-In from the Add-Ins List

Try It

Lesson 26. Managing External Data

Creating QueryTables from Web Queries

Creating a QueryTable for Access

Using Text Files to Store External Data

Try It

Lesson 27. Data Access with ActiveX Data Objects

Introducing ADO

An Introduction to Structured Query Language (SQL)

Try It

Lesson 28. Impressing Your Boss (or at Least Your Friends)

Selecting Cells and Ranges

Filtering Dates

Setting Page Breaks for Specified Areas

Using a Comment to Log Changes in a Cell

Using the Windows API with VBA

Scheduling Your Workbook for Suicide

Try It

Part V. Understanding the BASICs

Lesson 29. Overview of Office Automation from Excel

Why Automate Another Application?

Understanding Office Automation

Try It

Lesson 30. Working with Word from Excel

Activating a Word Document

Creating a New Word Document

Copying an Excel Range to a Word Document

Printing a Word Document from Excel

Importing a Word Document to Excel

Try It

Lesson 31. Working with Outlook from Excel

Opening Outlook

Composing an E-mail in Outlook from Excel

Putting It All Together

E-mailing a Single Worksheet

Try It

Lesson 32. Working with Access from Excel

Adding a Record to an Access Table

Exporting an Access Table to an Excel Spreadsheet

Creating a New Table in Access

Try It

Lesson 33. Working with PowerPoint from Excel

Creating a New PowerPoint Presentation

Copying a Worksheet Range to a PowerPoint Slide

Copying Chart Sheets to PowerPoint Slides

Running a PowerPoint Presentation from Excel

Try It

About the Author

Credits

Acknowledgments

WILEY END USER LICENSE AGREEMENT

Отрывок из книги

CONGRATULATIONS ON MAKING TWO EXCELLENT CHOICES! You want to learn programming for Microsoft Excel with Visual Basic for Applications (VBA), and you've purchased this book to teach you. Excel is the most powerful and widely used spreadsheet application in the world. VBA enables you to become much more productive and efficient, while getting your everyday Excel tasks done more quickly and with fewer errors. You'll gain a programming skill that is in high demand, which will improve your value in the workplace and your marketability when searching for employment.

This book covers VBA from the ground up, and assumes you have never programmed Excel before. If you've never recorded or written an Excel macro, this book shows you how. If you've worked with VBA before, this book has examples of programming techniques you might not have seen. The instruction and examples in this book teach VBA concepts that range in levels from fundamental to advanced. The techniques in this book apply just as well to the Excel business power user as to the keeper of the family budget.

.....

In the Record Macro dialog box, give the macro a meaningful name. Macro names cannot contain spaces, and they cannot begin with a numeral. Because you are the person doing the sorting, and you don't want to make the macro name too long, naming it mySort gives the macro more meaning than the default name of Macro1.

In Figure 2.12, notice the small box to the right of Ctrl+ in the Shortcut Key section. You can place any letter of the alphabet in that field, which, when pressed with the Ctrl key, will be one method (and a convenient one at that) by which you can run the macro.

.....