| Acknowledgments | xiii |
| Introduction | xiii |
Part I | Programming in Excel VBA | |
Chapter 1 | The Basics | 3 |
| Exploring the Visual Basic Editor in Excel | 3 |
Chapter 2 | Variables, Arrays, Constants, and Data Types | 11 |
| Variables | 11 |
| Data Types | 16 |
| VBA Data Types | 18 |
| Arrays | 20 |
| User-Defined Types | 22 |
| Constants | 23 |
| Reserved Words | 23 |
Chapter 3 | Modules, Functions, and Subroutines | 25 |
| Modules | 25 |
| The Difference Between Subroutines and Functions | 26 |
| Writing a Simple Subroutine | 27 |
| Writing a Simple Function | 28 |
| Public and Private Functions and Subroutines | 30 |
| Argument Data Types | 31 |
| Optional Arguments | 31 |
| Passing Arguments by Value | 31 |
Chapter 4 | Programming Basics: Decisions and Looping | 33 |
| Decisions | 34 |
| Looping | 37 |
Chapter 5 | Strings and Functions and Message Boxes | 43 |
| Strings | 43 |
| Functions | 47 |
| Conversion Functions | 49 |
| Format Function | 50 |
| Date and Time Functions | 54 |
| SendKeys Command | 60 |
| Message Boxes | 64 |
Chapter 6 | Operators | 67 |
| Arithmetic Operators | 68 |
| Comparison Operators | 70 |
| Concatenation Operator | 70 |
| Logical Operators | 71 |
| Other Operators | 74 |
Chapter 7 | Debugging | 77 |
| Types of Errors | 77 |
| Design Time, Runtime, and Break Mode | 78 |
| Breakpoints | 80 |
| Using Stop Statements | 80 |
| Running Selected Parts of Your Code | 80 |
| The Debug Window | 82 |
| Events That Can Cause Problems When Debugging | 84 |
| Using Message Boxes in Debugging | 84 |
| Avoiding Bugs | 86 |
Chapter 8 | Errors and the Error Function | 87 |
| The Resume Statement | 89 |
| Implications of Error Trapping | 90 |
| Generating Your Own Errors | 90 |
Chapter 9 | Dialogs | 91 |
| Viewing Your Form | 95 |
| Displaying Your Form in Code | 97 |
| Populating Your Form | 97 |
| Default Toolbox Controls | 98 |
Chapter 10 | Common Dialog Control | 107 |
| Using the Common Dialog Control | 108 |
| Default Dialogs | 114 |
Chapter 11 | Command Bars and Buttons | 117 |
| Command Bars | 118 |
| Command Buttons | 121 |
Part II | Object Models | |
Chapter 12 | The Excel Object Model | 125 |
| Properties and Methods Explained | 126 |
| Using the Object Browser | 134 |
| Hierarchy | 137 |
| Recording Macros | 138 |
Chapter 13 | The Excel Object Model--Main Objects | 141 |
| Application Object | 141 |
| Workbook Object | 147 |
| Windows Object | 149 |
| Worksheet Object | 153 |
| Range Object | 155 |
Chapter 14 | Using Excel to Interact with Other Office Programs | 161 |
| Driving Microsoft Outlook | 164 |
| Driving Excel from Other Office Programs | 166 |
Part III | Advanced Techniques in Excel VBA | |
Chapter 15 | Charts and Graphs | 171 |
Chapter 16 | Working with Databases | 177 |
| ODBC Links | 177 |
| Using ADO | 179 |
Chapter 17 | API Calls | 183 |
| What Is an API Call? | 183 |
| Using an API Call | 184 |
Chapter 18 | Class Modules | 193 |
| Inserting a Class Module | 194 |
| Creating an Object | 194 |
| Creating a Collection | 195 |
| Using the PNames Collection | 198 |
Chapter 19 | Animation | 199 |
Part IV | VBA in Action | |
Chapter 20 | Converting Labels to Numbers and Numbers to Labels | 205 |
Chapter 21 | Transposing a Range of Cells | 209 |
Chapter 22 | Adding Formula Details into Comments | 213 |
Chapter 23 | Calculating a Range | 217 |
Chapter 24 | Reversing a Label | 219 |
Chapter 25 | Who Created the Workbook? | 221 |
Chapter 26 | Evaluating a Cell | 225 |
Chapter 27 | Sorting Worksheets into Alphabetical Order | 229 |
Chapter 28 | Replacing Characters in a String | 231 |
Chapter 29 | Timed Events | 235 |
Chapter 30 | Auto Totaling a Matrix of Numbers | 237 |
Chapter 31 | Absolute and Relative Formulas | 241 |
Chapter 32 | Coloring Alternate Rows and Columns of the Spreadsheet | 245 |
Chapter 33 | Coloring Cells Containing Formulas | 251 |
Chapter 34 | Summing Cells by Reference to a Master Cell | 255 |
Chapter 35 | Globally Changing a Range of Values | 259 |
Chapter 36 | Displaying Hidden Sheets Without a Password | 263 |
Chapter 37 | Searching Multiple Sheets and Workbooks | 269 |
Chapter 38 | Brighten Up Your Comments | 277 |
Chapter 39 | An Alternative to Message Boxes | 287 |
Chapter 40 | Working with Shapes | 291 |
Chapter 41 | Turning Your VBA Code into an Add-In | 295 |
Appendix A | ASCII Character Codes | 303 |
| Index | 309 |