Contents
1 If You’ve Never Programmed Before 1
1.1 The “Idea” of a Program 2
1.2 The Concept of Assignment 3
1.3 Decisions and Loops 4
1.3.1 Decisions 5
1.3.2 Loops 5
1.4 A Simple Example 6
2 Overview of VBA for Excel 8
2.1 The Excel/VBA Environment 9
2.2 An Excel Interface and a VBA Macro Program 9
2.2.1 Hands-on Exercise: A Simple Addition Program 10
2.3 Other Ways to Obtain and Display Information 16
3 Recording Macros 20
3.1 Macro Recording 21
3.1.1 Hands-on Exercise: Macro Recording to Format a Range of Cells 21
3.2 Absolute and Relative References 24
3.2.1 Hands-on Exercise: Absolute versus Relative Recording Mode 24
3.3 Using Macro Recording to Learn about VBA 27
3.3.1 Hands-on Exercise: Using a Macro Recording to Learn How to Count
Data 27
3.4 What Keyboard Macros Can’t Do 29
4 Customized Worksheet Functions 31
4.1 The “Idea” of a Function 32
4.2 Worksheet Function Macros 33
4.2.1 Hands-on Exercise: A Simple Worksheet Function Macro 33
5 Modular Programming 39
5.1 Sub Procedures 40
5.1.1 Hands-on Exercise: The Kick Calculator 41
5.2 Function Procedures 46
5.3 More about Procedure Arguments 47
5.3.1 Hands-on Exercise: The Behavior of the Parameter List 48
5.4 Passing by Value or by Reference 50
5.4.1 Hands-on Exercise: Passing by Value or by Reference 51
5.5 Static Variables 52
6 Object-oriented Programming 55
6.1 Objects, Properties, Methods, and Collections 56
6.1.1 Hands-on Exercise: OOP and a Recorded Macro 57
6.2 Using Oop for Input/Output 61
6.3 Learning More About Oop 62
7 Debugging and Testing 65
7.1 Debugging 66
7.2 The VBA Help Facility 66
7.2.1 Help Menu 66
7.2.2 Context-Sensitive Help and the F1 Key 68
7.2.3 Accessing Help from Error Messages 69
7.3 Built-in Debugging Capabilities 69
7.3.1 Passive Debugging: VBA Error Messages 69
7.3.2 Active Debugging 71
7.3.3 Hands-on Exercise: The VBA Debugger 72
7.4 Testing 73
8 Data Typing And Variable Scope 76
8.1 Data Types 77
8.1.1 Numeric Information 77
8.1.2 Character, or “String,” Information 79
8.1.3 Logical, or “Boolean,” Information 79
8.2 Type Declaration 80
8.2.1 Variant Data Type 80
8.2.2 The Dim Statement 81
8.2.3 Option Explicit 82
8.2.4 Hands-on Exercise: Run Time for Single and Double Precision 83
8.2.5 Type Declaration Characters 84
8.2.6 The Const Statement 85
8.3 Variable Scope and Lifetime 85
8.3.1 The Parameter List 86
8.3.2 Scope and Declaration 87
9 Computations 90
9.1 Computations 91
9.1.1 Operator Priority 91
9.1.2 Left to Right 92
9.2 Built-in Numeric Functions 93
9.2.1 Accessing Excel Functions from VBA 94
10 Strings and Dialog Boxes 99
10.1 String Functions and Manipulations 100
10.1.1 Concatenating and Parsing Strings 100
10.1.2 LCase and UCase 101
10.2 Message Boxes 103
10.2.1 Buttons 105
10.3 Input Boxes 107
10.3.1 Hands-on Exercise: Data Typing, String Functions, and Input Boxes 108
11 Structured Programming: Decisions 113
11.1 Structured Programming 114
11.2 Flowcharts 115
11.3 The If/Then/Else Decision Structure 116
11.3.1 Indentation 117
11.3.2 Single Decision Structure (If/Then) 118
11.3.3 Single-Line If Statement 119
11.4 If/Then/Elseif Structure 120
11.5 Select Case Structure 121
11.5.1 If/Then/ElseIf Versus Select Case 122
11.6 Nesting 123
11.7 Compound Logical Expressions 125
11.7.1 Logical Complements and DeMorgan’s Theorem 128
12 Structured Programming: Loops 133
12.1 Decision Loops (Do/if Exit) 134
12.2 Count-controlled Loops 135
12.3 Nesting of Loops and Decisions 138
12.4 Recursion 139
12.4.1 Hands-on Exercise: Nonrecursive and Recursive Factorial Functions 140
13 Data Structures: Arrays and Records 147
13.1 Arrays 148
13.1.1 Hands-on Exercise: Loops and Arrays 150
13.1.2 Multidimensional Arrays 152
13.1.3 Changing an Array’s Lower Bound 153
13.1.4 Passing Arrays to Procedures 153
13.1.5 Hands-on Exercise: The Bubble Sort 155
13.1.6 Dynamic Arrays and the ReDim Statement 157
13.2 Records 158
14 Creating and Accessing Files 164
14.1 Sequential Files 165
14.1.1 Hands-on Exercise: File Manipulations 167
14.2 Other File Operations 170
14.2.1 GetOpenFilename Method 170
14.2.2 GetSaveAsFilename Method 172
15 Custom Dialogue Boxes 175
15.1 A Simple Custom Dialogue Box 176
15.1.1 Hands-on Exercise: Developing a Simple Custom Dialogue Box 176
15.2 Custom Dialogue Boxes and Modules 183
15.2.1 Hands-on Exercise: Custom Dialogue Box and Modules 183
References 188
Index 189
