Introduction to VBA for Excel

Cover......Page 1
Contents......Page 8
About This Book......Page 16
 1.1 The “Idea” Of A Program......Page 22
 1.2 The Concept Of Assignment......Page 23
  1.3.1 Decisions......Page 25
 1.4 A Simple Example......Page 26
 Problems......Page 27
 2.1 The Excel/Vba Environment......Page 29
  2.1.2 Macro Security And Saving Macros......Page 30
 2.2.1 Hands-On Exercise: A Simple Addition Program......Page 31
 2.3 Other Ways To Obtain And Display Information......Page 37
 Problems......Page 39
  3.1.1 Hands-On Exercise: Macro Recording To Format A Range Of Cells......Page 41
  3.2.1 Hands-On Exercise: Absolute Versus Relative Recording Mode......Page 44
 3.3 Using Macro Recording To Learn About Vba......Page 47
  3.3.1 Hands-On Exercise: Using A Macro Recording To Learn How To Count Data......Page 48
 3.4 What Keyboard Macros Can’T Do......Page 49
 Problems......Page 50
 4.1 The “Idea” Of A Function......Page 51
  4.2.1 Hands-On Exercise: A Simple Worksheet Function Macro......Page 53
 Key Terms......Page 56
 Problems......Page 57
5 Modular Programming......Page 59
  5.1.1 Hands-On Exercise: The Kick Calculator......Page 60
 5.2 Function Procedures......Page 66
 5.3 More About Procedure Arguments......Page 67
  5.3.1 Hands-On Exercise: The Behavior Of The Parameter List......Page 68
 5.4 Passing By Value Or By Reference......Page 70
  5.4.1 Hands-On Exercise: Passing By Value Or By Reference......Page 71
 5.5 Static Variables......Page 72
 Problems......Page 73
6 Object-Oriented Programming......Page 75
 6.1 Objects, Properties, Methods, And Collections......Page 76
  6.1.1 Hands-On Exercise: Oop And A Recorded Macro......Page 77
 6.2 Using Oop For Input/Output......Page 80
 6.3 Learning More About Oop......Page 81
 Problems......Page 83
 7.1 Debugging......Page 86
  7.2.1 Help Menu......Page 87
  7.2.2 Context-Sensitive Help And The F1 Key......Page 88
  7.3.1 Passive Debugging: Vba Error Messages......Page 89
  7.3.3 Hands-On Exercise: The Vba Debugger......Page 91
 7.4 Testing......Page 93
 Problems......Page 94
 8.1 Data Types......Page 96
  8.1.1 Numeric Information......Page 97
  8.1.2 Character, Or “String,” Information......Page 98
  8.2.1 Variant Data Type......Page 99
  8.2.2 The Dim Statement......Page 101
  8.2.4 Hands-On Exercise: Run Time For Single And Double Precision......Page 102
  8.2.6 The Const Statement......Page 104
  8.3.1 The Parameter List......Page 105
  8.3.2 Scope And Declaration......Page 106
 Key Terms......Page 107
 Problems......Page 108
  9.1.1 Operator Priority......Page 109
 9.2 Built-In Numeric Functions......Page 111
  9.2.1 Accessing Excel Functions From Vba......Page 113
 Key Terms......Page 114
 Problems......Page 115
  10.1.1 Concatenating And Parsing Strings......Page 117
  10.1.2 Lcase And Ucase......Page 119
 10.2 Message Boxes......Page 120
  10.2.1 Buttons......Page 122
 10.3 Input Boxes......Page 124
  10.3.1 Hands-On Exercise: Data Typing, String Functions, And Input Boxes......Page 125
 Problems......Page 128
 11.1 Structured Programming......Page 130
 11.2 Flowcharts......Page 132
 11.3 The If/Then/Else Decision Structure......Page 133
  11.3.1 Indentation......Page 134
  11.3.2 Single Decision Structure (If/Then)......Page 135
 11.4 If/Then/Elseif Structure......Page 136
 11.5 Select Case Structure......Page 138
 11.5.1 If/Then/Elseif Versus Select Case......Page 139
 11.6 Nesting......Page 140
 11.7 Compound Logical Expressions......Page 142
 Key Terms......Page 144
 Problems......Page 145
12 Structured Programming: Loops......Page 149
 12.1 Decision Loops (Do/If Exit)......Page 150
 12.2 Count-Controlled Loops......Page 151
 12.3 Nesting Of Loops And Decisions......Page 154
 12.4 Recursion......Page 155
  12.4.1 Hands-On Exercise: Nonrecursive And Recursive Factorial Functions......Page 156
 Problems......Page 158
 13.1 Arrays......Page 162
  13.1.1 Hands-On Exercise: Loops And Arrays......Page 165
  13.1.2 Multidimensional Arrays......Page 166
  13.1.3 Changing An Array’S Lower Bound......Page 167
  13.1.4 Passing Arrays To Procedures......Page 168
  13.1.5 Hands-On Exercise: The Bubble Sort......Page 169
  13.1.6 Dynamic Arrays And The Redim Statement......Page 172
 13.2 Records......Page 173
 Problems......Page 175
 14.1 Sequential Files......Page 179
  14.1.1 Hands-On Exercise: File Manipulations......Page 182
  14.2.1 Getopenfilename Method......Page 185
 Key Terms......Page 187
 Problems......Page 188
  15.1.1 Hands-On Exercise: Developing A Simple Custom Dialogue Box......Page 190
  15.2.1 Hands-On Exercise: Custom Dialogue Box And Modules......Page 197
 Problems......Page 199
References......Page 202
 B......Page 204
 D......Page 205
 H......Page 206
 M......Page 207
 R......Page 208
 S......Page 209
 Z......Page 210