MS Excel VBA Programming For The Absolute Beginners 2nd Edit 2

505 Pages·2005·12.09 MB·English

DUANE BIRNBAUM

Microsoft
®
Excel VBA
Programming
for the Absolute
Beginner
Second Edition

© 2005 by Thomson Course Technology PTR. All rights reserved. No
part of this book may be reproduced or transmitted in any form or by
any means, electronic or mechanical, including photocopying, record-
ing, or by any information storage or retrieval system without written
permission from Thomson Course Technology PTR, except for the
inclusion of brief quotations in a review.

The Premier Press and Thomson Course Technology PTR logo and
related trade dress are trademarks of Thomson Course Technology PTR
and may not be used without written permission.

Microsoft is a registered trademark of Microsoft Corporation in the
United States and/or other countries.

All other trademarks are the property of their respective owners.

Important: Thomson Course Technology PTR cannot provide software
support. Please contact the appropriate software manufacturer’s tech-
nical support line or Web site for assistance.

Thomson Course Technology PTR and the author have attempted
throughout this book to distinguish proprietary trademarks from
descriptive terms by following the capitalization style used by the
manufacturer.

Information contained in this book has been obtained by Thomson
Course Technology PTR from sources believed to be reliable. However,
because of the possibility of human or mechanical error by our sources,
Thomson Course Technology PTR, or others, the Publisher 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. Readers should be particularly
aware of the fact that the Internet is an ever-changing entity. Some
facts may have changed since this book went to press.

Educational facilities, companies, and organizations interested in
multiple copies or licensing of this book should contact the publisher
for quantity discount information. Training manuals, CD-ROMs, and
portions of this book are also available individually or can be tailored
for specific needs.

ISBN: 1-59200-729-5
Library of Congress Catalog Card Number: 2004114911
Printed in the United States of America
05 06 07 08 09 BH 10 9 8 7 6 5 4 3 2 1

Thomson Course Technology PTR,
a division of Thomson Course Technology
25 Thomson Place
Boston, MA 02210
http://www.courseptr.com

Publisher and General Manager
of Course Technology PTR:
Stacy L. Hiquet

Associate Director of Marketing:
Sarah O’Donnell

Marketing Manager:
Heather Hurley

Manager of Editorial Services:
Heather Talbot

Acquisitions Editor:
Mitzi Koontz

Senior Editor:
Mark Garvey

Marketing Coordinator:
Jordan Casey

Project Editor:
Scott Harris/Argosy Publishing

Technical Reviewer:
Arlie Hartman

PTR Editorial Services
Coordinator:
Elizabeth Furbish

Copy Editor:
D. A. de la Mora

Interior Layout Tech:
Shawn Morningstar

Cover Designer:
Mike Tanamachi

CD-ROM Producer:
Keith Davenport

Indexer:
Nancy Fulton

Proofreader:
Jan Cocker

First, a special thank you goes out to my family:

• My wife Jill, for putting up with the late nights and weekends I spent
writing

• My 8-year old son Aaron, who thinks it’s cool that his Dad writes such
long books with so many words, but wishes it included chapters on
dragons or wizards

• My 5-year old son Joshua, who wished his Dad would have played more
games with him instead of working on this book. Don’t worry, Josh;
because of the guilt trip you sent me on, I’ll more than make it up to you.

I would also like to thank Scott Harris at Argosy Publishing, Mitzi Koontz, and all
of the other contributors associated with Course Technology for their invaluable
help in putting this book together.

Acknowledgments

D

uane Birnbaum began programming in graduate school, where he wrote
custom software for interfacing the electronic equipment required for his
experiments and analyzing the data obtained from them. Since completing
his Ph.D. in physical chemistry in 1991, he has worked as a post-doctoral and research
scientist in academia and industry while continuing to teach on a part-time basis.
He has been teaching courses in introductory programming, database design, and
data analysis in the Computer Science department at Indiana University/Purdue
University at Indianapolis for the past 8 years.

About the Author

Introduction . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . x

Chapter 1
Visual Basic for Applications with Excel . . . . . . . .1

Project: Colorful Stats. . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 1
Installing and Enabling VBA . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 3
The VBA Integrated Development Environment (IDE). . . . . . . . . . . . . . . . . . . . . . . 5
Getting to the IDE from Excel . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 5
Components of the IDE . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 5
Programming Components within Excel. . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 10
Macro Selection . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 10
The Visual Basic Toolbar . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 11
Getting Help with VBA . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 15
VBA Help . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 16
Constructing the Colorful Stats Program . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 17
Requirements of the Colorful Stats Program . . . . . . . . . . . . . . . . . . . . . . . . . . 18
Chapter Summary . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 22

Chapter 2
Beginning Programs with VBA . . . . . . . . . . . . . . . . .25

Project: Biorhythms and the Time of Your Life . . . . . . . . . . . . . . . . . . . . . . . . . . . 25
Variables, Data Types, and Constants . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 26
Declaring Variables. . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 27
Data Types . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 31
Constants . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 40
Simple Input and Output with VBA . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 40
Collecting User Input with InputBox() . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 40
Output with MsgBox() . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 41
Manipulating Strings with VBA Functions . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 42
Fun with Strings . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 43
Constructing the Biorhythms and the Time of Your Life Program . . . . . . . . . . 47
Requirements for Biorhythms and the Time of Your Life . . . . . . . . . . . . . . . 48
Designing Biorhythms and the Time of Your Life . . . . . . . . . . . . . . . . . . . . . . 48
Coding Biorhythms and the Time of Your Life. . . . . . . . . . . . . . . . . . . . . . . . . 49
Chapter Summary . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 54

Contents

Chapter 3
Procedures and Conditions . . . . . . . . . . . . . . . . . . .55

Project: Poker Dice . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 55
VBA Procedures . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 56
Event Procedures. . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 56
Private, Public, and Procedure Scope . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 60
Sub Procedures . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 60
Function Procedures . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 64
Logical Operators with VBA . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 69
Conditionals and Branching. . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 71
Constructing the Poker Dice Program . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 77
Requirements for Poker Dice . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 77
Designing Poker Dice . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 78
Coding Poker Dice. . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 81
Chapter Summary . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 97

Chapter 4
Loops and Arrays . . . . . . . . . . . . . . . . . . . . . . . . . . . . .99

Project: Math Game. . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 100
Looping with VBA . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 100
Do Loops . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 101
For Loops. . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 103
Input Validation. . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 107
Validation with the InputBox() Function. . . . . . . . . . . . . . . . . . . . . . . . . . . . . 107
Validation with a Spreadsheet Cell. . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 109
Arrays . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 109
One-Dimensional Arrays . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 111
Multi-Dimensional Arrays . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 114
Dynamic Arrays . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 116
Programming Formulas into Worksheet Cells . . . . . . . . . . . . . . . . . . . . . . . . . . . 120
A1 Style References. . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 120
R1C1-Style References . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 122
Constructing the Math Game. . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 123
Requirements for the Math Game . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 123
Designing the Math Game . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 124
Coding the Math Game Program . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 134
Chapter Summary . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 147

vi
Microsoft Excel VBA Programming for the Absolute Beginner, Second Edition

Chapter 5
Basic Excel Objects . . . . . . . . . . . . . . . . . . . . . . . . . .149

Project: Battlecell. . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 149
VBA and Object-Oriented Programming . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 150
Objects Defined . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 151
VBA Collection Objects. . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 153
The Object Browser . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 155
Top-Level Excel Objects. . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 160
The Application Object . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 160
The Workbook and Window Objects . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 161
The Worksheet Object . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 169
The Range Object . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 170
Working with Objects . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 173
Constructing Battlecell . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 177
Requirements for Battlecell . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 177
Designing Battlecell . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 180
Coding Battlecell. . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 182
Chapter Summary. . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 205

Chapter 6
VBA UserForms and Additional Controls . . . .207

Project: Blackjack . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 208
Designing Forms with VBA . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 208
Adding a Form to a Project. . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 209
Components of the UserForm Object . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 210
Adding ActiveX Controls to a Form . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 212
Showing and Hiding Forms . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 213
Modal Forms. . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 214
Designing Custom Dialog Boxes Using Forms . . . . . . . . . . . . . . . . . . . . . . . . 215
Derived Data Types in VBA . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 233
Defining Custom Data Types in VBA . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 234
Defining Enumerated Types in VBA. . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 235
Chapter Project: Blackjack . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 237
Requirements for Blackjack . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 237
Designing Blackjack. . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 239
Writing the Code for Blackjack . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 246
Chapter Summary. . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 266

vii
Contents

Chapter 7
Error Handling, Debugging,
and Basic File I/O . . . . . . . . . . . . . . . . . . . . . . . . . . . .269

Project: Word Find . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 269
Error Handling. . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 270
Using the On Error Statement . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 271
Debugging. . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 274
Break Mode. . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 274
The Immediate Window . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 275
The Watch Window . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 277
The Locals Window. . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 279
File Input and Output (I/O) . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 279
File I/O Using Workbook and Worksheet Objects . . . . . . . . . . . . . . . . . . . . . 281
Using VBA File I/O Methods . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 282
Chapter Project: Word Find. . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 296
Requirements for Word Find . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 296
Designing Word Find . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 298
Writing the Code for Word Find . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 301
Chapter Summary. . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 326

Chapter 8
Using XML with Excel-VBA Projects . . . . . . . . . .329

Project: Revisiting the Math Game . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 329
Introduction to XML. . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 330
What Is XML? . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 331
XML Syntax. . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 334
XML Schemas. . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 337
XML Validation . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 338
XML and Excel . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 338
XML and VBA . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 343
The XmlMap Object . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 345
Chapter Project: The Math Game. . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 349
Requirements for the Math Game Program . . . . . . . . . . . . . . . . . . . . . . . . . . 350
Designing the Math Game . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 351
Coding the Math Game Program . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 357
Chapter Summary. . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 373

viii
Microsoft Excel VBA Programming for the Absolute Beginner, Second Edition

Chapter 9
Excel Charts . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . .377

Project: The Alienated Game . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 377
The Chart Object . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 378
Accessing Existing Charts . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 379
Manipulating Charts . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 383
Creating Charts . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 388
Chart Events. . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 392
Chapter Project: The Alienated Game. . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 396
Requirements for the Alienated Game . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 397
Designing the Alienated Game . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 397
Coding the Alienated Game . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 402
Chapter Summary . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 425

Chapter 10
VBA Shapes . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . .427

Project: Excetris . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 427
The Shapes Collection and Shape Objects. . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 428
Manipulating a Shape Object. . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 430
The ShapeRange Collection Object . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 432
Activating Shape Objects. . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 435
The OLEObjects Collection . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 436
Chapter Project: Excetris . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 439
Requirements for Excetris. . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 439
Designing Excetris . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 441
Coding Excetris . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 445
Chapter Summary. . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 471
A Final Word. . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 471

Index . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . .473

ix
Contents

V

isual Basic for Applications (VBA for short) is a programming environment
designed to work with Microsoft’s Office applications (Word, Excel,
Access, and PowerPoint). Components in each application (for example,
worksheets or documents) are exposed as objects to the programmer to use and
manipulate to a desired end. Almost anything you can do through the normal use
of the Office application can also be automated through programming.

VBA is a complete programming language, but you can’t use it outside the appli-
cation in which it is integrated. This does not mean VBA can be integrated only
with Office programs. Any software vendor that decides to implement VBA can
include it with their application.

VBA is relatively easy to learn, but to use it in a new application, you must first
become familiar with the object model of the application. For example, the

Document and Dictionary objects are specific to the Word object model, whereas
the Workbook, Worksheet, and Range objects are specific to the Excel object model.
As you proceed through this book, you will see that the Excel object model is
fairly extensive; however, if you are familiar with Excel, you will find that using
these objects is generally straightforward.

Why VBA?

As a beginning language, VBA will suit your needs well. VBA is not as vast as many
popular languages because such extensiveness is simply unnecessary. VBA was
built to work with and extend the abilities of Office applications, so it doesn’t
need the substance of a programming language used to build full-blown appli-
cations from scratch. The relative simplicity of VBA makes it less intimidating
and easier for you to learn. VBA, however, does share many of the programming
constructs common to all languages, so it also serves as a great introduction to
programming. For these reasons, and the fact that Excel is the most popular
spreadsheet application available, I am writing this book.

As a scientist, I never really gave business-orientated Excel a chance. The earliest
versions of Excel didn’t even have graphical capabilities; even after they were

Introduction

added, Excel still couldn’t match other spreadsheet applications geared toward the scientist.
After ignoring Excel for several years, I started a new job where Excel was the only spread-
sheet application available; it was then that I discovered that it uses a macro language based
on the already very popular Visual Basic. I started writing programs to handle some of the
routine data analyses required around the lab, and the time I have saved using these pro-
grams has sold me on Excel as a valuable component in any lab or business.

Who Should Read This Book?

The goal of this book is to help you learn VBA programming with Excel. No prior programming
experience is required or expected. Although you do not have to be an Excel user, you must
have a good understanding of the basic tools involved in using any spreadsheet application.
This includes a basic understanding of ranges and cell references, formulas, built-in functions,
and charts. I ask my students at the start of every semester if they know how to use Excel.
At least 90 percent of them say they are very comfortable with the application. Within two
weeks of the start of the semester it is clear that no more than 10 percent of the class can
write a proper formula—one that takes advantage of absolute and relative references,
and built-in functions. Furthermore, fewer than 5 percent know anything about chart types
and the kind of analyses they should be used in. If you’re not comfortable with spreadsheet
applications or it’s been a while since you have used a spreadsheet, then I recommend you
consider purchasing another introductory book on how to use the Excel application prior
to learning how to program in VBA for Excel. In addition to spreadsheets, I also expect you
to have a basic understanding of the Windows operating system.

What’s in This Book and What Is Required?

I developed the programs in this book using Excel 2003 for Windows. Although Excel and
VBA don’t change much from one version to the next, I can’t guarantee that the programs
in this book will execute without error in earlier versions of Excel. With each new version
of Excel, VBA is updated with new objects, and existing objects are expanded with new prop-
erties and methods. If I use even one new object, property, or method specific to VBA-Excel
2003 in a program, then it will generate an error if executed in a previous version of Excel;
therefore, you need Excel 2003—with VBA installed and activated—to use this book.

The chapter projects in this book feature the development of games using VBA with Excel.
This is somewhat unusual in the sense that prior to writing this book, I had never seen an
Excel application that runs any kind of a game; however, it does serve to make programming
more fun. After all, what’s the first thing anybody does when a new computer is purchased?

xi
Introduction

The answer: find the games that are installed and start playing. With this book, you get to
write the program and then play the game. It actually works quite well. The games developed
in this book illustrate the use of basic programming techniques and structures found in all
programming languages as well as all of the common (and some less common) components
in Excel.

What’s on the CD-ROM?

The CD that accompanies this book includes the following:

• The source code for the longer sample programs and the chapter projects discussed
in the book, including all supporting image and sound files

• Audacity, an open-source audio editor

• The GIMP for Windows, a photo retouching and image composition program

• POV-Ray, a tool for creating high-quality three-dimensional graphics

• SawCutter, a tool for designing sounds

• cEdit Professional, an advanced, alternative text editor and IDE

xii
Microsoft Excel VBA Programming for the Absolute Beginner, Second Edition

Visual Basic
for Applications
with Excel
1

C H A P T E R

I

n this first chapter, I introduce you to the programming tools available in
Excel. These tools include the VBA IDE (Integrated Development Environ-
ment), controls and functions available through the main Excel applica-
tion, and VBA on-line help. After your introduction to the VBA programming
environment, I take you through a very short and simple program that calculates
some basic statistics from a sample data set. The program displays the statistics
in a worksheet formatted with a large font, bright colors, and a border to complete
the Colorful Stats project.

Specifically this chapter will cover:

• Installing and enabling VBA

• The VBA IDE and components within

• Programming tools within Excel

• Using VBA on-line help

Project: Colorful Stats

The project in this chapter is short and simple, but will serve as your first intro-
duction to the VBA programming environment, ActiveX controls, event-driven
programming, and using VBA to interact with your spreadsheet. A view of the
Colorful Stats spreadsheet is shown in Figure 1.1.

C H A P T E R

Don’t concern yourself with syntax (the rules of the VBA language) at this time. In subse-
quent chapters, I will show you the tools needed to build VBA projects. For right now, I just
want you to see how easy it is to make something work and recognize that many of the key-
words we use in VBA programming projects in this book are already familiar to you as an
Excel user.

Keywords are words used by the programming language for a special purpose
and therefore are reserved. This means you cannot use a keyword in your program
for anything other than what was designed into the language.

H I N T

2
Microsoft Excel VBA Programming for the Absolute Beginner, Second Edition

In the Real World

Event-driven programming refers to the creation of a program that is designed to run when the
user generates a stimulus. For example, a keystroke or a mouse click may trigger specific
pieces of a program to execute. The event-driven programming model has been popular for
years (since the first graphical-based operating systems such as Windows and Macintosh were
introduced) and is now commonplace. It is vastly superior to older programs that did not allow
for much user interaction because the programmers dictated the flow of the program. In
event-driven programming, the user dictates the flow of the program and it is up to programmers
to anticipate the user’s needs.

Figure 1.1

The Colorful Stats
project.