Excel Macros For Dummies

Оглавление

Alexander Michael. Excel Macros For Dummies

Introduction

About This Book

Foolish Assumptions

Icons Used in This Book

Beyond the Book

Where to Go from Here

Part 1. Holy Macro Batman!

Chapter 1. Macro Fundamentals

Why Use a Macro?

Macro Recording Basics

Comparing Absolute and Relative Macro Recording

Other Macro Recording Concepts

Examples of Macros in Action

Chapter 2. Getting Cozy with the Visual Basic Editor

Working in the Visual Basic Editor

Working with the Project Window

Working with a Code Window

Customizing the VBA Environment

Chapter 3. The Anatomy of Macros

A Brief Overview of the Excel Object Model

A Brief Look at Variables

Understanding Event Procedures

Error Handling in a Nutshell

Part 2. Making Short Work of Workbook Tasks

Chapter 4. Working with Workbooks

Creating a New Workbook from Scratch

Saving a Workbook when a Particular Cell Is Changed

Saving a Workbook before Closing

Protecting a Worksheet on Workbook Close

Unprotecting a Worksheet on Workbook Open

Opening a Workbook to a Specific Tab

Opening a Specific Workbook Defined by the User

Determining Whether a Workbook Is Already Open

Determining Whether a Workbook Exists in a Directory

Closing All Workbooks at Once

Printing All Workbooks in a Directory

Preventing the Workbook from Closing Until a Cell Is Populated

Creating a Backup of the Current Workbook with Today’s Date

Chapter 5. Working with Worksheets

Adding and Naming a New Worksheet

Deleting All but the Active Worksheet

Hiding All but the Active Worksheet

Unhiding All Worksheets in a Workbook

Moving Worksheets Around

Sorting Worksheets by Name

Grouping Worksheets by Color

Copying a Worksheet to a New Workbook

Creating a New Workbook for Each Worksheet

Printing Specified Worksheets

Protecting All Worksheets

Unprotecting All Worksheets

Creating a Table of Contents for Your Worksheets

Zooming In and Out of a Worksheet with Double-Click

Highlighting the Active Row and Column

Part 3. One-Touch Data Manipulation

Chapter 6. Feeling at Home on the Range

Selecting and Formatting a Range

Creating and Selecting Named Ranges

Enumerating Through a Range of Cells

Inserting Blank Rows in a Range

Unhiding All Rows and Columns

Deleting Blank Rows

Deleting Blank Columns

Limiting Range Movement to a Particular Area

Selecting and Formatting All Formulas in a Workbook

Finding and Selecting the First Blank Row or Column

Chapter 7. Manipulating Data with Macros

Copying and Pasting a Range

Converting All Formulas in a Range to Values

Text to Columns on All Columns

Converting Trailing Minus Signs

Trimming Spaces from All Cells in a Range

Truncating ZIP Codes to the Left Five

Padding Cells with Zeros

Replacing Blanks Cells with a Value

Appending Text to the Left or Right of Your Cells

Cleaning Up Non-Printing Characters

Highlighting Duplicates in a Range of Data

Hiding All but Rows Containing Duplicate Data

Selectively Hiding AutoFilter Drop-down Arrows

Copying Filtered Rows to a New Workbook

Showing Filtered Columns in the Status Bar

Part 4. Macro-Charging Reports and Emails

Chapter 8. Automating Common Reporting Tasks

Refreshing All PivotTables in a Workbook

Creating a PivotTable Inventory Summary

Adjusting All Pivot Data Field Titles

Setting All Data Items to Sum

Applying Number Formatting for All Data Items

Sorting All Fields in Alphabetical Order

Applying a Custom Sort to Data Items

Applying PivotTable Restrictions

Applying Pivot Field Restrictions

Automatically Deleting PivotTable Drill-Down Sheets

Printing a PivotTable for Each Report Filter Item

Creating a New Workbook for Each Report Filter Item

Resizing All Charts on a Worksheet

Aligning a Chart to a Specific Range

Creating a Set of Disconnected Charts

Printing All Charts on a Worksheet

Chapter 9. Sending Emails from Excel

Mailing the Active Workbook as an Attachment

Mailing a Specific Range as an Attachment

Mailing a Single Sheet as an Attachment

Sending Mail with a Link to Your Workbook

Mailing All Email Addresses in Your Contact List

Saving All Attachments to a Folder

Saving Certain Attachments to a Folder

Chapter 10. Wrangling External Data with Macros

Working with External Data Connections

Using Macros to Create Dynamic Connections

Iterating through All Connections in a Workbook

Using ADO and VBA to Pull External Data

Part 5. Part of Tens

Chapter 11. Ten Handy Visual Basic Editor Tips

Applying Block Comments

Copying Multiple Lines of Code at Once

Jumping between Modules and Procedures

Teleporting to Your Functions

Staying in the Right Procedure

Stepping through Your Code

Stepping to a Specific Line in Your Code

Stopping Your Code at a Predefined Point

Seeing the Beginning and End of Variable Values

Turning Off Auto Syntax Check

Chapter 12. Ten Places to Turn for Macro Help

Let Excel Write the Macro for You

Use the VBA Help Files

Pilfer Code from the Internet

Leverage User Forums

Visit Expert Blogs

Mine YouTube for Video Training

Attend Live and Online Training Classes

Learn from the Microsoft Office Dev Center

Dissect the Other Excel Files in your Organization

Ask Your Local Excel Genius

Chapter 13. Ten Ways to Speed Up Your Macros

Halt Sheet Calculations

Disable Sheet Screen Updating

Turn Off Status Bar Updates

Tell Excel to Ignore Events

Hide Page Breaks

Suspend PivotTable Updates

Steer Clear of Copy and Paste

Use the With Statement

Don’t Explicitly Select Objects

Avoid Excessive Trips to the Worksheet

Dedication

Author’s Acknowledgments

About the Author

WILEY END USER LICENSE AGREEMENT

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

In its broadest sense, a macro is a sequence of instructions that automates some aspect of Excel so that you can work more efficiently and with fewer errors. You might create a macro, for example, to format and print a month-end sales report. After you develop the macro, you can execute it to perform many time-consuming procedures automatically.

Macros are written in VBA, which stands for Visual Basic for Applications. VBA is a programming language developed by Microsoft and a tool used to develop programs that control Excel.

.....

Notice that this macro, unlike your previous macro, works on both sets of data. Because the macro applies the totals relative to the currently active cell, the totals are applied correctly.

For this macro to work, you simply need to ensure that

.....