Jelen Bill - Microsoft Excel 2019 VBA and macros

Bill Jelen

Tracy Syrstad

Microsoft Excel 2019 VBA and Macros

Published with the authorization of Microsoft Corporation by:
Pearson Education, Inc.

Copyright 2019 by Pearson Education, Inc.

All rights reserved. Printed in the United States of America. This publication is protected by copyright, and permission must be obtained from the publisher prior to any prohibited reproduction, storage in a retrieval system, or transmission in any form or by any means, electronic, mechanical, photocopying, recording, or likewise. For information regarding permissions, request forms, and the appropriate contacts within the Pearson Education Global Rights & Permissions Department, please visit www.pearsoned.com/permissions/. 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-1-5093-0611-4
ISBN-10: 1-5093-0611-0

Library of Congress Control Number: 2018963483

1 18

Trademarks

Microsoft and the trademarks listed at http://www.microsoft.com on the Trademarks webpage are trademarks of the Microsoft group of companies. All other marks are property of their respective owners.

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 author, the publisher, and Microsoft Corporation 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.

Special Sales

For information about buying this title in bulk quantities, or for special sales opportunities (which may include electronic versions; custom cover designs; and content particular to your business, training goals, marketing focus, or branding interests), please contact our corporate sales department at or (800) 382-3419.

For government sales inquiries, please contact .

For questions about sales outside the U.S., please contact .

Editor-in-Chief: Brett Bartow

Executive Editor: Loretta Yates

Project Editor: Charlotte Kughen

Managing Editor: Sandra Schroeder

Senior Project Editor: Tracey Croom

Copy Editor: Charlotte Kughen

Indexer: Cheryl Lenser

Proofreaders: Sarah Kearns and Karen Davis

Technical Editor: Bob Umlas

Editorial Assistant: Cindy Teeters

Cover Designer: Twist Creative, Seattle

Compositor: Bronkella Publishing LLC

For Chip Pearson. Chips website on VBA helped tens of thousands of people around the globe. We were sorry to hear that he died this year in an auto accident and dedicate this edition of the book to Chip.

Bill Jelen & Tracy Syrstad

Thanks to Tracy Syrstad for being a great coauthor.

Bob Umlas is the smartest Excel guy I know and is an awesome technical editor. At Pearson, Loretta Yates is an excellent acquisitions editor. Thanks to the Kughens for guiding this book through production. I updated this edition in residence at the Kola Mi Writing Camp. My sincere thanks to the staff there for keeping me on track.

Along the way, Ive learned a lot about VBA programming from the awesome community at the MrExcel.com message board. VoG, Richard Schollar, and Jon von der Heyden all stand out as having contributed posts that led to ideas in this book. Thanks to Pam Gensel for Excel macro lesson #1. Mala Singh taught me about creating charts in VBA.

My family was incredibly supportive during this time. Thanks to Mary Ellen Jelen, Robert F. Jelen, Barbara Jelen, and Robert K. Jelen.

Bill

Thank you to all the moderators at the MrExcel forum who keep the board organized, despite the best efforts of the spammers. Thank you to Joe4, RoryA, and Petersss for helping process all the forums contact emails.

Programming is a constant learning experience, and I really appreciate the clients who have encouraged me to program outside my comfort zone so that my skills and knowledge have expanded.

World of Warcraft is how I de-stress. Id like to give a special thank you to my in-game friends who help make the game so much fun and let me unwind: Louisiv (for teaching me how to tank), War (best co-tank ever), Amabeast (for pushing me out of my comfort zone), Chraz (for keeping my toon alive), and Jagdeule (for showing me how great an MM hunter could be).

And last, but not least, thanks to Bill Jelen. His site, MrExcel.com, is a place where thousands come for help. Its also a place where I, and others like me, have an opportunity to learn from and assist others.

Tracy

Bill Jelen, Excel MVP and the host of MrExcel.com, has been using spreadsheets since 1985, and he launched the MrExcel.com website in 1998. Bill was a regular guest on Call for Help with Leo Laporte and has produced more than 2,200 episodes of his daily video podcast, Learn Excel from MrExcel. He is the author of 57 books about Microsoft Excel and writes the monthly Excel column for Strategic Finance magazine. Before founding MrExcel.com, Bill Jelen spent 12 years in the trenchesworking as a financial analyst for finance, marketing, accounting, and operations departments of a $500 million public company. He lives in Merritt Island, Florida, with his wife, Mary Ellen.

Tracy Syrstad is a Microsoft Excel developer and author of nine Excel books. She has been helping people with Microsoft Office issues since 1997, when she discovered free online forums where anyone could ask and answer questions. Tracy found out she enjoyed teaching others new skills, and when she began working as a developer, she was able to integrate the fun of teaching with one-on-one online desktop sharing sessions. Tracy lives on an acreage in eastern South Dakota with her husband, one dog, two cats, one horse, and a variety of wild foxes, squirrels, and rabbits.

In this Introduction, you will:

• Find out what is in this book

• Have a peek at the future of VBA and Windows versions of Excel

• Learn about special elements and typographical conventions in this book

• Find out where to find code files for this book

As corporate IT departments have found themselves with long backlogs of requests, Excel users have discovered that they can produce the reports needed to run their businesses themselves using the macro language Visual Basic for Applications (VBA). VBA enables you to achieve tremendous efficiencies in your day-to-day use of Excel. VBA helps you figure out how to import data and produce reports in Excel so that you dont have to wait for the IT department to help you.

Your first questions are likely: Should I invest time in learning VBA? How long will Microsoft support VBA? Will the new JavaScript language announced in May 2018 replace VBA?

Your investments in VBA will serve you well until at least 2046.

The last macro language changefrom XLM to VBAhappened in 1993. XLM is still supported in Excel to this day. That was a case where VBA was better than XLM, but XLM is still supported 26 years later. If Microsoft ever switches from VBA to JavaScript, I expect that they will continue to support VBA in the Windows and Mac versions of Excel for the next 26 years.