Excel 2007 VBA Macro Programming

Develop custom Excel VBA macros Perfect for power users, this practical resource reveals how to maximize the features and functionality of Excel 2007. You'll get in-depth details on Excel VBA programming and application development followed by 21 real-world projects—complete with source code—that show you how to set up specific subroutines and functions. The book then explains how to include the subroutines in the Excel menu system and transform a set of interrelated VBA macros into an Excel add-in package. Create your own Excel 2007 VBA macros right away with help from this hands-on guide. Excel 2007 VBA Macro Programming shows you how to: Write and debug VBA code Create custom dialog boxes and buttons Maximize the Excel object model Write code to interact with a database Add functionality to your programs with API calls Insert class modules Develop custom menus for the Ribbon Animate objects in Excel Create and manipulate Pivot Tables in VBA Expand calculation and search functions Create full-fledged Excel add-ins Use VBA to work with XML files Contents 12 Acknowledgments 20 Introduction 22 Part I: Programming in Excel VBA 26 Chapter 1 The Basics 28 Exploring the Visual Basic Editor in Excel 28 Saving Off Your Macro 36 Enabling the Developer Item in the Excel Menu 37 Chapter 2 Variables, Arrays, Constants, and Data Types 38 Variables 38 Data Types 43 VBA Data Types 45 Arrays 47 User-Defined Types 50 Constants 50 Reserved Words 51 Chapter 3 Modules, Functions, and Subroutines 52 Modules 52 The Difference Between Subroutines and Functions 53 Writing a Simple Subroutine 54 Writing a Simple Function 55 Public and Private Functions and Subroutines 57 Argument Data Types 58 Optional Arguments 59 Passing Arguments by Value 59 Chapter 4 Programming Basics: Decisions and Looping 60 Decisions 61 Looping 64 Chapter 5 Strings and Functions and Message Boxes 70 Strings 70 Functions 74 Conversion Functions 76 Format Function 77 Date and Time Functions 81 SendKeys Command 87 Message Boxes 91 Chapter 6 Operators 96 Arithmetic Operators 97 Comparison Operators 99 Concatenation Operator 100 Logical Operators 100 Other Operators 104 Chapter 7 Debugging 106 Types of Errors 106 Design Time, Runtime, and Break Mode 107 Breakpoints 109 Using Stop Statements 109 Running Selected Parts of Your Code 109 The Debug Window 111 Events That Can Cause Problems When Debugging 113 Using Message Boxes in Debugging 113 Avoiding Bugs 115 Chapter 8 Errors and the Error Function 116 The Resume Statement 118 Implications of Error Trapping 119 Generating Your Own Errors 119 Chapter 9 Dialogs 122 Viewing Your Form 126 Displaying Your Form in Code 128 Populating Your Form 128 Default Toolbox Controls 129 Chapter 10 Common Dialog Control 138 Using the Common Dialog Control 139 Default Dialogs 145 Chapter 11 Command Bars and Buttons 148 Command Bars 149 Command Buttons 153 Part II: Object Models 156 Chapter 12 The Excel Object Model 158 Properties and Methods Explained 159 Using the Object Browser 168 Hierarchy 171 Recording Macros 172 Chapter 13 The Excel Object Model—Main Objects 176 Application Object 176 Workbook Object 181 Windows Object 183 Worksheet Object 188 Range Object 190 Chapter 14 Using Excel to Interact with Other Office Programs 196 Driving Microsoft Outlook 199 Driving Excel from Other Office Programs 201 Part III: Advanced Techniques in Excel VBA 204 Chapter 15 Charts and Graphs 206 Working with the Series Collection Object 209 Exporting a Chart as a Picture File 210 Using Macro Recording 210 Chapter 16 Working with Databases 212 ODBC Links 213 Using ADO 215 Chapter 17 API Calls 218 What Is an API Call? 218 Using an API Call 219 Chapter 18 Class Modules 230 Inserting a Class Module 231 Creating an Object 231 Creating a Collection 233 Using the PNames Collection 235 Chapter 19 Animation 238 Chapter 20 Working with XML Files 242 The XML Document 243 How Does XML Relate to VBA? 246 XML Maps 246 Customizing an XML Map 251 Importing and Exporting Data 253 Manipulating XML Files Programmatically 254 Chapter 21 The Ribbon 258 Creating a Ribbon Customization 259 Using the Custom UI Editor 259 Creating Code for Your Custom Buttons 263 Images 264 How Can You Use VBA Code with the Ribbon 264 More on the Ribbon 267 Chapter 22 Pivot Tables 274 Creating a Simple Pivot Table from the Front End of Excel 274 Creating a Pivot Table Using VBA 278 Using VBA with a Pivot Table 279 Advanced VBA for Pivot Tables 282 Points to Bear in Mind on Pivot Tables 283 Part IV: VBA in Action 284 Chapter 23 Converting Labels to Numbers and Numbers to Labels 286 Chapter 24 Transposing a Range of Cells 290 Chapter 25 Adding Formula Details into Comments 294 Chapter 26 Calculating a Range 298 Chapter 27 Reversing a Label 300 Chapter 28 Who Created the Workbook? 302 Chapter 29 Evaluating a Cell 306 Chapter 30 Sorting Worksheets into Alphabetical Order 310 Chapter 31 Replacing Characters in a String 312 Chapter 32 Timed Events 316 Chapter 33 Auto-Totaling a Matrix of Numbers 318 Chapter 34 Absolute and Relative Formulas 322 Chapter 35 Coloring Alternate Rows and Columns of the Spreadsheet 328 Chapter 36 Coloring Cells Containing Formulas 334 Chapter 37 Summing Cells by Reference to a Master Cell 338 Chapter 38 Globally Changing a Range of Values 342 Chapter 39 Displaying Hidden Sheets Without a Password 346 Chapter 40 Searching Multiple Sheets and Workbooks 352 Chapter 41 Brighten Up Your Comments 362 Test It Out 369 Chapter 42 Importing a CSV File Containing Leading Zeros 372 Chapter 43 Working with Shapes 378 Chapter 44 Turning Your VBA Code into an Add-In 382 Appendix: ASCII Character Codes 392 Index 398 A 398 B 399 C 399 D 401 E 402 F 403 G 404 H 404 I 404 K 404 L 405 M 405 N 406 O 406 P 407 Q 408 R 408 S 408 T 409 U 410 V 410 W 411 X 411 Y 411 Z 411 Publisher's Note: Products purchased from Third Party sellers are not guaranteed by the publisher for quality, authenticity, or access to any online entitlements included with the product. Develop custom Excel VBA macros Perfect for power users, this practical resource reveals how to maximize the features and functionality of Excel 2007. You'll get in-depth details on Excel VBA programming and application development followed by 21 real-world projects--complete with source code--that show you how to set up specific subroutines and functions. The book then explains how to include the subroutines in the Excel menu system and transform a set of interrelated VBA macros into an Excel add-in package. Create your own Excel 2007 VBA macros right away with help from this hands-on guide. Excel 2007 VBA Macro Programming shows you how to: "Perfect for power users, this practical resource reveals how to maximize the features and functionality of Excel 2007. You'll get in-depth details on Excel VBA programming and application development followed by 21 real-world projects - complete with source code that show you how to set up specific subroutines and functions. The book then explains how to include the subroutines in the Excel menu system and transform a set of interrelated VBA macros into an Excel add-in package. Create your own Excel 2007 VBA macros right away with help from this hands-on guide."--Jacket Develop powerful, custom Excel applications using Visual Basic for Applications. Designed for power users, this unique resource helps you maximize the features and functionality of Excel. The book covers Excel VBA programming and application development - from the fundamentals to advanced techniques. Twenty-one real-world projects - complete with working code - walk you step-by-step through the process of creating Excel VBA macros. New content includes coverage of the Ribbon, XML, pivot tables, and cubes This resource for power users helps maximize the features and functionality of Microsoft Excel�� by providing step-by-step instructions for creating VBA macros, and includes more than 21 real-world projects, complete with working code. Original.