Introduction xxvii Part I: Introduction to Excel VBA Chapter 1: Essentials of Spreadsheet Application Development 3 What Is a Spreadsheet Application? 3 Steps for Application Development 4 Determining User Needs 5 Planning an Application That Meets User Needs 6 Determining the Most Appropriate User Interface 8 Customizing the Ribbon 8 Customizing shortcut menus 8 Creating shortcut keys 9 Creating custom dialog boxes 9 Using ActiveX controls on a worksheet 10 Executing the development effort 11 Concerning Yourself with the End User 12 Testing the application 12 Making the application bulletproof 13 Making the application aesthetically appealing and intuitive 15 Creating a user Help system 16 Documenting the development effort 16 Distributing the application to the user 16 Updating the application when necessary 17 Other Development Issues 17 The user''s installed version of Excel 17 Language issues 17 System speed 18 Video modes 18 Chapter 2: Introducing Visual Basic for Applications 19 Getting a Head Start with the Macro Recorder 19 Creating your first macro 20 Comparing absolute and relative macro recording 23 Other macro recording concepts 27 Working with the Visual Basic Editor 32 Understanding VBE components 32 Working with the Project window 33 Working with a Code window 36 Customizing the VBA environment 38 The Editor Format tab 40 The General tab 41 The Docking tab 41 VBA Fundamentals 42 Understanding objects 43 Understanding collections 43 Understanding properties 44 Deep Dive: Working with Range Objects 47 Finding the properties of the Range object 48 The Range property 48 The Cells property 49 The Offset property 52 Essential Concepts to Remember 53 Don''t Panic--You Are Not Alone 55 Read the rest of the book 55 Let Excel help write your macro 55 Use the Help system 55 Use the Object Browser 56 Pilfer code from the Internet 57 Leverage user forums 57 Visit expert blogs 58 Mine YouTube for video training 58 Learn from the Microsoft Office Dev Center 59 Dissect the other Excel files in your organization 59 Ask your local Excel genius 59 Chapter 3: VBA Programming Fundamentals 61 VBA Language Elements: An Overview 61 Comments 63 Variables, Data Types, and Constants 64 Defining data types 65 Declaring variables 67 Scoping variables 69 Working with constants 72 Working with strings 73 Working with dates 74 Assignment Statements 75 Arrays 77 Declaring arrays 77 Declaring multidimensional arrays 78 Declaring dynamic arrays 79 Object Variables 79 User-Defined Data Types 80 Built-In Functions 81 Manipulating Objects and Collections 84 With-End With constructs 84 For Each-Next constructs 85 Controlling Code Execution 87 GoTo statements 88 If-Then constructs 88 Select Case constructs 93 Looping blocks of instructions 96 Chapter 4: Working with VBA Sub Procedures 105 About Procedures 105 Declaring a Sub procedure 106 Scoping a procedure 107 Executing Sub Procedures 108 Executing a procedure with the Run Sub/UserForm command 109 Executing a procedure from the Macro dialog box 109 Executing a procedure with a Ctrl+shortcut key combination 110 Executing a procedure from the Ribbon 111 Executing a procedure from a customized shortcut menu 111 Executing a procedure from another procedure 112 Executing a procedure by clicking an object 116 Executing a procedure when an event occurs 118 Executing a procedure from the Immediate window 118 Passing Arguments to Procedures 119 Error-Handling Techniques 123 Trapping errors 123 Error-handling examples 124 A Realistic Example That Uses Sub Procedures 127 The goal 128 Project requirements 128 What you know 128 The approach 129 Some preliminary recording 129 Initial setup 131 Code writing 132 Writing the Sort procedure 133 More testing 137 Fixing the problems 138 Utility availability 141 Evaluating the project 142 Chapter 5: Creating Function Procedures 143 Sub Procedures versus Function Procedures 143 Why Create Custom Functions? 144 An Introductory Function Example 144 Using the function in a worksheet 145 Using the function in a VBA procedure 146 Analyzing the custom function 146 Function Procedures 148 A function''s scope 150 Executing function procedures 150 Function Arguments 153 Function Examples 153 Functions with no argument 153 A function with one argument 156 A function with two arguments 159 A function with an array argument 159 A function with optional arguments 160 A function that returns a VBA array 162 A function that returns an error value 165 A function with an indefinite number of arguments 166 Emulating Excel''s SUM Function 168 Extended Date Functions 171 Debugging Functions 173 Dealing with the Insert Function Dialog Box 174 Using the MacroOptions method 174 Specifying a function category 176 Adding a function description manually 177 Using Add-Ins to Store Custom Functions 178 Using the Windows API 179 Windows API examples 179 Determining the Windows directory 180 Detecting the Shift key 181 Learning more about API functions 182 Chapter 6: Understanding Excel''s Events 183 What You Should Know about Events 183 Understanding event sequences 184 Where to put event-handler procedures 184 Disabling events 186 Entering event-handler code 187 Event-handler procedures that use arguments 188 Getting Acquainted with Workbook-Level Events 190 The Open event 191 The Activate event 192 The SheetActivate event 192 The NewSheet event 193 The BeforeSave event 193 The Deactivate event 193 The BeforePrint event 194 The BeforeClose event 195 Examining Worksheet Events 197 The Change event 198 Monitoring a specific range for changes 199 The SelectionChange event 203 The BeforeDoubleClick event 204 The BeforeRightClick event 205 Monitoring with Application Events 206 Enabling Application-level events 207 Determining when a workbook is opened 208 Monitoring Application-level events 209 Accessing Events Not Associated with an Object 210 The OnTime event 210 The OnKey event 212 Chapter 7: VBA Programming Examples and Techniques 217 Learning by Example 217 Working with Ranges 218 Copying a range 218 Moving a range 219 Copying a variably sized range 220 Selecting or otherwise identifying various types of ranges 221 Resizing a range 223 Prompting for a cell value 224 Entering a value in the next empty cell 225 Pausing a macro to get a user-selected range 226 Counting selected cells 228 Determining the type of selected range 229 Looping through a selected range efficiently 231 Deleting all empty rows 233 Duplicating rows a variable number of times 234 Determining whether a range is contained in another range 236 Determining a cell''s data type 237 Reading and writing ranges 238 A better way to write to a range 240 Transferring one-dimensional arrays 242 Transferring a range to a variant array 242 Selecting cells by value 243 Copying a noncontiguous range 244 Working with Workbooks and Sheets 246 Saving all workbooks 246 Saving and closing all workbooks 247 Hiding all but the selection 247 Creating a hyperlink table of contents 249 Synchronizing worksheets 250 VBA Techniques 251 Toggling a Boolean property 251 Displaying the date and time 251 Displaying friendly time 253 Getting a list of fonts 254 Sorting an array 256 Processing a series of files 257 Some Useful Functions for Use in Your Code 259 The FileExists function 259 The FileNameOnly function 259 The PathExists function 260 The RangeNameExists function 260 The SheetExists function 261 The WorkbookIsOpen function 261 Retrieving a value from a closed workbook 262 Some Useful Worksheet Functions 264 Returning cell formatting information 264 A talking worksheet 266 Displaying the date when a file was saved or printed 266 Understanding object parents 267 Counting cells between two values 268 Determining the last nonempty cell in a column or row 269 Does a string match a pattern? 270 Extracting the nth element from a string 272 Spelling out a number 272 A multifunctional function 273 The SHEETOFFSET function 274 Returning the maximum value across all worksheets 275 Returning an array of nonduplicated random integers 276 Randomizing a range 278 Sorting a range 279 Windows API Calls 280 Understanding API Declarations 280 Determining file associations 281 Determining default printer information 282<.