Pro Excel 2007 VBA (Pro)

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