Jerry Kohl, president of Brighton Collectibles
Develop your Excel macro programming skills using VBA instantly with proven techniques
Automate Reports
Handle Errors
Master Pivot Tables
Produce Charts
Build User-Defined Functions
Migrate to Excel 2007
Query Web Data
Build Dialog Boxes
Use Data Visualizations
Automate Word
You are an expert in Excel, but the macro recorder doesnt work and you cant make heads or tails out of the recorded code. If this is you, buy this book. Macros that you record today might work today but not tomorrow. Recorded macros might handle a dataset with 14 records but not one with 12 or 16 records. These are all common problems with the macro recorder that unfortunately cause too many Excel gurus to turn away from writing macros. This book shows you why the macro recorder fails and the steps needed to convert recorded code into code that will work every day with every dataset. The book assumes that you know Excel well, but there is no need for prior programming experience. This book describes everything you could conceivably need to know to automate reports and design applications in Excel VBA. Whether you want to automate reports for your office or design full-blown applications for others, this book is for you.
Introduction 1
1 Unleash the Power of Excel with VBA 7
2 This Sounds Like BASIC, So Why Doesnt It Look Familiar? 29
3 Referring to Ranges 61
4 User-Defined Functions 75
5 Looping and Flow Control 101
6 R1C1-Style Formulas 121
7 Whats New in Excel 2007 and What's Changed 135
8 Create and Manipulate Names in VBA 143
9 Event Programming 155
10 UserForms--An Introduction 177
11 Creating Charts 197
12 Data Mining with Advanced Filter 249
13 Using VBA to Create Pivot Tables 281
14 Excel Power 337
15 Data Visualizations and Conditional Formatting 373
16 Reading from and Writing to the Web 393
17 XML in Excel 2007 413
18 Automating Word 421
19 Arrays 441
20 Text File Processing 449
21 Using Access as a Back End to Enhance Multi-User Access to Data 461
22 Creating Classes, Records, and Collections 477
23 Advanced UserForm Techniques 493
24 Windows Application Programming Interface (API) 517
25 Handling Errors 529
26 Customizing the Ribbon to Run Macros 543
27 Creating Add-Ins 569
Index 577