Оглавление
Dick Kusleika. Excel Macros For Dummies
Excel® Macros For Dummies® To view this book's Cheat Sheet, simply go to www.dummies.com and search for “Excel Macros For Dummies Cheat Sheet” in the Search box. Table of Contents
List of Illustrations
Guide
Pages
Introduction
About This Book
Foolish Assumptions
Icons Used in This Book
Beyond the Book
Where to Go from Here
Holy Macro Batman!
Macro Fundamentals
Choosing to Use a Macro
Macro Recording Basics
Examining the macro
Editing the macro
Testing the macro
Comparing Absolute and Relative Macro Recording
Recording macros with absolute references
Recording macros with relative references
Understanding Macro Security
Macro-enabled file extensions
Trusted documents
Trusted locations
Storing and Running Macros
Storing macros in your Personal Macro Workbook
Assigning a macro to a button and other form controls
FORM CONTROLS VERSUS ACTIVEX CONTROLS
Placing a macro on the Quick Access Toolbar
Exploring Macro Examples
Building navigation buttons
Dynamically rearranging PivotTable data
Offering one-touch reporting options
Getting Cozy with the Visual Basic Editor
Working in the Visual Basic Editor
The VBE menu bar
The VBE toolbars
The Project Explorer
The Code pane
The Immediate window
Working with the Project Explorer
Adding a new VBA module
Removing a VBA module
Working with a Code Pane
Minimizing and maximizing windows
Getting VBA code into a module
Customizing the VBE
The Editor tab
The Auto Syntax Check option
The Require Variable Declaration option
The Auto List Members option
The Auto Quick Info option
The Auto Data Tips option
The Auto Indent setting
The Drag-and-Drop Text Editing option
The Default to Full Module View option
The Procedure Separator option
The Editor Format tab
The Code Colors option
The Font option
The Size setting
The Margin Indicator Bar option
The General tab
The Docking tab
The Anatomy of Macros
A Brief Overview of the Excel Object Model
Understanding objects
Understanding collections
Understanding properties
Understanding methods
A Brief Look at Variables
The common variable types
Understanding Event Procedures
Worksheet events
Workbook events
Error Handling in a Nutshell
On Error GoTo SomeLabel
On Error Resume Next
On Error GoTo 0
Making Short Work of Workbook Tasks
Working with Workbooks
Installing Macros
Event macros
Personal Macro Workbook
Standard macros
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
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
Working with Worksheets
Installing Macros
Event macros
Personal Macro Workbook
Standard macros
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
One-Touch Data Manipulation
Feeling at Home on the Range
Installing Macros
Event macros
Personal Macro Workbook
Standard macros
Selecting and Formatting a Range
Creating and Selecting Named Ranges
Looping 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
Manipulating Data with Macros
Installing Macros
Event macros
Personal Macro Workbook
Standard 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
Macro-Charging Reports and Emails
Automating Common Reporting Tasks
Installing Macros
Event macros
Personal Macro Workbook
Standard macros
Refreshing All PivotTables in a Workbook
Creating a List of PivotTables
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
Sending Emails from Excel
Installing Macros
Event macros
Personal Macro Workbook
Standard macros
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
Wrangling External Data with Macros
Working with External Data Connections
Manually creating a connection
Manually editing data connections
Using Macros to Create Dynamic Connections
Iterating through All Connections in a Workbook
Using ADO and VBA to Pull External Data
Understanding ADO syntax
The connection string
Declaring a Recordset
Using ADO in a macro
Working with Text Files
Opening a text file
Reading the opened text file
A practical example: Logging workbook usage in a text file
A practical example: Importing a text file to a range
The Part of Tens
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
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
Ten Ways to Speed Up Your Macros
Halt Calculations
Disable 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
Index. Symbols
A
B
C
D
E
F
G
H
I
J
K
L
M
N
O
P
Q
R
S
T
U
V
W
X
Y
Z
Dedication
Author’s Acknowledgments
About the Author
WILEY END USER LICENSE AGREEMENT
