Index

Numerics

3D models, 575

24-hour time, 117–118

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

adding records to a, 452–453

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

adding on the fly, 461–462

checking for the existence of, 460

checking for the existence of a field, 461

operations, 451

ACE engine, 447–448

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

data bars to a range, 342–346

icon sets to a range, 347–350

names, 98–100

add-ins, 539

Excel

characteristics of, 539–540

closing, 545

converting workbooks to, 540–541

creating with VB Editor, 542–543

hidden workbook as an alternative, 545–547

installing, 543–544

removing, 545

saving files as, 541–542

security, 544–545

Office, 549

adding interactivity, 554–557, 559–560

defining, 558–559

Hello World, 550, 551–554

JavaScript and, 569–570

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

connection, 450, 451

cursor, 450–451

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

catching no records, 201–202

showing all records, 202

formula-based conditions, 194–195

returning above-average records, 200

using in the Excel user interface, 195–196

using with VBA, 196–200

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

copying all columns, 202–203

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

applications, events, 120–124

setting up a class module, 134

trapping, 134–135

Archibald, R., 260

Areas collection, 66

arrays, 125, 145

declaring, 125–126

dynamic, 130–131

filling, 127–128

formulas, 573

JavaScript, 562–563

multidimensional, declaring, 126–127

names and, 104

passing, 131–132

retrieving data from, 128–129

speeding up code with, 129–130

assigning, macros

to a form control, 12–13

to an object, 13

AutoFilter

filtering

by color, 179

by icon, 179–180

replacing loops with, 175–178

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

Autosum button, 24–25, 26

B

backward compatibility, .AddChart2 method, 337–338

Barresse, Z., 263, 278

Excel Tables: A Complete Guide for Creating, Using, and Automating Lists and Tables, 67

barriers to learning VBA, 2. See also learning VBA

BASIC, 2, 28

binding. See early binding; late binding, 499

blocks, With…End With, 50

bookmarks, 441–442

BookOpen() function, 288–289

borders, chart, 331

breakpoints, 40, 41

backing up or moving forward, 40

querying

by hovering, 42

using a Watches window, 42–43

using the Immediate window, 41–42

setting with watches, 43

Bricklin, D., 87

building

advanced filters, 184

Data Model, 247–249

LAMBDA functions, 307–308

pivot tables, 245–246, 255–256

UDFs (user-defined functions), 286–287

buttons

Autosum, 24–25, 26

command, 160, 161–162

command, running a macro with, 534–535

creating, 557–558

Help, 576

option

adding to a userform, 165–166

events, 166

spin, 168–169

toggle, 471–472

using images on, 527

C

calculated fields, 249–250

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

cell pointer, 20–21, 23

cells

creating a progress indicator, 274–275

duplicate, marking, 356–358

empty, 62–63, 226

formatting, 243–244

based on their value, 358

that contain blanks or errors, 359

that contain dates, 359

that contain text, 358–359

using a formula, 359–361

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

applying a color, 320–321

borders, 331

combo, 331–334

creating

macro recorder and, 318

using .AddChart2 method, 314–315

embedded, events, 118–120

events, 118, 136–137

exporting as a graphic, 337

filtering, 322

formatting, 327–328

line settings, 331

map, creating, 335

placing in a cell note, 265–267

referring to, 318–319

SetElement method, 323–327

sheet events, 119–120

specifying a title, 319–320

styles, 315–318

trendline, formatting, 331

waterfall, creating, 336–337

win/loss, formatting, 377–378

check boxes, 165, 466–468

class modules, 133

application events, trapping, 134–135

clsCtlColor, 485–486

collections, creating, 142–144

custom objects

creating, 137–139

using, 139–140

embedded chart events, trapping, 136–137

Excel state, 268–270

inserting, 133–134

minimizing duplicate code for ActiveX labels, 147–148

clauses

Step, 72–73

Until, 77–79

While, 77–79

cleaning up code, 45–52

Close method, 436–437

closing, add-ins, 545

code. See also M; XML

adding to new workbooks, 279–280

breakpoints, 40

cleaning up, 45–52

comments, 18

duplicate, minimizing, 147–148

early binding, referencing a Word object, 427–430

examining, 17–19, 33–34

Help topics, 37

JavaScript, 560–561

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

generating, 216–217

producing one report per product, 227–229

protecting, 513–514

speeding up with arrays, 129–130

Step Into feature, 38–40

stepping through, 40–41. See also breakpoints

streamlining, 46

TypeScript, creating a pivot table, 255

VBA, 27

collection(s), 29, 140

Areas, 66

controls and, 473–475

creating, 140–141

in a class module, 142–144

in a standard module, 141–142

dictionaries and, 145

FormatConditions, 340–341

ColName() function, 305–306

color(s)

adding to fields in a userform, 485–486

applying

to a chart, 320–321

to data bars, 343, 344–345, 352–354

to objects, 329

filtering by, 179

RGB, applying to sparklines, 373–374

scales, 339, 346–347

theme, applying to sparklines, 369–372

Columns property, 61

combining

forms, 169–171

workbooks, 262–263

combo boxes, 162–163, 164–165

combo charts, creating, 331–334

command buttons, 160

events, 161–162

running a macro with, 534–535

comments, 18

HTML, 557

JavaScript, 556, 561

names and, 100

XML, 519

comparing, VBA and TypeScript, 253–255

concatenation, 300–301

conceptual filter, 230–231

conditional formatting, 340–341

above/below average rules, 355

data bars, 352–354

icons sets, 351–352

marking unique or duplicate cells, 356–358

NumberFormat property, 361–362

top/bottom rules, 355–356

using a formula, 359–361

conditions, 81

formula-based

returning above-average records, 200

using in the Excel user interface, 195–196

using with VBA, 196–200

formulas-based, 194–195

configuration, pivot table, 213–214

constants

Help topics, 35

icon sets, 348–349

retrieving the real value of

using the Object Browser, 433–434

using the Watches window, 433

SetElement method, 323–327

values, 433

xlColumnDataType, 416–417

ContainsText() function, 303–304

controls

accelerator keys, 483–484

adding

on the fly, 479

at runtime, 477–478

adding to a ribbon, 520

attributes, 521–525

bug fix when adding to a form, 159

check boxes, 466–468

collections and, 473–475

combo boxes, 162–163, 164–165

command buttons, 160, 161–162

early binding, New keyword, 430–431

frames, 474, 475

graphic, 167

labels, 159, 161–162

list boxes, 162–163

events, 164–165

multicolumn, 486–487

MultiSelect property, 163–164

MultiPage, 169–171, 468

option buttons, 165–166

ProgIds, 480

programming, 158–159

RefEdit, 470–471

renaming, 159

scrollbars, 472–473

spin buttons, 168–169

tab strips, 468–470

text boxes, 160, 161–162

tip text, adding to userforms, 484

toggle button, 471–472

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

combo, 331–334

map, 335

using .AddChart2 method, 314–315

waterfall, 336–337

collections, 140–141

in a class module, 142–144

in a standard module, 141–142

custom objects, 137–139

custom properties, 148–151

dashboards, 378, 379–383

Data Model, 247–249

macro button

on the Quick Access Toolbar, 11–12

on the ribbon, 10–11

new instances of objects, 431

pivot tables in VBA, 212

adding fields to the data area, 214–217

configuration, 213–214

defining the pivot cache, 213

shared Access database, 449–450

sparklines, 363–365, 379–383

UDFs (user-defined functions), 285

userforms, 155–156

criteria

complex ranges, 193

logical AND, 193

logical OR, 192

ranges, 191–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

opening, 417–419

CurrentRegion property, selecting ranges with, 63

custom objects

creating, 137–139

using, 139–140

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

adding to a range, 342–346

color, applying, 344–345

using two colors in a range, 352–354

Data Model, 244

creating, 247–249

loading large files to, 423–424

tables

adding, 244–245

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

dynamic filters and, 180–181

grouping to months, quarters, or years, 221–223

of last save, retrieving, 292

retrieving, 292–293

debugging tools

breakpoints, 40, 41

backing up or moving forward, 40

queries and, 41–43

Step Into feature, 38–41

declaring

arrays, 125–127

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

displaying, 3–4

macro settings, 7–8

Modify group, 4

Relative References, 21–24

XML group, 4

dialog boxes

Advanced Filter, 184

File Open, 173–174

Go To Special, 64, 201

loops and, 182–183

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

dictionaries, 145–146

DIM statement, 79–80

displaying, Developer tab, 3–4

DLLs (Dynamic Link Libraries), 491

Document object, 435–437

Do…loops, 75–77

stopping, 77

While and Until clauses, 77–79

drilling-down a pivot table, 270–271

duplicate

cells, marking, 356–358

code, minimizing, 147–148

values, 340

dynamic arrays, 130–131

dynamic filters, 180–181

E

early binding

New keyword, 430–431

referencing a Word object, 427–430

email, validating an address, 293–295

embedded charts, events, 118–120, 136–137

empty cells, 62–63, 226

enabling, events, 113

End+down arrow shortcut, 47–48

EndKey method, 437

error handling, 501–503

client training and, 509–510

custom ribbon and, 529–533

Debug mode, 510

encountering errors on purpose, 509

On Error GoTo syntax, 505–506

Excel versions and, 515–516

generic, 506

ignoring errors, 506–507

message boxes and, 509

misleading errors, 503–504

page setup, 507–508

Power Query, 403

protecting code and, 513–514

runtime error 9, 511–512

runtime error 1004, 512–513

suppressing Excel warnings, 508

events, 112

application, 120–124

setting up a class module, 134

trapping, 134–135

Change, 267

chart, 118

embedded, 118–120

trapping, 136–137

check box, 467–468

combo box, 164–165

command button, 161–162

enabling, 113

graphic control, 167

label, 161–162

levels of, 111–112

list box, 164–165

MultiPage, 171

option button, 166

parameters, 112–113

QueryClose, 172

RefEdit control, 470–471

scrollbar, 473

sheet

chart-level, 119–120

workbook-level, 115–116

spin button, 169

tab strip, 469–470

text box, 161–162

toggle button, 471–472

userform, 157–158

workbook, 113–115

worksheet, 116–117

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

displaying, 3–4

macro settings, 7–8

Modify group, 4

XML group, 4

error handling, 501–503

Help button, 576

Point mode, 185

purchasing, 571

Quick Analysis tool, 573

Ready mode, 407

RELS file, 525–526

single-document interface, 572–573

updates, scheduling, 407

versions, 571

error handling and, 515–516

pivot table evolution, 211–212

.xlsm files, 525

Excel8CompatibilityMode property, 577–578

Execute method, 458

Exists method, 146

Exit For statement, 73–74

exporting

charts as a graphic, 337

to an XML file, 264–265

expressions

Case statement and, 84

text, 83

watches and, 42–43

extracting a unique list of values

with the user interface, 185–186

with VBA code, 186–189

F

fields

active, coloring, 485–486

adding on the fly, 462–463

adding to the pivot table data area, 214–217

calculated, 249–250

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

macro-supported, 4–5

File Open dialog box, 173–174

Filter In Place, 201

catching no records, 201–202

showing all records, 202

filtering, 229

filters

chart, 322

conceptual, 230–231

manual, 229–230

OLAP pivot table, 271–273

search, 234–236

ShowDetail property and, 250

slicers, 237–241

Timelines, 241–243

types, 232–234

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

Select Case construct, 83–84

For…Each loops, 79

For…Next loops, 69–71

Step clause, 72–73

using variables in the For statement, 72

Format method, 327–328

Format tab, 327–328

Shape Fill drop down, 328–330

FormatConditions collection, 340–341

formatting. See also conditional formatting

cells, 243–244

based on their value, 358

that contain blanks or errors, 359

that contain dates, 359

that contain text, 358–359

using a formula, 359–361

charts, 327–328

conditional, 340–341

line settings, 331

resetting on tables, 278–279

sparklines, 369

elements, 374–377

using RGB colors, 373–374

using theme colors, 369–372

win/loss charts, 377–378

forms, 160

combining, 169–171

controls. See also controls

assigning a macro to a, 12–13

bug fix when adding to a form, 159

combo boxes, 162–163

command buttons, 160

graphic, 167

labels, 159

list boxes, 162–163, 164–165

MultiPage, 169–171

option buttons, 166

programming, 158–159

renaming, 159

spin buttons, 168–169

text boxes, 160

fields, controlling in Word, 443–444

getting a file name, 173–174

illegal window closing, 172

retrieving information from, 160–161

transparent, setting up, 487–488

verifying field entry, 171

Formula property, 352–354

formula-based conditions, 194–195

returning above-average records, 200

using in the Excel user interface, 195–196

using with VBA, 196–200

formulas

A1 addressing, 87, 89–91

array, 573

conditional formatting and, 359–361

controls, combo boxes, 164–165

naming, 101

R1C1 addressing, 49, 87, 90, 91

absolute references, 92–93

mixed references, 93

referring to entire columns or rows, 93

relative references, 91–92

remembering column numbers associated with column letters, 95–96

replacing A1 formulas with, 94–95

toggling to, 88

frames, 474, 475

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

GetObject, 431–432

InputBox, 153–154

IsEmpty, 62–63

JavaScript, 560–561

LAMBDA, 307, 573

building, 307–308

sharing, 308

SLUGIFY.PLUS(), 309–310

TOC2HTML(), 310–311

MsgBox, 154–155

NOW, 292

recursive, 290

RGB, 373–374

SUM, 25

user-defined, 285, 304–305

BookOpen(), 288–289

building, 286–287

ColName(), 305–306

ContainsText(), 303–304

ConvertWeekDay(), 299–300

creating, 285

DateTime(), 292–293

FirstNonZeroLength(), 296–297

GetAddress(), 305

IsEmailValid(), 293–295

LastSaved(), 292

MSubstitute(), 297–298

NumUniqueValues(), 296

RetrieveNumbers(), 298–299

sharing, 288

SheetExists(), 289–291

SortConcat(), 300–301

SumColor(), 295

WinUserName(), 291–292

VLOOKUP, 107

XLOOKUP, named ranges and, 107–109

G

GetAddress() function, 305

GetObject function, 431–432

global

names, 97–98

variables, 402

Go To Special dialog box, 64, 201

loops and, 182–183

Visible Cells Only option, 181–182

Gonzàlez Ruiz, J. P., 268

gradients, applying to objects, 330

graphic controls, 167

grouping, dates, 221–223

H

Help topics, 32–34

code and, 37

constants, 35

optional parameters, 34–35

properties and, 38

ToolTips and, 42

Hide method, 156–157

hiding

names, 106

userforms, 156–157

HomeKey method, 437

HTML, 557

comments, 557

tags, 557

hyperlinks

returning the address, 305

running a macro from, 537

userforms and, 476–477

I

icon(s), 340, 575

filtering by, 179–180

plus sign, 322–323

Restore Down, 39

sets, adding to a range, 347–348, 351–352

constants, 348–349

specifying an icon set, 348–349

specifying ranges for each icon, 350

Stop Recording, 10

using on the ribbon, 527–529

If statements, nesting, 84–86

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

input boxes, 153–154

InsertLines method, 280

installing, add-ins, 543–544

Intersect method, 62

IsEmailValid() function, 293–295

IsEmpty function, 62–63

J

JavaScript

adding interactivity to Office add-ins, 559–560

arrays, 562–563

comments, 561

custom functions, 549

For each...next statement, 565–566

functions, 560–561

if statements, 564

for loops, 563

math functions, 567–568

Office add-ins and, 569–570

operators, 566–567

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

New, 430–431

Preserve, 131

Klann, D., 275

L

labels, 159, 161–162

LAMBDA functions, 307, 573

building, 307–308

sharing, 308

SLUGIFY.PLUS(), 309–310

TOC2HTML(), 310–311

Lanzo, L., 264

late binding, 145, 430

layout

pivot table, 250–251

report, 251

learning VBA, 3

barriers

macro recorder, 2

syntax, 2

line settings, trendline, 331

LineFormat object, 331

list boxes, 162–163

events, 164–165

multicolumn, 486–487

MultiSelect property, 163–164

Load method, 156

loading large files to the Data Model, 423–424

local names, 97–98, 104

logical AND, 193

logical OR, 192

loops, 69

Do, 75–77

stopping, 77

While and Until clauses, 77–79

exiting early, 73–74

For…Each, 79

For…Next, 69–71

Step clause, 72–73

using variables in the For statement, 72

Go To Special dialog box and, 182–183

JavaScript, 563

M, 402

If-then logic, 403–404

List.Generate, 404–406

nesting, 74–75

replacing with AutoFilter, 175–178

While...Wend, 79

Lotus 1–3, 24, 87

M

M, 385

global variables, 402

loops, 402

If-then logic, 403–404

List.Generate, 404–406

searching Spotify for an artist, 388–389

macro button, creating

on the Quick Access Toolbar, 11–12

on the ribbon, 10–11

macro recorder

absolute references, 19–20

charts and, 318

relative references, 20–24

shortcomings of, 15–16

tips for using, 25–26

macros. See also code

assigning

to a form control, 12–13

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

recording, 8–9

running, 10

from a hyperlink, 537

using a keyboard shortcut, 533–534

scheduling, 407, 408–411

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

supported file types, 4–5

testing, 19, 22–23

manual filter, 229–230

map charts, creating, 335

matrix, 126

MDB (multidimensional database) files, 447–448

message boxes, 154–155

methods, 28

AddAboveAverage, 355

.AddChart2, 313, 318–319

backward compatibility, 337–338

creating charts with, 314–315

AddTop10, 355

AddUniqueValues, 356–358

AutoFilter, 176–177

AutoSort, 226

Close, 436–437

Copy, 49

CopyFromRecordSet, 453, 455

CreatePivotTable, 213–214

Delete, 100

EndKey, 437

Execute, 458

Exists, 146

Format, 327–328

Hide, 156–157

HomeKey, 437

InsertLines, 280

Intersect, 62

Load, 156

Modify, 343

OneColorGradient, 330

OnTime, 406–407

Open, 436

parameters, 29–31

.Patterned, 330

PresetGradient, 330

PresetTextured, 329

PrintOut, 437

SaveAs2, 436

Select, 38

SelectAll, 474

SetElement, 322–327

.SetSourceData, 313

SparklineGroups.Add, 363

SpecialCells, 64–66, 201

selecting with, 277–278

TwoColorGradient, 330

TypeText, 437–438

Union, 62

UnselectAll, 474

UserPicture, 329

Miles, T., 261, 262

mixed references, 93

Moala, I., 277

modeless userforms, 475–476

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

named ranges, 55, 107–109

names, 97

adding, 98–100

checking for the existence of, 106

comments, 100

deleting, 100

formula, 101

global, 97–98

hiding, 106

local, 97–98, 104

numbers and, 103

reserved, 104–105

string, 101–103

table, 103–104

types of, 101

using arrays in, 104

nesting

If statements, 84–86

loops, 74–75

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

sorting, 301–303

NumFilesInCurDir() function, 290–291

NumUniqueValues() function, 296

O

Object Browser, 45, 433–434

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

creating, 137–139

using, 139–140

gradients, applying, 330

LineFormat, 331

patterns, applying, 330

properties, 38, 137. See also properties

Range, 54

texture, applying, 329

variables, 79–81

watches, 44

Word, 435

Document, 435–437

Range, 438–441

referencing via early binding, 427–430

referencing via late binding, 430

Selection, 437–438

ObjectThemeColor property, 329

Office 365, 314

Office add-ins, 549, 550

adding interactivity, 554–557, 559–560

defining, 558–559

Hello World, 550, 551–554

JavaScript and, 569–570

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

OnTime method, 406–407

Open method, 436

opening

files in a text editor, 550

text files

delimited files, 417–419

fixed-width files, 413–417

operators

JavaScript, 566–567

xlFilterIcon, 179–181

option buttons

adding to a userform, 165–166

events, 166

Ozgur, S. M., 549

P

PageFields parameter, 221

parameters, 29–31

event, 112–113

PageFields, 221

parsing, text files, 260–261

passing an array, 131–132

password box, 275–277

passwords

cracking, 514–515

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

building, 245–246

calculated fields, 249–250

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

configuration, 213–214

defining the pivot cache, 213

data visualizations, 252–253

defining, 220–221

determining the size of, 217–220

drilling-down, 270–271

evolution over various Excel versions, 211–212

filtering data sets, 229

filters

conceptual, 230–231

manual, 229–230

search, 234–236

ShowDetail property and, 250

slicers, 237–241

types, 232–234

formatting the intersection of values in, 243–244

grouping daily dates to months, quarters, or years, 221–223

layout, 250–251

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

TypeScript and, 255–256

Values area

adding numeric fields, 246–247

eliminating blank cells, 226

Power Pivot, 244. See also pivot table(s)

Power Query. See also M

Advanced Editor, 387–388

building a custom function, 390–393

loading large text files to the Data Model, 423–424

M and, 385

queries

AlbumTracks, 395, 397

ArtistsAlbums, 394–395, 396–397

duplicating to make a new query, 393–395

error handling, 403

fnGetToken, 392

grouping, 397–398

placing results on your dashboard, 398–401

SearchArtist, 391, 393, 396

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

BASIC, 2, 28

M, 385

object-oriented, 28

procedural, 28

Programming window, VB Editor, 17–19

progress indicator, cell, 274–275

Project Explorer, 14–15

properties, 31, 137

.Calculation, 223

Cells, referencing ranges with, 57–58

ChartColor, 320–321

Columns, 61

CurrentRegion, selecting ranges with, 63

custom, creating, 148–151

Excel8CompatibilityMode, 577–578

Formula, 352–354

Help topics, 38

MultiSelect, 163–164

NumberFormat, 361–362

ObjectThemeColor, 329

OFFSET, 177

Offset, referencing ranges with, 58–60

Priority, 341

Range(), 47

Resize, 60–61

Rows, 61

ShowDetail, 250

TableRange2, 217–218

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 a Watches window, 42–43

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

absolute references, 92–93

mixed references, 93

referring to entire columns or rows, 93

relative references, 91–92

remembering column numbers associated with column letters, 95–96

replacing A1 formulas with, 94–95

toggling to, 88

Range object, 54, 438–441

Range() property, 47

ranges, 53

color scales, adding, 346–347

creating from overlapping ranges, 62

data bars

adding, 342–346

using two colors, 352–354

finding the first nonzero-length cell in, 296–297

icon sets, 347–348

specifying, 348–349

specifying for subset of a range, 351–352

VBA constants, 348–349

joining, 62

named, 55, 107–109

noncontiguous, 66

referencing

in other worksheets, 55–56

relative to another range, 56–57

shortcuts, 55

using Cells property, 57–58

using Columns and Rows properties, 61

using the CurrentRegion property, 63

using the Offset property, 58–60

resizing, 60–61

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

Record Macro dialog box, 9–10

recording a macro, 8–9, 17

absolute references, 19–20

Autosum and, 24–25

preparations, 16–17

relative references, 20–24

tips, 25–26

records, counting, 221

recursive functions, 290

RefEdit control, 470–471

references

absolute, 19–20, 92–93

array, 104

mixed, 93

relative, 20–24, 91–92

relationships, creating between tables, 245

relative references, 20–24, 91–92

RELS file, 525–526

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

reserved names, 104–105

resetting, table format, 278–279

Resize property, 60–61

resizing

ranges, 60–61

userforms, 479

Restore Down icon, 39

RetrieveNumbers() function, 298–299

retrieving data

from arrays, 128–129

date and time of last save, 292

file names, 173–174

from forms, 160–161

from mixed text, 298–299

permanent date and time, 292–293

user ID, 291–292

returning

addresses of duplicate maximum values, 304–305

hyperlink address, 305

ReturnMax() function, 304–305

RGB color, applying to sparklines, 373–374

ribbon, 517, 572

adding a control, 520

adding custom icons, 528–529

buttons, using images on, 527

control attributes, 521–525

error handling, 529–533

macro button, creating, 10–11

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

scaling, sparklines, 366–369

scheduling macros, 407, 408–411

ScrollBar control, 472–473

Search box, AutoFilter and, 179

search filter, 234–236

security, 6

add-in, 544–545

Disable All Macros with Notification setting, 8

trusted locations

adding, 6–7

enabling macros outside trusted locations, 7–8

Select Case construct, 83–84

in JavaScript, 564–565

using on a worksheets, 306

Select method, 38

SelectAll method, 474

selecting, with SpecialCells, 277–278

Selection object, 437–438

SetElement method, 322–327

.SetSourceData method, 313

Shape Fill drop down, 329–330

shared Access database, creating, 449–450

sharing

LAMBDA functions, 308

UDFs (user-defined functions), 288

sheet events

chart, 119–120

workbook-level, 115–116

SheetExists() function, 289–290

shortcut(s)

accelerator keys, 483–484

Ctrl+T, 244

End+down arrow, 47–48

for referencing ranges, 55

running a macro with, 533–534

Show Values As tab of the Value Field Settings dialog box, 223

ShowDetail property, 250

size of pivot tables, determining, 217–220

slicers, 237–243, 574

SLUGIFY.PLUS() function, 309–310

SmartArt, 575

Smith, C., 267

sorting, 300–301

custom, 273–274

numeric and alpha characters, 301–303

SparklineGroups.Add method, 363–365

sparklines, 363

creating, 363–365

dashboards

creating, 378

creating individual sparklines in, 379–383

formatting, 369

elements, 374–377

using RGB colors, 373–374

using theme colors, 369–372

scaling, 366–369

win/loss charts, formatting, 377–378

SpecialCells method, 64–66, 201, 277–278

spin buttons, 168–169

Spotify. See also Power Query

creating a developer account, 386–387

querying the list of songs on an album, 395

Search API, 388–390

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

DIM, 79–80

Exit For, 73–74

For, 72

If, nesting, 84–86

Select Case, 83–84

Step Into feature, 38–40

Stop Recording icon, 10

streamlining code, 46

strings

JavaScript, 562

naming, 101–103

searching for within text, 303–304

styles, chart, 315–318

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

tab strips, 468–470

tables. See also Access

Access

adding fields on the fly, 462–463

adding on the fly, 461–462

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

naming, 103–104

referencing, 67

resetting the format, 278–279

tblTransfer, 449

testing, macros, 19, 22–23

text boxes, 160, 161–162

text files

importing, 413

with more than 1,048, 576 rows, 419–420

reading, 420–423

using Power Query to load, 423–424

opening

delimited files, 417–419

fixed-width files, 413–417

reading into memory and parsing, 260–261

writing, 424–425

Text Import Wizard, 35–37

texture, applying to objects, 329

theme colors, applying to sparklines, 369–372. See also color(s)

time. See also dates

24-hour, 117–118

of last save, retrieving, 292

retrieving, 292–293

Timelines, 241–243

tip text, adding to userforms, 484

TOC2HTML() function, 310–311

ToggleButton control, 471–472

toolbars, UserForm, 465–466

ToolTips, 42

top/bottom rules, 340, 355–356. See also conditional formatting

tracking, user changes, 267–268

transparent forms, setting up, 487–488

trapping

application events, 134–135

embedded chart events, 136–137

trendline, formatting, 331

trusted locations, 6–7

Tufte, E., 363

TwoColorGradient method, 330

Type property, 341

TypeScript, 576

comparing to VBA, 253–255

creating a pivot table, 255–256

TypeText method, 437–438

U

UDFs (user-defined functions), 285

BookOpen(), 288–290

building, 286–287

ColName(), 305–306

ContainsText(), 303–304

ConvertWeekDay(), 299–300

creating, 285

DateTime(), 292–293

FirstNonZeroLength(), 296–297

GetAddress(), 305

IsEmailValid(), 293–295

JavaScript, 549

LastSaved(), 292

MSubstitute(), 297–298

NumFilesInCurDir(), 290–291

NumUniqueValues(), 296

RetrieveNumbers(), 298–299

ReturnMax(), 304–305

sharing, 288

SortConcat(), 300–301

SumColor(), 295

WinUserName(), 291–292

UDTs (user-defined types)

creating custom properties, 148–151

declaring, 149

Union method, joining multiple ranges, 62

UnselectAll method, 474

Until clause, 77–79

updates, scheduling, 407

Urtis, T., 265, 270, 274

user ID, retrieving, 291–292

UserForm toolbar, 465–466

userforms, 153. See also controls; forms

accelerator keys, 483–484

adding option buttons, 165–166

calling, 156

check boxes, 466–468

coloring the active field, 485–486

controls

adding at runtime, 477–478

adding on the fly, 479

collections and, 473–475

ProgIds, 480

creating, 155–156

disabling the X for closing, 498

events, 157–158

graphics, 167

hiding, 156–157

hyperlinks, 476–477

illegal window closing, 172

images, adding on the fly, 480–481

input boxes, 153–154

message boxes, 154–155

modeless, 475–476

picture catalog, 481–483

programming, 157

RefEdit control, 470–471

resizing on the fly, 479

scrollbars, 472–473

setting tab stops, 484–485

sizing on the fly, 479

tab strips, 468–470

tip text, adding, 484

transparent, 487–488

verifying field entry, 171

UserPicture method, 329

utilities

combining workbooks, 262–263

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

object, 79–81

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

Help and, 33–34

Immediate window, 35, 41–42

Programming window, 17–19

Project Explorer, 14–15

Properties window, 15

settings, 14

VBA, 31–32

barriers to learning

macro recorder, 2

syntax, 2

charts

applying a color, 320–321

borders, 331

combo, 331–334

filtering, 322

formatting, 327–328

line settings, 331

map, 335

referring to, 318–319

SetElement method, 322–327

specifying a title, 319–320

styles, 315–318

trendline, 331

waterfall, 336–337

collections, 29

comparing to TypeScript, 253–255

constants, icon set, 348–349

data bars, 339, 352–354

adding to a range, 342–346

color, applying, 344–345

using two colors in a range, 352–354

data visualizations, 340–341

error handling, 501–503

Extensibility, 279–280

formula-based conditions, 196–200

icon sets, 347–348

creating for a subset of a range, 351–352

specifying ranges for each icon, 350

learning, 3

methods, 28, 29–31

objects, 28, 31

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 fields, 249–250

calculated items, 250

changing the calculation to show percentages, 223–225

conceptual filter, 230–231

configuration, 213–214

creating, 212

creating a Data model, 247–249

data visualizations, 252–253

defining, 220–221

defining the pivot cache, 213, 245–246

determining the size of, 217–220

eliminating blank cells in the Values area, 226

filter types, 232–234

filtering, 229

formatting the intersection of values in, 243–244

grouping daily dates to months, quarters, or years, 221–223

layout, 250–251

manual filter, 229–230

replicating the report for every product, 226–229

reports, 251

search filter, 234–236

ShowDetail property, 250

slicers, 237–241

Timelines, 241–243

procedural programming and, 29

simplifying Power Query queries, 396

AlbumTracks, 397

ArtistsAlbums, 396–397

SearchArtist, 396

syntax, 38

verifying, field entry, 171

Version property, 577

Visual Basic, BASIC and, 2

VLOOKUP function, 107

W

watches, 42–43

on objects, 44

setting a break point, 43

waterfall charts, creating, 336–337

Wei, J., 273

While clause, 77–79

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

bookmarks, 441–442

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

closing, 436–437

creating, 435–436

opening, 436

printing, 437

saving changes to, 436

templates, 436

objects, 435

Document, 435–437

Range, 438–441

Selection, 437–438

referencing an existing instance of, 431–432

referencing objects via early binding, 427–430

referencing objects via late binding, 430

workbooks, 2

adding code to, 279–280

checking for existing sheets in, 289–290

code, 546–547

combining, 262–263

converting to an add-in, 540–541

events, 113–115

open, checking for, 288–289

ribbon, 517–518

sheet events, 115–116

themes, 369

worksheets

copying data to without using Filter, 263–264

events, 116–117

ranges and, 54, 55–56

Select Case construct, 306

separating into workbooks, 261–262

writing, text files, 424–425

X-Y-Z

xlFilterCopy, 202

combining multiple techniques, 205–209

copying a subset of columns and reordering, 203–205

copying all columns, 202–203

xlFilterIcon operator, 179–181

XLOOKUP function, named ranges, 107–109

.xlsm file type, 5, 525

XML

adding a control to a ribbon, 520

creating a tab and a group, 519

customui folder, 518–519

defining an Office add-in, 558–559

exporting data to a file, 264–265