Jelen Bill - 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

For sales outside of the U.S., please contact

International Sales

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 1,500 episodes of his daily video podcast, Learn Excel from MrExcel. He is the author of 39 books about Microsoft Excel and writes the monthly Excel column for Strategic Finance magazine. His Excel tips appear regularly in CFO Excel Pro Newsletter and CFO 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 near Akron, Ohio, with his wife, Mary Ellen.

Tracy Syrstad is the project manager for the MrExcel consulting team. She was introduced to Excel VBA by a co-worker who encouraged her to learn VBA by recording steps, and then modifying the code as needed. Her first macro was a simple lookup and highlight for a part index, although it hardly seemed simple when she did it. She was encouraged by her success with that macro and others that followed. Shell never forget the day when it all clicked. She hopes this book will bring that click to its readers sooner and with less frustration. She lives near Sioux Falls, South Dakota, with her husband, John.

Bill Jelen

For Mary Ellen Jelen

Tracy Syrstad

To Nate P. Oliver, who shared his love of Excel with the world.

Thanks to Tracy Syrstad for being a great coauthor and for doing a great job of managing all the consulting projects at MrExcel.com.

Bob Umlas is the smartest Excel guy I know and is an awesome technical editor. At Pearson, Loretta Yates is an excellent acquisitions editor.

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, and Oliver Holloway brought me up to speed with accessing SQL Server. Scott Ruble and Robin Wakefield at Microsoft helped with the charting chapter. And I give a tip of the cap to JWalk for that HWND trick.

At MrExcel.com, thanks to Barb Jelen, Wei Jiang, Tracy Syrstad, Tyler Nash, and Scott Pierson.

My family was incredibly supportive during this time. Thanks to Zeke Jelen, Dom Grossi, and Mary Ellen Jelen.

Bill

Juan Pablo Gonzalez Ruiz is a great programmer, and I really appreciate his time and patience showing me new ways to write better programs.

Thank you to all the moderators at the MrExcel forum who keep the board organized, despite the best efforts of the spammers.

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.

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

As the reader of this book, you are our most important critic and commentator. We value your opinion and want to know what were doing right, what we could do better, what areas youd like to see us publish in, and any other words of wisdom youre willing to pass our way.

We welcome your comments. You can email or write to let us know what you did or didnt like about this bookas well as what we can do to make our books better.

Please note that we cannot help you with technical problems related to the topic of this book.

When you write, please be sure to include this books title and author as well as your name and email address. We will carefully review your comments and share them with the author and editors who worked on the book.

Email:

Mail: Que Publishing
ATTN: Reader Feedback
800 East 96th Street
Indianapolis, IN 46240 USA

Visit our website and register this book at quepublishing.com/register for convenient access to any updates, downloads, or errata that might be available 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 business themselves using the macro language