Microsoft Excel VBA programming for the absolute beginner

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

Duane 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

Contents vii

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

Contents ix

Visual 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?

Introduction xi

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

CHAPTER

In 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.

CHAPTER

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.

HINT

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.