Index
Symbols
3-D format, changing, 230–234
3-D rotation settings, 224–229
32-bit API declarations, changing to 64-bit, 538
64-bit API declarations, changing 32-bit declarations to, 538
A
A1-style references, 127–128
About dialog, customizing, 541
AboutMrExcel() procedure, 541
above/below average cells, formatting, 383
absolute mode, 25
absolute references, 133
accelerator keys, displaying, 529
Access databases. See databases
Activate event, 187
active control, coloring, 530–532
ActiveFilters property, 289
ActiveX controls
attaching macros to, 583–584
right-click menu for, 360–362
ActiveX data objects. See ADO
Add3ColorScale() procedure, 375
AddAboveAverage method, 383
AddChart method, 203
AddControl event, 187, 195, 199
AddCrazyIcons() procedure, 382
AddGlowToTitle() procedure, 223
add-ins
Add-Ins dialog, 588
characteristics of, 587–588
closing, 593
converting workbooks to, 588–590
hidden workbooks as alternative to add-ins, 593–594
installing, 591
removing, 593
security, 592
Add-Ins dialog, 588
Addition procedure, 81
AddTransfer() procedure, 480–481
AddTwoDataBars() procedure, 381
ADO (ActiveX data objects)
compared to DOA (data access objects), 477
connections, 478
cursors, 478
fields
adding on-the-fly, 489–490
checking existence of, 488
lock type, 479
overview, 478–480
records
adding, 480–481
deleting, 485
retrieving, 481–483
summarizing, 485–486
updating, 483–485
recordsets, 478
tables
adding on-the-fly, 489
checking existence of, 487–488
ADOAddField() procedure, 489–490
ADOCreateReplenish() procedure, 489
ADOWipeOutAttribute() procedure, 485
Advanced Filter
building with Excel interface, 258
case study: creating reports for each customer, 280–283
criteria ranges
case study, 268
explained, 265–266
formula-based conditions, 268–275
logical AND criteria, 267
logical OR criteria, 267
extracting unique list of values, 258–264
getting unique combinations of two or more fields, 263–264
with user interface, 259
with VBA code, 260–263
Filter in Place, 275–276, 283–285
overview, 257
xlFilterCopy with all records, 276–280
copying all columns, 277
copying a subset of columns and reordering, 278–280
AdvancedFilter method, 260
AfterUpdate event, 190, 193–197
ahtAddFilterItem API function, 546
aht_apiGetOpenFileName API function, 544–546
aht_apiGetSaveFileName API function, 544–546
AllColumnsOneCustomer() procedure, 277
AllowMultipleFilters property, 289
API declarations
32-bit versus 64-bit, 538
ahtAddFilterItem, 546
aht_apiGetOpenFileName, 544–546
aht_apiGetSaveFileName, 544–546
calling, 537
DisplaySize, 540
explained, 535–536
finding, 547
FindWindow, 541–543
GetComputerName, 538–539
GetSystemMenu, 541–542
KillTimer, 542–543
lOpen, 539
PlayWavSound, 543
SetTimer, 542–543
ShellAbout, 541
AppEvent_AfterCalculate() event, 176
AppEvent_NewWorkbook() event, 177
AppEvent_ProtectedViewWindowActivate() event, 177
AppEvent_ProtectedViewWindowBeforeClose() event, 177
AppEvent_ProtectedViewWindowDeactivate() event, 177
AppEvent_ProtectedViewWindowOpen() event, 177
AppEvent_ProtectedViewWindowResize() event, 177
AppEvent_SheetActivate() event, 177
AppEvent_SheetBeforeDoubleClick() event, 178
AppEvent_SheetBeforeRightClick() event, 178
AppEvent_SheetCalculate() event, 178
AppEvent_SheetChange() event, 178
AppEvent_SheetDeactivate() event, 178
AppEvent_SheetFollowHyperlink() event, 178
AppEvent_SheetPivotTableUpdate() event, 178
AppEvent_SheetSelectionChange() event, 178
AppEvent_WindowActivate() event, 179
AppEvent_WindowDeactivate() event, 179
AppEvent_WindowResize() event, 179
AppEvent_WorkbookActivate() event, 179
AppEvent_WorkbookAddinInstall() event, 179
AppEvent_WorkbookAddinUninstall() event, 179
AppEvent_WorkbookAfterXmlExport() event, 181
AppEvent_WorkbookAfterXmlImport() event, 181
AppEvent_WorkbookBeforeClose() event, 179
AppEvent_WorkbookBeforePrint() event, 180
AppEvent_WorkbookBeforeSave() event, 180
AppEvent_WorkbookBeforeXmlExport() event, 181
AppEvent_WorkbookBeforeXmlImport() event, 181
AppEvent_WorkbookNewSheet() event, 180
AppEvent_WorkbookOpen() event, 180
AppEvent_WorkbookPivotTableCloseConnection() event, 180
AppEvent_WorkbookPivotTableOpenConnection() event, 180
AppEvent_WorkbookRowsetComplete() event, 181
AppEvent_WorkbookSync() event, 181
application-level events, 176–181
trapping, 494–495
Application.OnTime, 399–400
scheduling
macros to run every two minutes, 403–404
macros to run x minutes in the future, 401–402
scheduled procedures with ready mode, 400
verbal reminders, 402
specifying a window of time for updates, 400
applications
checking version of, 144–145
compatibility issues
Compatibility mode, 145
explained, 144
historical stock/fund quotes, 362–363
ApplyLayout method, 203
ApplyTexture() procedure, 220
ApplyThemeColor() procedure, 220
Areas collection, 77
arrays
advantages of, 457–458
array formulas, 137–138
declaring, 453–454
defined, 453
dynamic arrays, 459–460
emptying, 456–457
filling, 455–456
multidimensional arrays, 454
names, 153–154
one-dimensional arrays, 454
passing, 460
art, SmartArt, 142–144
Assign3DPreset() procedure, 224
AssignBevel() procedure, 230
asterisks (*), 356–358
asymmetric pivot tables, named sets for, 322–323
attaching macros
to ActiveX controls, 583–584
to command buttons, 581–582
to shapes, 582–583
The Attribute “Attribute Name” on the Element “customui Ribbon” Is Not Defined in the DTD/Schema (error message), 577
AutoFilter
filtering by color, 253
filtering by icon, 254
replacing loops with, 249–251
selecting dynamic data range with, 254–255
selecting multiple items, 252
selecting visible cells only, 255–256
selecting with Search box, 252–255
turning off drop-downs in, 285
AutoFilterCustom() procedure, 285
automation (Word)
bookmarks, 448–449
constant values, 439–441
controlling form fields, 450–452
creating and referencing objects, 437–439
Document object, 442–443
early binding, 433–436
explained, 433
late binding, 436–437
macro recorder, 441
Range object, 444–447
Selection object, 443–444
AutoSort, 308
AutoSum button, 30–31
B
BASIC, 8
BeforeDragOver event, 187, 190, 193–199
BeforeDropOrPaste event, 187, 190, 193–199
BeforeUpdate event, 190, 193–197
below/above average cells, formatting, 383
bevel format, changing, 230–234
binding
early binding, 433–436
late binding, 436–437
bins, creating for frequency charts, 236–239
blank cells
eliminating from pivot tables, 308
formatting cells that contain blanks or errors, 387
bookmarks, 448–449
BookOpen() function, 83
Bottom 5 cells, formatting, 383–384
btnClose_Click() procedure, 512
BubbleSort() procedure, 98
built-in chart types, 208–210
buttons. See also specific buttons
attaching macros to, 581–582
custom icon images, 574–575
help buttons, 505–506
Microsoft Office icons, 573–574
C
.Calculation options, 306–307
calculations
calculated data fields, 324–325
calculated items, 325
changing to show percentages, 305–308
elapsed time, 353–354
calling
API declarations, 537
userforms, 186
Can’t find object or library (error message), 435–436
case of text, changing, 359–360
Case statements, 124
case studies
cleaning up recorded code, 62–64
converting Excel 2003 custom toolbar to Excel 2010, 575–577
criteria ranges, 268
custom functions, 80
data visualization, 327
entering A1 versus R1C1 references, 131
entering military time into cell, 171
filtering to top five or top 10, 319
formula-based conditions, 270
Go To Special instead of looping, 256–257
help buttons, 505–506
hidden workbook to hold macros and forms, 594
looping through directory files, 119–120
multicolumn list boxes, 532
named ranges for VLOOKUP, 156–157
page setup errors, 555
password cracking, 560
recording macros, 22–23
relative references, 26–28
A1-style references, 127–128
blank cells, eliminating from pivot tables, 308
checking for empty cells, 73–74
comments
charts in, 341–342
listing, 337–339
resizing, 339–341
conditional formatting. See conditional formatting
entering military time into, 171
noncontiguous cells, selecting/deselecting, 347–349
progress indicators, creating, 355–356
R1C1-style references
absolute references, 133
array formulas, 137–138
case study: entering A1 versus R1C1 references, 131
explained, 127–128
formulas, 129–132
mixed references, 133
multiplication table example, 134–135
referring to entire columns/rows, 134
relative references, 132–133
remembering column numbers associated with column letters, 136
switching to, 128
returning column letter of cell address, 103
reversing contents of, 101
selected cells, highlighting, 342–344, 344–345
selecting with SpecialCells, 360
setting workbook name in, 82
summing based on interior color, 89–90
Cells(), 59
Cells property
as parameters in Range property, 69
selecting ranges with, 68–69
centering cell comments, 340–341
ChangeFormat() procedure, 446
ChangeStyle() procedure, 447
ChangeTheChartLater() procedure, 207
changing
range size, 71–72
text case, 359–360
Chart_Activate() event, 173
Chart_BeforeDoubleClick() event, 173
Chart_BeforeRightClick() event, 173
Chart_Calculate() event, 173
Chart_Deactivate() event, 173
Chart_DragOver() event, 175
Chart_DragPlot() event, 175
chart events, 166–167, 172–175, 495–497
ChartFormat method, 203
ChartFormat object, 218
Chart Layout gallery, 211–213
Chart_MouseDown() event, 174
Chart_MouseMove() event, 174
Chart_MouseUp() event, 174
Chart_Resize() event, 174
charts
built-in chart types, 208–210
in cell comments, 341–342
chart events, 166–167, 172–175, 495–497
trapping, 495–497
creating, 204–207
dynamic charts, creating in userforms, 244–245
embedded charts, 172
exporting as graphics, 244–245
formatting
3-D rotation settings, 224–229
bevel and 3D format, 230–234
chart elements to which formatting applies, 218–234
Format method, 218–234
glow settings, 222–223
line settings, 222
object fill, 219–222
reflection settings, 223
shadow settings, 223
soft edges, 223–224
frequency charts, 236–239
Layout tab, 213–218
layouts, 211–213
new features (Excel 2010), 139–140
Open-High-Low-Close (OHLC) charts, 235–236
overview, 203
pivot charts, 246–247
referencing, 203–207
SetElement method, 213–218
sparklines. See sparklines
specifying size and location of, 204–205
stacked area charts, 239–243
styles, 211–213
template chart types, 210–211
Win/Loss charts, 426–427
Chart_Select() event, 174–175
Chart_SeriesChange() event, 175
ChartStyle property, 213
ChartType property, 208
CheckBox control, 512–513
check boxes, 512–513
CheckDisplayRes() procedure, 540
CheckForSheet() procedure, 84
checking
existence of names, 155–156
for open files, 539
whether workbook is open, 83
CheckUserRights() procedure, 86
class modules
creating collections in, 502–504
inserting, 493
cleaning up recorded code
case study, 62–64
tips for, 58–61
ClearAllFilters method, 289
ClearTable method, 289
Click event, 187, 190, 193–196, 200
clients, training about error handling, 557
Close method, 443
closing
add-ins, 593
documents, 443
Excel, 401
userform windows, 200–201
code protection, 559
collections
Areas, 77
creating
in class modules, 502–504
in standard module, 501–502
defined, 501
explained, 35
grouping controls into, 519–521
ColName() function, 103
color scales
adding to ranges, 374–375
explained, 367
coloring active control, 530–532
filtering by, 253
RGB colors in sparklines, 421–423
summing cells based on interior color, 89–90
theme colors for sparklines, 418–421
using two colors of data bars in range, 380–382
ColorFord() procedure, 251
ColorFruitRedBold() procedure, 121–122
ColumnExists() procedure, 488
ColumnHeaders() procedure, 455
columns
copying all columns, 277
remembering column numbers associated with column letters, 136
subset of columns, copying, 278–280
Columns property, 72
combining worksheets into workbooks, 334–335
combo boxes, 191–193
command buttons
attaching macros to, 581–582
events for, 189
CommandButton event, 191
comments
adding to names, 150
in cells
charts in, 341–342
listing, 337–339
resizing, 339–341
compact layout, 293–294
CompactLayoutColumnHeader property, 289
CompactLayoutRowHeader property, 289
CompactRowIndent property, 290
compatibility issues
checking application version with Version property, 144–145
Compatibility mode, 145
explained, 144
Compatibility mode, 145
complex expressions, 124
ComplexIf() procedure, 124–126
computer names, retrieving, 538–539
concatenation, 97–98
conceptual filters (pivot tables), 313–316
conditional formatting
color scales
adding to ranges, 374–375
explained, 367
data bars
adding to ranges, 369–374
explained, 367
determining which cells to format, 387–388
formatting cells based on value, 385
formatting cells in top 10 or bottom 5, 383–384
formatting cells that are above/below average, 383
formatting cells that contain blanks or errors, 387
formatting cells that contain dates, 386
formatting cells that contain text, 386
formatting unique or duplicate cells, 384–385
highlighting selected cell, 342–344
icon sets
adding to ranges, 375–378
explained, 368
new features (Excel 2010), 140–141
NumberFormat property, 388–389
VBA methods and properties, 368–369
conditions (If statement), 121
configuring pivot tables, 295–296
connections (ADO), 478
constant values
defined constants, 41–45
explained, 439
retrieving with Object Browser, 440–441
retrieving with Watch window, 440
ContainsText() function, 100–101
content management system, 407–409
controls. See also userforms
active control, coloring, 530–532
ActiveX controls
attaching macros to, 583–584
right-click menu for, 360–362
adding at runtime, 523–529
adding on-the-fly, 525
CheckBox, 512–513
grouping into collections, 519–521
programming, 188
RefEdit, 515
renaming, 188
Ribbon control arguments, 569–571
Ribbon control attributes, 566
running macros from, 16–17
ScrollBar, 517–519
TabStrip, 513–515
tip text, adding to userforms, 530
ToggleButton, 517
troubleshooting, 189
converting
Excel 2003 custom toolbar to Excel 2010, 575–577
pivot tables to values, 299–301
week numbers into dates, 96
workbooks to add-ins, 588–590
ConvertToFormulas method, 289
CopyFromRecordSet method, 481
copying
data into worksheets, 335–336
formulas, 129–130
macros into workbooks, 363–365
ranges, 61
subset of columns, 278–280
CopyToNewFolder() procedure, 120
counting
records, 303
unique values, 90–91
workbooks in directory, 84–85
CountMyWkbks() procedure, 85
cracking passwords, 560
CreatedStackedChart() procedure, 242–243
CreateFrequencyChart() procedure, 238–239
CreateMemo() procedure, 448–449
CreateObject() function, 438
CreateOHCLChart() procedure, 236
CreatePivot() procedure, 298–299
CreatePivotTable method, 295
CreateSummaryReportUsingPivot() procedure, 246–247, 300–301
criteria ranges
case study, 268
explained, 265–266
formula-based conditions, 268–275
logical AND criteria, 267
logical OR criteria, 267
Criteria reserved name, 155
CSV files, importing, 331–332
CurrentRegion property, 74–76
cursors, 478
custom About dialog, 541
custom functions. See UDFs (user-defined functions)
Custom UI Editor, 572
CustomerByProductReport() procedure, 309–312
customizing
data transposition, 345–347
icon images, 574–575
objects
creating custom objects, 497–498
Property Let/Property Get procedures, 499–501
referencing, 498–499
Ribbon to run macros
control arguments, 569–571
control attributes, 566
custom icon images, 574–575
Custom UI Editor tool, 572
customui folder, 564–565
error messages, 577–580
explained, 563–565
file structure, accessing, 571
Microsoft Office icons, 573–574
RELS file, 571–572
tab and group, 565–566
sort orders, 354–355
web pages, 406
customui folder, 564–565
D
dashboards
creating, 427–432
sparklines
creating, 412–413
creating 100’s of individual sparklines in a dashboard, 428–432
formatting, 418–421
observations about, 428
scaling, 414–418
types of sparklines, 411
data
getting from the Web, 391–392
publishing to web pages, 404–406
data access objects (DAO), 477
data bars
adding to ranges, 369–374
explained, 367
using two colors of data bars in range, 380–382
data transposition, customizing, 345–347
data visualizations
applying, 327
color scales, adding to ranges, 374–375
conditional formatting
determining which cells to format, 387–388
formatting cells based on value, 385
formatting cells in top 10 or bottom 5, 383–384
formatting cells that are above/below average, 383
formatting cells that contain blanks or errors, 387
formatting cells that contain dates, 386
formatting cells that contain text, 386
formatting unique or duplicate cells, 384–385
NumberFormat property, 388–389
data bars
adding to ranges, 369–374
using two colors of data bars in range, 380–382
explained, 368
icon sets
adding to ranges, 375–378
creating for subset of range, 378–380
VBA methods and properties for, 368–369
DataBar2() procedure, 372–373
DataBar3() procedure, 373
Database reserved name, 155
databases
ADO
connections, 478
cursors, 478
lock type, 479
overview, 478–480
recordsets, 478
fields
adding on-the-fly, 489–490
checking existence of, 488
Multidimensional Database (MDB) format, 475
records
adding, 480–481
deleting, 485
retrieving, 481–483
summarizing, 485–486
updating, 483–485
shared access databases, creating, 477–478
SQL Server, 490–491
tables
adding on-the-fly, 489
checking existence of, 487–488
DataExtract() procedure, 490–491
DataSets variable, 473
dates
converting week numbers into, 96
formatting cells that contain dates, 386
grouping to months, quarters, or years, 303–305
retrieving permanent date/time, 87
retrieving saved date/time, 86–87
DateTime() function, 87
DblClick event, 187, 190, 193–197, 200
Deactivate event, 187
Debug button, 551
Debug errors, 551–552
debugging tools
breakpoints, 49
jumping forward/backward in code, 49–50
querying variable values, 50–54
Run to Cursor, 50
stepping through code, 46–48
watches, 55
declaring
arrays, 453–454
variables, 20
defined constants, 41–45
defining
pivot cache, 295
ranges, 444–446
Delete method, 149–150
DeleteFord() procedure, 251
deleting
names, 149–150
records, 485
selections from recorded code, 58
delimited files, opening, 467–470
delimited strings, separating, 96–97
deselecting noncontiguous cells, 347–349
Design tab, changing layout from, 325–326
Developer tab, viewing, 9–10
directories
counting workbooks in, 84–85
listing files in, 329–331
looping through directory files, 119–120
Disable All Macros Except Digitally Signed Macros setting, 12
Disable All Macros with Notification setting, 11–12
Disable All Macros Without Notification setting, 11
disabling X button for closing userforms, 541–542
DisplayAllMember method, 289
DisplayContextTooltips property, 290
DisplayFieldCaptions property, 290
displaying R1C1-style references, 128
DisplayMemberPropertyTooltips property, 290
display-resolution information, retrieving, 540
DisplaySize API function, 540
dll (dynamic link libraries), 535
Do loops
explained, 113–115
Until clause, 115–117
While clause, 115–117
DOA (data access objects), 477
Document object
closing documents, 443
explained, 442
opening documents, 442
printing documents, 443
saving documents, 442–443
documents
closing, 443
creating, 442
exporting to, 336–337
opening, 442
printing, 443
saving, 442–443
drilling down pivot tables, 349–350
DropButtonClick event, 190, 193
duplicate cells, formatting, 384–385
duplicates, removing from ranges, 91–92
dynamic arrays, 459–460
dynamic charts, creating in userforms, 244–245
dynamic data ranges, selecting with AutoFilter, 254–255
dynamic link libraries (dll), 535
DynamicAutoFilter() procedure, 255
E
early binding, 433–436
elapsed time, calculating, 353–354
Element “customui Tag Name” Is Unexpected (error message), 578
e-mail addresses, validating, 88–89
embedded chart events, trapping, 495–497
embedded charts, 172
EmpAddCollection() procedure, 504
EmpPayCollection() procedure, 501–502
empty cells, checking for, 73–74
emptying arrays, 456–457
Enable All Macros (Not Recommended: Potentially Dangerous Code Can Run) setting, 12
enable/disable macro settings, 11–12
enabling
events, 161
macros, 12
encountering errors on purpose, 556
EndKey method, 443
Enter event, 190, 193–197, 200
Err object, 554
Error event, 187, 190, 193–197, 200
error handling
debug errors inside userform code, 551–552
encountering errors on purpose, 556
Err object, 554
error messages
The Attribute “Attribute Name” on the Element “customui Ribbon” Is Not Defined in the DTD/Schema, 577
Can’t find object or library, 435–436
Element “customui Tag Name” Is Unexpected, 578
Excel Found Unreadable Content, 579
Illegal Qualified Name Character, 578
runtime error 9: Subscript Out of Range, 557
runtime error 1004: Method Range of Object Global Failed, 558–559
Wrong Number of Arguments or Invalid Property Assignment, 580
errors caused by different versions, 561
errors while developing versus errors months later, 557
explained, 549–552
formatting cells that contain blanks or errors, 387
generic error handlers, 554
ignoring errors, 554
On Error GoTo syntax, 552–554
On Error Resume Next statement, 554–555
page setup errors, 555
problems with passwords, 560–561
protecting code, 559
suppressing Excel warnings, 556
training clients, 557
error messages
The Attribute “Attribute Name” on the Element “customui Ribbon” Is Not Defined in the DTD/Schema, 577
Can’t find object or library, 435–436
Element “customui Tag Name” Is Unexpected, 578
Excel Found Unreadable Content, 579
Illegal Qualified Name Character, 578
runtime error 9: Subscript Out of Range, 557
runtime error 1004: Method Range of Object Global Failed, 558–559
Wrong Number of Arguments or Invalid Property Assignment, 580
Evaluate method, 153
events. See also specific events
application-level events, 176–181, 494–495
chart events, 172–175
CheckBox control events, 513
for combo boxes, 191–193
for command buttons, 189
embedded chart events, trapping, 495–497
enabling, 161
explained, 160
for graphics, 195–202
for labels, 189
levels of events, 159–160
for list boxes, 191–193
for MultiPage control, 198–200
for option buttons, 194–195
parameters, 160
RefEdit control events, 516
Scrollbar control events, 519
for spin buttons, 196–202
TabStrip control events, 515
for text boxes, 189
ToggleButton control events, 517
userform events, 186–187
workbook events, 161–167
worksheet events, 168–172
EveryOtherRow() procedure, 455
Excel 97-2003 Workbook file type, 18
Excel 2003 custom toolbar, converting to Excel 2010, 575–577
Excel 2007 pivot table features, 288–290
Excel 2010
file types, 18–19
pivot table features, 288
Excel Binary Workbook file type, 18
Excel Found Unreadable Content (error message), 579
Excel Macro-Enabled Workbook file type, 18
Excel Workbook file type, 18
Excel8CompatibilityMode property, 145
Execute method, 485
exiting For...Next loop after condition is met, 111–112
ExportChart() procedure, 244
exporting
charts as graphics, 244–245
to Word document, 336–337
expressions in Case statements, 124
Extract reserved name, 155
F
FieldListSortAscending property, 290
fields
adding on-the-fly, 489–490
adding to pivot tables, 296–299
calculated data fields, 324–325
checking existence of, 488
field entry in userforms, verifying, 200
form fields, controlling in Word, 450–452
multiple value fields (pivot tables), 302–303
File menu, Save As command, 589
files
checking for open files, 539
CSV files, importing, 331–332
file structure, accessing, 571
file types in Excel 2010, 18–19
filenames, retrieving, 201–202
listing, 329–331
looping through directory files, 119–120
paths, retrieving, 543–546
RELS file, 571–572
fixed-width files, 463–467
importing files with fewer than 1,048,576 rows, 463–470
importing files with more than 1,048,576 rows, 470–473
reading and parsing, 332–333
writing, 473–474
filling arrays, 455–456
FillOutWordForm() procedure, 451–452
Filter in Place, 275–276, 283–285
FilterByFontColor() procedure, 253
FilterByIcon() procedure, 254
filtering
data into worksheets, 335–336
pivot tables
conceptual filters, 313–316
filtering to top five or top 10, 319
manual filters, 312–313
with named sets, 321–323
Search filter, 316–317
slicers, 319–321
FilterNoFontColor() procedure, 253
filters
Advanced Filter
building with Excel interface, 258
case study: creating reports for each customer, 280–283
extracting unique list of values, 258–264
Filter in Place, 275–276
overview, 257
xlFilterCopy with all records, 276–280
AutoFilter
filtering by color, 253
filtering by icon, 254
replacing loops with, 249–251
selecting dynamic data range with, 254–255
selecting multiple items, 252
selecting visible cells only, 255–256
selecting with Search box, 252–255
turning off drop-downs in, 285
finding
API declarations, 547
first nonzero-length cell, 93
FindJPGFilesInAFolder() procedure, 119–120
FindWindow API function, 541–543
first nonzero-length cell, finding in range, 93
FirstNonZeroLength() function, 93
fixed-width files, opening, 463–467
flow control
complex expressions in Case statements, 124
If statement
If...Then...Else, 121
If...Then...Else...End If, 122–123
If...Then...End If, 121–122
nested If statements, 124–126
Select Case...End Select statement, 123
folders, customui, 564–565
For...Next loops
exiting early after condition is met, 111–112
explained, 107–109
nesting, 112
Step clause, 110–111
variables, 110
Format method, 218–234
Format Shape dialog, 230
Format tab. See formatting
FormatAboveAverage() procedure, 383
FormatBelowAverage() procedure, 383
FormatBetween10And20() procedure, 386
FormatBorder() method, 222
FormatBottom5Items() procedure, 383–384
FormatConditions object, 368
FormatContainsA() procedure, 386
FormatDatesLastWeek() procedure, 386
FormatDuplicate() procedure, 385
FormatLessThan15() procedure, 386
FormatLineOrBorders() procedure, 222
FormatShadow() procedure, 223
FormatSoftEdgesWithLoop() procedure, 224
formatting
charts
3-D rotation settings, 224–229
bevel and 3D format, 230–234
chart elements to which formatting applies, 218–234
Format method, 218–234
glow settings, 222–223
line settings, 222
object fill, 219–222
reflection settings, 223
shadow settings, 223
soft edges, 223–224
conditional. See conditional formatting
ranges, 446–447
sparklines
RGB colors, 421–423
sparkline elements, 423–426
theme colors, 418–421
Win/Loss charts, 426–427
FormatTop10Items() procedure, 383
FormatTop12Percent() procedure, 384
FormatUnique() procedure, 385
FormatWithPicture() procedure, 221
forms. See userforms
formulas
array formulas, 137–138
determining which cells to format, 387–388
entering once and copying down the column, 129–130
formula-based conditions, 268–275
names, 151
R1C1 formulas, 61
frequency charts, 236–239
FruitRedVegGreen() procedure, 122
FTP, 409–410
functions. See specific functions
G
generic error handlers, 554
GetAddress() function, 102–103
GetComputerName API function, 538–539
GetFileName() function, 546
GetObject() function, 438–439
GetSettings() procedure, 558
GetSystemMenu API function, 541–542
GetUniqueCustomers() procedure, 260
GetUnsentTransfers() procedure, 481–482
global names, 147–148
glow settings, 222–223
Go To Special dialog, 256–257
graphics. See also icons
adding on-the-fly, 526–527
events for, 195–202
exporting charts as, 244–245
SmartArt, 142–144
groups, creating for Ribbon, 565–566
H
HandleAnError() procedure, 553
handling errors. See error handling
hard-coding, 60–61
help
adding to userforms, 529–532
accelerator keys, 529
coloring active control, 530–532
control tip text, 530
help buttons, 505–506
help files, 143
installing, 37–38
selecting libraries in, 45
help topics, 39
hidden workbooks
as alternative to add-ins, 593–594
case study: hidden workbook to hold macros and forms, 594
Hide method, 186
hiding
hidden workbooks
as alternative to add-ins, 593–594
case study: hidden workbook to hold macros and forms, 594
names, 155
userforms, 186
HighlightFirstUnique() procedure, 385–388
highlighting selected cells, 342–345
HighlightWholeRow() procedure, 388
historical stock/fund quotes application, 362–363
HomeKey method, 443
hovering, 53
hyperlink addresses, returning, 102–103
hyperlinks
in userforms, 522
running macros from, 584
I
icons
custom icon images, 574–575
filtering by, 254
icon sets
adding to ranges, 375–378
creating for subset of range, 378–380
explained, 368
Microsoft Office icons, 573–574
If statements
conditions, 121
If...Then...Else, 121
If...Then...Else...End If, 122–123
If...Then...End If, 121–122
nesting, 124–126
ignoring errors, 554
Illegal Qualified Name Character (error message), 578
images. See graphics; icons
Immediate window, 50–53
Import10() procedure, 470
ImportAll() procedure, 470–471
ImportData function, 156–157
importing
CSV files, 331–332
text files
files with fewer than 1,048,576 rows, 463–470
files with more than 1,048,576 rows, 470–473
IncrementRotationHorizontal property, 229
IncrementRotationVertical property, 229
IncrementRotationX property, 229
IncrementRotationY property, 229
IncrementRotationZ property, 229
InGridDropZones property, 290
Initialize event, 187
input boxes, 183–184
InputBox function, 183–184
inserting class modules, 493
InsertText() procedure, 444
installing
add-ins, 591
help files, 37–38
Intersect method, 73
IsEmailValid() function, 88–89
ISEMPTY function, 73–74
IsWordOpen() procedure, 438
J
jet engine, 476
joining multiple ranges, 72–73
jumping forward/backward in code, 49–50
K
keyboard shortcuts, running macros with, 580–581
KeyDown event, 187, 191–197, 200
KeyPress event, 187, 191–197, 200
keywords, New, 437
KillTimer API function, 542–543
L
labels, 189
last row, determining, 59–60
LastSaved() function, 86–87
late binding, 436–437
Layout tab, 213–218
LayoutRowDefault property, 290
layouts
charts, 211–213
compact layout, 293–294
pivot table layout, 325–327
lbl_Email_Click() procedure, 522
lbl_SelectAll_Click() procedure, 520
lbl_unSelectAll_Click() procedure, 520
lbl_Website_Click() procedure, 522
learning curve for VBA, 8
levels of events, 159–160
libraries
dynamic link libraries (dll), 535
selecting in help files, 45
lighting, VBA constants for, 233–234
Line Input method, 472
line settings, 222
LineFormat object, 222
list boxes
combo boxes versus, 191–193
multicolumn list boxes, 532
listing
cell comments, 337–339
files in directories, 329–331
lists, sorting, 354–355
Load method, 186
local names, 147–148
location of charts, specifying, 204–205
lock type (ADO), 479
logical AND criteria, 267
logical OR criteria, 267
loops
Do
explained, 113–115
Until clause, 115–117
While clause, 115–117
For Each, 117–119
exiting early after condition is met, 111–112
explained, 107–109
nesting, 112
Step clause, 110–111
variables, 110
Go To Special instead of looping, 256–257
looping through directory files, 119–120
replacing with AutoFilter, 249–251
While...Wend, 117
lOpen API function, 539
Lotus 1-2-3 macros, 29
M
macro recorder, 441
cleaning up recorded code
case study, 62–64
tips for, 58–61
examining code from, 39–46
absolute mode, 25
AutoSum button, 30–31
examining code in Programming window, 23–25
recording macros case study, 22–23
relative references, 26–29
relative references case study, 26–28
tips for, 31
Macro Security icon (Developer tab), 9
macros. See also specific procedures
attaching
to ActiveX controls, 583–584
to command buttons, 581–582
to shapes, 582–583
canceling previously scheduled, 400–401
closing, 401
copying into workbooks, 363–365
holding in hidden workbooks, 594
running, 14–17
from form controls, 16–17
from hyperlinks, 584
with keyboard shortcuts, 580–581
from Quick Access toolbar, 15–16
from Ribbon. See Ribbon
scheduling
to run every two minutes, 403–404
to run x minutes in the future, 401–402
security, 10–12
Disable All Macros with Notification setting, 12
enable/disable settings, 11–12
trusted locations, 10–11
testing, 25
Macros icon (Developer tab), 9
manual filters (pivot tables), 312–313
manually creating web queries, 392–395
material types, 232
maximum values in range, returning addresses of, 101–102
MaxPoint property, 371
MDB (Multidimensional Database) format, 475
Me keyword, 186
message boxes, 184
methods. See specific methods
Microsoft Office icons, adding to buttons, 573–574
military time, entering into cells, 171
MinPoint property, 371
mixed references, 133
mixed text
retrieving numbers from, 95
sorting numeric and alpha characters, 99–100
modeless userforms, 521
Modify method, 371
modules, 21
MouseDown event, 187, 191–196, 200
MouseMove event, 187, 191, 194–196, 200
MouseUp event, 187, 191, 194–196, 200
MoveAfterTheFact() procedure, 205
MoveAndFormatSlicer() procedure, 321
MsgBox function, 184
MSubstitute() function, 94–95
multicolumn list boxes, 532
multidimensional arrays, 454
Multidimensional Database (MDB) format, 475
MultiPage control, 198–200
multiple actions in With...End With blocks, 61
multiple characters, substituting, 94–95
multiple items, selecting, 252
multiple row fields, suppressing subtotals for, 326–327
multiple value fields (pivot tables), 302–303
MultipleIf() procedure, 122
multiplication table, building with R1C1-style references, 134–135
MultiSelect property, 192–193
MyFullName() function, 82–83
MyName() function, 82
N
Name property, 149
named ranges, 66
named sets, 321–323
NameExists function, 155–157
names
adding comments about, 150
array names, 153–154
checking existence of, 155–156
computer names, retrieving, 538–539
creating, 148–149
deleting, 149–150
explained, 147
formula names, 151
global versus local names, 147–148
hiding, 155
named ranges for VLOOKUP, 156–157
number names, 152–153
reserved names, 154–155
storing values in, 152
string names, 151–152
table names, 153
workbook names, setting in cell, 82
NASDAQMacro() procedure, 416–418
navigation keys, 31
nesting
If statements, 124–126
loops, 112
NetTransfers() procedure, 486
new features (Excel 2010)
charts, 139–140
conditional formatting, 140–141
objects/methods, 143
pivot tables, 140
Ribbon, 139
slicers, 140
SmartArt, 142
sorting, 141–142
tables, 141
New keyword, 437
NewDocument() procedure, 442
noncontiguous cells, selecting/deselecting, 347–349
noncontiguous ranges, returning, 77
NumberFormat() procedure, 388–389
NumberFormat property, 388–389
numbers
names, 152–153
retrieving from mixed text, 95
static random numbers, generating, 103
week numbers, converting into dates, 96
NumFilesInCurDir() function, 84–85
NumUniqueValues() function, 90–91
O
Object Browser, 56–57, 440–441
object-oriented languages, 33–34
object variables, 117–119
objects. See also specific objects
ActiveX data objects. See ADO
bookmarks, 448–449
in collections, 35
creating and referencing
CreateObject() function, 438
GetObject() function, 438–439
New keyword, 437
custom objects
creating, 497–498
Property Let/Property Get procedures, 499–501
referencing, 498–499
explained, 34
fill, 219–222
new features (Excel 2010), 143
returned by properties, 46
watches on, 55
ObjectThemeColor property, 219
objForm_LostFocus() procedure, 532
OHLC (Open-High-Low-Close) charts, 235–236
OldLoop() procedure, 250
OldLoopToDelete() procedure, 250
OneColorGradient method, 221, 222
one-dimensional arrays, 454
On Error GoTo syntax, 552–554
On Error Resume Next statement, 554–555
open files, checking for, 539
Open-High-Low-Close (OHLC) charts, 235–236
Open method, 442
opening
delimited files, 467–470
documents, 442
fixed-width files, 463–467
OpenSchema method, 487
optimizing
calculating elapsed time, 353–354
Page Setup, 350–353
option buttons, 194–195
optional parameters, 41
Origin parameter, 41
overlapping ranges, creating new ranges from, 73
P
parameters
event parameters, 160
explained, 35–36
optional parameters, 41
parsing text files, 332–333
PassAnArray() procedure, 460
passing arrays, 460
passwords
cracking, 560
password box protection, 356–358
problems with, 560–561
pasting ranges, 61
.Patterned method, 221
Peltier, Jon, 243
percentages, showing, 305–308
permanent date/time, retrieving, 87
Personal Macro Workbook, 13
pivot cache, 295
pivot charts, 246–247
pivot tables
building in Excel interface, 290–294
building in VBA, 294–301
adding fields to data area, 296–299
creating and configuring pivot table, 295–296
defining pivot cache, 295
calculated data fields, 324–325
calculated items, 325
changing calulations to show percentages, 305–308
changing layout of, 325–327
compact layout, 293–294
controlling sort order with AutoSort, 308
counting number of records, 303
data visualization, applying, 327
determining size of and converting pivot table to values, 299–301
drilling down, 349–350
eliminating blank cells in values area, 308
Excel 2007 new features, 288–290
Excel 2010 new features, 288
explained, 287
filtering data sets
conceptual filters, 313–316
filtering to top five or top 10, 319
manual filters, 312–313
Search filter, 316–317
slicers, 319–321
with named sets, 321–323
with ShowDetail, 325
grouping daily dates to months, quarters, or years, 303–305
limitations, 299
multiple value fields, 302–303
new features (Excel 2010), 140
replicating reports for every product, 309–312
supressing subtotals for multiple row fields, 326–327
PivotColumnAxis property, 290
PivotRowAxis property, 290
playing sounds, 543
PlayWavSound API function, 543
Pope, Andy, 243
PresetGradient method, 222
PresetTextured method, 220
Print_Area reserved name, 155
Print_Titles reserved name, 155
PrintDrillIndicators property, 290
printing documents, 443
PrintOut method, 443
Priority property, 369
private properties, 497
procedural languages, 33–34
procedures. See specific procedures
Programming window, examining macro recorder code in, 23–25
progress indicators, 355–356
Project Explorer, 20–21
properties. See also specific properties
explained, 36–37
return values, 46
Properties window, 21
Property Get procedure, 499–501
Property Let procedure, 499–501
protecting
code, 559
password boxes, 356–358
public properties, 497
publishing data to web pages, 404–406
Q
queries, 391–395
querying variable values, 50–54
Quick Access toolbar, 15–16
QuickFillMax() procedure, 456
QuickSort() procedure, 99–100
R
R1C1-style references, 61
absolute references, 133
array formulas, 137–138
case study: entering A1 versus R1C1 references, 131
explained, 127–128
formulas, 129–132
mixed references, 133
multiplication table example, 134–135
referring to entire columns/rows, 134
relative references, 132–133
remembering column numbers associated with column letters, 136
switching to, 128
random numbers, generating, 103
defining ranges, 444–446
formatting ranges, 446–447
Range property, 69
ranges
color scales, adding, 374–375
copying/pasting in one statement, 61
creating from overlapping ranges, 73
criteria ranges
case study, 268
explained, 265–266
formula-based conditions, 268–275
logical AND criteria, 267
data bars, adding, 369–374
defining, 444–446
first nonzero-length cell, finding, 93
formatting, 446–447
icon sets, adding, 375–378
specifying icon set, 376
specifying ranges for each icon, 377–378
joining multiple ranges, 72–73
names
adding comments about, 150
creating, 148–149
deleting, 149–150
referencing, 59
with Offset property, 69–70
in other sheets, 67
relative to another range, 68
shortcuts, 66–67
removing duplicates from, 91–92
resizing, 71–72
returning addresses of maximum values in range, 101–102
returning noncontiguous ranges, 77
selecting
with AutoFilter, 254–255
with Cells property, 68–69
with CurrentRegion property, 74–76
specifying
syntax, 66
with Columns/Rows properties, 72
Ranges(), 59
RangeText() procedure, 444
reading text files, 332–333
files with fewer than 1,048,576 rows, 463–470
importing files with more than 1,048,576 rows, 470–473
ReadLargeFile() procedure, 472–473
Record Macro dialog box, 13
Record Macro icon (Developer tab), 9
recorded code, cleaning up, 58–64
recording macros, 12–14. See also macro recorder
recording macros, 22–23
records
adding to databases, 480–481
counting number of, 303
deleting, 485
retrieving from databases, 481–483
showing after Filter in Place, 276
summarizing, 485–486
updating, 483–485
RefEdit control, 515
references
A1-style references, 127–128
case study: entering A1 versus R1C1 references, 131
R1C1-style references
absolute references, 133
array formulas, 137–138
explained, 127–128
formulas, 129–132
mixed references, 133
multiplication table example, 134–135
referring to entire columns/rows, 134
relative references, 132–133
remembering column numbers associated with column letters, 136
switching to, 128
referencing
charts, 203–207
custom objects, 498–499
objects
CreateObject() function, 438
GetObject() function, 438–439
New keyword, 437
ranges, 59
with Offset property, 69–70
in other sheets, 67
relative to another range, 68
shortcuts, 66–67
tables, 77–78
reflection settings, 223
refreshing web queries, 392–395
relative references, 26–31
case study, 26–28
R1C1-style references, 132–133
RELS file, 571–572
RememberTheName() procedure, 206
Remove Duplicates command, 384–385
RemoveControl event, 187, 195, 200
removing
add-ins, 593
duplicates from ranges, 91–92
renaming controls, 188
replacing loops with AutoFilter, 249–251
replicating reports for every product, 309–312
reports
creating with Advanced Filter, 280–283
replicating for every product, 309–312
reserved names, 154–155
Reset button, 549–550
ResetRotation method, 229
Resize event, 187
Resize property, 71–72
resizing
cell comments, 339–341
ranges, 71–72
userforms, 524
resolution, 540
RetrieveNumbers() function, 95
retrieving
file paths, 543–546
filenames, 201–202
records, 481–483
return values of properties, 46
ReturnsMaxs() function, 101–102
RevenueByCustomers() procedure, 261
ReverseContents() function, 101
reversing cell contents, 101
RGB colors, 421–423
Ribbon
changes in Excel 2010, 139
customizing to run macros
control arguments, 569–571
control attributes, 566
custom icon images, 574–575
Custom UI Editor tool, 572
customui folder, 564–565
error messages, 577–580
explained, 563–565
file structure, accessing, 571
Microsoft Office icons, 573–574
RELS file, 571–572
tab and group, 565–566
macro buttons, creating, 14–15
rotation, 224–229
RotationX property, 228
RotationY property, 229
RotationZ property, 229
RowAxisLayout method, 289
rows, determining last row, 59–60
Rows property, 72
Run to Cursor debugging tool, 50
RunCustReport() procedure, 278–279
running
macros, 14–17
from form controls, 16–17
from Quick Access toolbar, 15–16
from Ribbon, 14–15
timers, 542–543
RunReportForEachCustomer() procedure, 281–283
adding controls at, 523–529
errors
runtime error 9: Subscript Out of Range, 557
runtime error 1004: Method Range of Object Global Failed, 558–559
S
Save As command (File menu), 589
Save method, 442
saved date/time, retrieving, 86–87
saving documents, 442–443
sbX_Change() procedure, 245
sbY_Change() procedure, 245
scaling sparklines, 414–418
scheduling
macros
to run every two minutes, 403–404
to run x minutes in the future, 401–402
verbal reminders, 402
ScrollBar control, 517–519
Search box, 252–255
Search filter (pivot tables), 316–317
searching for strings within text, 100–101
security
add-ins, 592
macro security
Disable All Macros with Notification setting, 12
enable/disable settings, 11–12
trusted locations, 10–11
password box protection, 356–358
Select Case...End Select statement, 123
Select...Case statement, 104
Select statements, 123
SelectCase() procedure, 123
selected cells, highlighting, 342–345
selecting
cells, 360
libraries, 45
multiple items, 252
noncontiguous cells, 347–349
ranges
with Cells property, 68–69
with CurrentRegion property, 74–76
Selection object, 443–444
SelectSentence() procedure, 445
separating
delimited strings, 96–97
worksheets into workbooks, 333–334
SetElement method, 203, 213–218
SetPresetCamera values, 225–229
SetReportInItalics() procedure, 559
SetTimer API function, 542–543
SetValuesToTabStrip() procedure, 514
shadow settings, 223
shapes, attaching macros to, 582–583
shared access databases, creating, 477–478
sharing UDFs (user-defined functions), 81–82
sheet events (workbook level), 166–167
SheetExists() function, 83–84
sheets, verifying existence of, 83–84
ShellAbout API function, 541
Show method, 186
ShowAllData method, 276
ShowCustForm() procedure, 263
ShowDetail, 325
ShowDrillIndicators property, 290
ShowTableStyleColumnHeaders property, 290
ShowTableStyleColumnStripes property, 290
ShowTableStyleLastColumn property, 290
ShowTableStyleRowHeaders property, 290
ShowTableStyleRowStripes property, 290
SimpleFilter() procedure, 285
size
of charts, 204–205
of pivot tables, 299–301
slicers, 319–321
SmartArt, 142
soft edges, formatting, 223–224
SortConcat() function, 97–98
sorter() function, 99–100
sorting
AutoSort, 308
with custom sort orders, 354–355
new features (Excel 2010), 141–142
numeric and alpha characters, 99–100
with SortConcat() function, 97–98
SortUsingCustomLists property, 290
sounds, playing, 543
sparklines
formatting
RGB colors, 421–423
sparkline elements, 423–426
theme colors, 418–421
Win/Loss charts, 426–427
observations about, 428
scaling, 414–418
types of sparklines, 411
SpecifyExactLocation() procedure, 205
SpecifyLocation() procedure, 205
speed testing, 350–353
spin button events, 196–202
SpinDown event, 198
SpinUp event, 198
SQL Server, 490–491
stacked area charts, 239–243
standard modules, creating collections in, 501–502
StartRow parameter, 41
statements. See also loops
Case, 124
If
If...Then...Else, 121
If...Then...Else...End If, 122–123
If...Then...End If, 121–122
nesting, 124–126
On Error GoTo, 552–554
On Error Resume Next, 554–555
Select...Case, 104
Select Case...End Select, 123
Type..End Type, 506
state_period() function, 103
static random numbers, generating, 103
StaticRAND() function, 103
Step clause (For statement), 110–111
stepping through code, 46–48
stock quotes, historical stock/fund quotes application, 362–363
StopIfTrue property, 369
StoreDashboard() procedure, 430–431
StoreTheName() procedure, 207
storing values in names, 152
StringElement() function, 96–97
delimited strings, separating, 96–97
finding within text, 100–101
names, 151–152
Styles gallery, 212–213
Sub cbConfirm_Click() procedure, 484–485
subsets of ranges, creating icon sets for, 378–380
substituting multiple characters, 94–95
SubtotalLocation method, 289
subtotals, suppressing for multiple row fields, 326–327
SumColor() function, 89–90
summarizing records, 485–486
summing cells based on interior color, 89–90
suppressing
Excel warnings, 556
subtotals for multiple row fields, 326–327
SwapElements() procedure, 100
switching to R1C1-style references, 128
T
tab strips, 513–515
TableExists() procedure, 487–488
tables
adding on-the-fly, 489
checking existence of, 487–488
exporting to, 336–337
names, 153
new features (Excel 2010), 141
pivot tables. See pivot tables
referencing, 77–78
TableStyle2 property, 290
tabs
creating for Ribbon, 565–566
tab order for userforms, 530
TabStrip control, 513–515
template chart types, 210–211
Terminate event, 187
testing
macros, 25
speed testing, 350–353
text
case, changing, 359–360
control tip text, 530
formatting cells that contain text, 386
mixed text, sorting numeric and alpha characters, 99–100
retrieving numbers from mixed text, 95
searching for strings within, 100–101
text boxes, 189
text files
delimited files, opening, 467–470
fixed-width files, opening, 463–467
importing, 463–473
reading and parsing, 332–333
writing, 473–474
text files
delimited files, opening, 467–470
fixed-width files, opening, 463–467
importing
files with fewer than 1,048,576 rows, 463–470
files with more than 1,048,576 rows, 470–473
reading and parsing, 332–333
writing, 473–474
Text Import Wizard, 42, 464–467
Text to Columns Wizard, 43
TextToColumns method, 471
theme colors for sparklines, 418–421
time
elapsed time, calculating, 353–354
military time, entering into cells, 171
permanent date/time, retrieving, 87
saved date/time, retrieving, 86–87
timers, 542–543
ToggleButton control, 517
toolbars
converting Excel 2003 custom toolbar to Excel 2010, 575–577
UserForm toolbar, 511
ToolTips, 53
Top 10 cells
filtering to, 319
formatting, 383–384
Top5Customers() procedure, 317–319
Top10Filter() procedure, 252
Top/Bottom Rules, 383–384
TrailingMinusNumbers parameter, 42, 561
training clients about error handling, 557
transparent forms, 533–534
TransposeArray() procedure, 458
transposing data, 345–347
TrapChartEvent() procedure, 497
trapping
application events, 494–495
embedded chart events, 495–497
TrickyFormatting() procedure, 380
troubleshooting. See error handling; error messages
trusted locations, 10–11
TwoColorGradient() procedure, 221
Type..End Type statement, 506
types, user-defined types (UDTs), 506–509
TypeText method, 444
U
UDFs (user-defined functions)
BookOpen(), 83
case study, 80
ColName(), 103
ContainsText(), 100–101
creating, 79–81
DateTime(), 87
FirstNonZeroLength(), 93
GetAddress(), 102–103
IsEmailValid(), 88–89
LastSaved(), 86–87
MSubstitute(), 94–95
MyFullName(), 82–83
MyName(), 82
NumFilesInCurDir(), 84–85
NumUniqueValues(), 90–91
RetrieveNumbers(), 95
ReturnsMaxs(), 101–102
ReverseContents(), 101
sharing, 81–82
SheetExists(), 83–84
SortConcat(), 97–98
sorter(), 99–100
state_period(), 103
StaticRAND(), 103
StringElement(), 96–97
SumColor(), 89–90
UniqueValues(), 91–92
Weekday(), 96
WinUserName(), 85–86
UDTs (user-defined types), 506–509
Union method, 72–73
unique cells, formatting, 384–385
Unique Records Only, 283–285
unique values
counting, 90–91
extracting with Advanced Filter, 258–264
getting unique combinations of two or more fields, 263–264
with user interface, 259
with VBA code, 260–263
UniqueCustomerProduct() procedure, 263–264
UniqueCustomerRedux() procedure, 261
UniqueProductsOneCustomer() procedure, 266
UniqueValues() function, 91–92
Unload method, 186
Until clause (Do loops), 115–117
updating
records, 483–485
web queries, 395
Use Relative Reference icon (Developer tab), 9
UseBookmarks() procedure, 448
UseGetObject() procedure, 438
user-defined functions. See UDFs
user-defined types (UDTs), 506–509
UserForm toolbar, 511
UserForm_Initialize() procedure, 527–528
UserForm_QueryClose() procedure, 532
userforms, 183–202
calling, 186
command buttons, 189
controls
adding at runtime, 523–529
adding on-the-fly, 525
CheckBox, 512–513
grouping into collections, 519–521
programming, 188
RefEdit, 515
ScrollBar, 517–519
TabStrip, 513–515
ToggleButton, 517
troubleshooting, 189
creating, 184–185
Debug errors inside userform code, 551–552
disabling X button for closing userforms, 541–542
dynamic charts, creating, 244–245
field entry, verifying, 200
filenames, retrieving, 201–202
help, adding, 529–532
accelerator keys, 529
coloring active control, 530–532
control tip text, 530
hiding, 186
hyperlinks in, 522
images
adding on-the-fly, 526–527
graphics events, 195–202
input boxes, 183–184
labels, 189
list boxes, 191–193
message boxes, 184
modeless userforms, 521
MultiPage control, 198–200
option buttons, 194–195
resizing on-the-fly, 524
spin buttons, 196–202
tab order, 530
text boxes, 189
transparent forms, 533–534
UserForm toolbar, 511
viewing code, 186
windows, closing, 200–201
USERID function, 85–86
UserIDs, retrieving, 85–86
V
validating e-mail addresses, 88–89
values
constant values
explained, 439
retrieving with Object Browser, 440–441
retrieving with Watch window, 440
converting pivot tables to, 299–301
duplicates, removing from ranges, 91–92
formatting cells based on, 385
maximum values in range, returning addresses of, 101–102
storing in names, 152
unique values
counting, 90–91
extracting with Advanced Filter, 258–264
variables
DataSets, 473
hard-coding versus, 60–61
in For statements, 110
object variables, 117–119
querying values of, 50–54
requiring declaration, 20
wdApp, 435
wdDoc, 435
VB Editor, 19–21
converting files to add-ins, 590–591
debugging tools
breakpoints, 49
jumping forward/backward in code, 49–50
querying variable values, 50–54
Run to Cursor, 50
stepping through code, 46–48
watches, 55
Object Browser, 56–57
Programming window, 23–25
Project Explorer, 20–21
Properties window, 21
settings, 19–20
VBA (Visual Basic for Applications)
advantages of, 8–9
learning curve, 8
syntax, 34–37
VBA Extensibility, 363–365
verbal reminders, scheduling, 402
verifying field entry, 200
Version property, 144–145
versions, errors caused by different versions, 561
viewing
Developer tab, 9–10
Project Explorer, 20
Properties window, 21
userform code, 186
visible cells, selecting with AutoFilter, 255–256
Visual Basic for Applications. See VBA (Visual Basic for Applications)
Visual Basic icon (Developer tab), 9
visualizations. See data visualizations
VLOOKUP function, 156–157
W
warnings, suppressing, 556
Watch window, 440
watches
querying variable values with, 53–54
setting breakpoints, 55
wdApp variable, 435
wdDoc variable, 435
web pages
creating custom, 406
publishing data to, 404–406
web queries, 391–392
building, 396–399
creating manually and refreshing with VBA, 392–395
scraping, 399
updating, 395
week numbers, converting into dates, 96
Weekday() function, 96
While clause (Do loops), 115–117
While...Wend loops, 117
Window API declarations
Windows API declarations
32-bit versus 64-bit, 538
ahtAddFilterItem, 546
aht_apiGetOpenFileName, 544–546
aht_apiGetSaveFileName, 544–546
calling, 537
DisplaySize, 540
explained, 535–536
finding, 547
FindWindow, 541–543
GetComputerName, 538–539
GetSystemMenu, 541–542
KillTimer, 542–543
lOpen, 539
PlayWavSound, 543
SetTimer, 542–543
ShellAbout, 541
windows for userforms, closing, 200–201
Win/Loss charts, 426–427
WinUserName() function, 85–86
With...End With blocks, 61
wizards, Text Import Wizard, 464–467
Word automation
bookmarks, 448–449
constant values
explained, 439
retrieving with Watch window, 440
controlling form fields, 450–452
creating and referencing objects
CreateObject() function, 438
GetObject() function, 438–439
New keyword, 437
Document object
closing documents, 443
creating documents, 442
explained, 442
opening documents, 442
printing documents, 443
saving documents, 442–443
early binding, 433–436
explained, 433
late binding, 436–437
macro recorder, 441
Range object, 444–447
defining ranges, 444–446
formatting ranges, 446–447
Selection object, 443–444
Word documents, exporting to, 336–337
WordEarlyBinding() procedure, 435
WordLateBinding() procedure, 437
Workbook_Activate() event, 161
Workbook_AddInInstall() event, 165
Workbook_AddInUninstall event, 165
Workbook_AfterXmlExport() event, 166
Workbook_AfterXmlImport() event, 166
Workbook_BeforeClose() event, 163–164
Workbook_BeforePrint() event, 163, 494
Workbook_BeforeSave() event, 162
Workbook_BeforeXmlExport() event, 166
Workbook_BeforeXmlImport() event, 166
Workbook_Deactivate() event, 161
Workbook_NewSheet() event, 164
Workbook_Open() event, 161
Workbook_Open() procedure, 594
Workbook_PivotTableCloseConnection() event, 165
Workbook_PivotTableOpenConnection() event, 165
Workbook_RowsetComplete() event, 165
Workbook_SheetActivate() event, 166
Workbook_SheetBeforeDoubleClick() event, 167
Workbook_SheetBeforeRightClick() event, 167
Workbook_SheetCalculate() event, 167
Workbook_SheetChange () event, 167
Workbook_SheetDeactivate() event, 167
Workbook_SheetFollowHyperlink() event, 167
Workbook_SheetPivotTableUpdate() event, 167
Workbook_SheetSelectionChange() event, 167
Workbook_Sync() event, 165
Workbook_WindowActivate() event, 165
Workbook_WindowDeactivate() event, 165
Workbook_WindowResize() event, 164
workbooks
checking whether open, 83
combining worksheets into, 334–335
converting to add-ins, 588–590
copying macros into, 363–365
counting number of workbooks in directory, 84–85
events
Workbook_Activate(), 161
Workbook_AddInInstall(), 165
Workbook_AddInUninstall, 165
Workbook_AfterXmlExport(), 166
Workbook_AfterXmlImport(), 166
Workbook_BeforeClose(), 163–164
Workbook_BeforePrint(), 163
Workbook_BeforeSave(), 162
Workbook_BeforeXmlExport(), 166
Workbook_BeforeXmlImport(), 166
Workbook_Deactivate(), 161
Workbook_NewSheet(), 164
Workbook_Open(), 161
Workbook_PivotTableCloseConnection(), 165
Workbook_PivotTableOpenConnection(), 165
Workbook_RowsetComplete(), 165
Workbook_Sync(), 165
Workbook_WindowActivate(), 165
Workbook_WindowDeactivate(), 165
Workbook_WindowResize(), 164
as alternative to add-ins, 593–594
case study: hidden workbook to hold macros and forms, 594
permanent date/time, retrieving, 87
saved date/time, retrieving, 86–87
separating worksheets into, 333–334
Workbooks object, 40
Worksheet_Activate() event, 168
Worksheet_BeforeDoubleClick() event, 168
Worksheet_BeforeRightClick() event, 169
Worksheet_BeforeRightClick() procedure, 160
Worksheet_Calculate() event, 169
Worksheet_Change() event, 170
Worksheet_Change() procedure, 161
Worksheet_Deactivate() event, 168
Worksheet_FollowHyperlink() event, 171
Worksheet_PivotTableUpdate() event, 172
Worksheet_SelectionChange() event, 170
worksheets
combining into workbooks, 334–335
events, 168–172
filtering/copying data into, 335–336
referencing ranges in other sheets, 67
Select...Case statements on, 104
separating into workbooks, 333–334
WriteFile() procedure, 474
WriteHTML() procedure, 554
writing text files, 473–474
Wrong Number of Arguments or Invalid Property Assignment (error message), 580
X—Y—Z
X button, disabling, 541–542
xlApp_NewWorkbook() procedure, 495
XLFilterInPlace constant, 275
.xls file type, 18
.xlsb file type, 18
.xlsm file type, 18
.xlsx file type, 18