Writing Excel Macros with VBA

Writing Excel Macros with VBA 2e.pdf......Page
Table of Content......Page 2
Preface to the Second Edition......Page 8
Figure P-1. New objects in the Excel XP object hierarchy......Page 9
Organization of This Book......Page 10
About the Code......Page 11
Conventions in this Book......Page 12
Acknowledgments......Page 13
Figure 1-2. The Select Special utility......Page 15
1.2 Setting a Chart's Data Point Labels......Page 16
Figure 1-5. Set Data Labels dialog......Page 17
1.3 Topics in Learning Excel Programming......Page 18
Part I: The VBA Environment......Page 20
Table 2-1. Some Programming Languages......Page 21
2.2.1 Comments......Page 22
2.2.2 Readability......Page 23
2.2.3 Modularity......Page 25
Figure 3-2. The Project Explorer......Page 27
3.1.2 Project Contents......Page 28
3.1.2.4 Class modules......Page 29
Figure 3-3. A UserForm dialog box......Page 30
Figure 3-4. The Properties window......Page 31
3.3.2 The Object and Procedure List Boxes......Page 32
3.3.2.3 A UserForm object......Page 33
3.4 The Immediate Window......Page 34
Figure 3-11. The Docking options......Page 35
4.1.1.1 Navigating the code window at design time......Page 37
4.1.1.3 Bookmarks......Page 38
Figure 4-1. The Add Procedure dialog box......Page 39
Figure 4-2. An error message......Page 40
Figure 4-3. The Options dialog box......Page 41
4.5.3 Run-Time Errors......Page 42
Figure 4-7. The result of a logical error......Page 43
Figure 4-8. Top-and-bottom windows for easy debugging......Page 44
Example 4-2. Sample Code for Tracing Methods......Page 45
4.6.1.5 Set Next Statement (Ctrl-F9 or choose Set Next Statement from the Debug menu)......Page 46
Figure 4-10. The Locals and Watches windows......Page 47
Figure 4-11. The Add Watch dialog box......Page 48
4.7.1 Recording Macros......Page 49
Figure 4-12. Excel's Macro dialog box......Page 50
Part II: The VBA Programming Language......Page 52
5.3 Constants......Page 53
5.3.1 Enums......Page 54
5.4 Variables and Data Types......Page 56
5.4.1 Variable Declaration......Page 57
5.4.2 The Importance of Explicit Variable Declaration......Page 58
5.4.3 Numeric Data Types......Page 59
5.4.5 String Data Type......Page 60
5.4.8 Excel Object Data Types......Page 61
5.4.8.2 The Set statement......Page 62
5.4.9.2 Dynamic arrays......Page 63
5.4.10 Variable Naming Conventions......Page 64
Table 5-4. Naming Convention for Some Object Variables......Page 65
Figure 5-2. Examples of variable scope......Page 66
5.4.11.2 Module-level variables......Page 67
5.4.12.1 Static variables......Page 68
Example 5-2. ToComic() Modified to Use a Static Variable......Page 69
5.4.13 Variable Initialization......Page 70
Table 5-5. VBA Operators and Relations......Page 71
Figure 6-1. The message dialog displayed by Example 6-1......Page 73
6.2 Calling Subroutines......Page 74
Example 6-3. Using an Optional Argument......Page 75
6.3.2 Named Arguments......Page 76
Example 6-4. Testing the ByVal and ByRef Keywords......Page 77
Example 6-5. Passing an Argument by Value......Page 78
6.6 Project References......Page 79
6.6.1 Fully Qualified Procedure Names......Page 80
Table 7-1. VBA Functions[1]......Page 81
Table 7-2. The MsgBox Buttons Argument Values......Page 82
7.2 The InputBox Function......Page 83
7.3 VBA String Functions......Page 84
7.4.1.4 The IsNumeric function......Page 88
Example 7-1. The Switch Function......Page 89
7.4.5 The Beep Statement......Page 90
7.5.1 The On Error Goto Label Statement......Page 91
7.5.4 The On Error Resume Next Statement......Page 92
7.5.5 The Resume Statement......Page 93
Example 7-2. Error Handling with the Resume Statement......Page 94
8.2 The For Loop......Page 95
Example 8-1. Finding the First Nonempty Cell......Page 96
8.3 The For Each Loop......Page 97
8.4 The Do Loop......Page 98
8.5 The Select Case Statement......Page 99
Table 8-2. Some Date- and Time-Related Functions......Page 100
Table 8-3. Format Function Examples......Page 101
Part III: Excel Applications and the Excel Object Model......Page 102
9.1.1 Properties......Page 103
Table 9-1. Some Excel Collection Objects......Page 104
9.2.1 The Base of a Collection......Page 105
Figure 9-1. A small portion of the Excel object model (the tag means that the object is new in version X of Excel)......Page 106
9.4 Object Model Syntax......Page 107
9.5 Object Variables......Page 108
Figure 9-2. Integer variables in memory......Page 109
Figure 9-4. Two object variables referencing the same object......Page 110
9.5.5 The Is Operator......Page 111
9.5.7 Global Members......Page 112
Table 9-2. Excel global members......Page 113
10.1 Providing Access to an Application's Features......Page 114
Figure 10-1. Dialog for a charting utility......Page 115
Figure 10-3. The Commands tab of the Customize dialog......Page 116
10.2 Where to Store an Application......Page 117
10.2.2 Excel Templates......Page 118
Figure 10-4. The New dialog showing template icons......Page 119
10.2.3.1 Creating an add-in......Page 120
Figure 10-7. The Add-Ins dialog......Page 121
Figure 10-9. Protection tab......Page 122
10.2.3.2 Characteristics of an add-in......Page 123
10.3.1 Creating the Source Workbook......Page 124
Example 10-2. The CreateCustomMenuItem Procedure......Page 125
10.3.4 Final Steps......Page 126
11.3 Accessing an Event Procedure......Page 127
Table 11-1. Worksheet Events (DocEvents)......Page 128
Table 11-2. Workbook Events......Page 129
Table 11-4. Application Events......Page 130
Figure 11-2. Application-level events......Page 131
Table 11-5. Refresh Events......Page 132
Figure 12-2. An Office menu......Page 133
12.1.3 Command-Bar Controls......Page 134
12.2 The CommandBars Collection......Page 135
Example 12-1. Listing Excel's CommandBar Objects......Page 136
Example 12-2. Creating a New Toolbar......Page 137
12.4 Command-Bar Controls......Page 138
12.5 Built-in Command-Bar-Control IDs......Page 139
Example 12-3. Code to Generate a List of Control IDs......Page 140
Example 12-4. Outputting the IDs of Command-Bar Controls......Page 141
Example 12-5. An Example Menu......Page 142
Example 12-6. An Example Toolbar......Page 143
Example 12-8. Macro Invoked by Selecting a Composer from the List Box......Page 144
12.9 Augmenting the SRXUtils Application......Page 145
Figure 12-5. DataSheet of SRXUtils.xls......Page 146
Example 12-11. The CreateCustomMenus Procedure......Page 147
Example 12-12. The CreateCustomMenu Procedure......Page 148
Example 12-13. Code for the Printing Procedures......Page 149
Example 12-14. The RunUtility Procedure......Page 150
Example 12-16. The Workbook_BeforeClose Event Handler......Page 151
Example 12-17. The DeleteCustomMenus Procedure......Page 152
Table 13-1. XlBuiltInDialog constants and values......Page 153
13.1 The Show Method......Page 155
Figure 14-1. A UserForm dialog box (design time)......Page 157
14.3 ActiveX Controls......Page 158
Figure 14-2. A Sort dialog box......Page 159
Figure 14-3. Control toolbars......Page 160
14.6 Example: The ActivateSheet Utility......Page 161
Table 14-1. Nondefault Properties of the ListBox Control......Page 162
14.6.3.1 Cancel button code......Page 163
Example 14-1. The ActivateSelectedSheet Procedure......Page 164
Example 14-3. The UserForm_Initialize Event Procedure......Page 165
14.7 ActiveX Controls on Worksheets......Page 166
Table 14-4. Members of the OLEObject object......Page 167
Table 14-5. ProgIDs for ActiveX Controls......Page 169
Table 15-2. Excel 10 objects and their child counts......Page 171
15.2 Excel Enums......Page 173
Table 15-4. The Excel Enums and their number of constants (Excel 8)......Page 174
Figure 15-1. The Microsoft Object Browser......Page 175
Figure 16-1. The Excel Application object and its children (the tag means that the object is new in version X of Excel)......Page 177
Figure 16-2. The Excel Application object along with its hidden children......Page 178
Table 16-1. Application object members[2]......Page 179
Table 16-2. Members that return child objects......Page 182
16.1.2 Members that Affect the Display......Page 183
16.1.3 Members that Enable Excel Features......Page 184
Table 16-3. Special Keys for the Key Parameter......Page 186
16.1.4.2 OnTime method......Page 187
16.1.5.3 Calculation property (R/W Long)......Page 188
Table 16-4. XLFileFormat constants......Page 189
16.1.6.5 GetOpenFilename method......Page 190
16.1.6.6 GetSaveAsFilename method......Page 191
16.1.7 Members that Affect the Current State of Excel......Page 192
16.1.8.1 ConvertFormula method......Page 195
16.1.8.2 Evaluate method......Page 196
16.1.8.3 Goto method......Page 197
16.1.9.1 CellFormat, FindFormat and ReplaceFormat object......Page 199
16.1.9.2 InputBox method......Page 200
Figure 16-3. Illustration of Type = 64......Page 201
16.1.9.5 Intersect method......Page 202
16.2.1 Name Objects and the Names Collections......Page 203
16.2.2 The Windows Collection and Window Objects......Page 205
16.2.3 The WorksheetFunction Object......Page 206
17.1.1 Add Method......Page 208
17.1.5 Open Method......Page 209
17.1.6 OpenText Method......Page 210
Figure 17-2. A comma-delimited text file opened in Excel......Page 212
Table 17-1. Members of the Workbook object......Page 213
Figure 17-4. Children of the Workbook object......Page 214
Table 17-2. Members of Workbook that return children......Page 215
17.2.4 FileFormat Property (Read-Only Long )......Page 216
17.2.6 HasPassword Property (Read-Only Boolean)......Page 217
17.2.10 Protect Method......Page 218
17.2.14 SaveAs Method......Page 219
17.3.1 The CustomView Object......Page 220
17.3.4 The Styles Collection and the Style Object......Page 221
17.4 Example: Sorting Sheets in a Workbook......Page 222
Example 17-2. The SortAllSheets Procedure......Page 223
Table 18-1. Members of the Worksheet Object......Page 225
Table 18-2. Members That Return Objects......Page 226
Figure 18-2. A data form......Page 232
Figure 18-4. The end result......Page 233
Table 18-3. Members of the PageSetup Object......Page 235
18.3.1 The Protection Object......Page 236
18.3.2 The AllowEditRange Object......Page 237
Figure 18-7. Print sheets dialog......Page 238
18.4.1.2 Print button......Page 239
Example 18-3. The cmdPrint_Click Event Handler......Page 240
Example 18-5. The PrintSelectedSheets Procedure......Page 241
Table 19-1. Members of the Range Object......Page 243
19.1 The Range Object as a Collection......Page 244
Table 19-2. Excel Members That Return a Range Object......Page 245
19.2.1 Range Property......Page 246
19.2.2 Cells Property......Page 247
19.2.3 Column, Columns, Row, and Rows Properties......Page 248
Figure 19-1. A noncontiguous range......Page 249
Figure 19-2. The range as a union......Page 250
19.3.3 Address Property (Read-Only String)......Page 251
19.3.4 AutoFill Method......Page 252
Figure 19-4. Autofilling B1:B5 in Figure 19-3......Page 253
Figure 19-5. A worksheet before autofiltering......Page 254
19.3.7 AutoFormat Method......Page 255
19.3.8 BorderAround Method......Page 257
19.3.11 ColumnDifferences and RowDifferences Methods......Page 258
19.3.13 Width, Height, Top, and Left Properties......Page 259
19.3.14 Consolidate Method......Page 260
Figure 19-8. Sheet3 before consolidation......Page 261
19.3.15 Copy and Cut Methods......Page 262
Figure 19-11. Illustrating CurrentRegion......Page 263
Figure 19-12. Illustrating the Dependents property......Page 264
19.3.24 Fill Methods......Page 265
19.3.25 Find Method......Page 266
19.3.26 FindNext and FindPrevious Methods......Page 267
Figure 19-13. Illustrating the Formula property......Page 268
Figure 19-14. Illustrating the FormulaArray property......Page 269
19.3.32 IndentLevel Property and InsertIndent Method......Page 270
19.3.35 Merge-Related Methods and Properties......Page 271
19.3.39 PasteSpecial Method......Page 272
19.3.40 PrintOut Method......Page 273
19.3.42 Replace Method......Page 274
19.3.45 Sort Method......Page 275
19.3.46 SpecialCells Method......Page 276
19.3.47 TextToColumns Method......Page 277
Table 19-3. FieldInfo Values for xlDelimited Text......Page 278
19.3.48 Value Property......Page 279
19.4.1 The Areas Collection......Page 280
Figure 19-19. Illustrating the Border object......Page 281
Figure 19-21. The xlInsideVertical constant......Page 282
Table 19-4. Some Common Colors......Page 283
19.4.3.3 LineStyle property......Page 284
19.4.5 The Comment Object......Page 285
19.4.7 The FormatConditions Collection......Page 286
Figure 19-24. A conditionally formatted range......Page 287
19.4.8.2 Pattern property......Page 288
19.4.11 The Validation Object......Page 289
Figure 19-25. The Settings tab of the Data Validation dialog......Page 290
Figure 19-26. The Input Message tab of the Data Validation dialog......Page 291
Figure 19-27. The Error Alert tab of the Data Validation dialog......Page 292
Example 19-1. The GetUsedRange Function......Page 293
19.6 Example: Selecting Special Cells......Page 294
19.6.1 Designing the Utility......Page 295
19.6.2.2 Control names......Page 296
19.6.3 Writing the Code......Page 297
Example 19-5. The cmdClose_Click Event Procedure......Page 298
Example 19-8. The GetSearchRange Procedure......Page 299
Example 19-9. The cmdSelect_Click Event Procedure......Page 300
Example 19-10. The SelectIfDifferent Procedure......Page 301
Example 19-11. The SelectIfSame Procedure......Page 302
Example 19-12. The SelectIfEmpty Procedure......Page 303
Example 19-13. The cmdCompleteColumns_Click Procedure......Page 304
Table 20-1. Source Data for PivotTable (for 1998)......Page 305
Figure 20-1. Step 1 in the PivotTable wizard......Page 307
Figure 20-3. Step 3 in the PivotTable wizard......Page 308
Figure 20-6. The PivotTable......Page 309
20.3 The PivotTableWizard Method......Page 310
20.4 The PivotTable Object......Page 312
Example 20-1. The CreatePivotFields Procedure......Page 313
Example 20-2. The ShowFields Procedure......Page 314
Example 20-3. The CreatePivot Procedure......Page 316
20.5 Properties and Methods of the PivotTable Object......Page 317
20.5.1.1 ColumnFields property......Page 318
20.5.1.6 RowFields property......Page 319
Figure 20-9. No grand totals......Page 320
Figure 20-10. No totals at all......Page 321
20.5.3.3 DataLabelRange property......Page 322
20.5.3.5 RowRange property......Page 323
Figure 20-18. The TableRange2 range......Page 324
20.5.4 PivotSelect and PivotSelection......Page 325
Figure 20-20. Reversing the word order to select company data for Boston only......Page 326
20.5.5.2 CalculatedFields method......Page 327
20.5.5.3 Errors-related properties......Page 328
20.5.5.7 Refreshing a pivot table......Page 329
20.5.5.12 SourceData property......Page 330
Table 20-3. Members of the PivotField Object......Page 331
20.7.1 AutoShow-Related Members......Page 332
20.7.2 Sorting Pivot Fields......Page 333
20.7.3.1 Function property......Page 334
Figure 20-26. DataRange for Store Type......Page 335
20.7.6 Name, Value, and SourceName Properties......Page 336
20.7.8 Data Field Calculation......Page 337
20.7.8.2 Calculations not requiring a BaseField/BaseItem......Page 338
Figure 20-29. Calculation = xlPercentOfRow......Page 339
Figure 20-31. Calculation = xlPercentOfTotal......Page 340
Figure 20-32. Illustrating a calculation......Page 341
Figure 20-34. Illustrating the Calculation property......Page 342
20.7.12 MemoryUsed Property......Page 343
Figure 20-35. ShowAllItems = False......Page 344
Figure 20-36. ShowAllItems = True......Page 345
Table 20-4. Values for the Subtotals Method's Index Parameter......Page 346
20.8.2 MemoryUsed Property......Page 347
20.9 The PivotItem Object......Page 348
20.9.2 LabelRange Property......Page 349
20.9.6 RecordCount Property......Page 350
Figure 20-39. Illustrating ShowDetail......Page 351
20.10 PivotCell and PivotItemList Objects......Page 352
20.10.1 ColumnItems, RowItems, and DataField Properties......Page 353
20.10.2 CustomSubtotalFunction Property......Page 354
20.10.4 PivotTable, PivotField, and PivotItem Properties......Page 355
20.11 Calculated Items and Calculated Fields......Page 356
Figure 20-43. Illustrating a calculated field and calculated item......Page 357
Figure 20-44. The output of ListFormulas......Page 358
20.12.1.1 List box......Page 359
20.12.2.2 Cancel button code......Page 360
Example 20-9. The Initialize Event......Page 361
Example 20-10. The PrintSelectedPTs Procedure......Page 362
21.1 Chart Objects and ChartObject Objects......Page 363
21.2 Creating a Chart......Page 364
21.2.2 Creating Embedded Charts......Page 365
Figure 21-4. The results of creating an embedded chart object......Page 366
Example 21-2. The CreateChart Procedure......Page 367
Figure 21-6. Illustrating z-order......Page 369
Table 21-1. ChartType Constants......Page 370
21.3.1.1 ApplyCustomType method......Page 372
21.4 Children of the Chart Object......Page 373
Figure 21-10. Axes-related objects......Page 374
21.6.2.1 HasTitle property (R/W Boolean)......Page 377
21.6.2.2 The Border property and the Border object......Page 379
21.6.4 CategoryType Property and BaseUnit Property......Page 380
21.6.6 Display Units......Page 381
21.6.7 Gridline-Related Properties and the Gridline Object......Page 382
21.6.9 Tick Mark-Related Properties......Page 383
21.6.9.1 The TickLabels object......Page 384
21.6.10 Units-Related Properties......Page 385
Figure 21-13. Illustrating ReversePlotOrder (before)......Page 386
21.7 The ChartArea Object......Page 387
21.8 The ChartGroup Object......Page 388
Figure 21-16. The ChartGroup object......Page 389
21.8.2 DropLines......Page 390
Figure 21-19. SeriesLines......Page 391
Figure 21-21. A data table......Page 392
Figure 21-23. The Legend object......Page 393
21.12.2 The LegendKey Object......Page 394
21.14 The PlotArea Object......Page 395
Table 21-4. Members of the Series Object......Page 396
21.15.1 Adding a New Series......Page 397
21.15.2 The DataLabel Object......Page 398
Figure 21-27. Illustrating the data point legend......Page 399
Figure 21-28. Explosion = 20......Page 400
21.15.3.2 MarkerSize and MarkerStyle......Page 401
Table 21-7. Members of the Chart Object......Page 402
Table 21-8. Members that Return Children......Page 403
21.16.1 ChartWizard Method......Page 404
21.16.2 Export Method......Page 405
21.17 Example: Scrolling Through Chart Types......Page 406
Example 21-3. Code in the Chart Sheet's Code Module......Page 407
Example 21-4. The ChartTypes.txt File......Page 408
Figure 21-32. Print Charts dialog......Page 409
21.18.1.3 Cancel button......Page 410
21.18.2.4 The Form's Initialize event......Page 411
21.18.2.5 The PrintCharts procedure......Page 412
Figure 21-33. A data label in edit mode......Page 413
Figure 21-35. DataSheet......Page 414
Example 21-11. The LabelDataSeries Procedure......Page 415
Example 21-13. The Option Buttons' Click Events......Page 417
Example 21-15. The DoDataLabels Procedure......Page 418
Example 21-16. The cmdUndo_Click Event Procedure......Page 420
Figure 22-2. A smart tag in Excel XP......Page 421
22.3 SmartTag Object......Page 422
22.4 SmartTagAction Object......Page 423
22.5 SmartTagOptions Object......Page 424
Part IV: Appendixes......Page 425
A.2 Z-Order......Page 426
A.3 Creating Shapes......Page 427
Table A-1. MsoAutoShapeType Constants (and Values)......Page 428
Example A-2. The Delay Procedure......Page 429
Example A-4. DrawSine2, to Generate a Dampened Sine Curve of Small Stars......Page 430
Example A-5. Code to Produce a Random Series of Stars......Page 431
Example A-6. The DrawHypocycloid Procedure......Page 432
Figure A-5. A hypocycloid......Page 433
Figure A-7. The Diagram dialog box......Page 434
Table A-4. Members of the DiagramNodeChildren object......Page 435
Example B-1. The GetInstalledPrinters Procedure......Page 437
Figure B-1. Installed printers......Page 439
C.1 Built-in Command-Bar Controls......Page 440
Figure D-1. FaceIDs for icons 0 -799......Page 458
Figure D-2. FaceIDs for icons 800 -1599......Page 459
Figure D-3. FaceIDs for icons 1600 -2399......Page 460
Figure D-4. FaceIDs for icons 2400 -3199......Page 461
Figure D-5. FaceIDs for icons 3200 -3399......Page 462
E.2 Getting a Reference to the Excel Application Object......Page 464
E.2.1.1 The CreateObject function......Page 465
E.2.1.3 No object library reference......Page 466
Appendix F. High-Level and Low-Level Languages......Page 468
F.1 BASIC......Page 469
F.2 Visual Basic......Page 470
F.3 C and C++......Page 471
F.4 Visual C++......Page 472
F.5 Pascal......Page 473
F.7 COBOL......Page 474
F.8 LISP......Page 475
Appendix G. New Objects in Excel XP......Page 477
Properties......Page 482
Figure G-2. The Watch Window......Page 489
Colophon......Page 490