Tom Urtis - Excel VBA 24-Hour Trainer

Published by
Wiley Publishing, Inc.
10475 Crosspoint Boulevard
Indianapolis, IN 46256
www.wiley.com

Copyright 2011 by Wiley Publishing, Inc., Indianapolis, Indiana

Published by Wiley Publishing, Inc., Indianapolis, Indiana

Published simultaneously in Canada

ISBN: 978-0-470-89069-1
978-1-118-08764-0 (ebk)
978-1-118-08760-2 (ebk)
978-1-118-08755-8 (ebk)

Manufactured in the United States of America

10 9 8 7 6 5 4 3 2 1

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 permitted under Sections 107 or 108 of the 1976 United States Copyright Act, without either the prior written permission of the Publisher, or authorization through payment of the appropriate per-copy fee to the Copyright Clearance Center, 222 Rosewood Drive, Danvers, MA 01923, (978) 750-8400, fax (978) 646-8600. 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 .

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 Web site 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 United States at (877) 762-2974, outside the United States at (317) 572-3993 or fax (317) 572-4002.

Wiley also publishes its books in a variety of electronic formats. Some content that appears in print may not be available in electronic books.

Library of Congress Control Number: 2011922792

Trademarks: Wiley and the Wiley logo are trademarks or registered trademarks of John Wiley & Sons, Inc. and/or its affiliates, in the United States and other countries, and may not be used without written permission. Excel is a registered trademark of Microsoft Corporation. All other trademarks are the property of their respective owners. Wiley Publishing, Inc. is not associated with any product or vendor mentioned in this book.

To Bill and Mary Urtis

Tom Urtis is a Microsoft Office developer and programming expert with over 20 years of experience in developing customized Office programs with Visual Basic for Applications (VBA) and Application Programming Interface (API). In 2000 Tom founded Atlas Programming Management ( www.atlaspm.com ), an Office solutions company in Silicon Valley that specializes in Excel to provide consulting, project development, training, and support for a diverse international clientele. As an Excel trainer, Tom created the Excel Aptitude Test (XAT, www.xatcorp.com ), which measures knowledge of Excel for a customized training curriculum based on the test score.

Tom is co-author of Holy Macro! Its 2,500 Excel VBA Examples, and he has served as a technical editor and consultant for other Excel books and training materials. Tom received the Most Valuable Professional award for Excel from Microsoft in 2008, and it has been renewed each year thereafter in recognition of his Excel skills and contributions to the Excel community. Tom is one of some 100 Excel experts worldwide who hold the Excel MVP award.

A native of New York state, Tom is a graduate of Michigan State University, and has lived and worked in the San Francisco Bay Area for 30 years. Tom is an avid sports fan and collector of rare sports memorabilia, and he enjoys the outdoor life that California offers. He can be reached by email, at .

Mike Alexander is a Microsoft MVP and the author of several books on advanced business analysis with Microsoft Access and Excel. He has more than 15 years of experience consulting and developing Office solutions. In his spare time he runs a free tutorial site, www.datapigtechnologies.com , where he shares basic Access and Excel tips with the Office community.

Executive Editor

Carol Long

Project Editor

Christopher J. Rivera

Development Editor

Kezia Endsley

Technical Editor

Michael Alexander

Production Editor

Kathleen Wisor

Copy Editor

Kim Cofer

Editorial Director

Robyn B. Siesky

Editorial Manager

Mary Beth Wakefield

Freelancer Editorial Manager

Rosemarie Graham

Associate Director of Marketing

David Mayhew

Production Manager

Tim Tate

Vice President and Executive Group Publisher

Richard Swadley

Vice President and Executive Publisher

Barry Pruett

Associate Publisher

Jim Minatel

Project Coordinator, Cover

Katie Crocker

Compositor

JoAnn Kolonick, Happenstance Type-O-Rama

Proofreader

Louise Watson, Word One

Indexer

Robert Swanson

Cover Designer

Michael Trent

Cover Image

Richard Cano

The production of this book was made possible by the combined efforts of highly talented people, starting with the entire Wiley Publishing team. Thanks to Carol Long, the Executive Editor, who got the project approved and kept the process moving from start to finish. Thanks to Mike Alexander, who introduced me to Wiley Publishing and was the Technical Editor. Thanks to Ed Connor and Christopher Rivera, the Project Editors, and to Kim Cofer, who edited copy. Thanks to Kezia Endsley, the Design Editor. Thanks to Rosemarie Graham, Carol Kessel, Mary Beth Wakefield, and Ashley Zurcher of Wiley Publishing for all their assistance. Many thanks to the Excel development team at Microsoft Corporation for improving Excel with each new release of Office, while considering suggestions from Excel users. Finally, a special thanks to the global Excel community. Youve shown me creative ways to use Excel over the years, and taught me how to explain technical concepts to beginning Excel users.

Congratulations on making two excellent choices! You want to learn programming for Microsoft Excel with Visual Basic for Applications (VBA), and youve purchased this book to teach you. Excel is the most powerful and widely used spreadsheet application in the world. VBA enables you to become much more productive and efficient, while getting your everyday Excel tasks done more quickly and with fewer errors. Youll gain a programming skill that is in high demand, which will improve your value in the workplace and your marketability when searching for employment.