Writing Excel Macros with VBA

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