Index
Numerics
32-bit and 64-bit compatible API declarations, 511-512
A
A1 references
versus R1C1 references, 99-100
above-average records, returning with formula-based conditions, 221
absolute references, using with R1C1 references, 104-105
Access
ADO
records, adding to database, 473-474
tables, checking for existence of, 480-481
fields, adding to database, 482-483
shared Access database, creating, 471
accessing
Developer tab, 9
ActiveX controls
creating right-click menu, 299-300
AddAboveAverage method, 392
adding
code to new workbooks, 302-303
comments to names, 114
controls to userforms, 181
macro button
to Quick Access toolbar, 17
to Ribbon, 16
trusted location to hard drive, 12
add-ins
closing, 560
hidden workbooks as alternative to, 561-562
removing, 560
Add-Ins group (Developer tab), 10
AddTop10 method, 393
AddUniqueValues method, 393-395
adjusting macro default settings, 11-14
ADO (ActiveX data objects)
versus DAO, 470
fields, checking for in Access database, 481-482
records
adding to Access database, 473-474
deleting in Access database, 478-479
retrieving from Access database, 475-476
summarizing in Access database, 479-480
updating in Access database, 476-478
tables, checking for in Access database, 480-481
Advanced Filter command, extracting unique list of values with, 206-207
advanced filters
reports, creating from, 226-229
xlFilterCopy parameter, 222-225
allowing macros outside of trusted locations, 13
Anderson, Ken, 437
APIs
32-bit and 64-bit compatible declarations, 511-512
Windows API, 509
application-level events, 123, 140-148
applying data visualizations to pivot tables, 270
apps
Hello World
interactivity, adding, 568-570
Napa Office 365 Development Tools, 582
Office apps, JavaScript incorporation in, 581-582
Areas collection, returning noncontiguous ranges, 76
array formulas
arrays
multidimensional, declaring, 150
passing, 156
reinitializing, 155
speeding up code with, 153-155
assigning
macros to text boxes, form controls, or shapes, 18-19
names
to numbers, 116
to tables, 117
assignment operators (JavaScript), 578-579
attaching macros
to shapes, 552
attributes for Ribbon controls, 538-540
AutoFilter
dynamic date range, selecting, 202-203
field drop-downs, turning off, 229-230
filtering
by color, 201
by icon, 201
multiple items, selecting, 200
visible cells, selecting, 203
automation
CreateObject function, 454
early binding, referencing Word object, 451-453
late binding, referencing Word object, 453-454
New keyword, referencing Word application, 454
AutoShow feature (VBA), 255-257
AutoSort option (pivot tables), 246
AutoSum button and recording macros, 30-31
B
bins, creating for frequency charts, 365-368
blank cells, removing in pivot table values area, 246
BookOpen() function, 309
breakpoints, 49
building
cell progress indicator, 294-295
Data Model pivot table, 262-267
list of unique combinations of two fields, 211
built-in charts, specifying, 333-334
buttons
macro button
adding to Quick Access toolbar, 17
adding to Ribbon, 16
option buttons, adding to userforms, 186-187
toggle buttons, 491
C
calculated data fields (pivot tables), 267
calculated items (pivot tables), 268
canceling scheduled macros, 429
capturing data periodically, 427-428
case of text, changing, 297-298
case studies
A1 versus R1C1 references, 102-103
combo charts, creating, 361-363
controls, adding to userforms, 181
custom Excel 2003 toolbar, converting to Excel 2013, 547
looping through all files in a directory, 91-92
military time, entering in cells, 136
multicolumn list boxes, 505
named ranges, using with VLOOKUP() function, 120-121
page setup problems, error handling, 524
password cracking, 530
specific cells, selecting with SpecialCells method, 74-76
standard add-in security, 559
storing macros and forms with hidden code workbooks, 562
cell progress indicator, building, 294-295
cells
empty cells, verifying, 73
military time, entering, 136
selecting with SpecialCells method, 74-76
visible cells, selecting, 203
Cells property, selecting ranges, 68-69
changing
size of ranges, 71
characteristics of standard add-ins, 555-556
chart sheet events, 123, 137-140
embedded chart events, trapping, 161-163
charts
built-in charts, specifying, 333-334
combo charts, creating, 359-363
creating
in Excel 2003 through Excel 2013, 340-341
in Excel 2007 through Excel 2013, 340-341
exporting as a graphic, 372-373
formatting
with SetElement method, 350-355
frequency charts, creating bins for, 365-368
location of, specifying, 336-337
OHLC charts, creating, 364-365
pivot charts, creating, 373-375
pivot tables, 231
AutoSort option, 246
blank cells, removing in values area, 246
calculated data fields, 267
calculated items, 268
compatibility in different Excel versions, 231-232
daily dates, grouping, 241-243
Data Model pivot table, building, 262-267
fields, adding to data area, 234-237
multiple value fields, 240-241
percentages, displaying, 243-245
pivot cache, defining, 232-233
reports, replicating for every product, 246-249
referring to, 337
sparklines
creating in a dashboard, 414-418
stacked area charts, creating, 368-372
class modules
application-level events, trapping, 160-161
collections, creating, 168-170
custom objects
creating, 163
Property Get procedures, 165-166
Property Let procedures, 165-166
embedded chart events, trapping, 161-163
Excel state class module, 290-292
inserting, 159
cleaning up recorded code, 56-60
clients, training on error handling, 526-527
closing add-ins, 560
code
adding to new workbooks, 302-303
breakpoints, 49
examining in recorded macros, 23-25
protecting, 529
recorded
speeding up with arrays, 153-155
Code group (Developer tab), 9
creating
ColName() function, 327
color of text, filtering, 201
color scales, adding to a range, 384-385
columns
copying, 223
referring to with R1C1 references, 105
remembering column numbers associated with column letters, 107-108
Columns property, referring to ranges, 72
combo charts, creating, 359-363
CommandButton control, 180-182
commands, extracting unique list of values with Advanced Filter, 206-207
comments
adding to names, 114
comparing
A1 versus R1C1 references, 99-100
DAO and ADO, 470
compatibility
32-bit and 64-bit compatible API declarations, 511-512
Excel versions
Excel8CompatibilityMode property, 588
file types supporting macros, 10-11
complex criteria, working with, 214-215
AddAboveAverage method, 392
AddTop10 method, 393
AddUniqueValues method, 393-395
NumberFormat property, 398
conditions
for If...Then...Else constructs, 90-95
list of values, replacing with, 214-221
configuring pivot tables, 233-234
constants
retrieving value of, 457
SetElement method, 352
ContainsText() function, 324-325
content management system, using Excel as, 434-437
controlling
illegal windows closing on userforms, 191-193
controls
ActiveX
right-click menu, creating, 299-300
programming, 180
renaming, 180
toggle buttons, 491
Controls group (Developer tab), 10
custom Excel 2003 toolbar to Excel 2013, 547
files to an add-in, 558
workbooks to an add-in, 556-558
copying
columns, 223
data to separate worksheets, 277-278
CreateObject function, 454
creating
bins for frequency charts, 365-368
charts
in Excel 2003 through Excel 2013, 340-341
in Excel 2007 through Excel 2013, 340-341
collections
custom objects, 163
global names, 112
local names, 113
reports from advanced filters, 226-229
user-defined functions, 305-307
web pages with VBA, 434
criteria ranges, joining
with logical AND, 214
with logical OR, 213
CSS (Cascasding Style Sheets), 571
CurrentRegion property, selecting ranges, 73-76
custom Excel 2003 toolbar, converting to Excel 2013, 547
custom functions
BookOpen(), 309
ColName(), 327
FirstNonZeroLength(), 318
LastSaved(), 312
MyFullName(), 308
MyName(), 308
NumUniqueValues(), 316
RetrieveNumbers(), 320
ReturnMaxs(), 326
ReverseContents(), 325
StringElement(), 321
SumColor(), 315
custom icons, applying to Ribbon, 545-546
custom objects
creating, 163
Property Get procedures, 165-166
Property Let procedures, 165-166
custom properties, creating with UDTs, 172-174
custom sort order, specifying, 293
Custom UI Editor, 543
customized data, transposing, 286-288
customizing the Ribbon
accessing the file structure, 537-542
Custom UI Editor, 543
D
daily dates, grouping in pivot tables, 241-243
DAO (data access objects) versus ADO, 470
dashboards
sparklines, creating in, 414-418
data bars
using two colors in a range, 390-392
data visualizations
applying to pivot tables, 270
color scales, adding to a range, 384-385
data bars
using two colors in a range, 390-392
icon sets
creating for a subset of a range, 388-390
declaring
32-bit and 64-bit compatible declarations, 511-512
multidimensional, 150
UDTs, 172
default settings, adjusting for macros, 11-14
defining
pivot cache for pivot tables, 232-233
deleting
blank cells in pivot table values area, 246
records from Access database, 478-479
deselecting noncontiguous cells, 288-290
Developer tab
accessing, 9
Add-Ins group, 10
Code group, 9
Controls group, 10
Modify group, 10
dialog boxes
Go To Special, replacing loops with, 204
Record Macro dialog, filling out, 14-15
directory files
Disable All Macros with Notification setting, 13-14
displaying
drill-down recordsets on Pivot Table, 292-293
percentages in pivot tables, 243-245
drill-down recordsets, displaying on pivot tables, 292-293
dynamic date range, selecting with AutoFilter, 202-203
E
early binding, referencing Word object, 451-453
embedded chart events, trapping, 161-163
empty cells, verifying, 73
enabling events, 125
encountering errors on purpose, 526
encountering errors on purpose, 526
Excel version compatibility, 530-531
ignoring errors, 524
page setup problems, case study, 524
protecting code, 529
Runtime Error 9: Subscript Out of Range, 527-528
Runtime Error 1004: Method Range of Object Global Failed, 528-529
warnings, suppressing, 526
error messages for custom Ribbon, troubleshooting, 548-551
events
application-level events, 140-148
embedded chart events, trapping, 161-163
enabling, 125
for userforms, 178
examining code in recorded macros, 23-25
Excel 2003
custom toolbar, converting to Excel 2013, 547
Excel 2007, creating charts, 340-341
Excel 2010, creating charts, 340-341
Excel 2013
charts
Data Model pivot table, building, 262-267
Excel state class module, 290-292
Excel8CompatibilityMode property, 588
Excel, using as content management system, 434-437
existence of names, checking for, 119
existing instance of Word, referencing with GetObject function, 455-456
exiting For...Next loops after condition is met, 83-84
exporting
extracting unique list of values
F
field drop-downs, turning off in AutoFilter, 229-230
fields
form fields, controlling in Word, 465-467
pivot table
adding to pivot table data area, 234-237
suppressing subtotals in multiple row fields, 269
file types supporting macros, 10-11
filenames, retrieving from user, 193-194
files
converting to an add-in, 558
filling out
Filter in Place, running, 221-222
filtering
Advanced Filter command, extracting unique list of values with, 206-207
advanced filters
reports, creating from, 226-229
AutoFilter, turning off field drop-downs, 229-230
by color, 201
data to separate worksheets, 277-278
by icon, 201
multiple items, selecting, 200
pivot tables
xlFilterCopy parameter, 222-225
FirstNonZeroLength() function, 318
flow control
If...Then...Else construct, conditions, 90-95
loops
for loops (JavaScript), 575
For statement, using variables in, 82
form controls, assigning macros, 18-19
form fields, controlling in Word, 465-467
Format method, formatting charts with, 355-359
formatting. See also conditional formatting
charts
with SetElement method, 350-355
forms
helping users fill out, 496-502
formula-based conditions, 216-221
formulas
A1, replacing with R1C1 formulas, 106-107
array formulas, entering, 108-109
rows and columns, referring to, 105
using with absolute references, 104-105
using with mixed references, 105
using with relative references, 104
exiting after condition is met, 83-84
nesting, 84
For statement, using variables in, 82
frequency charts, creating bins for, 365-368
functions
CreateObject, 454
custom functions
BookOpen(), 309
ColName(), 327
FirstNonZeroLength(), 318
LastSaved(), 312
MyFullName(), 308
MyName(), 308
NumUniqueValues(), 316
RetrieveNumbers(), 320
ReturnMaxs(), 326
ReverseContents(), 325
StringElement(), 321
SumColor(), 315
ISEMPTY(), 73
MsgBox(), 176
user-defined
G
GetAddress() function, 326-327
creating, 112
Go To Special dialog box, replacing loops with, 204
Graphic controls, adding to userforms, 187-188
graphics
SmartArt, 586
grouping daily dates in pivot tables, 241-243
H
hard drive, trusted locations
adding, 12
allowing macros outside of, 13
Hello World app
interactivity, adding, 568-570
help buttons, case study, 170-171
recorded macros, examining, 39-46
hidden workbooks as alternative to add-ins, 561-562
hiding
names, 119
hovering as means of querying, 52
HTML
CSS, 571
tags, 570
hyperlinks
running macros from, 554
I
icon sets
creating for a subset of a range, 388-390
icons
custom icons, applying to Ribbon, 545-546
filtering, 201
Microsoft Office icons, applying to Ribbon, 544-545
If...Then...Else construct, conditions, 90-95
ignoring errors, 524
illegal window closing, controlling on userforms, 191-193
Immediate window (VB Editor), 50-52
importing
protected password box, creating, 295-297
inserting class modules, 159
interactivity, adding to Hello World app, 568-570
interrupting macros, 125
Intersect method, creating new ranges from overlapping ranges, 72
IsEmailValid() function, 313-315
ISEMPTY() function, 73
Item property, 68
J
Jet Database Engine, 470
Jiang, Wei, 293
joining
criteria ranges
with logical AND, 214
with logical OR, 213
multiple ranges, 72
JS (JavaScript)
if statements, 576
for loops, 575
select...case statement, 576-577
strings, 574
K
Kapor, Mitch, 29
keyboard shortcut, running macros with, 551
Klann, Daniel, 295
L
LastSaved() function, 312
late binding, referencing Word object, 453-454
layout of pivot tables, changing from Design tab, 268
learning VBA, barriers to learning, 7-9
list of values, replacing with condition, 214-221
listing files in a directory, 271-273
comments, adding, 114
creating, 113
location of charts, specifying, 336-337
logical AND, joining multiple criteria ranges, 214
logical operators (JavaScript), 578-579
logical OR, joining multiple criteria ranges, 213
loops
exiting after condition is met, 83-84
nesting, 84
For statement, using variables in, 82
looping through all files in a directory, case study, 90-92
for loops (JavaScript), 575
replacing
with Go To Special dialog box, 204
lower bound of arrays, declaring, 150
M
macro button
adding to Quick Access toolbar, 17
adding to Ribbon, 16
macros
assigning
attaching to shapes, 552
default settings, adjusting, 11-14
Disable All Macros with Notification setting, 13-14
interrupting, 125
recorded
recording
running
with command button, 552
from hyperlinks, 554
with keyboard shortcut, 551
scheduled macros, canceling, 429
mathematical operators (JavaScript), 578-579
message boxes, 176
methods
AddAboveAverage, 392
AddTop10, 393
for data visualizations, 378-379
Format, formatting charts with, 355-359
Intersect, creating new ranges from overlapping ranges, 72
SetElement, formatting charts with, 350-355
SpecialCells
Union, joining multiple ranges, 72
Microsoft Office icons, applying to Ribbon, 544-545
military time, entering in cells, 136
mixed references, using with R1C1 references, 105
modeless userforms, 495
Modify group (Developer tab), 10
MsgBox() function, 176
MSubstitute() function, 318-319
multicolumn list boxes, case study, 505
multidimensional arrays, declaring, 150
multiple ranges, joining, 72
multiple value fields (pivot tables), 240-241
MyFullName() function, 308
MyName() function, 308
N
named ranges, 66
VLOOKUP() function, using with, 120-121
names
assigning
to numbers, 116
to tables, 117
checking for existence of, 119
comments, adding, 114
deleting, 113
creating, 112
hiding, 119
creating, 113
Napa Office 365 Development Tools, 582
nesting
For...Next loops, 84
New keyword, referencing Word application, 454
noncontiguous cells, selecting/deselecting, 288-290
noncontiguous ranges, returning with Areas collection, 76
NumberFormat property, 398
numbers, assigning names to, 116
NumFilesInCurDir() function, 310-311
NumUniqueValues() function, 316
O
object-oriented languages, 34
objects
custom objects
creating, 163
Property Get procedures, 165-166
Property Let procedures, 165-166
DAO versus ADO, 470
programming, changes in Excel 2013, 583-586
RANGE, 65
referring to, 65
Word object
referencing with early binding, 451-453
referencing with late binding, 453-454
WORKSHEET, 65
obstacles to recording macros, 21-31
Office apps, JavaScript incorporation in, 581-582
Offset property, referring to ranges, 69-70
OHLC (Open-High-Low-Close) charts, creating, 364-365
On Error GoTo syntax, error handling, 522-523
operators (JavaScript), 578-579
option buttons, adding to userforms, 186-187
overlapping ranges, creating new ranges from, 72
Ozgur, Suat Mehmet, 274
P
page setup problems, case study, 524
passing arrays, 156
passwords
cracking, 530
protected password box, creating, 295-297
Peltier, Jon, 372
percentages, displaying in pivot tables, 243-245
Pieterse, Jan Karel, 512
pivot cache, defining, 232-233
pivot charts, creating, 373-375
pivot tables, 231
AutoSort option, 246
blank cells, removing in values area, 246
calculated data fields, 267
calculated items, 268
compatibility in different Excel versions, 231-232
daily dates, grouping, 241-243
Data Model pivot table, building, 262-267
data visualizations, applying, 270
drill-down recordsets, displaying, 292-293
fields
multiple value fields, 240-241
layout, changing from Design tab, 268
percentages, displaying, 243-245
pivot cache, defining, 232-233
report layout, 269
reports, replicating for every product, 246-249
subtotals, suppressing for multiple row fields, 269
Pope, Andy, 372
procedural languages, BASIC, 33-34
procedures
Property Get procedures, 165-166
Property Let procedures, 165-166
programming
controls, 180
objects, changes in Excel 2013, 583-586
programming languages
object-oriented, 34
Project Explorer window (VB Editor), 20-21
Cells, selecting ranges, 68-69
Columns, referring to ranges, 72
CurrentRegion, selecting ranges, 73-76
custom properties, creating with UDTs, 172-174
for data visualizations, 378-379
Excel8CompatibilityMode, 588
Item, 68
NumberFormat, 398
Offset, referring to ranges, 69-70
Resize, 71
Rows, referring to ranges, 72
ShowDetail, 268
Properties window (VB Editor), 21
Property Get procedures, 165-166
Property Let procedures, 165-166
protected password box, creating, 295-297
protecting code, 529
publishing data to a web page, 432-438
Q
querying in VB Editor
by hovering, 52
with Watches window, 53
Quick Access toolbar, adding macro button, 17
Quick Analysis tool, 585
R
array formulas, entering, 108-109
columns and rows, referring to, 105
replacing A1 formulas with, 106-107
using with absolute references, 104-105
using with mixed references, 105
using with relative references, 104
RANGE object, 65
referring to, 65
ranges, 65
changing size of, 71
color scales, adding to, 384-385
creating from overlapping ranges, 72
dynamic date ranges, selecting with AutoFilter, 202-203
multiple, joining, 72
named ranges, 66
noncontiguous, returning with Areas collection, 76
referring to
in other sheets, 67
shortcuts, 66
relative to other ranges, specifying, 67-68
with CurrentRegion property, 73-76
specifying, 66
with Columns and Rows properties, 72
subsets of, creating icon sets for, 388-390
tables, referencing, 77
reading text files
Record Macro dialog, filling out, 14-15
recorded macros
recording macros
referencing
tables, 77
referring
to charts, 337
to ranges, 65
with Columns and Rows properties, 72
in other sheets, 67
relative to other ranges, 67-68
shortcuts, 66
reinitializing arrays, 155
relative references
using with R1C1 references, 104
removing
blank cells in pivot table values area, 246
names, 113
standard add-ins, 560
renaming controls, 180
replacing
A1 formulas with single R1C1 formula, 106-107
list of values with a condition, 214-221
loops
with Go To Special dialog box, 204
replicating pivot table reports for every product, 246-249
report layout (pivot tables), 269
reports, creating from advanced filters, 226-229
Resize property, 71
resizing
ranges, 71
RetrieveNumbers() function, 320
retrieving
constant values, 457
filenames from userforms, 193-194
records from Access database, 475-476
stock ticker averages, 301
unique combinations of two or more fields, 211
returning above-average records with formula-based conditions, 221
returning noncontiguous ranges with Areas collection, 76
ReturnMaxs() function, 326
ReverseContents() function, 325
Ribbon
custom icons, applying, 545-546
customizing
accessing the file structure, 537-542
Custom UI Editor, 543
macro button, adding, 16
Microsoft Office icons, applying, 544-545
right-click menu, creating for ActiveX object, 299-300
rows, referring to with R1C1 references, 105
Rows property, referring to ranges, 72
Ruiz, Juan Pablo, 290
running macros
with command button, 552
from hyperlinks, 554
with keyboard shortcut, 551
runtime, adding controls during, 496-502
Runtime Error 9: Subscript Out of Range, error handling, 527-528
Runtime Error 1004: Method Range of Object Global Failed, error handling, 528-529
S
saving new files as .xlsm file type, 11
scheduling
SDI (single document interface), 533, 583-584
Search box (AutoFilter), 200-201
security
Disable All Macros with Notification setting, 13-14
standard add-ins, 559
trusted locations
adding, 12
allowing macros outside of, 13
cells
with SpecialCells method, 74-76, 298-299
visible cells, 203
dynamic date range with AutoFilter, 202-203
multiple items with AutoFilter, 200
with CurrentRegion property, 73-76
separating worksheets into workbooks, 275-276
SetElement method
constants, 352
formatting charts with, 350-355
settings, VB Editor, 20
shapes
attaching macros to, 552
shared Access database, creating, 471
sharing user-defined functions, 307-308
sheet events for workbook events, 129-132
SheetExists() function, 309-310
shortcuts, referring to ranges, 66
ShowDetail property, 268
size of charts, specifying, 336-337
size of pivot tables, determining, 238-240
SmartArt, 586
SortConcat() function, 321-323
sparklines
SpecialCells method
specifying
custom sort order, 293
ranges, 66
with Columns and Rows properties, 72
in other sheets, 67
relative to other ranges, 67-68
speeding up code with arrays, 153-155
stacked area charts, creating, 368-372
standard add-ins
closing, 560
hidden workbooks as alternative to, 561-562
removing, 560
security, 559
standard modules, creating collections, 167-168
StaticRAND() function, 327-328
stock ticker averages, retrieving, 301
storing macros and forms with hidden code workbooks, 562
StringElement() function, 321
strings
for JavaScript, 574
subsets
of ranges, creating icon sets for, 388-390
SumColor() function, 315
summarizing records in Access database, 479-480
suppressing
pivot table subtotals for multiple row fields, 269
warnings, 526
switching to R1C1 references, 100-101
syntax, specifying ranges, 66
T
tables
checking for existence of in Access database, 480-481
names, assigning, 117
referencing, 77
tabs
tags (HTML), 570
text, changing case of, 297-298
text boxes, assigning macros to, 18-19
text files
reading one row at a time, 445-449
writing, 449
tips for cleaning up recorded code, 56-60
tips for using macro recorder, 31
title of charts, specifying, 342-343
toggle buttons, 491
toolbars
custom Excel 2003 toolbar, converting to Excel 2013, 547
UserForm, 485
training clients on error handling, 526-527
transposing customized data, 286-288
trapping
application-level events, 160-161
embedded chart events, 161-163
troubleshooting custom Ribbon error messages, 548-551
trusted locations
adding to hard drive, 12
allowing macros outside of, 13
turning off field drop-downs in AutoFilter, 229-230
U
UDTs (user-defined types)
custom properties, creating, 172-174
declaring, 172
Union method, joining multiple ranges, 72
unique list of values, extracting
with Advanced Filter command, 206-207
UniqueValues() function, 316-318
Until clause (Do loops), 87-88
upper bound of arrays, declaring, 150
Urtis, Tom, 281, 288, 292-294, 299
user-defined functions
UserForm toolbar, 485
userforms
programming, 180
toggle buttons, 491
events, 178
field entry, verifying, 191
filenames, retrieving, 193-194
helping users fill out, 496-502
illegal window closing, controlling, 191-193
message boxes, 176
modeless, 495
option buttons, adding, 186-187
V
variables
in For statements, 82
Variant-type, 151
Variant-type variables, 151
variations of For...Next loops, 82-83
VB Editor
code
breakpoints, 49
controls, programming, 180
Project Explorer window, 20-21
Properties window, 21
querying
by hovering, 52
with Watches window, 53
settings, 20
VBA (Visual Basic for Applications), 7
ignoring errors, 524
pivot tables, creating, 232-240
programming language components, 37
web pages, creating with, 434
web queries, building, 424-427
VBA Extensibility, adding code to new workbooks, 302-303
verbal reminders, scheduling, 430-431
verifying
empty cells, 73
userform field entry, 191
visible cells, selecting, 203
VLOOKUP() function, using named ranges for, 120-121
W
Wallentin, Dennis, 277
warnings, suppressing, 526
Watches window (VB Editor)
as means of querying, 53
web pages
creating with VBA, 434
hyperlinks, using in userforms, 495-496
web queries
capturing data periodically, 427-428
While clause (Do loops), 87-88
Windows API, 509
win/loss charts, formatting, 412-413
WinUserName() function, 311-312
Word
form fields, controlling, 465-467
Word object, referencing
workbooks
converting to an add-in, 556-558
creating from worksheets, 275-276
Disable All Macros with Notification setting, 13-14
working with complex criteria, 214-215
worksheet events, 123, 132-136
WORKSHEET object, 65
worksheets, separating into workbooks, 275-276
writing text files, 449
X-Y-Z
XcelFiles, 283
xlExpression version (conditional formatting), 396-397
xlFilterCopy parameter, 222-225
.xlsm file type, saving new files as, 11