Ewo

F1 key, 33-34 FacelD property, 489

fields calculated data fields,

260-264 checking existence of,

412-413 inserting, 413-414 multiple pivot table fields,

258-260 page fields, 276-279 PivotFields, filtering,

280-283 verifying, 170

File menu commands

Close & Delete, 295

Save As, 499

Save as Web Page, 341

File not loaded completely error message, 394 File Transfer Protocol (FTP), 347

FilelsOpen API function,

456-457 files closing and deleting,

295-297 CSV files, 297-299 exporting to Word, 302-303 file paths, 77, 462-465 fixed-width files, 388-391 Help file, 33-34 listing, 293-295 looping through, 115-116

MDB (Multidimensional

Database) files, 401 open files, checking for,

456-457 text files importing, 387-398 writing, 398-399 .xla files, 497

XML (Extensible Markup Language). See XML

filling arrays, 380-382

Filter In Place option, 225-226

filters

Advanced Filter, 207-208 case study, 230-233 copying all columns, 227 copying subset of columns, 228-229 criteria ranges, 215-218 Filter In Place option,

225-226 formula-based conditions, 219-224 no matching records, handling, 224 unique list of values, extracting, 208-215 xlFilterCopy, 226-227 AutoFilters, 233-234,

292-293 Filter In Place option,

225-226 Word, 373-376

Filter_NewSheet() function, 301-302

finding strings within text, 98

FindMinMax() function, 128

FindWindow API function,

458-459 first non-zero-length cell, finding, 91

FirstNonZeroLength function, 91 fixed-width files, 388-391 fly-out menus, 485-486 FollowHyperlinkO event, 152

For statement, 103-105 For...Each loops, 113-114

For...Next loops, 103-105

Exit For clause, 107 exiting early, 107 nesting, 107-108 Step clause, 106-107 variables, 106 Format Control dialog box, 492

Format3D() function, 199 FormatConditions object, 127

formatting conditional formatting array formulas, 129 AutoFilter with more than two conditions, 292-293 case study, 128-129 cell highlighting, 308 conditional formatting with more than three conditions, 291-292 setting up in user interface, 126-127 setting up in VBA, 127 pivot table reports, 253 Word ranges, 369-370

forms (UserForms), 20, 433

accelerator keys, 447 active controls, coloring,

448-450 adding controls to, 164-166 assigning macros to, 17-18 calling, 163 clickable links, 461 collections, 434-435 combining, 170-171 ComboBoxes, 166 control tip text, 448 creating, 162-163 field entries, verifying, 170 graphics, 168-169 hiding, 163 hyperlinks, 439 illegal window closing, 171 images, adding on the fly,

443-444 ListBoxes, 166-167 modeless UserForms, 438 multicolumn ListBoxes, 450 option buttons, 167-168 Picture Catalog example,

444-446 RefEdit control, 438 resizing, 163

runtime, adding controls at,

440-443 scrollbars, 446-447 sizing on the fly, 441-442 SpinButtons, 169-170 tab order, 448 Tabstrips, 436-438 toggle buttons, 436 transparent forms, 451-452 unloading, 163 user interaction methods,

161-162 UserForm toolbar, 433 X button, disabling, 459

Forms toolbar, 17 formula bar, hiding, 326

formula-based conditions above-average records, returning, 224 in Excel user interface, 220 setting up, 219 VBA code, 220-224 formulas array formulas, 129 formula-based conditions above-average records, returning, 224 in Excel user interface, 220

setting up, 219 VBA code, 220-224 names, 134 R1C1 references absolute references,

122-123 advantages of, 117-120 case study, 121 column numbers, remembering, 125-126 conditional formatting,

126-129 displaying, 118-119 entire columns/rows, referencing, 123 mixed references, 123 multiplication table example, 124-125 relative references, 121-122 FreeFile() function, 396

FTP (File Transfer

Protocol), 347 functions. See names of individual functions fund information, retrieving, 327-328

0 0

Post a comment