About the Author

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.

Contents

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

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

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

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 a 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

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

Biorhythm Awareness

Biorhythm Awareness

Who else wants to take advantage of biorhythm awareness to avoid premature death, escape life threatening diseases, eliminate most of your life altering mistakes and banish catastrophic events from your life.

Get My Free Ebook


Post a comment