Excel VBA Macro Programming

Richard Shepherd

McGraw-Hill /Osborne

New York ‚  ‚  ‚ Chicago ‚  ‚  ‚  San Francisco
Lisbon ‚  ‚  ‚ London ‚  ‚  ‚ Madrid ‚  ‚  ‚ Mexico City ‚  ‚  ‚ Milan
New Delhi ‚  ‚  ‚ San Juan ‚  ‚  ‚ Seoul ‚  ‚  ‚ Singapore ‚  ‚  ‚ Sydney ‚  ‚  ‚ Toronto

The McGraw-Hill Companies

McGraw-Hill /Osborne
2100 Powell Street, 10th Floor
Emeryville, California 94608
U.S.A.

To arrange bulk purchase discounts for sales promotions, premiums, or fund-raisers, please contact McGraw-Hill /Osborne at the above address. For information on translations or book distributors outside the U.S.A., please see the International Contact Information page immediately following the index of this book.

Excel ‚ VBA Macro Programming

Copyright ‚ 2004 by The McGraw-Hill Companies. All rights reserved. Printed in the United States of America. Except as permitted under the Copyright Act of 1976, no part of this publication may be reproduced or distributed in any form or by any means, or stored in a database or retrieval system, without the prior written permission of the publisher, with the exception that the program listings may be entered, stored, and executed in a computer system, but they may not be reproduced for publication.

‚  ‚  ‚ 1234567890 FGR FGR 01987654

ISBN 0-07-223144-0

Publisher: ‚  ‚  Brandon A. Nordin
Vice President & Associate Publisher: ‚  ‚  Scott Rogers
Senior Acquisitions Editor: ‚  ‚  Nancy Maragioglio
Project Editor: ‚  ‚  Emily Rader
Acquisitions Coordinator: ‚  ‚  Athena Honore
Technical Editor: ‚  ‚  David Schultz
Copy Editor: ‚  ‚  Sally Engelfried
Proofreader: ‚  ‚  Linda Medoff
Indexer: ‚  ‚  Valerie Robbins
Composition: ‚  ‚  Jean Butterfield, Tara A. Davis
Illustrators: ‚  ‚  Kathleen Edwards, Melinda Lytle, Michael Mueller
Series Designer: ‚  ‚  Roberta Steele
Cover Illustration: ‚  ‚  Theresa Havener

This book was composed with Corel VENTURA Publisher.

Information has been obtained by McGraw-Hill /Osborne from sources believed to be reliable. However, because of the possibility of human or mechanical error by our sources, McGraw-Hill /Osborne, or others, McGraw-Hill /Osborne does not guarantee the accuracy, adequacy, or completeness of any information and is not responsible for any errors or omissions or the results obtained from use of such information.

To my wife, Elaine, and my son, Alexander.

About the Author

Richard Shepherd has worked for many years for major companies in the United Kingdom writing spreadsheet macros to solve specific problems. He has developed advanced spreadsheets and macros for budgeting, business planning, and profit and loss reporting working for blue- chip companies within the U.K. He originally started working with Lotus 123 spreadsheets, but moved some years ago to Microsoft Excel. He qualified as an accountant with the Chartered Association of Certified Accountants in 1976 and has been a Fellow since 1981.

About the Technical Editor

David Schultz has worked for 12 years as a programmer and engineer in the computer field at a major retailer in the United States. He has worked on a wide variety of business systems using different languages on many platforms. David enjoys a good challenge when programming ‚debugging is the best part! When not working on computers, David enjoys his family and collecting coin-op games .

Acknowledgments

With grateful thanks to all the people at Osborne/McGraw-Hill who have made this book happen. Special thanks to Nancy Maragioglio, who has driven this project along and dealt with the vast numbers of e- mails this book generated. Others who deserve a special mention for all the help they have given to the project include Wendy Rinaldi, Athena Honore, David Schultz, and Emily Rader.

Finally, a big thank you to Elaine and Alexander for all their support on this project.