Introduction to VBA for Excel

Steven C. Chapra presently holds the Louis Berger Chair for Computing and Engineering in the Civil and Environmental Engineering Department at Tufts University. Dr. Chapra received engineering degrees from Manhattan College and the University of Michigan. Before joining the faculty at Tufts, he taught at Texas A&M University, the University of Colorado, and Imperial College, London. His research interests focus on surface water-quality modeling and advanced computer applications in environmental engineering. He has published over 50 refereed journal articles, 20 software packages and 6 books. He has received a number of awards including the 1987 ASEE Merriam/Wiley Distinguished Author Award, the 1993 Rudolph Hering Medal, and teaching awards from Texas A&M, the University of Colorado, and the Association of Environmental Engineering and Science Professors.

• Contents1 If You’ve Never Programmed Before 11.1 The “Idea” of a Program 21.2 The Concept of Assignment 31.3 Decisions and Loops 41.3.1 Decisions 51.3.2 Loops 51.4 A Simple Example 6  2 Overview of VBA for Excel 82.1 The Excel/VBA Environment 92.2 An Excel Interface and a VBA Macro Program 92.2.1 Hands-on Exercise: A Simple Addition Program 102.3 Other Ways to Obtain and Display Information 16  3 Recording Macros 203.1 Macro Recording 213.1.1 Hands-on Exercise: Macro Recording to Format a Range of Cells 213.2 Absolute and Relative References 243.2.1 Hands-on Exercise: Absolute versus Relative Recording Mode 243.3 Using Macro Recording to Learn about VBA 273.3.1 Hands-on Exercise: Using a Macro Recording to Learn How to CountData 273.4 What Keyboard Macros Can’t Do 29  4 Customized Worksheet Functions 314.1 The “Idea” of a Function 324.2 Worksheet Function Macros 334.2.1 Hands-on Exercise: A Simple Worksheet Function Macro 33  5 Modular Programming 395.1 Sub Procedures 405.1.1 Hands-on Exercise: The Kick Calculator 415.2 Function Procedures 465.3 More about Procedure Arguments 475.3.1 Hands-on Exercise: The Behavior of the Parameter List 485.4 Passing by Value or by Reference 505.4.1 Hands-on Exercise: Passing by Value or by Reference 515.5 Static Variables 52  6 Object-oriented Programming 556.1 Objects, Properties, Methods, and Collections 566.1.1 Hands-on Exercise: OOP and a Recorded Macro 576.2 Using Oop for Input/Output 616.3 Learning More About Oop 62  7 Debugging and Testing 657.1 Debugging 667.2 The VBA Help Facility 667.2.1 Help Menu 667.2.2 Context-Sensitive Help and the F1 Key 687.2.3 Accessing Help from Error Messages 697.3 Built-in Debugging Capabilities 697.3.1 Passive Debugging: VBA Error Messages 697.3.2 Active Debugging 717.3.3 Hands-on Exercise: The VBA Debugger 727.4 Testing 73  8 Data Typing And Variable Scope 768.1 Data Types 778.1.1 Numeric Information 778.1.2 Character, or “String,” Information 798.1.3 Logical, or “Boolean,” Information 798.2 Type Declaration 808.2.1 Variant Data Type 808.2.2 The Dim Statement 818.2.3 Option Explicit 828.2.4 Hands-on Exercise: Run Time for Single and Double Precision 838.2.5 Type Declaration Characters 848.2.6 The Const Statement 858.3 Variable Scope and Lifetime 858.3.1 The Parameter List 868.3.2 Scope and Declaration 87  9 Computations 909.1 Computations 919.1.1 Operator Priority 919.1.2 Left to Right 929.2 Built-in Numeric Functions 939.2.1 Accessing Excel Functions from VBA 94  10 Strings and Dialog Boxes 9910.1 String Functions and Manipulations 10010.1.1 Concatenating and Parsing Strings 10010.1.2 LCase and UCase 10110.2 Message Boxes 10310.2.1 Buttons 10510.3 Input Boxes 10710.3.1 Hands-on Exercise: Data Typing, String Functions, and Input Boxes 108  11 Structured Programming: Decisions 11311.1 Structured Programming 11411.2 Flowcharts 11511.3 The If/Then/Else Decision Structure 11611.3.1 Indentation 11711.3.2 Single Decision Structure (If/Then) 11811.3.3 Single-Line If Statement 11911.4 If/Then/Elseif Structure 12011.5 Select Case Structure 12111.5.1 If/Then/ElseIf Versus Select Case 12211.6 Nesting 12311.7 Compound Logical Expressions 12511.7.1 Logical Complements and DeMorgan’s Theorem 128  12 Structured Programming: Loops 13312.1 Decision Loops (Do/if Exit) 13412.2 Count-controlled Loops 13512.3 Nesting of Loops and Decisions 13812.4 Recursion 13912.4.1 Hands-on Exercise: Nonrecursive and Recursive Factorial Functions 140  13 Data Structures: Arrays and Records 14713.1 Arrays 14813.1.1 Hands-on Exercise: Loops and Arrays 15013.1.2 Multidimensional Arrays 15213.1.3 Changing an Array’s Lower Bound 15313.1.4 Passing Arrays to Procedures 15313.1.5 Hands-on Exercise: The Bubble Sort 15513.1.6 Dynamic Arrays and the ReDim Statement 15713.2 Records 158  14 Creating and Accessing Files 16414.1 Sequential Files 16514.1.1 Hands-on Exercise: File Manipulations 16714.2 Other File Operations 17014.2.1 GetOpenFilename Method 17014.2.2 GetSaveAsFilename Method 172  15 Custom Dialogue Boxes 17515.1 A Simple Custom Dialogue Box 17615.1.1 Hands-on Exercise: Developing a Simple Custom Dialogue Box 17615.2 Custom Dialogue Boxes and Modules 18315.2.1 Hands-on Exercise: Custom Dialogue Box and Modules 183  References 188Index 189