Table Of ContentExcel® 2007
Macros
Made EASY
This page intentionally left blank
Excel® 2007
Macros
Made EASY
Gail Perry
New York Chicago San Francisco Lisbon
London Madrid Mexico City Milan New Delhi
San Juan Seoul Singapore Sydney Toronto
Copyright © 2009 by The McGraw-Hill Companies. All rights reserved. Except as permitted under the United States 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.
ISBN: 978-0-07-159959-7
MHID: 0-07-159959-2
The material in this eBook also appears in the print version of this title: ISBN: 978-0-07-159958-0, MHID: 0-07-159958-4.
All trademarks are trademarks of their respective owners. Rather than put a trademark symbol after every occurrence of a trademarked name, we use names
in an editorial fashion only, and to the benefit of the trademark owner, with no intention of infringement of the trademark. Where such designations appear
in this book, they have been printed with initial caps.
McGraw-Hill eBooks are available at special quantity discounts to use as premiums and sales promotions, or for use in corporate training programs. To
contact a representative please visit the Contact Us page at www.mhprofessional.com.
Information has been obtained by McGraw-Hill from sources believed to be reliable. However, because of the possibility of human or mechanical error by
our sources, McGraw-Hill, or others, McGraw-Hill 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 the use of such information.
TERMS OF USE
This is a copyrighted work and The McGraw-Hill Companies, Inc. (“McGraw-Hill”) and its licensors reserve all rights in and to the work. Use of this work
is subject to these terms. Except as permitted under the Copyright Act of 1976 and the right to store and retrieve one copy of the work, you may not decom-
pile, disassemble, reverse engineer, reproduce, modify, create derivative works based upon, transmit, distribute, disseminate, sell, publish or sublicense the
work or any part of it without McGraw-Hill’s prior consent. You may use the work for your own noncommercial and personal use; any other use of the work
is strictly prohibited. Your right to use the work may be terminated if you fail to comply with these terms.
THE WORK IS PROVIDED “AS IS.” McGRAW-HILL AND ITS LICENSORS MAKE NO GUARANTEES OR WARRANTIES AS TO THE ACCURA-
CY, ADEQUACY OR COMPLETENESS OF OR RESULTS TO BE OBTAINED FROM USING THE WORK, INCLUDING ANY INFORMATION THAT
CAN BE ACCESSED THROUGH THE WORK VIA HYPERLINK OR OTHERWISE, AND EXPRESSLY DISCLAIM ANY WARRANTY, EXPRESS OR
IMPLIED, INCLUDING BUT NOT LIMITED TO IMPLIED WARRANTIES OF MERCHANTABILITY OR FITNESS FOR A PARTICULAR PURPOSE.
McGraw-Hill and its licensors do not warrant or guarantee that the functions contained in the work will meet your requirements or that its operation will be
uninterrupted or error free. Neither McGraw-Hill nor its licensors shall be liable to you or anyone else for any inaccuracy, error or omission, regardless of
cause, in the work or for any damages resulting therefrom. McGraw-Hill has no responsibility for the content of any information accessed through the work.
Under no circumstances shall McGraw-Hill and/or its licensors be liable for any indirect, incidental, special, punitive, consequential or similar damages that
result from the use of or inability to use the work, even if any of them has been advised of the possibility of such damages. This limitation of liability shall
apply to any claim or cause whatsoever whether such claim or cause arises in contract, tort or otherwise.
About the Author
Gail Perry is a CPA, financial journalist, and the author of more than 20 books
on financial software, taxes, and personal finance. She is the managing editor
for AccountingWEB, a contributing editor for SMB Finance magazine, and an
instructor.
This page intentionally left blank
Contents at a Glance
Chapter 1 Recording Macros 1
Chapter 2 Editing Macros 19
Chapter 3 Creating Macros in Visual Basic 35
Chapter 4 Storing Macros 53
Chapter 5 Understanding Macro Commands 73
vii
Chapter 6 Using Visual Basic Subroutines
and Creating Functions 91
Chapter 7 Creating Interactive Macros 103
Chapter 8 Using Macros to Format Cells 119
Chapter 9 Using Variables in Macros 133
Chapter 10 Creating If/Then/Else Routines 147
Chapter 11 Exploring Loops 157
Chapter 12 Adding Controls to Your Worksheets 171
Index 183
This page intentionally left blank
Introduction
Welcome to Excel 2007 Macros Made Easy! This book was written for everyday
Excel users, like yourself, who want to free themselves from the drudgery
of repeating the same tasks over and over again. In this book, you’ll learn
powerful shortcuts and time-saving methods for expediting and remembering
tasks. Unlike other books in this series, it is focused on teaching you just
the things you need to know to get up and running with spreadsheet macros
quickly and easily. You won’t need to have a degree in programming or to set
aside hours and hours for study to be able to get through the lessons in this ix
little book and put them to work for you right away. In fact, you should be able
to create your first macro in about a minute.
Inside this book, you’ll discover how to teach Excel to do repetitive tasks
that take up valuable time and energy that you could spend on more productive
endeavors. You’ll also learn how to protect the integrity of your data by creating
macros that prevent incorrect information from being entered into your
spreadsheets. Using Excel macros, you can even interact with other users,
asking them to input specific data. So, get ready to jump in! It’s time to let Excel
macros free you from drudgery and make your work life smoother and easier.
Elements Used in This Book
Reading page after page of unbroken text, especially in a how-to book, can be
deadly dull. I tried to guard against that by providing lots of illustrations along
the way to help you see what you need to be doing on your computer, as well
as to provide visual interest. I also added a number of special elements to
