Excel Macros For Dummies

291 Pages·2016·5.9 MB·English

by  Alexander M.

Excel® Macros
by Michael Alexander
Excel® Macros For Dummies®
Published by: John Wiley & Sons, Inc., 111 River Street, Hoboken, NJ 07030‐5774, www.wiley.com
Copyright © 2015 by John Wiley & Sons, Inc., Hoboken, New Jersey
Published simultaneously in Canada
No part of this publication may be reproduced, stored in a retrieval system or transmitted in any form or
by any means, electronic, mechanical, photocopying, recording, scanning or otherwise, except as permit-
ted under Sections 107 or 108 of the 1976 United States Copyright Act, without the prior written permission
of the Publisher. Requests to the Publisher for permission should be addressed to the Permissions
Department, John Wiley & Sons, Inc., 111 River Street, Hoboken, NJ 07030, (201) 748‐6011, fax (201)
748‐6008, or online at http://www.wiley.com/go/permissions.
Trademarks: Wiley, For Dummies, the Dummies Man logo, Dummies.com, Making Everything Easier, and
related trade dress are trademarks or registered trademarks of John Wiley & Sons, Inc. and may not be
used without written permission. Microsoft and Excel are registered trademarks of Microsoft Corporation.
All other trademarks are the property of their respective owners. John Wiley & Sons, Inc. is not associated
with any product or vendor mentioned in this book.
LIMIT OF LIABILITY/DISCLAIMER OF WARRANTY: THE PUBLISHER AND THE AUTHOR MAKE NO
REPRESENTATIONS OR WARRANTIES WITH RESPECT TO THE ACCURACY OR COMPLETENESS OF
THE CONTENTS OF THIS WORK AND SPECIFICALLY DISCLAIM ALL WARRANTIES, INCLUDING
WITHOUT LIMITATION WARRANTIES OF FITNESS FOR A PARTICULAR PURPOSE. NO WARRANTY
MAY BE CREATED OR EXTENDED BY SALES OR PROMOTIONAL MATERIALS. THE ADVICE AND
STRATEGIES CONTAINED HEREIN MAY NOT BE SUITABLE FOR EVERY SITUATION. THIS WORK IS
SOLD WITH THE UNDERSTANDING THAT THE PUBLISHER IS NOT ENGAGED IN RENDERING LEGAL,
ACCOUNTING, OR OTHER PROFESSIONAL SERVICES. IF PROFESSIONAL ASSISTANCE IS REQUIRED,
THE SERVICES OF A COMPETENT PROFESSIONAL PERSON SHOULD BE SOUGHT. NEITHER THE
PUBLISHER NOR THE AUTHOR SHALL BE LIABLE FOR DAMAGES ARISING HEREFROM. THE FACT
THAT AN ORGANIZATION OR WEBSITE IS REFERRED TO IN THIS WORK AS A CITATION AND/OR A
POTENTIAL SOURCE OF FURTHER INFORMATION DOES NOT MEAN THAT THE AUTHOR OR THE
PUBLISHER ENDORSES THE INFORMATION THE ORGANIZATION OR WEBSITE MAY PROVIDE OR
RECOMMENDATIONS IT MAY MAKE. FURTHER, READERS SHOULD BE AWARE THAT INTERNET
WEBSITES LISTED IN THIS WORK MAY HAVE CHANGED OR DISAPPEARED BETWEEN WHEN THIS
WORK WAS WRITTEN AND WHEN IT IS READ.
For general information on our other products and services, please contact our Customer Care Department
within the U.S. at 877‐762‐2974, outside the U.S. at 317‐572‐3993, or fax 317‐572‐4002. For technical support,
please visit www.wiley.com/techsupport.
Wiley publishes in a variety of print and electronic formats and by print‐on‐demand. Some material
included with standard print versions of this book may not be included in e‐books or in print‐on‐demand.
If this book refers to media such as a CD or DVD that is not included in the version you purchased, you
may download this material at http://booksupport.wiley.com. For more information about Wiley
products, visit www.wiley.com.
Library of Congress Control Number is available from the publisher.
ISBN 978‐1‐119‐08934‐6 (pbk); ISBN 978‐1‐119‐08926‐1 (ebk); ISBN 978‐1‐119‐08935‐3 (ebk)
Manufactured in the United States of America
10 9 8 7 6 5 4 3 2 1
Contents at a Glance
Introduction ................................................................ 1
Part I: Holy Macro Batman! ......................................... 7
Chapter 1: Macro Fundamentals ......................................................................................9
Chapter 2: Getting Cozy with Visual Basic Editor........................................................29
Chapter 3: The Anatomy of Macros ...............................................................................43
Part II: Making Short Work of Workbook Tasks ............ 57
Chapter 4: Working with Workbooks ............................................................................59
Chapter 5: Working with Worksheets ...........................................................................85
Part III: One‐Touch Data Manipulation ...................... 113
Chapter 6: Feeling at Home on the Range ...................................................................115
Chapter 7: Manipulating Data with Macros ................................................................137
Part IV: Macro‐Charging Reports and Emails ............. 173
Chapter 8: Automating Common Reporting Tasks ....................................................175
Chapter 9: Sending Emails from Excel.........................................................................213
Part V: The Part of Tens ........................................... 229
Chapter 10: Ten Handy Visual Basic Editor Tips .......................................................231
Chapter 11: Ten Places to Turn for Macro Help ........................................................239
Chapter 12: Ten Ways to Speed Up Your Macros ......................................................245
Index ...................................................................... 255

Table of Contents
Introduction ................................................................. 1
About This Book ..............................................................................................2
Foolish Assumptions .......................................................................................2
Icons Used In This Book .................................................................................3
Beyond the Book .............................................................................................3
Where to Go from Here ...................................................................................4
Part I: Holy Macro Batman! .......................................... 7
Chapter 1: Macro Fundamentals . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 9
Why Use a Macro? ...........................................................................................9
Macro Recording Basics ...............................................................................10
Examining the macro ...........................................................................12
Testing the macro ................................................................................13
Editing the macro.................................................................................13
Comparing Absolute and Relative Macro Recording ................................14
Recording macros with absolute references....................................14
Recording macros with relative references .....................................17
Other Macro Recording Concepts ...............................................................19
Macro‐enabled file extensions............................................................19
Macro security in Excel 2010 ..............................................................19
Trusted locations .................................................................................20
Storing macros in your personal macro workbook .........................21
Assigning a macro to a button and other form controls ................22
Placing a macro on the Quick Access toolbar .................................23
Examples of Macros in Action .....................................................................24
Building navigation buttons ...............................................................24
Dynamically rearranging pivot table data ........................................25
Offering one‐touch reporting options ...............................................26
Chapter 2: Getting Cozy with Visual Basic Editor . . . . . . . . . . . . . . . . . 29
Working in Visual Basic Editor ....................................................................29
Understanding VBE components .......................................................30
Working with the Project Window ..............................................................31
Adding a new VBA module .................................................................32
Removing a VBA module .....................................................................33
vi
Excel Macros For Dummies
Working with a Code Window ......................................................................34
Minimizing and maximizing windows ...............................................34
Getting VBA code into a module ........................................................35
Customizing the VBA Environment .............................................................37
The Editor tab ......................................................................................37
The Editor Format tab .........................................................................39
The General tab ....................................................................................40
The Docking tab ...................................................................................40
Chapter 3: The Anatomy of Macros . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 43
An Overview of the Excel Object Model .....................................................43
Understanding objects ........................................................................44
Understanding collections ..................................................................45
Understanding properties ..................................................................45
Understanding methods .....................................................................46
A Brief Look at Variables ..............................................................................46
Understanding Event Procedures ...............................................................49
Worksheet events ................................................................................49
Workbook events .................................................................................51
Error Handling in a Nutshell .........................................................................53
On Error GoTo SomeLabel ..................................................................53
On Error Resume Next ........................................................................54
On Error GoTo 0 ...................................................................................55
Part II: Making Short Work of Workbook Tasks ............. 57
Chapter 4: Working with Workbooks . . . . . . . . . . . . . . . . . . . . . . . . . . . . 59
Creating a Workbook from Scratch .............................................................59
How the macro works .........................................................................60
How to use the macro .........................................................................61
Saving a Workbook when a Particular Cell Is Changed ............................61
How the macro works .........................................................................62
How to use the macro .........................................................................63
Saving a Workbook before Closing ..............................................................63
How the macro works .........................................................................64
How to use the macro .........................................................................65
Protecting a Worksheet on Workbook Close .............................................66
How the macro works .........................................................................66
How to use the macro .........................................................................67
Unprotecting a Worksheet on Workbook Open ........................................68
How the macro works .........................................................................68
How to use the macro .........................................................................68
Opening a Workbook to a Specific Tab .......................................................69
How the macro works .........................................................................70
How to use the macro .........................................................................70
vii
Table of Contents
Opening a Specific Workbook Defined by the User ...................................71
How the macro works .........................................................................71
How to use the macro .........................................................................72
Determining Whether a Workbook Is Already Open .................................73
How the macro works .........................................................................73
How to use the macro .........................................................................75
Determining Whether a Workbook Exists in a Directory .........................76
How the macro works .........................................................................76
How to use the macro .........................................................................77
Closing All Workbooks at Once ...................................................................77
How the macro works .........................................................................77
How to use the macro .........................................................................78
Printing All Workbooks in a Directory ........................................................78
How the macro works .........................................................................79
How to use the macro .........................................................................80
Preventing the Workbook from Closing Until a Cell Is Populated ...........80
How the macro works .........................................................................80
How to use the macro .........................................................................81
Creating a Backup of a Current Workbook with Today’s Date ................82
How the macro works .........................................................................82
How to use the macro .........................................................................83
Chapter 5: Working with Worksheets . . . . . . . . . . . . . . . . . . . . . . . . . . . 85
Adding and Naming a New Worksheet ........................................................85
How the macro works .........................................................................85
How to use the macro .........................................................................86
Deleting All but the Active Worksheet ........................................................87
How the macro works .........................................................................87
How to use the macro .........................................................................88
Hiding All but the Active Worksheet ...........................................................88
How the macro works ...................................................................................89
How to use the macro .........................................................................90
Unhiding All Worksheets in a Workbook ....................................................90
How the macro works .........................................................................91
How to use the macro .........................................................................91
Moving Worksheets Around .........................................................................92
How the macro works .........................................................................92
How to use the macro .........................................................................93
Sorting Worksheets by Name .......................................................................93
How the macro works .........................................................................94
How to use the macro .........................................................................95
Grouping Worksheets by Color ...................................................................96
How the macro works .........................................................................96
How to use the macro .........................................................................98
Copying a Worksheet to a New Workbook .................................................98
How the macro works .........................................................................98
How to use the macro .........................................................................99
viii
Excel Macros For Dummies
Creating a Workbook for Each Worksheet .................................................99
How the macro works .........................................................................99
How to use the macro .......................................................................101
Printing Specified Worksheets ...................................................................101
How the macro works .......................................................................101
How to use the macro .......................................................................102
Protecting All Worksheets ..........................................................................102
How the macro works .......................................................................103
How to use the macro .......................................................................103
Unprotecting All Worksheets .....................................................................104
How the macro works .......................................................................104
How to use the macro .......................................................................105
Creating a Table of Contents for Your Worksheets ................................106
How the macro works .......................................................................106
How to use the macro .......................................................................109
Zooming In and Out of a Worksheet with Double‐Click ..........................109
How the macro works .......................................................................109
How to use the macro .......................................................................110
Highlighting the Active Row and Column .................................................110
How the macro works .......................................................................111
How to use the macro .......................................................................112
Part III: One‐Touch Data Manipulation ...................... 113
Chapter 6: Feeling at Home on the Range . . . . . . . . . . . . . . . . . . . . . . . 115
Selecting and Formatting a Range .............................................................115
How the macro works .......................................................................116
How to use the macro .......................................................................117
Creating and Selecting Named Ranges .....................................................117
How the macro works .......................................................................119
How to use the macro .......................................................................120
Enumerating through a Range of Cells .....................................................120
How the macro works .......................................................................120
How to use the macro .......................................................................122
Inserting Blank Rows in a Range ................................................................122
How the macro works .......................................................................122
How to use the macro .......................................................................123
Unhiding All Rows and Columns ...............................................................123
How the macro works .......................................................................124
How to use the macro .......................................................................124
Deleting Blank Rows ....................................................................................124
How the macro works .......................................................................125
How to use the macro .......................................................................126