® Excel 2013 VBA and Macros Bill Jelen Tracy Syrstad 800 East 96th Street, Indianapolis, Indiana 46240 USA Excel® 2013 VBA and Macros Copyright © 2013 by Pearson Education, Inc. All rights reserved. No part of this book shall be reproduced, stored in a retrieval system, or transmitted by any means, electronic, mechanical, photocopying, recording, or otherwise, without written permission from the publisher. No patent liability is assumed with respect to the use of the information contained herein. Although every precaution has been taken in the preparation of this book, the publisher and author assume no responsibility for errors or omissions. Nor is any liability assumed for damages resulting from the use of the information contained herein. ISBN-13: 978-0-7897-4861-4 ISBN-10: 0-7897-4861-4 Library of Congress Cataloging-in-Publication Data is on file. Printed in the United States of America First Printing: February 2013 Editor-in-Chief Greg Wiegand Executive Editor Loretta Yates Development Editor Charlotte Kughen Managing Editor Sandra Schroeder Project Editor Mandie Frank Copy Editor Cheri Clark Indexer Tim Wright Proofreader Paula Lowell Technical Editor Bob Umlas Editorial Assistant Cindy Teeters Designer Anne Jones Compositor Jake McFarland Trademarks All terms mentioned in this book that are known to be trademarks or service marks have been appropriately capitalized. Que Publishing cannot attest to the accuracy of this information. Use of a term in this book should not be regarded as affecting the validity of any trademark or service mark. Warning and Disclaimer Every effort has been made to make this book as complete and as accurate as possible, but no warranty or fitness is implied. The information provided is on an “as is” basis. The authors and the publisher shall have neither liability nor responsibility to any person or entity with respect to any loss or damages arising from the information contained in this book. Bulk Sales Que Publishing offers excellent discounts on this book when ordered in quantity for bulk purchases or special sales. For more information, please contact U.S. Corporate and Government Sales 1-800-382-3419 [email protected] For sales outside of the U.S., please contact International Sales [email protected] Contents at a Glance Introduction 1 Unleash the Power of Excel with VBA 2 This Sounds Like BASIC, So Why Doesn’t It Look Familiar? 3 Referring to Ranges 4 Looping and Flow Control 5 R1C1-Style Formulas 6 Create and Manipulate Names in VBA 7 Event Programming 8 Arrays 9 Creating Classes, Records, and Collections 10 Userforms: An Introduction 11 Data Mining with Advanced Filter 12 Using VBA to Create Pivot Tables 13 Excel Power 14 Sample User-Defined Functions 15 Creating Charts 16 Data Visualizations and Conditional Formatting 17 Dashboarding with Sparklines in Excel 2013 18 Reading from and Writing to the Web 19 Text File Processing 20 Automating Word 21 Using Access as a Back End to Enhance Multiuser Access to Data 22 Advanced Userform Techniques 23 Windows API 24 Handling Errors 25 Customizing the Ribbon to Run Macros 26 Creating Add-Ins 27 An Introduction to Creating Apps for Office 28 What Is New in Excel 2013 and What Has Changed Index Table of Contents Introduction Getting Results with VBA What Is in This Book? Reduce the Learning Curve Excel VBA Power Techie Stuff Needed to Produce Applications Does This Book Teach Excel? The Future of VBA and Windows Versions of Excel Versions of Excel Special Elements and Typographical Conventions Code Files Next Steps 1 Unleash the Power of Excel with VBA The Power of Excel Barriers to Entry The Macro Recorder Doesn’t Work! Visual Basic Is Not Like BASIC Good News: Climbing the Learning Curve Is Easy Great News: Excel with VBA Is Worth the Effort Knowing Your Tools: The Developer Tab Understanding Which File Types Allow Macros Macro Security Adding a Trusted Location Using Macro Settings to Enable Macros in Workbooks Outside of Trusted Locations Using Disable All Macros with Notification Overview of Recording, Storing, and Running a Macro Filling Out the Record Macro Dialog Running a Macro Creating a Macro Button on the Ribbon Creating a Macro Button on the Quick Access Toolbar Assigning a Macro to a Form Control, Text Box, or Shape Understanding the VB Editor VB Editor Settings The Project Explorer The Properties Window Understanding Shortcomings of the Macro Recorder Examining Code in the Programming Window Running the Macro on Another Day Produces Undesired Results Possible Solution: Use Relative References When Recording Never Use the AutoSum or Quick Analysis While Recording a Macro Three Tips When Using the Macro Recorder Next Steps 2 This Sounds Like BASIC, So Why Doesn’t It Look Familiar? I Can’t Understand This Code Understanding the Parts of VBA “Speech” VBA Is Not Really Hard VBA Help Files: Using F1 to Find Anything Using Help Topics Examining Recorded Macro Code: Using the VB Editor and Help Optional Parameters Defined Constants Properties Can Return Objects Using Debugging Tools to Figure Out Recorded Code Stepping Through Code More Debugging Options: Breakpoints Backing Up or Moving Forward in Code Not Stepping Through Each Line of Code Querying Anything While Stepping Through Code Using a Watch to Set a Breakpoint Using a Watch on an Object Object Browser: The Ultimate Reference Seven Tips for Cleaning Up Recorded Code Tip 1: Don’t Select Anything Tip 2: Cells(2,5) Is More Convenient Than Range(“E2”) Tip 3: Use More Reliable Ways to Find the Last Row Tip 4: Use Variables to Avoid Hard-Coding Rows and Formulas Tip 5: R1C1 Formulas That Make Your Life Easier Tip 6: Learn to Copy and Paste in a Single Statement Tip 7: Use With...End With to Perform Multiple Actions Next Steps 3 Referring to Ranges The Range Object Syntax to Specify a Range Named Ranges Shortcut for Referencing Ranges Referencing Ranges in Other Sheets Referencing a Range Relative to Another Range Use the Cells Property to Select a Range Use the Offset Property to Refer to a Range Use the Resize Property to Change the Size of a Range Use the Columns and Rows Properties to Specify a Range Use the Union Method to Join Multiple Ranges Use the Intersect Method to Create a New Range from Overlapping Ranges Use the ISEMPTY Function to Check Whether a Cell Is Empty Use the CurrentRegion Property to Select a Data Range Use the Areas Collection to Return a Noncontiguous Range Referencing Tables Next Steps 4 Looping and Flow Control For...Next Loops Using Variables in the For Statement Variations on the For...Next Loop Exiting a Loop Early After a Condition Is Met Nesting One Loop Inside Another Loop Do Loops Using the While or Until Clause in Do Loops While...Wend Loops The VBA Loop: For Each Object Variables Flow Control: Using If...Then...Else and Select Case Basic Flow Control: If...Then...Else Conditions If...Then...End If Either/Or Decisions: If...Then...Else...End If Using If...ElseIf...End If for Multiple Conditions Using Select Case...End Select for Multiple Conditions Complex Expressions in Case Statements Nesting If Statements Next Steps 5 R1C1-Style Formulas