Microsoft Excel VBA Programming for the Absolute Beginner

425 Pages·2002·8.85 MB·English

by  Duane Birnbaum

Y

L
F

M

A

E

T

Team-Fly®
00 XLVBA FM.qxd 2/25/03 7:12 AM Page i
Microsoft®
Excel VBA
Programming
DUANE BIRNBAUM
00 XLVBA FM.qxd 2/25/03 7:12 AM Page ii
© 2002 by Premier Press, Inc. All rights reserved. No part Publisher:
of this book may be reproduced or transmitted in any Stacy L. Hiquet
form or by any means, electronic or mechanical, includ-
Marketing Manager:
ing photocopying, recording, or by any information
Heather Buzzingham
storage or retrieval system without written permission
from Premier Press, except for the inclusion of brief Managing Editor:
quotations in a review. Sandy Doell
The Premier Press logo, top edge printing, Series Editor:
and related trade dress are trademarks of Andy Harris
Premier Press, Inc. and may not be used
Project Editor:
without written permission. All other
Estelle Manticas
trademarks are the property of their
respective owners. Editorial Assistant:
Margaret Bauer
Microsoft is a registered trademark of Microsoft Technical Reviewer:
Corporation. Greg Perry
Important:Premier Press cannot provide software sup- Copy Editor:
port. Please contact the appropriate software manufac- Linda Seifert
turer’s technical support line or Web site for assistance.
Interior Layout:
Premier Press and the author have attempted through- Shawn Morningstar
out this book to distinguish proprietary trademarks
Cover Design:
from descriptive terms by following the capitalization
Mike Tanamachi
style used by the manufacturer.
Indexer:
Information contained in this book has been obtained
Sherry Massey
by Premier Press from sources believed to be reliable.
However, because of the possibility of human or
Proofreader:
mechanical error by our sources, Premier Press, or oth-
Jenny Davidson
ers, the Publisher does not guarantee the accuracy, ade-
quacy, 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.
ISBN: 1-931841-04-7
Library of Congress Catalog Card Number: 2001099839
Printed in the United States of America
02 03 04 05 RI 10 9 8 7 6 5 4 3 2 1
00 XLVBA FM.qxd 2/25/03 7:12 AM Page iii
Acknowledgments
S
pecial thanks to my family—Jill, Aaron, and Joshua. You were all won-
derfully patient with me while I worked on this book. Your love and
understanding are greatly appreciated.
Thanks to all the people at Premier Press, especially Stacy, who gave me the
opportunity to write this book, and to everyone else behind the scenes who
worked to make it look good. Special thanks to Estelle for all the help, and for
putting up with my numerous changes during the review process.
I would also like to thank Andy Harris for recommending me to Premier, and for
his guidance in the early development of the book. Thanks to Greg Perry for an
outstanding technical review. I only wish I’d had time to include more of his sug-
gestions; doing so would have made the book even better.
Finally, thanks to all of the contributors of the software and support files on the
CD and to the reader who supports them (and who, of course, also purchases this
book).
00 XLVBA FM.qxd 2/25/03 7:12 AM Page iv
About the Author
D
uane Birnbaum began programming in graduate school, where he
wrote custom software for interfacing the various electronic devices
required for his experiments and analyzing the data obtained from
them. Since completing his Ph.D. in physical chemistry, he has been
working as a post-doctoral and research scientist in academia and industry while
continuing to teach on a part-time basis. For the last five years he has been work-
ing as a research scientist in the biotechnology industry and serving as a part-
time lecturer in the Computer Science department of Indiana University/Purdue
University. He teaches introductory classes in data analysis, database design, and
Visual Basic.
00 XLVBA FM.qxd 2/25/03 7:12 AM Page v
Contents
Introduction. . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . x
1
Visual Basic for
CHAPTER
Applications with Excel. . . . . . . 1
Project: Colorful Header . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 2
The VBA Integrated Development Environment (IDE). . . . . . 3
Getting to the IDE from Excel . . . . . . . . . . . . . . . . . . . . . . 3
Components of the IDE . . . . . . . . . . . . . . . . . . . . . . . . . . . 4
Programming Components within Excel . . . . . . . . . . . . . . . . 8
Macro Selection. . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 8
The Visual Basic Toolbar . . . . . . . . . . . . . . . . . . . . . . . . . . 9
Getting Help with VBA . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 13
Excel Help. . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 14
VBA Help . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 14
Installing VBA Help Files . . . . . . . . . . . . . . . . . . . . . . . . . 15
Constructing the Colorful Header Program . . . . . . . . . . . . . 15
Chapter Summary. . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 19
2
Beginning Programs with VBA. . 21
CHAPTER
Project: Time of Your Life . . . . . . . . . . . . . . . . . . . . . . . . . . . 22
Variables, Data Types, and Constants. . . . . . . . . . . . . . . . . . 22
Declaring Variables . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 23
Data Types . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 27
Constants. . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 36
Simple Input and Output with VBA . . . . . . . . . . . . . . . . . . . 37
Collecting User Input with InputBox(). . . . . . . . . . . . . . . 37
Output with MsgBox() . . . . . . . . . . . . . . . . . . . . . . . . . . . 38
Manipulating Strings with VBA Functions . . . . . . . . . . . . . . 39
Fun with Strings. . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 40
00 XLVBA FM.qxd 2/25/03 7:12 AM Page vi
vi
Constructing the Time of Your Life Program . . . . . . . . . . . . 44
Project Statement. . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 44
s
tn Project Tools. . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 44
e
t Project Algorithm. . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 44
n
o Adding the Code . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 45
C
Chapter Summary. . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 49
f
o
3
e
l
b
Procedures and Conditions . . . 51
a CHAPTER
T
Project: Poker Dice . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 52
VBA Procedures . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 52
Event Procedures. . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 52
Sub Procedures . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 56
Function Procedures . . . . . . . . . . . . . . . . . . . . . . . . . . . . 61
Logical Operators in VBA. . . . . . . . . . . . . . . . . . . . . . . . . 64
Conditionals and Branching. . . . . . . . . . . . . . . . . . . . . . . 67
Constructing the Poker Dice Program . . . . . . . . . . . . . . . . . 72
Project Statement. . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 73
Project Tools. . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 73
Project Algorithm. . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 75
Adding the Code . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 76
Chapter Summary. . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 84
4
Loops and Arrays. . . . . . . . . . . 87
CHAPTER
Project: The Math Game. . . . . . . . . . . . . . . . . . . . . . . . . . . . 88
Looping with VBA. . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 88
Do Loops . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 89
For Loops. . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 91
Input Validation. . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 94
Validation with the InputBox() Function . . . . . . . . . . . . . 95
Validation with a Spreadsheet Cell . . . . . . . . . . . . . . . . . 97
Arrays . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 99
One-Dimensional Arrays . . . . . . . . . . . . . . . . . . . . . . . . 101
Multi-Dimensional Arrays . . . . . . . . . . . . . . . . . . . . . . . 104
Dynamic Arrays . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 106
Programming Formulas into Worksheet Cells . . . . . . . . . . 109
A1 Style References. . . . . . . . . . . . . . . . . . . . . . . . . . . . 109
R1C1 Style References. . . . . . . . . . . . . . . . . . . . . . . . . . 111
00 XLVBA FM.qxd 2/25/03 7:12 AM Page vii
vii
Chapter Project: Math Game. . . . . . . . . . . . . . . . . . . . . . . . 112
Project Statement. . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 112
T
a
Project Tools. . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 113
b
l
Project Algorithm. . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 119 e
o
Adding the Code . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 122
f

Chapter Summary. . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 131 C
o
5 n
t
e
Basic Excel Objects . . . . . . . . 133 n
CHAPTER t
s
Project: Battlecell. . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 134
VBA and OOP . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 134
Objects Defined. . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 135
VBA Collection Objects. . . . . . . . . . . . . . . . . . . . . . . . . . . . 137
The Object Browser. . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 139
Top-Level Excel Objects . . . . . . . . . . . . . . . . . . . . . . . . . . . 144
The Application Object. . . . . . . . . . . . . . . . . . . . . . . . . . 145
The Workbook and Window Objects. . . . . . . . . . . . . . . 145
The Worksheet Object . . . . . . . . . . . . . . . . . . . . . . . . . . 153
The Range Object. . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 154
Working with Objects. . . . . . . . . . . . . . . . . . . . . . . . . . . 157
Chapter Project: Battlecell. . . . . . . . . . . . . . . . . . . . . . . . . . 161
Project Statement. . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 161
Project Tools. . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 162
Project Algorithm. . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 162
Adding the Code . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 164
Chapter Summary. . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 184
6
Enhancing VBA Programs:
CHAPTER
Adding Multimedia
and Intelligence . . . . . . . . . . . 187
Project: Enhanced Battlecell . . . . . . . . . . . . . . . . . . . . . . . . 188
Adding Multimedia to a Program. . . . . . . . . . . . . . . . . . . . 188
Animation in VBA. . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 188
Sound in VBA. . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 200
Simulating Intelligence in Programs. . . . . . . . . . . . . . . . . . 203
Chapter Project: Enhanced Battlecell . . . . . . . . . . . . . . . . . 204
Project Statement. . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 204
Project Tools. . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 205
Project Algorithm. . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 205
00 XLVBA FM.qxd 2/25/03 7:12 AM Page viii
viii
Adding the Code . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 208
Chapter Summary. . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 224
s
t 7
n
e
t
n UserForms and
CHAPTER
o
C Additional Controls . . . . . . . . 227

f
o
Project: Blackjack. . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 228
e
lb UserForms. . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 228
a
T Adding a UserForm to a Project . . . . . . . . . . . . . . . . . . 229
Components of the UserForm Object . . . . . . . . . . . . . . 229
Adding ActiveX Controls to a UserForm. . . . . . . . . . . . 231
Showing and Hiding UserForms. . . . . . . . . . . . . . . . . . 233
Modal Forms . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 233
Custom Dialog Boxes with UserForms . . . . . . . . . . . . . 235
Chapter Project: Blackjack. . . . . . . . . . . . . . . . . . . . . . . . . . 246
Project Statement. . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 247
Project Tools. . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 247
Project Algorithm. . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 247
Adding the Code . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 250
Chapter Summary. . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 267
8
Data Access, File I/O, Error
CHAPTER
Handling, and Debugging. . . . 269
Project: Word Find. . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 270
File Input and Output (I/O) . . . . . . . . . . . . . . . . . . . . . . . . . 270
Using VBA File I/O Methods . . . . . . . . . . . . . . . . . . . . . 271
Error Handling. . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 283
Using the On Error Statement. . . . . . . . . . . . . . . . . . . . 283
Debugging. . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 287
Break Mode . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 287
The Immediate Window. . . . . . . . . . . . . . . . . . . . . . . . . 289
The Watch Window . . . . . . . . . . . . . . . . . . . . . . . . . . . . 290
The Locals Window . . . . . . . . . . . . . . . . . . . . . . . . . . . . 291
Chapter Project: Word Find. . . . . . . . . . . . . . . . . . . . . . . . . 292
Project Statement. . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 292
Project Tools. . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 293
Project Algorithm. . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 293
Adding the Code . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 296
Chapter Summary. . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 310
00 XLVBA FM.qxd 2/25/03 7:12 AM Page ix
9
ix
Excel Charts . . . . . . . . . . . . . . 313
CHAPTER
Project: The Alienated Game . . . . . . . . . . . . . . . . . . . . . . . 314 T
a
The Chart Object. . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 314 b
l
e
Accessing Existing Charts . . . . . . . . . . . . . . . . . . . . . . . 314
o
Manipulating Charts. . . . . . . . . . . . . . . . . . . . . . . . . . . . 318 f
C
Creating Charts. . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 322 o
n
Chart Events. . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 326 t
e
Chapter Project: The Alienated Game. . . . . . . . . . . . . . . . . 332 n
t
s
Project Statement. . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 333
Project Tools. . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 333
Project Algorithm. . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 333
Adding the Code . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 335
Chapter Summary. . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 355
10
VBA Shapes . . . . . . . . . . . . . . 357
CHAPTER
Project: Excetris. . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 358
The Shapes Collection and Shape Objects. . . . . . . . . . . . . 358
Manipulating a Shape Object . . . . . . . . . . . . . . . . . . . . 360
The ShapeRange Collection Object . . . . . . . . . . . . . . . . . . 362
Activating Shape Objects . . . . . . . . . . . . . . . . . . . . . . . . . . 365
The OLEObjects Collection . . . . . . . . . . . . . . . . . . . . . . . . . 366
Chapter Project: Excetris. . . . . . . . . . . . . . . . . . . . . . . . . . . 368
Project Statement. . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 369
Project Tools. . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 369
Project Algorithm. . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 370
Adding the Code . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 372
Chapter Summary. . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 395
A Final Word . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 396
Index. . . . . . . . . . . . . . . . . . . . 397