Excel Programming: Your Visual Blueprint for Creating Interactive Spreadsheets

352 Pages·2001·10.072 MB·English

by  Jinjer Simon

013646-X FM.F 10/16/01 2:35 PM Page i
Excel Programming
Your visual blueprint for
creating interactive spreadsheets
by Jinjer Simon
TM
From
®
&
Best-Selling Books • Digital Downloads • e-Books • Answer Networks • e-Newsletters • Branded Web Sites • e-Learning
New York, NY • Cleveland, OH • Indianapolis, IN
013646-X FM.F 10/16/01 2:35 PM Page ii
Excel Programming: Your visual blueprint for
creatinginteractivespreadsheets Trademark Acknowledgments
Published by Hungry Minds, the Hungry Minds logo, Visual, the Visual
Hungry Minds, Inc. logo, Read Less - Learn More and related trade dress are
909 Third Avenue
registered trademarks or trademarks of Hungry Minds, Inc.,
New York, NY 10022
www.hungryminds.com in the United States and/or other countries and may not be
Copyright © 2002 Hungry Minds, Inc. used without written permission. The maranGraphics logo
is a registered trademark or trademark of maranGraphics,
Certain design and illustrations are copyright © 1992-2002 maranGraphics, Inc.,
used with maranGraphics’ permission. All rights reserved. No part of this book, Inc. All other trademarks are the property of their
including interior design, cover design, and icons, may be reproduced or respective owners. Hungry Minds, Inc. and maranGraphics,
transmitted in any form, by any means (electronic, photocopying, recording, or Inc. are not associated with any product or vendor
otherwise) without the prior written permission of the publisher.
mentioned in this book.
maranGraphics, Inc.
5755 Coopers Avenue FOR PURPOSES OF ILLUSTRATING THE CONCEPTS
Mississauga, Ontario, Canada
L4Z 1R9 AND TECHNIQUES DESCRIBED IN THIS BOOK, THE
AUTHOR HAS CREATED VARIOUS NAMES, COMPANY
Library of Congress Control Number: 2001092900
ISBN: 0-7645-3646-X NAMES, MAILING, E-MAIL AND INTERNET
Printed in the United States of America ADDRESSES, PHONE AND FAX NUMBERS AND SIMILAR
10 9 8 7 6 5 4 3 2 1 INFORMATION, ALL OF WHICH ARE FICTITIOUS. ANY
1V/QX/RR/QR/IN RESEMBLANCE OF THESE FICTITIOUS NAMES,
Distributed in the United States by Hungry Minds, Inc. ADDRESSES, PHONE AND FAX NUMBERS AND SIMILAR
INFORMATION TO ANY ACTUAL PERSON, COMPANY
Distributed by CDG Books Canada Inc. for Canada; by Transworld Publishers
Limited in the United Kingdom; by IDG Norge Books for Norway; by IDG Sweden AND/OR ORGANIZATION IS UNINTENTIONAL AND
Books for Sweden; by IDG Books Australia Publishing Corporation Pty. Ltd. for PURELY COINCIDENTAL.
Australia and New Zealand; by TransQuest Publishers Pte Ltd. for Singapore,
Malaysia, Thailand, Indonesia, and Hong Kong; by Gotop Information Inc. for Permissions
Taiwan; by ICG Muse, Inc. for Japan; by Intersoft for South Africa; by Eyrolles for
France; by International Thomson Publishing for Germany, Austria and
Switzerland; by Distribuidora Cuspide for Argentina; by LR International for Brazil; maranGraphics
by Galileo Libros for Chile; by Ediciones ZETA S.C.R. Ltda. for Peru; by WS
Computer Publishing Corporation, Inc., for the Philippines; by Contemporanea de Certain text and illustrations by maranGraphics, Inc., used
Ediciones for Venezuela; by Express Computer Distributors for the Caribbean and with maranGraphics’ permission.
West Indies; by Micronesia Media Distributor, Inc. for Micronesia; by Chips
Computadoras S.A. de C.V. for Mexico; by Editorial Norma de Panama S.A. for Microsoft
Panama; by American Bookshops for Finland.
©2002 Microsoft Corporation. All rights reserved.
For U.S. corporate orders, please call maranGraphics at 800-469-6616 or fax
905-890-9434.
For general information on Hungry Minds’ products and services please contact our
Customer Care Department within the U.S. at 800-762-2974, outside the U.S. at
317-572-3993 or fax [phone].
For sales inquiries and reseller information, including discounts, premium and bulk
quantity sales, and foreign-language translations, please contact our Customer Care
Department at 800-434-3422, fax [phone], or write to Hungry Minds, Inc.,
Attn: Customer Care Department, 10475 Crosspoint Boulevard, Indianapolis, IN
46256.
For information on licensing foreign or domestic rights, please contact our Sub-
Rights Customer Care Department at 212-884-5000.
For information on using Hungry Minds’ products and services in the classroom or
for ordering examination copies, please contact our Educational Sales Department
at 800-434-2086 or fax [phone].
For press review copies, author interviews, or other publicity information, please
contact our Public Relations department at 317-572-3168 or fax [phone].
For authorization to photocopy items for corporate, personal, or educational use,
is a trademark of
please contact Copyright Clearance Center, 222 Rosewood Drive, Danvers, MA
01923, or fax [phone]. Hungry Minds, Inc.
Screen shots displayed in this book are based on pre-released software and are
subject to change.
LIMIT OF LIABILITY/DISCLAIMER OF WARRANTY:THE PUBLISHER AND AUTHOR HAVE USED
THEIR BEST EFFORTS IN PREPARING THIS BOOK. THE PUBLISHER AND AUTHOR MAKE NO
ROEFP TRHEES ECNOTNATTEIONNTSS OORF TWHAIRSR BAONOTKIE ASN WDI TSPHE RCEIFSIPCEACLTL YT OD ITSHCEL AAICMC AUNRYA CIMY POLRI ECDO WMAPLRERTAENNTEIESSS U.S. Corporate Sales U.S. Trade Sales
OF MERCHANTABILITY OR FITNESS FOR A PARTICULAR PURPOSE. THERE ARE NO
WARRANTIES WHICH EXTEND BEYOND THE DESCRIPTIONS CONTAINED IN THIS Contact maranGraphics Contact Hungry Minds
POARR AWGRRAITPTHE. NN OS WALAERSR ANMTAYT EMRAIYA LBSE. CTRHEAET EADC COUR REAXCTYE NADNEDD BCY OSMALPELSE TREENPREESSSE NOTFA TITVHEES at [phone] or at [phone]
INFORMATION PROVIDED HEREIN AND THE OPINIONS STATED HEREIN ARE NOT fax [phone]. or [phone].
GUARANTEED OR WARRANTED TO PRODUCE ANY PARTICULAR RESULTS, AND THE ADVICE
AND STRATEGIES CONTAINED HEREIN MAY NOT BE SUITABLE FOR EVERY INDIVIDUAL.
NEITHER THE PUBLISHER NOR AUTHOR SHALL BE LIABLE FOR ANY LOSS OF PROFIT OR ANY
OTHER COMMERCIAL DAMAGES, INCLUDING BUT NOT LIMITED TO SPECIAL, INCIDENTAL,
CONSEQUENTIAL, OR OTHER DAMAGES.
013646-X FM.F 10/16/01 2:35 PM Page iii
Excel Programming
Your visual blueprint for
creating interactive spreadsheets
013646-X FM.F 10/16/01 2:35 PM Page iv
maranGraphics is a family-run business
located near Toronto, Canada.
At maranGraphics, we believe in producing great rebuild every screen shot to provide the best clarity
computer books — one book at a time. possible, making our screen shots the most precise and
easiest to read in the industry. We strive for perfection,
maranGraphics has been producing high-technology
and believe that the time spent handcrafting each
products for over 25 years, which enables us to offer the
element results in the best computer books money can
computer book community a unique communication
buy.
process.
Thank you for purchasing this book. We hope you
Our computer books use an integrated communication
enjoy it!
process, which is very different from the approach used
in other computer books. Each spread is, in essence, a Sincerely,
flow chart — the text and screen shots are totally
incorporated into the layout of the spread. Introductory Robert Maran
text and helpful tips complete the learning experience.
President
maranGraphics’ approach encourages the left and right
maranGraphics
sides of the brain to work together — resulting in faster
orientation and greater memory retention. [e-mail]
Above all, we are very proud of the handcrafted nature www.maran.com
of our books. Our carefully-chosen writers are experts
www.hungryminds.com/visual
in their fields, and spend countless hours researching
and organizing the content for each topic. Our artists
013646-X FM.F 10/16/01 2:35 PM Page v
CREDITS
Acquisitions, Editorial, and Production
Media Development Book Design
Project Editor maranGraphics®
Maureen Spears Production Coordinator
Acquisitions Editor Dale White
Jen Dorsey Layout
Product Development Supervisor LeAndra Johnson, Adam Mancilla,
Lindsay Sandman Kristin Pickett, Jill Piscitelli,
Copy Editors ErinZeltner
Timothy Borek, Jill Mazurczyk Screen Artists
Technical Editor Ronda David-Burroughs,
Allen Wyatt DavidGregory, Mark Harris,
Jill A. Proll
Editorial Manager
Rev Mengle Cover Illustration
Russ Marini
Media Development Manager
Laura Carpenter VanWinkle Proofreaders
John Bitter, Susan Moritz,
Permissions Editor
CarlPierce, Christy Pingleton,
Laura Moss
Charles Spencer
Media Development Specialist
Indexer
Angela Denny
TECHBOOKS Production Services
Editorial Assistants
Special Help
Amanda Foxworth, Jean Rogers
Christy Pingleton, Leslie Kersey
GENERAL AND ADMINISTRATIVE
Hungry Minds Technology Publishing Group:Richard Swadley, Senior Vice President and Publisher; Mary Bednarek,
Vice President and Publisher, Networking; Joseph Wikert, Vice President and Publisher, Web Development Group;
MaryC. Corder, Editorial Director, Dummies Technology; Andy Cummings, Publishing Director, Dummies Technology;
Barry Pruett, Publishing Director, Visual/Graphic Design
Hungry Minds Manufacturing:Ivor Parker, Vice President, Manufacturing
Hungry Minds Marketing:John Helmus, Assistant Vice President, Director of Marketing
Hungry Minds Production for Branded Press:Debbie Stailey, Production Director
Hungry Minds Sales:Michael Violano, Vice President, International Sales and Sub Rights
013646-X FM.F 10/16/01 2:35 PM Page vi
ABOUT THE AUTHOR
Jinjer Simonhas been actively involved in the computer industry for the past 17 years. Her involvement
in the industry has included programming, providing software technical support, training end-users,
developing written and online user documentation, creating software tutorials, and developing Web
sites. She is the author of several computer books, including Windows CE For Dummiesand Windows CE
2 For Dummies.
Jinjer and her husband live in Coppell, Texas with their two children. She currently works as a
consultant for MillenniSoft, Inc., providing Web site development and online documentation
development.
013646-X FM.F 10/16/01 2:35 PM Page vii
AUTHOR’S ACKNOWLEDGMENTS
As an author, it is always exciting to finish the last chapter and send it to the publisher. At that point my
work ends and many other individuals take on the responsibility of making sure the book actually
makes it to the shelf. Because there are so many individuals involved, I am always concerned about
overlooking someone. Therefore, before I thank specific individuals I would like to acknowledge the
efforts of everyone at Hungry Minds who had a hand in completing the production of this book.
I really enjoyed working with the Hungry Minds team. Jen Dorsey, my acquisitions editor, did a great job
of getting this project up and going, and dealing with all the little issues along the way. My project
editor, Maureen Spears, was great to deal with. She was very patient as I learned how to develop using
the visual blueprint style. Working along with her on the copy editing were Tim Borek and Jill
Mazurczyk, who both did a great job making sure I said everything correctly. Along with them, Leslie
Kersey and Amanda Foxworth helped make the process run smoothly. My technical editor, Allen Wyatt
did a fantastic job of making sure the content is technically accurate.
I also want to acknowledge the efforts of the graphics and production staff who tied everything together.
I want to acknowledge my agents, Neil Salkind and David Rogelberg at Studio B, for helping me get this
project. Finally, I want to thank my husband, Richard, and children, Alex and Ashley, for their patience
while I completed this project.
013646-X FM.F 10/16/01 2:35 PM Page viii
TABLE OF CONTENTS
HOW TO USE THIS BOOK
. . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . .XIV
1) GETTING STARTED WITH EXCEL MACROS
An Introduction to Macros ................................................................................................2
Record a Macro ....................................................................................................................4
Run a Macro ........................................................................................................................6
Create and Launch a Keyboard Shortcut ..........................................................................8
Delete a Macro from a Workbook ..................................................................................10
Delete from the Personal Macro Workbook ..................................................................12
Assign a Macro to a Toolbar Button ................................................................................14
Assign a Macro to a Menu ................................................................................................16
Set Macro Security ............................................................................................................18
2) USING THE VISUAL BASIC EDITOR
An Introduction tothe Visual Basic Editor ......................................................................20
Activate the Visual Basic Editor ........................................................................................22
Arrange the Visual Basic Editor Windows ......................................................................24
Set Properties for a Project ..............................................................................................26
Set Display Options for the Code Window ....................................................................28
Add a New Module ............................................................................................................30
Remove a Module ..............................................................................................................32
Rename a Macro ................................................................................................................34
Create a Startup Macro ....................................................................................................36
Hide a Macro ......................................................................................................................38
Assign a Digital Signature to a Macro ..............................................................................40
Update a Recorded Macro ................................................................................................42
3) VBA PROGRAMMING BASICS
An Introduction to VBA ....................................................................................................44
Create a Subroutine ..........................................................................................................50
Create a Function ..............................................................................................................52
Declare a Variable ..............................................................................................................54
Perform Mathematical Calculations ................................................................................56
Create a Constant ..............................................................................................................58
Comment Your Code ........................................................................................................60
Join Two Strings ..................................................................................................................62
viii
013646-X FM.F 10/16/01 2:35 PM Page ix
EXCEL PROGRAMMING:
Your visual blueprint for
creating interactive spreadsheets
4) WORKING WITH THE EXCEL OBJECT MODEL
An Introduction to the Excel Object Model ..................................................................64
Using the Object Browser ................................................................................................66
Create an Object Variable ................................................................................................68
Change the Properties of an Object ................................................................................70
Compare Object Variables ................................................................................................72
Using an Object Method ..................................................................................................74
Display a Built-in Dialog Box ............................................................................................76
5) USING VARIABLES AND ARRAYS
Assign Values to Variables ................................................................................................78
Using Global Variables ......................................................................................................80
Declare an Array ................................................................................................................82
Declare a Multidimensional Array ....................................................................................84
Convert a List into an Array ..............................................................................................86
Redimension an Array ......................................................................................................88
Create a User-Defined Data Type ....................................................................................90
6) ADDING CONTROL STATEMENTS
Execute a Task While a Condition is True ......................................................................92
Perform Multiple Tasks Until a Condition is Met ..........................................................94
Execute Tasks a Specific Number of Times ....................................................................96
Using the For Each Next Loop ..........................................................................................98
Conditionally Execute a Group of Statements ............................................................100
Execute a Statement Based Upon the Value ................................................................102
Jump to a Specific Location in a Macro ........................................................................104
Conditionally Call a Subroutine ....................................................................................106
ix
013646-X FM.F 10/16/01 2:35 PM Page x
TABLE OF CONTENTS
7) USING BUILT-IN FUNCTIONS AND STATEMENTS
Using Excel Worksheet Functions ..................................................................................108
Using the MsgBox Function ............................................................................................110
Using the InputBox Function ..........................................................................................112
Retrieve Current Date and Time ....................................................................................114
Determine the Amount of Time Between Dates ..........................................................116
Format a Date Expression ..............................................................................................118
Format a Numeric Expression ........................................................................................120
Remove Extra Spacing from a String ..............................................................................122
Return a Portion of a String ............................................................................................124
Compare Two Strings ......................................................................................................126
8) DEBUGGING MACROS
Debugging Basics ............................................................................................................128
Debug a Procedure with Inserted Break Points ..........................................................130
Using Watch Expressions to Debug a Procedure ........................................................132
Step through a Procedure ..............................................................................................134
Resume Execution if an Error is Encountered ..............................................................136
Process a Runtime Error ..................................................................................................138
9)WORKING WITH OTHER WORKBOOKS AND FILES
Open a Workbook ..........................................................................................................140
Open a Text File as a Workbook ....................................................................................142
Open a File Requested by the User ..............................................................................144
Save a Workbook ............................................................................................................146
Save Workbook in Format Specified by User ..............................................................148
Determine if a Workbook is Open ................................................................................150
Close a Workbook ..........................................................................................................152
Create a New Workbook ................................................................................................154
Delete a File ......................................................................................................................156
Find a File ..........................................................................................................................158
10) WORKING WITH WORKSHEETS
Add a Sheet ......................................................................................................................160
Delete a Sheet ..................................................................................................................162
Move a Sheet ....................................................................................................................164
x