Index
Symbols
:= (colon-equal sign), 30
{ } (curly braces), in JavaScript, 536
-- (decrement operator), in JavaScript, 543
++ (increment operator), in JavaScript, 543
+ (plus sign), in JavaScript, 537
“” (quotation marks) in JavaScript, 537
; (semicolons), in JavaScript, 536
[ ] (square brackets), as Evaluate method, 103
3D Models, 551
24-hour clocks, formatting cells as, 115-116
32-bit API declarations, compatibility, 471-472
64-bit API declarations, compatibility, 471-472
9 runtime error (subscript out of range), 488-489
1004 runtime error
method range of object global failed, 489-490
A
A1 references
replacing multiple with single R1C1 reference, 93-95
About dialog box, customizing, 475
above average conditional formatting, 344
above/below average rules, 330
absolute references
with R1C1 references, 92
accelerator keys in userforms, 461-462
database connection terminology, 428-429
fields
checking for existence, 438-439
creating, 440
MDB files, 425
creating shared databases, 427-428
pass-through queries, 429
records
deleting, 435
tables
checking for existence, 437-438
creating, 439
accessing file structure, 501
ACE engine, 426
Activate event (userforms), 155
active control, coloring, 463-464
ActiveCell property, 45
ActiveX controls, running macros from, 512-513
ActiveX Data Objects (ADOs), 426-429
ActiveX labels, minimizing duplicate code, 144-146
Add method
array names, 104
Document object, 415
formula names, 101
number names, 103
Add Watch dialog box, 43
add-ins. see also Office add-ins
advantages of, 515
closing, 521
converting workbooks to, 516-517
with Save As, 517
with VB Editor, 518
hidden workbooks versus, 521-523
removing, 521
saving data in, 516
security, 520
types of, 515
viewing, 519
AddAboveAverage method (FormatConditions object), 344
AddChart method, 328
AddControl event
frames, 163
MultiPage controls, 168
userforms, 155
AddFields method (pivot tables), 218
adding
button images (on ribbon), 503-505
color scales to ranges, 336-337
comments to names, 100
controls
to userforms, 157
Data Model fields to pivot tables, 243
fields in pivot table data area, 212-215
interactivity to Office add-ins, 530-532, 535
tables to Data Model, 242
value fields to Data Model pivot tables, 243-244
VBA code to workbooks with VBA Extensibility, 276-277
addresses (cell)
of duplicate max values, returning, 301-302
column letter of, returning, 302-303
addresses
hyperlink, returning, 302
AddTop10 method (FormatConditions object), 345
AddUniqueValues method (FormatConditions object), 346
ADOs (ActiveX Data Objects), 426-429
clearing, 198
formula-based conditions, 191-198
joining with logical AND, 191
joining with logical OR, 190-191
in Excel interface, 182
extracting unique list of values, 182
filter in place, 199
viewing all records after, 200
returning all matching records, 200
copying subset of columns, 201-203
creating individual reports, 203-207
AfterUpdate event
list boxes/combo boxes, 162
spin buttons, 166
text box control, 159
alerts, suppressing, 486
alphanumeric data, sorting, 298-300
AND, joining criteria ranges with, 191
API (application programming interface) declarations, 469
32-bit and 64-bit compatibility, 471-472
checking open network files, 473
creating running timer, 476-477
customizing About dialog box, 475
disabling X button in userforms, 475-476
playing sounds, 477
returning computer name, 472-473
returning display resolution, 474
application events
in class modules, 118, 132-133
application states, enabling/disabling, 263, 265
Application.EnableEvents = False events, preventing recursive procedure calling, 116
applying math functions to arrays, 544
Areas collection (Range object), selecting noncontiguous ranges, 66
arguments
arranging VBA and Excel windows, 39
array formulas, R1C1 references with, 96
applying math functions to, 544
dynamic arrays, declaring, 128-129
formulas, 549
functions, passing to, 130
in JavaScript, 538
multidimensional, declaring, 124-125
names, creating, 104
optimizing code with, 128
values, passing to/from, 130
assigning
macros to form controls, 12-13
shortcut keys to macros, 9
assignment operators in JavaScript, 542-543
associating column names with numbers in R1C1 references, 95
attributes
troubleshooting, 506
author contact information, 554
avoiding when copying data, 257-258
filtering
by color, 177
by icon, 178
on selected columns only, 207-208
selecting
with Search box, 177
AutoShow, filtering pivot tables, 232-234
AutoSort in pivot tables, 224
AutoSum in recorded macros, 23-26
B
backward compatibility, creating charts, 328
backward in code, moving, 40
BASIC, Visual Basic versus, 2
BeforeDragOver event
frames/option buttons, 163
graphics, 165
label/text box/command button control, 159
list boxes/combo boxes, 162
MultiPage controls, 168
spin buttons, 166
userforms, 155
BeforeDropOrPaste event
frames/option buttons, 163
graphics, 165
label/text box/command button control, 159
list boxes/combo boxes, 162
MultiPage controls, 168
spin buttons, 166
userforms, 155
BeforeUpdate event
list boxes/combo boxes, 162
option buttons, 164
spin buttons, 166
text box control, 159
below average conditional formatting, 344
blank cells
in pivot table value areas, eliminating, 223-224
breaking out of loops, 539
breakpoints, 40
in Watches window, 43
Browse dialog box, 6
building multiplication tables, 93-95
buttons
command buttons, running macros, 510-511
on ribbon, adding images to, 503-505
C
calculated fields in pivot tables, 246-247
calculated items in pivot tables, 247
calculations in pivot tables, changing to percentages, 221-223
calling
userforms, 154
canceling scheduled macros, 382-383
Cascading Style Sheets (CSS), 534
Case Else statements in Select Case...End Select constructs, 83
case of text, changing, 273-274
case sensitivity
in text editors, 526
of XML, 526
Case statements in Select Case...End Select constructs, 83-84
CBool function, 284
cell ranges.See ranges
cell references.See references
cells
comments
finding first nonzero-length in range, 292-293
formatting as military time, 115-116
highlighting
above/below average, 344
by date, 348
by text, 348
top/bottom values, 345
by value, 347
progress indicators, creating, 269-270
in ranges
selecting specific cells with SpecialCells method, 63-65
returning
addresses of duplicate max values, 301-302
column letter of address, 302-303
hyperlink addresses, 302
selecting with SpecialCells, 274-275
summing based on color, 289-290
Cells object, as array, 124
Cells property (Range object), selecting ranges, 57-58
Change event
formatting cells as military time, 115
list boxes/combo boxes, 162
MultiPage controls, 168
option buttons, 164
spin buttons, 166
text box control, 159
tracking user changes, 262-263
changing
colors in waterfall charts, 327-328
default file type, 5
names.See renaming
part of pivot table, 215
pivot table calculations to percentages, 221-223
pivot table layout, 248
rows/formulas to variables in recorded code, 49
shortcut keys for macros, 18
characters, substituting multiple, 293-294
chart colors, applying, 312-313
chart events, 116
for embedded charts, 116-117, 134-135
chart styles, list of, 307-310
chart titles, specifying, 311-312
charts
combo charts, creating, 323-325
creating, 305
with AddChart2 method, 306-307
backward compatibility, 328
exporting as graphics, 328
filtering, 313
formatting
line settings, 322
referring to specific chart, 310-311
with SetElement method, 314-319
map charts, creating, 326
new types, 305
Power BI Custom Visuals, 306
version compatibility, 549
waterfall charts, creating, 326-328
check box controls (Word), 422-424
application events in, 118, 132-133
collections, creating, 140-142
custom objects
embedded chart events in, 116-117, 134-135
enabling/disabling application states, 263, 265
minimizing duplicate ActiveX label code, 144-146
cleaning up recorded code, tips for, 46, 50-52
copy/paste statements, 49
R1C1 formulas, 49
range references, 47
variables for rows/formulas, 49
With...End With statements, 50
ClearAllFilters method (pivot tables), 229
clearing
Advanced Filter criteria ranges, 198
conceptual filters (pivot tables), 229
Click event
frames/option buttons, 164
graphics, 165
list boxes/combo boxes, 162
MultiPage controls, 168
userforms, 155
clients, training in error handling, 487
clip art, assigning macros to, 12
Close method (Document object), 416
closing
add-ins, 521
documents (Word), 416
Excel, canceling scheduled macros, 383
Properties window, 513
userforms
code optimization with arrays, 128
creating, 138
dictionaries, compared, 142-144
grouping controls into, 451-453
minimizing ActiveX label code, 144-146
objects versus, 29
colon-equal sign (:=), 30
color scales, 329
coloring active control, 463-464
colors
changing in waterfall charts, 327-328
chart colors, applying, 312-313
for data bars, 333
fill color, formatting charts, 320-322
filtering by with AutoFilter, 177
multiple colors for data bars, 341-343
summing cells based on, 289-290
column sets, importing text files into, 402-403
column sparklines, 353
ColumnGrand property (pivot tables), 248
columns
associating names with numbers in R1C1 references, 95
copying all with Advanced Filter, 200-201
copying subset with Advanced Filter, 201-203
referencing with R1C1 references, 93
reordering with Advanced Filter, 201-203
returning letter of address, 302-303
selected columns, AutoFilter on, 207-208
sorting and concatenating, 296-298
Columns property (Range object), referencing ranges, 61
combining
combo boxes
events, 162
combo charts, creating, 323-325
command button controls, 157-159
events, 159
comments, 18
adding to names, 100
in HTML, 533
Compatibility mode, troubleshooting, 552
complex expressions in Case statements, 84
computer name, returning, 472-473
concatenating
in JavaScript, 537
conceptual filters for pivot tables, 228-231
conditional compilation, 472
conditional formatting. see also data visualizations
above/below average cells, 344
custom number formats, 350-351
date-based, 348
progress indicators, creating, 269-270
text-based, 348
top/bottom values, 345
unique/duplicate cells, 346-347
value-based, 347
conditions
formula-based in Advanced Filter, 191-198
in If...Then...Else constructs, 81-82
in Select Case...End Select constructs, 83-84
configuring pivot tables, 211-212
constant values
compatibility, 552
retrieving when referencing Word, 412-414
constants
for SetElement method, 314-318
content management system, Excel as, 388-390
content panes (Office add-ins), 534, 544
content problem error message, 507-508
controls
active control, coloring, 463-464
adding
grouping into collections, 451-453
multicolumn list boxes, 464-465
organizing on UserForm toolbar, 443-444
ProgIDs for, 458
programming, 156
renaming, 156
scrollbars as sliders, 450-451
troubleshooting new controls, 157
converting
formulas to R1C1 style, 96
pivot tables to values, 215-217
week numbers to dates, 295-296
with Save As, 517
with VB Editor, 518
copying
all columns with Advanced Filter, 200-201
in recorded code, 49
subset of columns with Advanced Filter, 201-203
counting
records in pivot tables, 219
workbooks in directory, 284-285
Create New Theme Colors dialog box, 359
CreateObject function, referencing Word, 411
CreatePivotTable method, 211-212
creating
array names, 104
charts, 305
with AddChart2 method, 306-307
backward compatibility, 328
custom web pages, 387
fields (database), 440
formula names, 101
icon sets for subset of range, 340-341
individual reports with Advanced Filter, 203-207
macro buttons
on Quick Access Toolbar, 11
on ribbon, 10
map charts, 326
multidimensional arrays, 124-125
number names, 103
ranges from overlapping ranges, 62
shared Access databases, 427-428
table relationships in Data Model, 242
tables (database), 439
transparent userforms, 465-466
UDFs (user-defined functions), 279-281
criteria ranges in Advanced Filter, 189-190
clearing, 198
formula-based conditions, 191-198
joining
with logical AND, 191
Criteria reserved name, 105
CSS (Cascading Style Sheets), 534
CSV (comma-separated values) files, importing and deleting, 254
.csv file extension, opening files with, 397
curly braces ({ }), in JavaScript, 536
CurrentRegion property (Range object), 175
selecting ranges, 63
cursor locations, 428
cursor types, 428
cursors, 428
custom functions in JavaScript, 525. see also UDFs (user-defined functions)
custom icons, adding to buttons (on ribbon), 504-505
custom number formats, 350-351
custom objects, 131. see also class modules
custom properties, creating, 146-149
custom sort order, creating, 268-269
Custom UI Editor tool, 502
custom web pages, creating, 387
customizing
About dialog box, 475
ribbon, 493
accessing file structure, 501
creating customui folder/file, 494-495
renaming/opening workbooks, 502
troubleshooting error messages, 493, 505-509
visibility of ribbon, 494
customui folder/file, creating, 494-495
D
daily dates, grouping in pivot tables, 219-221
DAOs (data access objects), 426
data
in arrays, retrieving, 126-127
web data
scheduling retrieval, 381
data access objects (DAOs), 426
data area for pivot tables, adding fields, 212-215
data bars, 329
Data field (pivot tables), 218-219
Data Model, 241
importing text files into, 403-404
pivot cache, defining, 243
pivot tables
adding text fields, 243
tables
adding, 242
creating relationships, 242
data sets for pivot tables, replicating reports, 224-227
data types for input boxes, 152
data visualizations, 329. see also conditional formatting
color scales, adding to ranges, 336-337
data bars
icon sets
creating for subset of range, 340-341
VBA methods/properties for, 330-331
Database reserved name, 105
databases, SQL Server, 440-442. see also Access; MDB files
dates
converting week numbers to, 295-296
daily dates, grouping in pivot tables, 219-221
highlighting cells based on, 348
retrieving
permanent, 287
saved, 286
DblClick event
frames/option buttons, 164
graphics, 165
label/text box/command button control, 159
list boxes/combo boxes, 162
MultiPage controls, 168
userforms, 155
Deactivate event (userforms), 155
Debug mode, errors not showing up, 487-488
debugging tools. see also error handling
breakpoints, 40
moving forward/backward in code, 40
running code while stepping, 41
ToolTips, 42
32-bit and 64-bit compatibility, 471-472
checking open network files, 473
creating running timer, 476-477
customizing About dialog box, 475
disabling X button in userforms, 475-476
playing sounds, 477
returning computer name, 472-473
returning display resolution, 474
declaring
multidimensional arrays, 124-125
decrement operator (--), in JavaScript, 543
default file type, changing, 5
defined constants in Help topics, 35-37
defining
in Data Model, 243
slicer cache, 235
add-ins, 521
CSV files, 254
names, 100
part of pivot table, 215
pivot cache, 216
records (database), 435
selections in recorded code, 46-47
delimited strings, extracting elements from, 296
delimited text files, 393
delimiter settings, 401
Design tab (pivot tables), Layout group, 248
dictionaries, collections versus, 142-144
Dim statements, declaring variables, 79
directories
counting workbooks in, 284-285
disabling
events, 111
displaying File Open dialog box, 170-171
display resolution, returning, 474
DLLs (dynamic link libraries), 469
Document object (Word), 415
closing documents, 416
opening documents, 416
printing documents, 417
documents (Word)
closing, 416
opening, 416
printing, 417
selections in, 417
navigating, 417
DoEvents syntax, 488
drilling down pivot tables, 265-266
drop-down menus (AutoFilter), hiding, 207-208
DropButtonClick event
combo boxes, 162
text box control, 159
duplicate ActiveX label code, minimizing, 144-146
duplicate cells, highlighting, 346-347
duplicate max values, returning addresses of, 301-302
duplicate values, removing, 291-292
duplicate values rules, 330
dynamic arrays, declaring, 128-129
dynamic cursors, 428
dynamic filters in AutoFilter, 178-179
dynamic link libraries (DLLs), 469
E
Edit Watch dialog box, 43
editing macros
Properties window, 15
VB Editor settings, 14
either/or decisions in If...Then...Else constructs, 82
ElseIf statements, 82
email addresses, validating, 287-289
embedded charts, events for, 116-118, 134-135
empty cells in ranges, finding, 62-63
empty files, checking for, 489-490
enabling
events, 111
End If statements, 81
EndKey method (Selection object), 417
Enter event
frames/option buttons, 164
list boxes/combo boxes, 162
MultiPage controls, 169
spin buttons, 166
text box/command button control, 159
Err object, 484
Error event
frames/option buttons, 164
graphics, 165
label/text box/command button control, 159
list boxes/combo boxes, 162
MultiPage controls, 169
spin buttons, 166
userforms, 155
error handling. see also troubleshooting
checking for empty files, 489-490
with Err object, 484
errors not in Debug mode, 487-488
by Excel version, 492
method range of object global failed (error 1004), 489-490
multiple error handlers, 484
purposely encountering errors, 486-487
subscript out of range (error 9), 488-489
suppressing alerts, 486
training clients in, 487
error messages, troubleshooting on ribbon, 493, 505-509
errors. see also runtime errors
highlighting cells with, 348-349
Evaluate method, avoiding, 103
events. see also procedures
application events
in class modules, 118, 132-133
chart events, 116
for embedded charts, 116-117, 134-135
for combo boxes, 162
for command button controls, 159
enabling/disabling, 111
for graphic controls, 165
for label controls, 159
levels of, 109
for list boxes, 162
in MultiPage controls, 168-169
parameters, 110
for scrollbars, 451
for TabStrip controls, 447-448
for text box controls, 159
viewing and inserting, 110
where to use, 109
workbook events, list of, 111-113
workbook-level sheet events, list of, 113-114
worksheet events, list of, 114-115
examining recorded macro code, 33-34
breakpoints, 40
moving forward/backward in code, 40
optional parameters, 34
properties returning objects, 38
running code while stepping, 41
with ToolTips, 42
Excel
checking if open on network, 473
as content management system, 388-390
interface
Advanced Filter in, 182
extracting unique list of values, 183-184
formula-based conditions, 193-194
pivot tables, history in, 209-210
referencing Word from, 407
CreateObject function, 411
late binding, 410
New keyword, 410
retrieving constant values, 412-414
Excel 97-2003 Workbook (.xls) file type, 5
Excel 2019
changing default, 5
help features, object model, 551
new features, 547
3D Models, 551
array formulas, 549
icons, 550
pivot tables, 550
Quick Analysis tool, 549
ribbon, 548
single-document interface (SDI), 548-549
slicers, 550
SmartArt, 551
Office 365 subscription versus, 547
Excel Binary Workbook (.xlsb) file type, 5
Excel Macro-Enabled Workbook (.xlsm) file type, 4-5
Excel versions
backward compatibility when creating charts, 328
error handling in, 492
verifying
with Excel8CompatibilityMode property, 553
with Version property, 552
Excel Workbook (.xlsx) file type, 4
Excel8CompatibilityMode property, 553
existence of names, checking for, 106
Exit Do statement, 76
Exit event
frames/option buttons, 164
list boxes/combo boxes, 162
MultiPage controls, 169
spin buttons, 166
text box/command button control, 159
exiting
exporting
charts as graphics, 328
Extract reserved name, 105
extracting
elements from delimited strings, 296
unique list of values, 182
F
F1 shortcut key (Help topics), 32-33
FieldInfo parameter, values in, 396-397
fields
calculated fields in pivot tables, 246-247
Data Model fields, adding to pivot tables, 243
database fields
checking for existence, 438-439
creating, 440
multiple value fields
extracting unique list of values, 187-188
in pivot table data area, adding, 212-215
requiring in userforms, 169
row fields in pivot tables, suppressing subtotals, 249
skipping during imports, 395
value fields, adding to Data Model pivot tables, 243-244
file formats, troubleshooting, 509
file names, setting in cells, 282
File Open dialog box, displaying, 170-171
file path, setting in cell, 282
file structure, accessing, 501
files
CSV files, importing and deleting, 254
empty files, checking for, 489-490
opening in Notepad, 526
saving, changing default file type, 5
selecting in userforms, 170-171
text files, reading/parsing in memory, 254-255
fill color, formatting charts, 320-322
Filter. See AutoFilter
filter in place in Advanced Filter, 199
viewing all records after, 200
filtering. see also Advanced Filter
charts, 313
pivot tables
with conceptual filters, 228-231
with Search box, 232
with Timeline slicers, 238-241
record sets in pivot tables, 247
finding
first nonzero-length cell in range, 292-293
last data row in recorded code, 47-48
fixed-width text files, opening, 393-397
flow control. see also loops
If...Then...Else constructs, 81-82
either/or decisions in, 82
ElseIf statements in, 82
End If statements in, 81
Select Case...End Select constructs, 83-84
for (... in ...) statement in JavaScript, 541-542
For each..next statements in JavaScript, 541-542
for loops in JavaScript, 539
For statements in For...Next loops, 69-71
nesting, 74
Step clause in For statement, 72-73
variables in For statement, 71-72
form controls, assigning macros to, 12-13
Format method (formatting charts), 319-320
FormatConditions collection, 330
formatting
cells as military time, 115-116
charts
line settings, 322
referring to specific chart, 310-311
with SetElement method, 314-319
conditional formatting. see also data visualizations
above/below average, 344
custom number formats, 350-351
date-based, 348
text-based, 348
top/bottom values, 345
unique/duplicate cells, 346-347
value-based, 347
slicers, 238
sparklines, 359
tables, resetting formatting, 275
forms in hidden workbooks, 522-523. see also userforms
formula-based conditions in Advanced Filter criteria ranges, 191-198
formulas
array formulas, R1C1 references with, 96
for arrays, 549
changing to variables in recorded code, 49
conditional formatting with, 349-350
converting to R1C1 style, 96
names, creating, 101
R1C1, in recorded code, 49
in text fields, troubleshooting, 396
forward in code, moving, 40
found a problem error message, 507-508
FreeFile function, 401
FTP, publishing web data via, 390-391
Function Arguments dialog box, 281
functions. see also declarations (API); UDFs (user-defined functions)
arrays, passing to, 130
in JavaScript, 535
math functions in JavaScript, 543-544
names, checking for existence, 106
recursive, 284
G
GetObject function, referencing Word, 411-412
GetUserName API function, 470-471
globally unique identifiers (GUIDs), 528
Go To Special dialog box, 63-65
selecting visible cells only, 179-180
gradients, formatting charts, 321-322
graphics, exporting charts as, 328
grouping
controls into collections, 451-453
daily dates in pivot tables, 219-221
GroupName property (option buttons), 163
groups (on ribbon), creating, 495-496
GUIDs (globally unique identifiers), 528
H
Hello World example (Office add-ins), 526-530
help features
in Excel 2019
macro recorder as, 32
object model, 551
in userforms
coloring active control, 463-464
tip text, 462
OpenText method, 33
optional parameters, 34
properties returning objects, 38
hiding
drop-down menus (AutoFilter), 207-208
names, 105
rows with Go To Special dialog box, 180-181
userforms, 154
highlight cells rules, 330
highlighting
cells
above/below average, 344
by date, 348
by text, 348
top/bottom values, 345
by value, 347
rows, 350
HomeKey method (Selection object), 417
horizontal axis in sparklines, viewing, 364-365
hovering, querying variables by, 42
HTML
creating custom web pages, 387
in Office add-ins, 532
CSS files, 534
tags, 533
hyperlinks
returning addresses of, 302
I
icon sets, 329
creating for subset of range, 340-341
icons
custom icons, adding to buttons (on ribbon), 504-505
filtering with AutoFilter, 178
Microsoft Office icons, adding to buttons (on ribbon), 503-504
new features, 550
if statements
Exit Do statements in, 76
in If...Then...Else constructs, 81-82
in JavaScript, 539
If...Then...Else constructs, 81-82
either/or decisions in, 82
ElseIf statements in, 82
End If statements in, 81
illegal qualified name character error message, 506-507
illegally closing userforms, 169-170
images, adding
to buttons (on ribbon), 503-505
CSV files, 254
from text files, 393
into Data Model with Power Query, 403-404
file types available, 393
more than 1,048,576 rows, 399-404
reading one row at a time, 400-401
improving. See optimizing
inactive worksheets, referencing ranges in, 55-56
increment operator (++) in JavaScript, 543
Initialize event (userforms), 155
initializing Office add-ins, 545
return data types, 152
secure password input, 270-273
Insert Function dialog box, 280
inserting
comments for names, 100
events, 110
modules, 15
text in selections (Word), 417-418
interactivity, adding to Office add-ins, 530-532, 535
interface
Excel
Advanced Filter in, 182
extracting unique list of values, 183-184
formula-based conditions, 193-194
Office add-ins, 534
interrupting macros, 111
Intersect method (Range object), creating ranges from overlapping ranges, 62
invalid file format error message, 509
IsEmpty function (Range object), finding empty cells, 62-63
Ivy charts, creating, 305
J
adding interactivity with, 530-532, 535
arrays, 538
changes for Office add-ins, 544-545
curly braces ({ }) in, 536
custom functions, 525
For each..next statements, 541-542
for loops, 539
functions, 535
if statements, 539
initializing Office add-ins, 545
line breaks, 536
reading/writing to worksheets, 545
Select...Case statements, 540-541
semicolons (;) in, 536
spaces in, 536
variables, 537
writing to content/task panes, 544
Jet engine, 426
joining
criteria ranges
with logical AND, 191
ranges with Union method, 61
K
keyboard shortcuts, running macros, 510
KeyDown event
frames/option buttons, 164
list boxes/combo boxes, 162
MultiPage controls, 169
spin buttons, 166
text box/command button control, 159
userforms, 155
KeyPress event
frames/option buttons, 164
list boxes/combo boxes, 162
MultiPage controls, 169
spin buttons, 167
text box/command button control, 159
userforms, 155
KeyUp event
frames/option buttons, 164
list boxes/combo boxes, 162
MultiPage controls, 169
spin buttons, 167
text box/command button control, 159
userforms, 155
L
labels in sparklines, viewing, 365
last data row, finding in recorded code, 47-48
Layout event
frames, 164
MultiPage controls, 169
userforms, 155
Layout group (Design tab) for pivot tables, 248
layout settings for pivot tables, 248-249
libraries for object models, setting priority, 409
line breaks in JavaScript, 536
line continuation, 18
line settings, formatting charts, 322
line sparklines, 353
list boxes
events, 162
ListCount property, 162
ListCount property (list boxes), 162
listing files in directory, 251-253
listings
Data Model pivot table, 245-246
pivot table generation, 214-215
pivot table static summary, 216-217
pivot table timelines, 239-241
replicating pivot table reports, 224-227
creating, 98
lock types, 428
logical AND, joining criteria ranges with, 191
logical operators in JavaScript, 542-543
logical OR, joining criteria ranges with, 190-191
loops. see also flow control
breaking out, 539
for loops in JavaScript, 539
nesting, 74
Step clause in For statement, 72-73
variables in For statement, 71-72
Go To Special dialog box versus, 180-181
replacing with AutoFilter, 173-176
While...Wend, 79
Lotus 1-2-3 macro recorder, Excel macro recorder versus, 1, 24
M
M language, 375
Macintosh computers, compatibility, 492
macro buttons, creating
on Quick Access Toolbar, 11
on ribbon, 10
macro recorder, 8
cleaning up code, tips for, 46-52
Developer tab icons, 4
breakpoints, 40
moving forward/backward in code, 40
optional parameters, 34
properties returning objects, 38
running code while stepping, 41
with ToolTips, 42
fields in Record Macro dialog box, 9
as help resource, 32
preparations for recording, 16-17
recording macro, 17
viewing code in Programming window, 17-19
macros
assigning
to shortcut keys, 9
editing
Properties window, 15
VB Editor settings, 14
interrupting, 111
restarting, 111
running
with ActiveX controls, 512-513
with keyboard shortcuts, 510
with macro button on Quick Access Toolbar, 11
with macro button on ribbon, 10
with shortcut keys, 10
saving, 9
scheduling, 381
for every two minutes, 385
for x minutes in future, 383-384
Ready mode, 382
windows of time for, 382
security, 5
enabling/disabling macros, 7-8
shortcut keys, changing, 18
testing, 19
manually filtering pivot tables, 227-228
map charts, creating, 326
map() function (JavaScript), 544
markers in sparklines, viewing, 365-366
matching records, returning all with Advanced Filter, 200
copying subset of columns, 201-203
creating individual reports, 203-207
math functions in JavaScript, 543-544
mathematical operators in JavaScript, 542-543
matrix. See arrays
max values, returning addresses of duplicates, 301-302
MDB (multidimensional database) files, 425
creating shared databases, 427-428
database connection terminology, 428-429
fields
checking for existence, 438-439
creating, 440
records
deleting, 435
tables
checking for existence, 437-438
creating, 439
memory, reading/parsing text files in, 254-255
message boxes, 152
errors versus, 487
method range of object global failed (error 1004), 489-490
methods, 28
Microsoft Access. See Access
Microsoft ActiveX Data Objects Library, 426
Microsoft Jet Database Engine, 426
Microsoft Office icons, adding to buttons (on ribbon), 503-504
Microsoft Office Trusted Location dialog box, 6
Microsoft Scripting Runtime reference library, 284
military time, formatting cells as, 115-116
minimizing duplicate ActiveX label code, 144-146
minutes in future, scheduled macros for, 383-384
mixed alphanumeric data, sorting, 298-300
mixed references with R1C1 references, 92-93
mixed text, retrieving numbers from, 294-295
modules, inserting, 15
MouseDown event
frames/option buttons, 164
graphics, 165
label/text box/command button control, 159
list boxes/combo boxes, 162
MultiPage controls, 169
userforms, 155
MouseMove event
frames/option buttons, 164
graphics, 165
label/text box/command button control, 159
list boxes/combo boxes, 162
MultiPage controls, 169
userforms, 155
MouseUp event
frames/option buttons, 164
graphics, 165
label/command button control, 159
label/text box/command button control, 159
list boxes/combo boxes, 162
MultiPage controls, 169
userforms, 155
moving
forward/backward in code, 40
part of pivot table, 215
MsgBox function, 152
multicolumn list boxes, 464-465
multidimensional arrays, declaring, 124-125
multidimensional database files. See MDB
MultiPage controls, 167-169, 446
multiple A1 references, replacing with single R1C1 reference, 93-95
multiple actions in recorded code, 50
multiple characters, substituting, 293-294
multiple colors for data bars, 341-343
multiple error handlers, 484
multiple fields, extracting unique list of values, 187-188
multiple items, selecting with AutoFilter, 176-177
multiple queries for web data, 377-378
multiple value fields in pivot tables, 218-219
multiplication tables, building, 93-95
MultiSelect property (list boxes), 161-162
N
Name Manager dialog box, 97-98
name of workbook, setting in cell, 282
named ranges
for VLOOKUP() function, 106-108
names
of arrays, creating, 104
capabilities of, 100
checking for existence, 106
comments, adding to, 100
deleting, 100
of formulas, creating, 101
hiding, 105
of numbers, creating, 103
renaming, 99
values, storing with, 102
navigating
documents (Word) with bookmarks, 421-422
selections (Word), 417
navigation keys in recorded macros, 26
nesting
For...Next loops, 74
network files, checking if open, 473
network requirements for Office add-ins, 526-529
new features, 547
3D Models, 551
array formulas, 549
icons, 550
Quick Analysis tool, 549
ribbon, 548
single-document interface (SDI), 548-549
slicers, 550
SmartArt, 551
New keyword, referencing Word, 410
Next statements in For...Next loops, 69-71
noncontiguous ranges, selecting, 66
nonzero-length cells, finding first in range, 292-293
Notepad, 526
NOW function, 287
NumberFormat property (FormatConditions object), 350-351
numbers
names, creating, 103
retrieving from mixed text, 294-295
week numbers, converting to dates, 295-296
O
priority of libraries, setting, 409
CreateObject function, 411
late binding, 410
New keyword, 410
retrieving constant values, 412-414
object-oriented languages
parts of speech analogy, 28-31
procedural languages versus, 27-28
objects, 28
collections versus, 29
custom. See custom objects
properties, 31
returned by properties, 38
Office 365 subscription
Excel 2019 versus, 547
Power BI Custom Visuals support, 306
adding interactivity, 530-532, 535
case sensitivity, 526
content/task panes, writing to, 544
HTML in, 532
CSS files, 534
tags, 533
initializing, 545
interface types, 534
JavaScript changes for, 544-545. see also JavaScript
Office Add-Ins dialog box, refreshing, 535
Office icons, adding to buttons (on ribbon), 503-504
Offset property (Range object), 175
OLAP pivot tables, filtering, 266-268
On Error Go To syntax, 482-483
On Error Resume Next syntax, 484-485
online data. See web data
OnTime method, 381
canceling scheduled macros, 382-383
for every two minutes, 385
for x minutes in future, 383-384
Ready mode, 382
windows of time in, 382
Open method (Document object), 416
open network files, checking for, 473
open workbooks, checking for, 282-283
opening
documents (Word), 416
files in Notepad, 526
fixed-width text files, 393-397
VB Editor, 18
workbooks when customizing ribbon, 502
OpenText method, 33, 35, 37, 393
file types for, 393
fixed-width text files, 393-397
importing more than 1,048,576 rows, 399
operators in JavaScript, 542-543
optimistic lock types, 428
optimizing code with arrays, 128
option buttons, 163
GroupName property, 163
optional parameters in Help topics, 34
OR, joining criteria ranges with, 190-191
organizing controls on UserForm toolbar, 443-444
overlapping ranges, creating ranges from, 62
P
Page Setup dialog box, ignoring errors, 485-486
for events, 110
optional, 34
parsing text files in memory, 254-255
parts of speech analogy (object-oriented languages), 28-31
pass-through queries, 429
passing
arrays to functions, 130
values to/from arrays, 130
passwords
for add-ins, 520
secure password input, 270-273
pasting in recorded code, 49
patterns, formatting charts, 321
percentages, changing pivot table calculations to, 221-223
performance of code, optimizing with arrays, 128
permanent date/time, retrieving, 287
Personal Macro Workbook, 9
pessimistic lock types, 429
pictures, formatting charts, 321
pivot cache
deleting, 216
pivot table events (workbook-level), list of, 113-114
pivot tables, 209
AutoSort, 224
calculated items in, 247
calculations, changing to percentages, 221-223
daily dates, grouping, 219-221
data area, adding fields, 212-215
Data Model example code, 244-246
Data Model fields, adding, 243
data sets, replicating reports for, 224-227
data visualizations in, 249-250
filtering
with conceptual filters, 228-231
with Search box, 232
with Timeline slicers, 238-241
Layout group (Design tab), 248
moving/changing part of, 215
new features, 550
OLAP pivot tables, filtering, 266-268
pivot cache
deleting, 216
record sets, filtering, 247
reports, layout settings, 248-249
subtotals, suppressing, 249
value fields
values area, eliminating blank cells, 223-224
VBA terminology for, 210
playing sounds, 477
plus sign (+) in JavaScript, 537
Power BI Custom Visuals, 306
Power Query add-in, 375
importing text files into Data Model, 403-404
preparing to record macros, 16-17
Preserve keyword, 129
Print_Area reserved name, 105
Print_Titles reserved name, 105
printing documents (Word), 417
PrintOut method (Document object), 417
priority of libraries, setting, 409
private variables, 135
procedural languages, object-oriented languages versus, 27-28
procedures, preventing recursive calling, 116. see also events
ProgIDs for controls, 458
programming
controls, 156
Programming window, viewing code in, 17-19
progress indicators, creating, 269-270
properties, 31, 135. see also variables
returning objects, 38
Properties window, 15
closing, 513
Property Get procedures, 137
Property Let procedures, 136
Property Set procedures, 137
protecting
code
disadvantages of, 490
Excel password versions, 491-492
hidden names, 106
public variables, 135
publishing web data
Excel as content management system, 388-390
writing macro for, 387
purposely encountering errors, 486-487
Q
QueryClose event (userforms), 155, 169-170
querying
variables
with ToolTips, 42
in Watches window, 43
with multiple queries, 377-378
scheduling retrieval, 381
Quick Access Toolbar, macro buttons on, 11
Quick Analysis tool, 549
quotation marks (““) in JavaScript, 537
R
R1C1 references
absolute references with, 92
with array formulas, 96
associating column numbers with column names, 95
converting formulas to, 96
for entire columns/rows, 93
in recorded code, 49
relative references with, 91-92
replacing multiple A1 references, 93-95
RAND function, 303
random numbers, generating static, 303
Range object, 541
Areas collection, selecting noncontiguous ranges, 66
Cells property, selecting ranges, 57-58
Columns property, referencing ranges, 61
CurrentRegion property, selecting ranges, 63
Intersect method, creating ranges from overlapping ranges, 62
IsEmpty function, finding empty cells, 62-63
Offset property, referencing ranges, 58-59
relative to other ranges, 56
syntax, 54
Resize property, resizing ranges, 60-61
Rows property, referencing ranges, 61
SpecialCells method, selecting specific cells, 63-65
Union method, joining ranges, 61
in Word, 418
adding
creating from overlapping ranges, 62
finding
first nonzero-length cell in, 292-293
joining with Union method, 61
multiple data bar colors in, 341-343
named ranges
for VLOOKUP() function, 106-108
referencing
with Columns property, 61
in recorded code, 47
relative to other ranges, 56
with Rows property, 61
syntax, 54
resizing with Resize property, 60-61
selecting
with CurrentRegion property, 63
noncontiguous ranges, 66
specific cells with SpecialCells method, 63-65
sorting
numerically then alphabetically, 298-300
subsets of, creating icon sets for, 340-341
tables
selecting, 66
reading. see also importing
from worksheets with JavaScript, 545
text files into memory, 254-255
Ready mode, scheduled macros and, 382
Record Macro dialog box, 8-9, 17
record sets, 428
filtering in pivot tables, 247
recording macros, 8
cleaning up code, tips for, 46-52
Developer tab icons, 4
breakpoints, 40
moving forward/backward in code, 40
optional parameters, 34
properties returning objects, 38
running code while stepping, 41
with ToolTips, 42
fields in Record Macro dialog box, 9
as help resource, 32
preparations for recording, 16-17
recording macro, 17
viewing code in Programming window, 17-19
records
copying to worksheets, 257-258
counting in pivot tables, 219
database
deleting, 435
recursive functions, 284
recursive procedure calling, preventing, 116
R1C1
absolute references with, 92
with array formulas, 96
associating column numbers with column names, 95
converting formulas to, 96
for entire columns/rows, 93
relative references with, 91-92
replacing multiple A1 references, 93-95
to ranges in recorded code, 47
referencing
with Columns property, 61
relative to other ranges, 56
with Rows property, 61
syntax, 54
Word, 407
CreateObject function, 411
late binding, 410
New keyword, 410
retrieving constant values, 412-414
refreshing Office Add-Ins dialog box, 535
relationships, creating between tables in Data Model, 242
relative references
to ranges, 56
reminders (verbal), scheduling, 384-385
RemoveControl event
frames, 164
MultiPage controls, 169
userforms, 155
removing. See deleting
controls, 156
names, 99
workbooks when customizing ribbon, 502
reordering columns with Advanced Filter, 201-203
replacing
loops with AutoFilter, 173-176
multiple A1 references with single R1C1 reference, 93-95
replicating reports in pivot tables, 224-227
reports
creating individual with Advanced Filter, 203-207
from pivot tables, layout settings, 248-249
replicating in pivot tables, 224-227
required fields in userforms, 169
requiring variable declarations, 14
Reset button (VB Editor), 480
Reset command (stopping code), 40
resetting table formatting, 275
Resize event (userforms), 156
Resize property (Range object), 60-61
resizing
ranges with Resize property, 60-61
userforms, 457
resolution (of display), returning, 474-475
resources for information. See help resources
restarting macros, 111
Restore Down icon (arranging window), 39
retrieving
constant values when referencing Word, 412-414
numbers from mixed text, 294-295
permanent date/time for workbooks, 287
saved date/time for workbooks, 286
with multiple queries, 377-378
scheduling retrieval, 381
return data types for input boxes, 152
returning
all matching records with Advanced Filter, 200
copying subset of columns, 201-203
creating individual reports, 203-207
cell addresses of duplicate max values, 301-302
column letter of cell addresses, 302-303
display resolution, 474
hyperlink addresses, 302
reversing cell contents, 300-301
ribbon
customizing, 493
accessing file structure, 501
creating customui folder/file, 494-495
renaming/opening workbooks, 502
troubleshooting error messages, 493, 505-509
visibility of ribbon, 494
macro buttons on, 10
new features, 548
RibbonX Visual Designer, 503
row fields in pivot tables, suppressing subtotals, 249
RowGrand property (pivot tables), 248
rows
changing to variables in recorded code, 49
formatting with AutoFilter, 173-176
hiding with Go To Special dialog box, 180-181
highlighting, 350
referencing with R1C1 references, 93
Rows property (Range object), referencing ranges, 61
running
code while stepping, 41
macros
with ActiveX controls, 512-513
with form controls, 12,-13
with keyboard shortcuts, 510
with macro button on Quick Access Toolbar, 11
with macro button on ribbon, 10
with shortcut keys, 10
running timers, creating, 476-477
runtime, adding controls, 455-461
subscript out of range, 488-489
method range of object global failed, 489-490
S
Save As command, converting workbooks to add-ins, 517
saved date/time, retrieving, 286
saving
add-in data, 516
files, changing default file type, 5
macros, 9
scheduling macros, 381
for every two minutes, 385
for x minutes in future, 383-384
Ready mode, 382
windows of time for, 382
Scroll event
frames, 164
MultiPage controls, 169
userforms, 156
scrollbars, as sliders, 450-451
SDI (single-document interface), 548-549
Search box
filtering pivot tables, 232
selecting with in AutoFilter, 177
searching for strings, 300
secure password input, 270-273
security
add-ins, 520
macros, 5
enabling/disabling macros, 7-8
passwords
for add-ins, 520
secure password input, 270-273
protecting code
disadvantages of, 490
Excel password versions, 491-492
Select Case...End Select constructs, 83-84
Select...Case statements
selecting
cells with SpecialCells, 274-275
multiple items with AutoFilter, 176-177
ranges
with CurrentRegion property, 63
noncontiguous ranges, 66
specific cells with SpecialCells method, 63-65
with Search box in AutoFilter, 177
tables, 66
Selection object (Word), 417
navigating, 417
Selection property, 38
selections, deleting in recorded code, 46-47
semicolons (;) in JavaScript, 536
separating worksheets into workbooks, 255-256
Set statements for object variables, 80
SetElement method (formatting charts), 314-319
shapes
assigning macros to, 12
shared Access databases, creating, 427-428
sharing
UDFs (user-defined functions), 281
workbooks, 425
sheet events, workbook-level, 113-114
shortcut keys
assigning to macros, 9
changing for macros, 18
running macros, 10
ShowAllData method, 200
ShowDetail property (pivot tables), 247
single rows, importing text files by, 400-401
single-document interface (SDI), 548-549
size of pivot tables, finding, 215-217
skipping fields during imports, 395
slicer cache, defining, 235
slicers
filtering pivot tables, 234-238
formatting, 238
new features, 550
sliders, scrollbars as, 450-451
SmartArt
assigning macros to, 12
new features, 551
sorting
pivot tables, 224
ranges
numerically then alphabetically, 298-300
sounds, playing, 477
spaces in JavaScript, 536
SparklineGroup object, 353
formatting, 359
types of, 353
Speak On Enter feature, 384
SpecialCells method (Range object), selecting specific cells, 63-65, 274-275
speeding up. See optimizing
SpinDown event (spin buttons), 167
SpinUp event (spin buttons), 167
spreadsheets. See worksheets
SQL statements
pass-through queries, 429
viewing, 431
square brackets ([ ]), as Evaluate method, 103
standard add-ins. See add-ins
standard modules, creating collections, 139-140
states, enabling/disabling, 263, 265
static cursors, 428
static random numbers, generating, 303
Step clause in For statement in For...Next loops, 72-73
storing values with names, 102
strings
delimited strings, extracting elements from, 296
searching text for, 300
styles (chart), list of, 307-310
subscript out of range (error 9), 488-489
subset of columns, copying with Advanced Filter, 201-203
subsets of ranges, creating icon sets for, 340-341
substituting multiple characters, 293-294
SubtotalLocation property (pivot tables), 248
subtotals, suppressing in pivot tables, 249
summarizing records (database), 436-437
summing cells based on color, 289-290
suppressing
alerts, 486
subtotals in pivot tables, 249
switch() statement in JavaScript, 540-541
switching to SQL Server, 440-442
T
tab order in userforms, 462-463
tables
adding to Data Model, 242
creating relationships in Data Model, 242
database
checking for existence, 437-438
creating, 439
resetting formatting, 275
selecting, 66
tabs (on ribbon), creating, 495-496
Tag property, 453
tags (HTML), 533
task pane (Office add-ins), 534, 544
Terminate event (userforms), 156
test expressions, 83
testing macros, 19
text
highlighting cells based on, 348
inserting in selections (Word), 417-418
retrieving numbers from, 294-295
searching for strings, 300
text boxes, assigning macros to, 12
text editors, case sensitivity in, 526
text fields, troubleshooting formulas in, 396
text files
importing from, 393
into Data Model with Power Query, 403-404
file types available, 393
more than 1,048,576 rows, 399-404
reading one row at a time, 400-401
reading/parsing in memory, 254-255
Text Import Wizard, 16, 35, 37
textures, formatting charts, 320
Then statements in If...Then...Else constructs, 81-82
time formats, military time, 115-116
time windows for scheduled macros, 382
timers, creating running, 476-477
tip text in userforms, 462
titles (chart), specifying, 311-312
toggling R1C1 references, 88-89
ToolTips in VB Editor, 42
Top 10 AutoShow, filtering pivot tables, 232-234
tracking user changes, 262-263
training clients in error handling, 487
transparency of userforms, 465-466
TrapAppEvent procedure, 133
troubleshooting. see also error handling
Compatibility mode, 552
error messages on ribbon, 493, 505-509
formulas in text fields, 396
new controls, 157
trusted locations, adding, 6-7
two minutes, scheduled macros for, 385
Type...End Type statements, 146
TypeText method (Selection object), 417-418
U
cells
finding first nonzero-length in range, 292-293
returning addresses of duplicate max values, 301-302
returning column letter of address, 302-303
returning hyperlink addresses, 302
summing based on color, 289-290
characters, substituting multiple, 293-294
columns, sorting and concatenating, 296-298
delimited strings, extracting elements from, 296
email addresses, validating, 287-289
in JavaScript, 525
random numbers, generating static, 303
ranges, sorting numerically then alphabetically, 298-300
sharing, 281
text
retrieving numbers from, 294-295
searching for strings, 300
values
week numbers, converting to dates, 295-296
workbooks
checking for worksheet existence, 283-284
counting in directory, 284-285
retrieving permanent date/time, 287
retrieving saved date/time, 286
setting file path in cell, 282
setting name in cell, 282
worksheets, Select...Case statements in, 303-304
UDTs (user-defined types), 146-149
Union method (Range object), joining ranges, 61
unique cells, highlighting, 346-347
unique list of values, extracting, 182
unique values
Until clause in Do...Loop loops, 77-78
Unviewable+ VBA Project software, 490
updating records (database), 433-435
user changes, tracking, 262-263
user-defined functions (UDFs). See UDFs
user-defined types (UDTs), 146-149
calling, 154
controls
grouping into collections, 451-453
multicolumn list boxes, 464-465
ProgIDs for, 458
programming, 156
renaming, 156
scrollbars as sliders, 450-451
troubleshooting new controls, 157
help features
coloring active control, 463-464
tip text, 462
hiding, 154
message boxes, 152
required fields, 169
resizing, 457
V
validating email addresses, 287-289
value fields in pivot tables
values
arrays
passing to/from, 130
converting pivot tables to, 215-217
custom number formats, 350-351
extracting unique, 182
first unique, highlighting, 349-350
highlighting cells based on, 347
storing with names, 102
values area of pivot tables, eliminating blank cells, 223-224
variables. see also properties
arrays. See arrays
changing rows/formulas to in recorded code, 49
in For statement in For...Next loops, 71-72
in JavaScript, 537
querying
with ToolTips, 42
in Watches window, 43
VB Editor
converting workbooks to add-ins, 518
debugging tools
breakpoints, 40
moving forward/backward in code, 40
running code while stepping, 41
ToolTips, 42
opening, 18
Programming window, viewing code in, 17-19
Properties window, 15
settings, 14
VBA (Visual Basic for Applications)
barriers to entry, 1
macro recorder limitations, 1-2
Visual Basic versus BASIC, 2
data visualization methods/properties, 330-331
help resources
macro recorder, 32
new features, 547
3D Models, 551
array formulas, 549
icons, 550
pivot tables, 550
Quick Analysis tool, 549
ribbon, 548
single-document interface (SDI), 548-549
slicers, 550
SmartArt, 551
object models, 407
as object-oriented language
parts of speech analogy, 28-31
procedural languages versus, 27-28
pivot table terminology, 210
power of, 1
protecting code
disadvantages of, 490
Excel password versions, 491-492
VBA Extensibility, adding code to workbooks, 276-277
verbal reminders, scheduling, 384-385
verifying workbook version
with Excel8CompatibilityMode property, 553
with Version property, 552
Version property, 552
versions of Excel
error handling, 492
verifying
with Excel8CompatibilityMode property, 553
with Version property, 552
viewing
add-ins, 519
all records after filter in place, 200
custom ribbon, 494
events, 110
horizontal axis in sparklines, 364-365
macro code in Programming window, 17-19
Project Explorer, 14
sparkline labels, 365
SQL statements, 431
VBA and Excel windows, 39
Word instances, 411
visible cells, selecting, 179-180
Visual Basic, BASIC versus, 2
Visual Basic for Applications. See VBA (Visual Basic for Applications)
visualizations. See data visualizations
VLOOKUP() function
finding results in web data, 378
W
warnings, suppressing, 486
retrieving constants, 413
waterfall charts, creating, 326-328
publishing
Excel as content management system, 388-390
writing macro for, 387
with multiple queries, 377-378
scheduling retrieval, 381
week numbers, converting to dates, 295-296
Wend statements in While...Wend loops, 79
While clause in Do...Loop loops, 77-78
While...Wend loops, 79
win/loss sparklines, 353, 366-367
windows (VBA and Excel), viewing, 39
Windows API. See API (application programming interface)
Windows computers, compatibility, 492
windows of time for scheduled macros, 382
WinWord.exe, 411
With...End With statements in recorded code, 50
Word
referencing from Excel, 407
CreateObject function, 411
late binding, 410
New keyword, 410
retrieving constant values, 412-414
viewing instances, 411
workbook events, list of, 111-113
workbook-level sheet events, list of, 113-114
workbooks
adding code with VBA Extensibility, 276-277
Compatibility mode, troubleshooting, 552
converting to add-ins, 516-517
with Save As, 517
with VB Editor, 518
counting in directory, 284-285
file structure, accessing, 501
renaming/opening when customizing ribbon, 502
retrieving permanent date/time, 287
retrieving saved date/time, 286
setting file path in cell, 282
setting name in cell, 282
sharing, 425
user changes, tracking, 262-263
verifying version
with Excel8CompatibilityMode property, 553
with Version property, 552
worksheets. See worksheets
worksheet events, list of, 114-115
as arrays, 124
checking for existence, 283-284
combining into single workbook, 256-257
inactive worksheets, referencing ranges in, 55-56
reading/writing with JavaScript, 545
Select...Case statements in, 303-304
separating into workbooks, 255-256
user changes, tracking, 262-263
writing
to content/task panes with JavaScript, 544
to worksheets with JavaScript, 545
X-Y-Z
X button in userforms, disabling, 475-476
.xlsb (Excel Binary Workbook) file type, 5
.xls (Excel 97-2003 Workbook) file type, 5
.xlsm (Excel Macro-Enabled Workbook) file type, 4-5
.xlsx (Excel Workbook) file type, 4
XML
case sensitivity, 526
Zoom event
frames, 164
MultiPage controls, 169
userforms, 156