Microsoft Excel VBA programming for the absolute beginner

544 Pages·2007·11.67 MB·English

by  Duane Birnbaum

Microsoft
®
Excel VBA
®
Programming
for the Absolute
Beginner,
Third Edition
DUANE BIRNBAUM
MICHAEL VINE
© 2007 Thomson Course Technology, a division of Thomson Learning Publisher and General
Inc. All rights reserved. No part of this book may be reproduced or trans- Manager, Thomson Course
mitted in any form or by any means, electronic or mechanical, including Technology PTR:
photocopying, recording, or by any information storage or retrieval Stacy L. Hiquet
system without written permission from Thomson Course Technology
Associate Director of
PTR, except for the inclusion of brief quotations in a review.
Marketing:
The Thomson Course Technology PTR logo and related trade dress are Sarah O´Donnell
trademarks of Thomson Course Technology, a division of Thomson
Manager of Editorial
Learning Inc., and may not be used without written permission.
Services:
Microsoft, Excel, and VBA are either registered trademarks or trade- Heather Talbot
marks of Microsoft Corporation in the United States and/or other
Marketing Manager:
countries.
Mark Hughes
All other trademarks are the property of their respective owners.
Acquisitions Editor:
Important: Thomson Course Technology PTR cannot provide software Mitzi Koontz
support. Please contact the appropriate software manufacturer’s
Marketing Assistant:
technical support line or Web site for assistance.
Adena Flitt
Thomson Course Technology PTR and the authors have attempted
Project Editor:
throughout this book to distinguish proprietary trademarks from
Jenny Davidson
descriptive terms by following the capitalization style used by the
manufacturer. Technical Reviewer:
Keith Davenport
Information contained in this book has been obtained by Thomson
Course Technology PTR from sources believed to be reliable. However, PTR Editorial Services
because of the possibility of human or mechanical error by our sources, Coordinator:
Thomson Course Technology PTR, or others, the Publisher does not Erin Johnson
guarantee the accuracy, adequacy, or completeness of any information
and is not responsible for any errors or omissions or the results obtained Interior Layout Tech:
from use of such information. Readers should be particularly aware of Digital Publishing Solutions
the fact that the Internet is an ever-changing entity. Some facts may have
Cover Designer:
changed since this book went to press.
Mike Tanamachi
Educational facilities, companies, and organizations interested in
Indexer:
multiple copies or licensing of this book should contact the Publisher
Sharon Shock
for quantity discount information. Training manuals, CD-ROMs, and
portions of this book are also available individually or can be tailored Proofreader:
for specific needs. Sara Gullion
ISBN-10: 1-59863-394-5
ISBN-13: 978-1-59863-394-8
eISBN-10: 1-59863-763-0
Library of Congress Catalog Card Number: 2007923300
Printed in the United States of America
07 08 09 10 11 TW 10 9 8 7 6 5 4 3 2 1
Thomson Course Technology PTR,
a division of Thomson Learning Inc.
25 Thomson Place
Boston, MA 02210
http://www.courseptr.com
Michael Vine
To my daughter, Olivia, who wields magical powers with a
pinch of smile, a splash of brown eyes, and a bouquet of
“I love you, daddy.”
A
CKNOWLEDGMENTS
Duane Birnbaum
As always I must thank family: Jill, Aaron, and Joshua.
Special thanks to Michael Vine for writing the revisions for this edition. It was
simply bad timing for me and I was very happy that Michael quickly jumped in
and agreed to do this edition. I would also like to thank Mitzi Koontz and all of the
other contributors associated with Course Technology for their invaluable help in
putting this book together.
A A
BOUT THE UTHORS
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 complet-
ing 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. For the past 10 years he taught courses in introductory programming,
database design, and data analysis in the Computer Science department at Indiana
University/Purdue University at Indianapolis. Recently, he started a new position
as a consulting engineer for Double-Take Software in Indianapolis.
Michael Vine has taught computer programming, web design, and database
classes at Indiana University/Purdue University in Indianapolis, IN, and at MTI
College of Business and Technology in Sacramento, CA. Michael has over 13 years’
experience in the information technology profession. He currently works full time
at a Fortune 100 company as an IT Project Manager overseeing the development
of enterprise data warehouses.
This page intentionally left blank
Contents
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.................................................................................... 6
Components of the IDE................................................................................................ 7
Programming Components within Excel.......................................................................11
Additional Development Tools................................................................................ 11
Getting Help with VBA........................................................................................................16
Constructing the Colorful Stats Program......................................................................17
Requirements of the Colorful Stats Program...................................................... 17
Designing the Colorful Stats Program................................................................... 18
Coding the Colorful Stats Program........................................................................ 18
Summary.................................................................................................................................21
Challenges...............................................................................................................................22
CHAPTER 2 BEGINNING PROGRAMMING WITH VBA................. 23
Project: Biorhythms and the Time of Your Life............................................................23
Variables, Data Types, and Constants..............................................................................24
Declaring Variables..................................................................................................... 25
Object and Standard Modules.................................................................................. 26
Variable Scope.............................................................................................................. 28
Data Types..................................................................................................................... 29
Numerical Data Types................................................................................................ 30
String Data Types........................................................................................................ 35
Variant Data Types...................................................................................................... 36
Boolean Data Types..................................................................................................... 38
Date Data Types........................................................................................................... 38
Constants....................................................................................................................... 38
Simple Input and Output with VBA.................................................................................39
Collecting User Input with InputBox().................................................................. 39
Output with MsgBox()................................................................................................ 40
viii Microsoft Excel VBA Programming for the Absolute Beginner
Manipulating Strings with VBA Functions................................................................... 41
Fun with Strings.......................................................................................................... 42
Constructing the Biorhythms and the Time of Your Life Program........................ 45
Requirements for Biorhythms and the Time of Your Life................................ 46
Designing Biorhythms and the Time of Your Life.............................................. 47
Coding Biorhythms and the Time of Your Life.................................................... 48
Summary................................................................................................................................ 52
Challenges.............................................................................................................................. 53
CHAPTER 3 PROCEDURES AND CONDITIONS........................... 55
Project: Poker Dice............................................................................................................... 55
Event Procedures.................................................................................................................. 56
Parameters with Event Procedures......................................................................... 57
Private, Public, and Procedure Scope..................................................................... 59
Sub Procedures..................................................................................................................... 60
ByVal and ByRef........................................................................................................... 63
Function Procedures........................................................................................................... 65
Creating Your Own VBA Functions......................................................................... 66
Using Excel Application Functions in VBA........................................................... 68
Logical Operators with VBA.............................................................................................. 69
AND, OR, and NOT Operators................................................................................... 70
Conditionals and Branching............................................................................................. 71
If/Then/Else.................................................................................................................... 71
Select/Case..................................................................................................................... 75
Constructing the Poker Dice Program........................................................................... 77
Requirements for Poker Dice................................................................................... 77
Designing Poker Dice................................................................................................. 78
Coding Poker Dice....................................................................................................... 81
Summary................................................................................................................................ 97
Challenges.............................................................................................................................. 98
CHAPTER 4 LOOPS AND ARRAYS................................................ 99
Project: Math Game........................................................................................................... 100
Looping with VBA.............................................................................................................. 100
Do Loops....................................................................................................................... 100
For Loops...................................................................................................................... 103
Input Validation................................................................................................................. 106
Validation with the InputBox() Function............................................................ 107
Validation with a Spreadsheet Cell...................................................................... 109
Arrays.................................................................................................................................... 109
One-Dimensional Arrays.......................................................................................... 111
Contents ix
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........................................................................ 124
Designing the Math Game...................................................................................... 125
Coding the Math Game Program.......................................................................... 134
Summary.............................................................................................................................. 147
Challenges........................................................................................................................... 149
CHAPTER 5 EXCEL OBJECTS........................................................ 151
Project: BattleCell.............................................................................................................. 152
VBA and Object-Oriented Programming..................................................................... 152
Objects Defined.................................................................................................................. 154
VBA Collection Objects..................................................................................................... 155
The Object Browser........................................................................................................... 157
Top-Level Excel Objects.................................................................................................... 164
The Application Object............................................................................................ 164
The Workbook and Window Objects................................................................... 165
The Worksheet Object.............................................................................................. 173
The Range Object............................................................................................................... 174
Using the Cells Property.......................................................................................... 176
Working with Objects...................................................................................................... 177
The With/End With Structure................................................................................ 177
The Object Data Type................................................................................................ 178
For/Each and Looping through a Range.............................................................. 179
Constructing BattleCell................................................................................................... 181
Requirements for BattleCell................................................................................... 181
Designing BattleCell................................................................................................. 184
Coding BattleCell....................................................................................................... 186
Adding Sound to Your VBA Program................................................................... 207
Summary.............................................................................................................................. 210
Challenges........................................................................................................................... 210
CHAPTER 6 VBA USERFORMS AND ADDITIONAL
CONTROLS............................................................... 213
Project: Blackjack............................................................................................................... 214
Designing Forms with VBA............................................................................................. 214
Adding a Form to a Project..................................................................................... 215