Pro Excel 2007 VBA : [learn to build high-performance applications in Excel 2007 using VBA

Pro Excel 2007 VBA is a professionals’ guide to understanding and implementing the key principles and techniques of programming Microsoft Office Excel 2007. Automating complex spreadsheets, streamlining time-intensive work processes, gaining the competitive edge, and making more money for your businesses: these are all reasons to take Excel to the next level. This book teaches how to do just that, with Jim DeMarco using his own real-life experience to develop your understanding to this professional level. The text dives right into teaching the most efficient way to create complex and feature rich VBA code, exposing all the new features and capabilities that make Excel 2007 such an exciting edition to work with. Prelims......Page 1 Contents at a Glance......Page 7 Contents......Page 9 About the Author......Page 13 About the Technical Reviewer......Page 15 Acknowledgments......Page 17 How This Book Is Structured......Page 19 Prerequisites......Page 20 Contacting the Author......Page 21 Macro Security Settings......Page 23 Trusted Locations......Page 24 Lowering the Security Level......Page 25 The Visual Basic Development Environment......Page 26 The Immediate Window......Page 32 The Locals Window......Page 33 Locals Window Elements......Page 34 Watch Window Elements......Page 35 Recording a Macro......Page 36 Formatting the Table......Page 38 Adding Totals......Page 39 Same Task, Different Code......Page 40 Writing a Macro in the VBE......Page 42 More Macro Security......Page 43 The Object Browser......Page 46 Object Browser Window Elements......Page 47 Standard Code Modules......Page 49 Functions......Page 50 Class Modules......Page 51 The cEmployee Class......Page 53 Using the cEmployee Class......Page 55 The Class-y Way of Thinking......Page 57 UserForms......Page 58 Toolbox Window Elements......Page 59 Object-Oriented Programming: An Overview......Page 61 OOP: Is It Worth the Extra Effort?......Page 62 Summary......Page 63 Importing Access Data......Page 65 Simplifying the Code......Page 68 Importing Text Data......Page 70 Macro Recorder–Generated Text Import Code......Page 73 TextFileColumnDataTypes Property......Page 75 Using DAO in Excel 2007......Page 76 DAO Example 1: Importing Access Data Using Jet......Page 77 DAO Example 2: Importing Access Data Using ODBC......Page 82 DAO Example 3: Importing SQL Data Using ODBC......Page 87 ADO Example 1: Importing SQL Data......Page 89 ADO Example 2: Importing SQL Data Based on a Selection......Page 97 ADO Example 3: Updating SQL Data......Page 102 Of Excel, Data, and Object Orientation......Page 109 Using the cExcelSetup and cData Objects......Page 117 Summary......Page 118 Importing XML in Excel 2007......Page 121 Appending XML Data......Page 128 Saving XML Data......Page 129 Building an XML Data Class......Page 130 A Final Test......Page 139 Inside the Excel 2007 XML File Format......Page 141 Viewing the XML......Page 142 The _rels Folder......Page 143 The [Content_Types].xml File......Page 145 Relationships......Page 147 Adding a Ribbon to Run Your Custom Macros......Page 150 Creating the XML File That Contains the Markup to Modify the UI......Page 151 Summary......Page 154 Designing the Form......Page 155 UserForm Toolbox Controls......Page 157 The Working Class......Page 161 Coding the UserForm......Page 165 Creating Wizard-Style Data Entry UserForms......Page 172 Laying Out the Wizard Form......Page 174 The Personal Information Page......Page 176 The Address Information Page......Page 178 The Equipment Information Page......Page 180 The Access Information Page......Page 181 HRWizard Classes......Page 182 The HRWizard Business Objects......Page 183 The Data Class......Page 191 Managing the Wizard......Page 194 Initializing the Application......Page 200 Summary......Page 213 Getting Started......Page 215 Looking at the Code......Page 220 Summarizing with Pie Charts......Page 224 Creating the Pie Chart......Page 228 A Look at the Code......Page 232 More Pie for Everyone......Page 233 Storing Chart Location in an Array......Page 238 Completing the PlaceChartDynamic Procedure......Page 239 Testing the Code......Page 242 Summary......Page 243 Putting Data into a PivotTable Report......Page 245 The Macro Code......Page 251 Refreshing Data in an Existing PivotTable Report......Page 257 Blank Data Records......Page 260 Changing the Number Format......Page 264 Changing Field Names......Page 265 Adding Formatting to a PivotTable Report......Page 267 Summary......Page 269 The Debugger’s Toolkit......Page 271 Option Explicit......Page 274 Displaying Information with Message Boxes......Page 275 Using the Debug Object......Page 279 Stepping Through Code......Page 283 Checking Variables in Break Mode......Page 289 The Watch Window......Page 294 Is the File There?......Page 297 Trapping Specific Errors......Page 300 Debugging the Error Handler......Page 304 Summary......Page 307 Creating a Report in Word......Page 309 The Helper Functions......Page 312 Creating an Instance of Word......Page 313 Adding Charts to the Report......Page 317 Creating a PowerPoint Presentation......Page 320 Coding the Presentation......Page 321 PowerPoint Helper Functions......Page 322 Completing the MakePowerPointPresentation Procedure......Page 326 Running the Code......Page 328 Adding Text to the Chart Slides......Page 330 Summary......Page 336 Using ActiveX Components in Your Excel 2007 Projects......Page 337 Using an ActiveX Component in Excel 2007......Page 338 Excel in the .NET World......Page 345 Retrieving Data Using .NET......Page 349 Creating a Custom Task Pane and Data Input Form Using .NET......Page 364 Summary......Page 372 Index......Page 373 Pro Excel 2007 VBA is the complete guide to creating Excel VBA applications, including coverage of new features in Excel 2007. Covering object–oriented programming techniques, debugging, and error handling methodologies, this book quickly turns novice into expert as it explores COM and.NET components and the advantages of compiled code. The book also covers XML for Excel web development. This book is unique insomuch that author Jim DeMarco is a true expert in the field of Excel programming, and he conveys the Visual Basic for Applications story in the context of the real world. Not just another dry programming guide, Pro Excel 2007 VBA is a journey from novice to expert where the author is your traveling companion. Bon voyage! This is a professionals’ guide to understanding and implementing the key principles and techniques of programming Microsoft Office Excel 2007. Automating complex spreadsheets, streamlining time-intensive work processes, gaining the competitive edge, and making more money for your businesses: these are all reasons to take Excel to the next level. This book teaches how to do just that, with the author using his own real-life experience to develop your understanding to this professional level. The text teaches the most efficient way to create complex and feature rich VBA code, exposing all the new features and capabilities that make Excel 2007 so exciting. Explaining how to implement the key principles and techniques of programming Microsoft Office Excel 2007, a detailed manual provides instructions on building Excel applications using VBA, with coverage of such topics as the automation of complex spreadsheets, the macro recorder and code modules, using XML, charting, pivot tables, debugging, .NET integration, and more. Original. (Intermediate/Advanced) Covering object-oriented programming techniques, debugging, and error handling methodologies, this book quickly turns novice into expert as it explores COM and .NET components and the advantages of compiled code. The book also covers XML for Excel Web development