Numerics
3D models, 575
A
A1 addressing, 87, 89–91, 94–95
above/below average rules, 340, 355. See also conditional formatting
absolute references, R1C1 addressing and, 92–93
Access
creating a shared database, 449–450
database
deleting records via ADO, 458
retrieving records from, 453–455
summarizing records via ADO, 458–459
updating an existing record, 455–458
MDB (multidimensional database) files, 447–448
reading from, 451
tables
adding fields on the fly, 462–463
checking for the existence of, 460
checking for the existence of a field, 461
operations, 451
ActiveX controls
minimizing duplicate code, 147–148
running a macro with, 536
AddAboveAverage method, 355
.AddChart2 method, 313, 314–315, 318–319, 337–338
adding
color scales to a range, 346–347
add-ins, 539
Excel
closing, 545
converting workbooks to, 540–541
creating with VB Editor, 542–543
hidden workbook as an alternative, 545–547
removing, 545
Office, 549
adding interactivity, 554–557, 559–560
writing to the content or task pane, 569
AddTop10 method, 355
AddUniqueValues method, 356–358
ADOs (ActiveX Data Objects), 448
adding tables on the fly, 461–462
checking for the existence of a field, 461
checking for the existence of a table, 460
deleting records from an Access database, 458
lock type, 451
record set, 450
summarizing records via, 458–459
Advanced Filter, 183
building, 184
criteria ranges and, 191–192, 193
extracting a unique list of values, 185
changing the list range to a single column, 185
copying the customer heading before filtering, 185–186
Filter In Place, 201
showing all records, 202
formula-based conditions, 194–195
returning above-average records, 200
using in the Excel user interface, 195–196
joining multiple criteria
with a logical AND, 193
with a logical OR, 192
replacing a list of values with a condition created from a formula, 193–194
retrieving unique combinations of two or more fields, 190
xlFilterCopy, 202
combining multiple techniques, 205–209
copying a subset of columns and reordering, 203–205
alpha characters, sorting, 301–303
API (Application Programming Interface), 491. See also Spotify
declarations, 492
32-bit- and 64-bit-compatible, 493–494
private, 492
using, 493
functions
checking whether an Excel file is open on a network, 495–496
creating a running timer, 498–499
customizing the About dialog box, 497
disabling the X for closing a user form, 498
playing sounds, 499
retrieving display-resolution information, 496–497
retrieving the computer name, 495
getting credentials for accessing, 386–387
setting up a class module, 134
Archibald, R., 260
Areas collection, 66
formulas, 573
multidimensional, declaring, 126–127
names and, 104
speeding up code with, 129–130
assigning, macros
to an object, 13
AutoFilter
filtering
by color, 179
selecting a dynamic date range, 180–181
selecting multiple items, 178–179
selecting using the Search box, 179
turning off drop-down menus, 209–210
AutoSort method, 226
B
backward compatibility, .AddChart2 method, 337–338
Excel Tables: A Complete Guide for Creating, Using, and Automating Lists and Tables, 67
barriers to learning VBA, 2. See also learning VBA
binding. See early binding; late binding, 499
blocks, With…End With, 50
borders, chart, 331
backing up or moving forward, 40
querying
by hovering, 42
using the Immediate window, 41–42
setting with watches, 43
Bricklin, D., 87
building
advanced filters, 184
pivot tables, 245–246, 255–256
UDFs (user-defined functions), 286–287
buttons
command, running a macro with, 534–535
Help, 576
option
events, 166
using images on, 527
C
calculated items, 250
.Calculation property, 223
calculations, changing to show percentages, 223–225
calling, userforms, 156
canceling a scheduled macro, 408
Case statement, complex expressions in, 84
cells
creating a progress indicator, 274–275
based on their value, 358
that contain blanks or errors, 359
that contain dates, 359
notes, placing charts in, 265–267
returning the column letter of an address, 305–306
selecting visible only, 181–182
Cells property, referencing ranges with, 57–58
ChartFormat object, 327
charts, 313, 574. See also sparklines
borders, 331
creating
macro recorder and, 318
using .AddChart2 method, 314–315
exporting as a graphic, 337
filtering, 322
line settings, 331
map, creating, 335
placing in a cell note, 265–267
trendline, formatting, 331
class modules, 133
application events, trapping, 134–135
collections, creating, 142–144
custom objects
embedded chart events, trapping, 136–137
minimizing duplicate code for ActiveX labels, 147–148
clauses
closing, add-ins, 545
adding to new workbooks, 279–280
breakpoints, 40
comments, 18
duplicate, minimizing, 147–148
early binding, referencing a Word object, 427–430
Help topics, 37
late binding, referencing a Word object, 430
picture catalog userform, 481–483
pivot tables
building with a timeline, 243
building with two slicers, 238–240
creating a Data model, 247–249
creating a static summary from, 218–219
producing one report per product, 227–229
speeding up with arrays, 129–130
stepping through, 40–41. See also breakpoints
streamlining, 46
TypeScript, creating a pivot table, 255
VBA, 27
Areas, 66
dictionaries and, 145
color(s)
adding to fields in a userform, 485–486
applying
to data bars, 343, 344–345, 352–354
to objects, 329
filtering by, 179
RGB, applying to sparklines, 373–374
theme, applying to sparklines, 369–372
Columns property, 61
combining
combo charts, creating, 331–334
command buttons, 160
comments, 18
HTML, 557
names and, 100
XML, 519
comparing, VBA and TypeScript, 253–255
conditional formatting, 340–341
above/below average rules, 355
marking unique or duplicate cells, 356–358
NumberFormat property, 361–362
conditions, 81
formula-based
returning above-average records, 200
using in the Excel user interface, 195–196
configuration, pivot table, 213–214
constants
Help topics, 35
retrieving the real value of
using the Object Browser, 433–434
using the Watches window, 433
values, 433
ContainsText() function, 303–304
controls
adding
on the fly, 479
adding to a ribbon, 520
bug fix when adding to a form, 159
early binding, New keyword, 430–431
graphic, 167
ProgIds, 480
renaming, 159
tip text, adding to userforms, 484
ConvertWeekDay() function, 299–300
copy and paste, 49. See also xlFilterCopy
Copy method, 49
CopyFromRecordSet method, 453, 455
Count function, 221
COUNTIF function, 360
counting, unique functions, 296
CreateObject function, 431
CreatePivotTable method, 213–214
creating
cell progress indicator, 274–275
charts
map, 335
using .AddChart2 method, 314–315
macro button
on the Quick Access Toolbar, 11–12
new instances of objects, 431
pivot tables in VBA, 212
adding fields to the data area, 214–217
defining the pivot cache, 213
shared Access database, 449–450
UDFs (user-defined functions), 285
criteria
complex ranges, 193
logical AND, 193
logical OR, 192
replacing a list of values with a condition created from a formula, 193–194
CSS (Cascading Style Sheets), 558
CSV files
importing and deleting, 260
CurrentRegion property, selecting ranges with, 63
custom objects
custom properties, creating, 148–151
D
DAOs (data access objects), 448
dashboards
creating, 378
creating individual sparklines in, 379–383
placing query results on, 398–401
data bars, 339
using two colors in a range, 352–354
Data Model, 244
loading large files to, 423–424
tables
creating a relationship between, 245
data sets, converting fixed-width reports to, 280–283
data visualizations, 252–253, 339
above/below average rules, 340
color scales, 339
conditional formatting, 340–341
data bars, 339
highlight cells, 340
icon sets, 340
top/bottom rules, 340
date(s)
converting week number to, 299–300
grouping to months, quarters, or years, 221–223
of last save, retrieving, 292
debugging tools
backing up or moving forward, 40
declaring
UDTs (user-defined types), 149
Delete method, 100
deleting, names, 100
delimited files, opening, 417–419
Design tab, changing the layout, 250–251
Developer tab
Add-ins group, 4
Code group, 4
Controls group, 4
Disable All Macros with Notification setting, 8
Modify group, 4
XML group, 4
dialog boxes
Advanced Filter, 184
Visible Cells Only option, 181–182
Name Manager, 97
Record Macro, filling out, 9–10
Show Values As tab of the Value Field Settings, 223
displaying, Developer tab, 3–4
DLLs (Dynamic Link Libraries), 491
stopping, 77
While and Until clauses, 77–79
drilling-down a pivot table, 270–271
duplicate
values, 340
E
early binding
referencing a Word object, 427–430
email, validating an address, 293–295
embedded charts, events, 118–120, 136–137
enabling, events, 113
End+down arrow shortcut, 47–48
EndKey method, 437
Debug mode, 510
encountering errors on purpose, 509
generic, 506
message boxes and, 509
Power Query, 403
suppressing Excel warnings, 508
events, 112
setting up a class module, 134
Change, 267
chart, 118
enabling, 113
graphic control, 167
MultiPage, 171
option button, 166
QueryClose, 172
scrollbar, 473
sheet
spin button, 169
Excel. See also ribbon
add-ins
closing, 553
converting workbooks to, 550–551
creating with VB Editor, 551–552
hidden workbook as an alternative, 554–555
installing, 552
removing, 554
saving files as, 551
security, 553
Compatibility mode, 576
Developer tab
Add-ins group, 4
Code group, 4
Controls group, 4
Disable All Macros with Notification setting, 8
Modify group, 4
XML group, 4
Help button, 576
Point mode, 185
purchasing, 571
Quick Analysis tool, 573
Ready mode, 407
single-document interface, 572–573
updates, scheduling, 407
versions, 571
pivot table evolution, 211–212
.xlsm files, 525
Excel8CompatibilityMode property, 577–578
Execute method, 458
Exists method, 146
exporting
charts as a graphic, 337
expressions
Case statement and, 84
text, 83
extracting a unique list of values
with the user interface, 185–186
F
fields
adding to the pivot table data area, 214–217
checking for the existence of via ADOs, 461
form, controlling in Word, 443–444
protected password box, 275–277
file
operations, 257
exporting data to an XML file, 264–265
importing and deleting a CSV file, 260
listing files in a directory, 257–260
reading a text file into memory and parsing, 260–261
types
.xlsm, 5
Filter In Place, 201
showing all records, 202
filtering, 229
filters
chart, 322
ShowDetail property and, 250
FirstNonZeroLength() function, 296–297
fixed-width reports, converting to a data set, 280–283
flow control. See also loops
conditions, 81
If-Else If-End If construct, 82–83
If-Then-Else construct, 81
If-Then-Else-End If construct, 82
If-Then-End If construct, 82
For…Each loops, 79
using variables in the For statement, 72
FormatConditions collection, 340–341
formatting. See also conditional formatting
based on their value, 358
that contain blanks or errors, 359
that contain dates, 359
line settings, 331
sparklines, 369
forms, 160
controls. See also controls
bug fix when adding to a form, 159
command buttons, 160
graphic, 167
labels, 159
option buttons, 166
renaming, 159
text boxes, 160
fields, controlling in Word, 443–444
illegal window closing, 172
retrieving information from, 160–161
transparent, setting up, 487–488
verifying field entry, 171
formula-based conditions, 194–195
returning above-average records, 200
using in the Excel user interface, 195–196
formulas
array, 573
conditional formatting and, 359–361
controls, combo boxes, 164–165
naming, 101
R1C1 addressing, 49, 87, 90, 91
mixed references, 93
referring to entire columns or rows, 93
remembering column numbers associated with column letters, 95–96
replacing A1 formulas with, 94–95
toggling to, 88
Frankston, B., 87
functions
API
checking whether an Excel file is open on a network, 495–496
creating a running timer, 498–499
customizing the About dialog box, 497
disabling the X for closing a user form, 498
playing sounds, 499
retrieving display-resolution information, 496–497
retrieving the computer name, 495
building in Power Query, 390–393
Count, 221
COUNTIF, 360
CreateObject, 431
sharing, 308
NOW, 292
recursive, 290
SUM, 25
creating, 285
GetAddress(), 305
LastSaved(), 292
NumUniqueValues(), 296
sharing, 288
SumColor(), 295
VLOOKUP, 107
XLOOKUP, named ranges and, 107–109
G
GetAddress() function, 305
global
variables, 402
Go To Special dialog box, 64, 201
Visible Cells Only option, 181–182
Gonzàlez Ruiz, J. P., 268
gradients, applying to objects, 330
graphic controls, 167
H
code and, 37
constants, 35
properties and, 38
ToolTips and, 42
hiding
names, 106
HomeKey method, 437
HTML, 557
comments, 557
tags, 557
hyperlinks
returning the address, 305
running a macro from, 537
I
Restore Down, 39
sets, adding to a range, 347–348, 351–352
specifying an icon set, 348–349
specifying ranges for each icon, 350
Stop Recording, 10
If-Else If-End If construct, 82–83
If-Then-Else construct, 81
If-Then-Else-End If construct, 82
If-Then-End If construct, 82
images, adding to userforms on the fly, 480–481
Immediate window (VB Editor), 35, 41–42
importing
CSV files, 260
text files, 413
InsertLines method, 280
Intersect method, 62
IsEmailValid() function, 293–295
J
JavaScript
adding interactivity to Office add-ins, 559–560
comments, 561
custom functions, 549
For each...next statement, 565–566
if statements, 564
for loops, 563
Select Case construct, 564–565
strings, 562
variables, 561
Jet engine, 448
joining, ranges, 62
Jones, K., 263
Excel Tables: A Complete Guide for Creating, Using, and Automating Lists and Tables, 67
K
Kaji, M., 260
Kapor, M., 87
keyboard shortcuts. See shortcut(s)
keywords
Preserve, 131
Klann, D., 275
L
sharing, 308
Lanzo, L., 264
layout
report, 251
learning VBA, 3
barriers
macro recorder, 2
syntax, 2
line settings, trendline, 331
LineFormat object, 331
Load method, 156
loading large files to the Data Model, 423–424
logical AND, 193
logical OR, 192
loops, 69
stopping, 77
While and Until clauses, 77–79
For…Each, 79
using variables in the For statement, 72
Go To Special dialog box and, 182–183
JavaScript, 563
M, 402
replacing with AutoFilter, 175–178
While...Wend, 79
M
M, 385
global variables, 402
loops, 402
searching Spotify for an artist, 388–389
macro button, creating
on the Quick Access Toolbar, 11–12
macro recorder
charts and, 318
macros. See also code
assigning
to an object, 13
attaching
to an ActiveX control, 536
to a shape, 535
canceling, 408
copying to new workbooks, 279–280
passwords and, 515
running, 10
from a hyperlink, 537
using a keyboard shortcut, 533–534
searching Spotify database for an artist, 400
security, 6
adding a trusted location, 6–7
Disable All Macros with Notification setting, 8
enabling outside trusted locations, 7–8
map charts, creating, 335
matrix, 126
MDB (multidimensional database) files, 447–448
methods, 28
AddAboveAverage, 355
backward compatibility, 337–338
AddTop10, 355
AutoSort, 226
Copy, 49
Delete, 100
EndKey, 437
Execute, 458
Exists, 146
HomeKey, 437
InsertLines, 280
Intersect, 62
Load, 156
Modify, 343
OneColorGradient, 330
Open, 436
.Patterned, 330
PresetGradient, 330
PresetTextured, 329
PrintOut, 437
SaveAs2, 436
Select, 38
SelectAll, 474
.SetSourceData, 313
SparklineGroups.Add, 363
TwoColorGradient, 330
Union, 62
UnselectAll, 474
UserPicture, 329
mixed references, 93
Moala, I., 277
Modify method, 343
modules, 15
MSubstitute() function, 297–298
multicolumn list boxes, 486–487
multidimensional arrays, declaring, 126–127
MultiPage control, 169–171, 468
N
Name Manager dialog box, 97
names, 97
checking for the existence of, 106
comments, 100
deleting, 100
formula, 101
hiding, 106
numbers and, 103
types of, 101
using arrays in, 104
nesting
New keyword, referencing a Word application, 430–431
noncontiguous ranges, 66
NOW function, 292
NumberFormat property, 361–362
numbers
naming, 103
retrieving from mixed text, 298–299
NumFilesInCurDir() function, 290–291
NumUniqueValues() function, 296
O
object-oriented programming, 28
object(s), 28
assigning a macro to an, 13
properties, 31
objects, 49. See also class modules; DAOs (data access objects)
Chart, 318
ChartFormat, 327
color, applying, 329
creating new instances of, 431
custom
gradients, applying, 330
LineFormat, 331
patterns, applying, 330
properties, 38, 137. See also properties
Range, 54
texture, applying, 329
watches, 44
Word, 435
referencing via early binding, 427–430
referencing via late binding, 430
ObjectThemeColor property, 329
Office 365, 314
adding interactivity, 554–557, 559–560
writing to the content or task pane, 569
OFFSET property, 177
Offset property, referencing ranges with, 58–60
OLAP pivot table, filtering by a list of items, 271–273
Oliver, N., 257
OneColorGradient method, 330
Open method, 436
opening
files in a text editor, 550
text files
operators
option buttons
events, 166
Ozgur, S. M., 549
P
PageFields parameter, 221
PageFields, 221
passwords
macros and, 515
.Patterned method, 330
patterns, applying to objects, 330
picture, filling objects with a, 329
Pieterse, J. K., 491
pivot table(s), 211, 574. See also Data Model
adding model fields to, 246
advanced features, 220
AutoSort option, 226
calculated items, 250
changing the calculation to show percentages, 223–225
counting the number of records, 221
creating in VBA, 212
adding fields to the data area, 214–217
defining the pivot cache, 213
determining the size of, 217–220
evolution over various Excel versions, 211–212
filtering data sets, 229
filters
ShowDetail property and, 250
formatting the intersection of values in, 243–244
grouping daily dates to months, quarters, or years, 221–223
OLAP, filtering by a list of items, 271–273
producing a static summary, 218–220
reports, 217
layout, 251
multiple value fields, 220–221
replicating for every product, 226–229
suppressing subtotals for multiple row fields, 252
Values area
adding numeric fields, 246–247
eliminating blank cells, 226
Power Pivot, 244. See also pivot table(s)
Power Query. See also M
building a custom function, 390–393
loading large text files to the Data Model, 423–424
M and, 385
queries
ArtistsAlbums, 394–395, 396–397
duplicating to make a new query, 393–395
error handling, 403
fnGetToken, 392
placing results on your dashboard, 398–401
refreshing Spotify credentials after they expire, 390
searching Spotify database for an artist, 388–390
storing global variables in a Settings record, 402–403
Preserve keyword, 131
PresetGradient method, 330
PresetTextured method, 329
PrintOut method, 437
Priority property, 341
procedural programming, 28
VBA and, 29
programming languages. See also M
M, 385
object-oriented, 28
procedural, 28
Programming window, VB Editor, 17–19
progress indicator, cell, 274–275
.Calculation, 223
Cells, referencing ranges with, 57–58
Columns, 61
CurrentRegion, selecting ranges with, 63
Excel8CompatibilityMode, 577–578
Help topics, 38
ObjectThemeColor, 329
OFFSET, 177
Offset, referencing ranges with, 58–60
Priority, 341
Range(), 47
Rows, 61
ShowDetail, 250
Type, 341
Version, 577
Properties window, VB Editor, 15
protected password box, 275–277
Q
QueryClose event, 172
querying, in Break mode
by hovering, 42
using the Immediate window, 41–42
Quick Access Toolbar, creating a macro button, 11–12
Quick Analysis tool, 24–25, 573
R
R1C1 addressing, 49, 87, 90, 91
mixed references, 93
referring to entire columns or rows, 93
remembering column numbers associated with column letters, 95–96
replacing A1 formulas with, 94–95
toggling to, 88
Range() property, 47
ranges, 53
creating from overlapping ranges, 62
data bars
finding the first nonzero-length cell in, 296–297
specifying for subset of a range, 351–352
joining, 62
noncontiguous, 66
referencing
relative to another range, 56–57
shortcuts, 55
using Columns and Rows properties, 61
using the CurrentRegion property, 63
using the Offset property, 58–60
specifying for icons, 350
syntax, 54
tables, 67
worksheets and, 54
reading
from an Access database, 451
text files with more than 1,048, 576 rows, 420–423
records, counting, 221
recursive functions, 290
references
array, 104
mixed, 93
relationships, creating between tables, 245
relative references, 20–24, 91–92
removing, add-ins, 545
renaming, controls, 159
reports
fixed-width, converting to a data set, 280–283
pivot table, 217
layout, 251
multiple value fields, 220–221
replicating for every product, 226–229
suppressing subtotals for multiple row fields, 252
resetting, table format, 278–279
resizing
userforms, 479
Restore Down icon, 39
RetrieveNumbers() function, 298–299
retrieving data
date and time of last save, 292
permanent date and time, 292–293
returning
addresses of duplicate maximum values, 304–305
hyperlink address, 305
RGB color, applying to sparklines, 373–374
adding a control, 520
buttons, using images on, 527
using Microsoft Office icons, 527–528
Rows property, 61
rules
above/below average, 340
top/bottom, 340
running a macro, 10
with a command button, 534–535
using a keyboard shortcut, 533–534
S
SaveAs2 method, 436
scheduling macros, 407, 408–411
Search box, AutoFilter and, 179
security, 6
Disable All Macros with Notification setting, 8
trusted locations
enabling macros outside trusted locations, 7–8
using on a worksheets, 306
Select method, 38
SelectAll method, 474
selecting, with SpecialCells, 277–278
.SetSourceData method, 313
shared Access database, creating, 449–450
sharing
LAMBDA functions, 308
UDFs (user-defined functions), 288
sheet events
SheetExists() function, 289–290
shortcut(s)
Ctrl+T, 244
for referencing ranges, 55
Show Values As tab of the Value Field Settings dialog box, 223
ShowDetail property, 250
size of pivot tables, determining, 217–220
SLUGIFY.PLUS() function, 309–310
SmartArt, 575
Smith, C., 267
numeric and alpha characters, 301–303
SparklineGroups.Add method, 363–365
sparklines, 363
dashboards
creating, 378
creating individual sparklines in, 379–383
formatting, 369
win/loss charts, formatting, 377–378
SpecialCells method, 64–66, 201, 277–278
Spotify. See also Power Query
creating a developer account, 386–387
querying the list of songs on an album, 395
searching the database for an artist, 388–389
SQL Server, 463–464. See also Access
standard modules, 141–142. See also class modules
statements
Case, complex expressions in, 84
For, 72
Stop Recording icon, 10
streamlining code, 46
strings
JavaScript, 562
searching for within text, 303–304
substituting, multiple characters, 297–298
subtotals, suppressing for multiple row fields, 252
Sullivan, J., 271
SUM function, 25
SumColor() function, 295
T
tab stops, setting on userforms, 484–485
tables. See also Access
Access
adding fields on the fly, 462–463
checking for the existence of, 460
checking for the existence of a field, 461
adding to the Data Model, 244–245
creating a relationship between, 245
referencing, 67
tblTransfer, 449
text files
importing, 413
with more than 1,048, 576 rows, 419–420
using Power Query to load, 423–424
opening
reading into memory and parsing, 260–261
texture, applying to objects, 329
theme colors, applying to sparklines, 369–372. See also color(s)
time. See also dates
of last save, retrieving, 292
tip text, adding to userforms, 484
ToolTips, 42
top/bottom rules, 340, 355–356. See also conditional formatting
tracking, user changes, 267–268
transparent forms, setting up, 487–488
trapping
embedded chart events, 136–137
trendline, formatting, 331
Tufte, E., 363
TwoColorGradient method, 330
Type property, 341
TypeScript, 576
creating a pivot table, 255–256
U
UDFs (user-defined functions), 285
creating, 285
GetAddress(), 305
JavaScript, 549
LastSaved(), 292
NumUniqueValues(), 296
sharing, 288
SumColor(), 295
UDTs (user-defined types)
creating custom properties, 148–151
declaring, 149
Union method, joining multiple ranges, 62
UnselectAll method, 474
updates, scheduling, 407
userforms, 153. See also controls; forms
adding option buttons, 165–166
calling, 156
coloring the active field, 485–486
controls
adding on the fly, 479
ProgIds, 480
disabling the X for closing, 498
graphics, 167
illegal window closing, 172
images, adding on the fly, 480–481
programming, 157
resizing on the fly, 479
sizing on the fly, 479
tip text, adding, 484
verifying field entry, 171
UserPicture method, 329
utilities
converting a fixed-width report to a data set, 280–283
copying data to separate worksheets without using Filter, 263–264
creating a cell progress indicator, 274–275
creating a custom sort order, 273–274
creating an Excel state class module, 268–270
drilling-down a pivot table, 270–271
exporting data to an XML file, 264–265
filtering an OLAP pivot table by a list of items, 271–273
importing and deleting a CSV file, 260
listing files in a directory, 257–260
placing a chart in a cell note, 265–267
reading a text file into memory and parsing, 260–261
resetting a table’s format, 278–279
selecting with SpecialCells, 277–278
separating worksheets into workbooks, 261–262
tracking user changes, 267–268
using a protected password box, 275–277
using VBA Extensibility to add code to new workbooks, 279–280
V
validating, email addresses, 293–295
values
duplicate, minimizing, 340
unique, counting, 296
variables, 49
declaring, 14
global, 402
JavaScript, 561
For statement, 72
Variant, 128
Variant variables, 128
VB Editor, 13–14. See also Object Browser
Break mode, 38–43. See also breakpoints
converting a file to an add-in, 542–543
Properties window, 15
settings, 14
barriers to learning
macro recorder, 2
syntax, 2
charts
borders, 331
filtering, 322
line settings, 331
map, 335
trendline, 331
collections, 29
comparing to TypeScript, 253–255
using two colors in a range, 352–354
formula-based conditions, 196–200
creating for a subset of a range, 351–352
specifying ranges for each icon, 350
learning, 3
Office 365 and, 314
pivot tables
adding fields to the data area, 214–217
adding model fields to, 246
adding numeric fields to the Values area, 246–247
advanced features, 220
AutoSort option, 226
calculated items, 250
changing the calculation to show percentages, 223–225
creating, 212
creating a Data model, 247–249
defining the pivot cache, 213, 245–246
determining the size of, 217–220
eliminating blank cells in the Values area, 226
filtering, 229
formatting the intersection of values in, 243–244
grouping daily dates to months, quarters, or years, 221–223
replicating the report for every product, 226–229
reports, 251
ShowDetail property, 250
procedural programming and, 29
simplifying Power Query queries, 396
AlbumTracks, 397
SearchArtist, 396
syntax, 38
verifying, field entry, 171
Version property, 577
Visual Basic, BASIC and, 2
VLOOKUP function, 107
W
on objects, 44
setting a break point, 43
waterfall charts, creating, 336–337
Wei, J., 273
While.Wend loops, 79
Windows API, 491. See also API (Application Programming Interface)
win/loss charts, formatting, 377–378
WinUserName() function, 291–292
With…End With block, 50
Word
constants, retrieving the real value of, 433–434
using the Object Browser, 433–434
using the Watches window, 433
controlling form fields in, 443–444
documents
opening, 436
printing, 437
saving changes to, 436
templates, 436
objects, 435
referencing an existing instance of, 431–432
referencing objects via early binding, 427–430
referencing objects via late binding, 430
workbooks, 2
checking for existing sheets in, 289–290
converting to an add-in, 540–541
themes, 369
worksheets
copying data to without using Filter, 263–264
Select Case construct, 306
separating into workbooks, 261–262
X-Y-Z
xlFilterCopy, 202
combining multiple techniques, 205–209
copying a subset of columns and reordering, 203–205
xlFilterIcon operator, 179–181
XLOOKUP function, named ranges, 107–109
XML
adding a control to a ribbon, 520
creating a tab and a group, 519