| |
| |
| Acknowledgments | |
| |
| |
| Introduction | |
| |
| |
| |
| Programming in Excel VBA | |
| |
| |
| |
| The Basics | |
| |
| |
| Exploring the Visual Basic Editor in Excel | |
| |
| |
| |
| Variables, Arrays, Constants, and Data Types | |
| |
| |
| Variables | |
| |
| |
| Data Types | |
| |
| |
| VBA Data Types | |
| |
| |
| Arrays | |
| |
| |
| User-Defined Types | |
| |
| |
| Constants | |
| |
| |
| Reserved Words | |
| |
| |
| |
| Modules, Functions, and Subroutines | |
| |
| |
| Modules | |
| |
| |
| The Difference Between Subroutines and Functions | |
| |
| |
| Writing a Simple Subroutine | |
| |
| |
| Writing a Simple Function | |
| |
| |
| Public and Private Functions and Subroutines | |
| |
| |
| Argument Data Types | |
| |
| |
| Optional Arguments | |
| |
| |
| Passing Arguments by Value | |
| |
| |
| |
| Programming Basics: Decisions and Looping | |
| |
| |
| Decisions | |
| |
| |
| Looping | |
| |
| |
| |
| Strings and Functions and Message Boxes | |
| |
| |
| Strings | |
| |
| |
| Functions | |
| |
| |
| Conversion Functions | |
| |
| |
| Format Function | |
| |
| |
| Date and Time Functions | |
| |
| |
| SendKeys Command | |
| |
| |
| Message Boxes | |
| |
| |
| |
| Operators | |
| |
| |
| Arithmetic Operators | |
| |
| |
| Comparison Operators | |
| |
| |
| Concatenation Operator | |
| |
| |
| Logical Operators | |
| |
| |
| Other Operators | |
| |
| |
| |
| Debugging | |
| |
| |
| Types of Errors | |
| |
| |
| Design Time, Runtime, and Break Mode | |
| |
| |
| Breakpoints | |
| |
| |
| Using Stop Statements | |
| |
| |
| Running Selected Parts of Your Code | |
| |
| |
| The Debug Window | |
| |
| |
| Events That Can Cause Problems When Debugging | |
| |
| |
| Using Message Boxes in Debugging | |
| |
| |
| Avoiding Bugs | |
| |
| |
| |
| Errors and the Error Function | |
| |
| |
| The Resume Statement | |
| |
| |
| Implications of Error Trapping | |
| |
| |
| Generating Your Own Errors | |
| |
| |
| |
| Dialogs | |
| |
| |
| Viewing Your Form | |
| |
| |
| Displaying Your Form in Code | |
| |
| |
| Populating Your Form | |
| |
| |
| Default Toolbox Controls | |
| |
| |
| |
| Common Dialog Control | |
| |
| |
| Using the Common Dialog Control | |
| |
| |
| Default Dialogs | |
| |
| |
| |
| Command Bars and Buttons | |
| |
| |
| Command Bars | |
| |
| |
| Command Buttons | |
| |
| |
| |
| Object Models | |
| |
| |
| |
| The Excel Object Model | |
| |
| |
| Properties and Methods Explained | |
| |
| |
| Using the Object Browser | |
| |
| |
| Hierarchy | |
| |
| |
| Recording Macros | |
| |
| |
| |
| The Excel Object Model--Main Objects | |
| |
| |
| Application Object | |
| |
| |
| Workbook Object | |
| |
| |
| Windows Object | |
| |
| |
| Worksheet Object | |
| |
| |
| Range Object | |
| |
| |
| |
| Using Excel to Interact with Other Office Programs | |
| |
| |
| Driving Microsoft Outlook | |
| |
| |
| Driving Excel from Other Office Programs | |
| |
| |
| |
| Advanced Techniques in Excel VBA | |
| |
| |
| |
| Charts and Graphs | |
| |
| |
| |
| Working with Databases | |
| |
| |
| ODBC Links | |
| |
| |
| Using ADO | |
| |
| |
| |
| API Calls | |
| |
| |
| What Is an API Call? | |
| |
| |
| Using an API Call | |
| |
| |
| |
| Class Modules | |
| |
| |
| Inserting a Class Module | |
| |
| |
| Creating an Object | |
| |
| |
| Creating a Collection | |
| |
| |
| Using the PNames Collection | |
| |
| |
| |
| Animation | |
| |
| |
| |
| VBA in Action | |
| |
| |
| |
| Converting Labels to Numbers and Numbers to Labels | |
| |
| |
| |
| Transposing a Range of Cells | |
| |
| |
| |
| Adding Formula Details into Comments | |
| |
| |
| |
| Calculating a Range | |
| |
| |
| |
| Reversing a Label | |
| |
| |
| |
| Who Created the Workbook? | |
| |
| |
| |
| Evaluating a Cell | |
| |
| |
| |
| Sorting Worksheets into Alphabetical Order | |
| |
| |
| |
| Replacing Characters in a String | |
| |
| |
| |
| Timed Events | |
| |
| |
| |
| Auto Totaling a Matrix of Numbers | |
| |
| |
| |
| Absolute and Relative Formulas | |
| |
| |
| |
| Coloring Alternate Rows and Columns of the Spreadsheet | |
| |
| |
| |
| Coloring Cells Containing Formulas | |
| |
| |
| |
| Summing Cells by Reference to a Master Cell | |
| |
| |
| |
| Globally Changing a Range of Values | |
| |
| |
| |
| Displaying Hidden Sheets Without a Password | |
| |
| |
| |
| Searching Multiple Sheets and Workbooks | |
| |
| |
| |
| Brighten Up Your Comments | |
| |
| |
| |
| An Alternative to Message Boxes | |
| |
| |
| |
| Working with Shapes | |
| |
| |
| |
| Turning Your VBA Code into an Add-In | |
| |
| |
| |
| ASCII Character Codes | |
| |
| |
| Index | |