| < Day Day Up > |
|
C drive, searching through subdirectories in, 289
Calculate event, 275
Calculate method
of Application object, 119, 156
calling, 27
of Worksheet object, 156
CalculateBeforeSave property, 157
CalculatedFields collection, 350, 354
CalculatedFields method, 350
CalculateFull method, 120
Calculate_Table macro, 170
Calculate_Table routine, 167-69
calculations, limiting, 120
Calculator application, starting, 446
Call keyword, running a Sub procedure, 94
call stack, 48, 266
Call Stack dialog box, 45
Cancel argument
of BeforeSave event procedure, 271
of Exit event, 403
Cancel button
adding to a user form, 427
adding to a wizard application, 435
disabling, 424
CancelUpdate method, 491, 494
Caps Lock key, 126
Caption property
of CheckBox control, 406
of CommandBarButton object, 376
of CommandBarControl object, 373
of CommandButton control, 404
of Frame control, 408
of Label control, 404, 421
of PivotField object, 354
of PivotItem object, 356
of TabStrip control, 416
updating for a user form, 437
of user form control, 402, 403
of UserForm object, 398
of Window object, 113
case sensitivity of FIND function, 203
Case statements, 89-90
Categories pane of Customize dialog box, 33
category area in a PivotChart, 346
CBool() conversion function, 61
CByte() conversion function, 61
CCur() conversion function, 61
CD. See Companion CD for this book
CDate() conversion function, 61
CDbl() conversion function, 61
CDec() conversion function, 61
cell edit mode, 249
Cell object, 107
Cell Protection dialog box, 388
CellBorder procedure, 109
cells, 14, 15, 161. See also active cell; blank cells; empty cells
allowing users to format, 158
allowing users to leave blank, 188
changing comments, 275
copying to an array first, 186
counting the number in a range, 206
default size for, 172
detecting empty, 176-77
determining if text, 193
determining if within a specific range, 125
displaying values in bold type, 161
formatting, 16, 233-35
limiting user selection of, 159
manipulating groups of, 172-77
mapping schema elements to, 557-62
measuring the dimensions of, 174
number of digits visible in, 175
protecting data, 103
range of, 521, 522
reading or writing to multiple, 185
recalculating, 119
recalculating formulas for, 120
referencing, 167-69, 172
researching the contents of, 8
resizing programmatically, 172
sections of, 233
selecting a range of, 162-64
selecting discontiguous groups of, 17
selecting for a formula, 16
specifying the character separating, 131
xlDialog boxes used to format, 391
Cells method, 172, 179, 422
Cells property, 172
Center in Form item on the Format menu, 46
CenterHorizontally property, 154
CenterVertically property, 154
Change event, 265
of Application object, 266
of CheckBox control, 406
of MultiPage control, 437
quirks associated with, 275
of RowNumber control, 425
of ScrollBar control, 411
of SpinButton control, 407, 408
of TextBox control, 405
of user form control, 402, 403, 404
for worksheet, 274, 275
of Worksheet object, 266
ChangeChartType procedure, 338
ChangePattern procedure, 235
changing cells, maximum number, 15
character string, 214
characters, 197, 199, 200-202
Characters object, 326
Characters property, 233
Chart Area. See Plot Area object
Chart events, 265, 329
chart functionality, 526
chart handles, 323
Chart object, 321
allowing access to all attributes, 327
creating, 358
exposing events, 327
properties of, 336
triggers available for, 329
Chart Object Model, 321, 326
Chart property, 337
chart sheets, 13, 149, 322
activating charts on, 328
printing, 339
programmatic reference to a chart on, 339
at the same level as worksheets, 326
chart title, defining as a formula, 325
Chart Titles object, 322
Chart Web Components, 534
Chart wizard, 322
ChartIsSelected function, 331
ChartObject object, 321, 327
contained in a Worksheet object, 326
getting a reference to, 340
naming, 323
properties of, 337
ChartObjects collection, 321, 327, 338
charts, 321. See also embedded charts; PivotCharts
activating, 327-29
adding series to, 340
building three-dimensional columns, 111
changing the type of all, 338
converting to use arrays, 334
creating, 118, 322
creating macros to modify existing, 327
deactivating, 330
defining the labels of, 335
deleting series from, 340
determining if activated, 331
displaying information about, 139
distributing independently of the source data, 333
formatting, 336-38
hierarchy of, 326
locations for, 322
manipulating, 111, 327-39
modifying all in a workbook, 338
modifying data series of, 331
modifying formats in, 328
modifying to use date from arrays, 333-35
printing, 339
programming, 339
renaming existing, 323
saving to a Web page, 522
selecting to save in a Web page, 521
types of, 321
xlDialog boxes used to modify, 391
Charts collection, 326
ChartTitle object, 337
ChartTitle property, 325
ChartType property, 325, 336
CheckBox control, 406
CHIINV function, 4
child controls, 408
child objects, 456
Chrt object variable, 324
CInt() conversion function, 62
class (.cls) file, 45
class modules, 42, 299, 300
creating, 305
inserting into current projects, 45
Class Modules folder, 42
classes, 299
adding business rules, 317-18
building, 305-11, 312-18
calling external code, 304
changing the name of, 305
creating simple, 312
defining events for, 310
designing, 312-18
determining if new instances created, 302
extending, 313
marking as Public or Private, 303
modifying, 304
special events for, 311
testing independently, 304
Class_Initialize event, 311
class-level variables
defining, 305
differentiating from local variables or parameters, 311
initializing, 311
marking as Public, 304
prefixing with Me, 311
Class_Terminate event, 311
CLEAN function, 195
Clear All Breakpoints item on the Debug menu, 47
Clear Formats command, 275
Clear key, 126
Clear method
of Collection object, 315
of CommandBarComboBox object, 378
of Errors collection, 486
of FileDialogFilters collection, 293
of ListBox control, 413
of XPath object, 558
ClearContents method, 515
ClearData routine, 423
Click event
of CheckBox control, 406
of ListBox control, 413
of user form control, 402, 404
client. See database client
client/server concept, 470
Clip Art task pane, 20
Clipboard task pane, 20
CLng function, 62, 423
Close button, 19
Close method
of Connection object, 484
of Recordset object, 491
of ThisWorkbook, 138
Close window button, 53
.cls (class) file, importing, 45
CLSID property, 245
cmdShowProductImage_Click procedure, 293
code
adding to a project, 53
associated with properties, 304
branching to another section of, 82
compiling for the currently selected project, 47
digitally signing, 48
executing remaining, 47
isolating inside a class, 304
jumping to the next line of, 47
making readable, 54
placing in Break mode, 48
running up to the cursor, 47
setting the next line of, 47
stepping through one line at a time, 47
code formatting options, 50
Code item on the View menu, 44
code modules
adding a procedure to, 86
creating a Function procedure in, 100
declarations section of, 92
naming, 95
Code Settings options, 50
Code window
displaying for the currently selected item, 44
in Visual Basic Editor, 43
code windows. See also windows
customizing, 51
handling, 53
managing, 53-55
minimizing or resizing, 53
navigating, 53
switching among open, 53
Collapse Proj. Hides Windows option, 52
Collate parameter, 148
Collection class, 313-16
Collection object, 315
Collection object variable, 313
collections, 28, 108
finding the number of objects in, 28
of objects, 79
removing items from, 315
returning a single item from, 315
colon (:) in a line label, 82
Color argument of BorderAround method, 237
Color Model list box, 224
color palette. See Excel color palette
Color property
of Borders object, 236
compared to PatternColor, 235
of Font object, 232
of Interior object, 234
of Worksheet object, 230
color systems, 220
Color tab in Options dialog box, 224, 226
color wheel, 220
ColorIndex argument of BorderAround method, 237
ColorIndex property
of Borders object, 236
compared to PatternColorIndex, 235
of Font object, 232
of Interior object, 234
of Worksheet object, 230
colors
creating on a computer, 220
displaying, 223
in graphics, 225
represented by VBA constants, 88
in worksheets, 219-31
Colors property
of Workbook object, 223
of Workbooks collection, 225
column fields for a PivotTable, 358
column format for a text file, 133
column names
listing, 478
resolving, 477
Column property, 413
ColumnCount property, 412
ColumnFields property, 350, 354
ColumnGrand property, 350
ColumnHeads property, 412
columns
allowing users to delete, 158
allowing users to format, 158
allowing users to insert into, 158
changing in tables, 479
in a database table, 468
maximum number of, 14
maximum width of, 14
referencing, 172
retrieving, 472
Columns property of Select, 172
ColumnWidths property of ListBox control, 412
COM add-ins, 239
building, 256
creating, 248-50
IDTExtensibility2 interface and, 248
providing entries in Windows registry, 250
typical uses of, 247
COM Add-Ins button, 248
COM Add-ins dialog box, 247
COM tab in Add Reference dialog box, 259
combo box controls, 378-79, 430
combo boxes, 379
ComboBox control, 414, 505
comma (,), as a separator, 132
Comma parameter, 133
comma-delimited data, 132
command bar button, 433
command bar controls, 371, 372-73
command bars
adding floating, 370
deleting, 371
displaying, 371
in Excel window, 19
positioning, 368
types of, 368
command button controls, 435
command buttons, triggering, 404
Command object
in ADO object model, 482, 487-90
in RunQuery routine, 513
CommandBar objects, 367-68, 369
CommandBar property, 380
CommandBarButton object, 375-76
CommandBarComboBox object, 378-79
CommandBarControl objects, 373-75
CommandBarControls collection, 371-73
CommandBarPopup object, 380
CommandBars collection, 366-67
CommandButton controls, 401, 404, 421, 425-27
CommandButton2_Click event, 425
CommandButton7_Click event, 428
CommandLineSafe registry entry, 250
Commands tab in Customize dialog box, 32, 33, 146
CommandText property
of Command object, 488
of PivotCache object, 352
CommandType property
of Command object, 488, 514
of PivotCache object, 352
comments in VBA code, 328
communication across the Internet, 536
Companion CD for this book
Excel Query program, 498
FormApp.xls, 419
MySuppliers.xml file, 549
MySuppliers.xsd, 550
WizardApp.xls, 432
comparison operators in the Where clause, 473
comparisonoperator argument in a Select Case statement, 76
Compile item on Debug menu, 47
Compile on Demand option, 52
complex element, 552
complex Root element, 548
complexType statement in an XML schema, 11
concatenation (&) operator, 198-200
condition statements, 74
conditional criteria, validating, 198
conditional formats
creating, 89, 232
pitfalls of, 89-90
Conditional Sum Wizard add-in, 240
CONFIDENCE function, 4
Conflict dialog box, 543
conflict-resolution dialog box, 136
connection information, configuring, 516
Connection object
in ADO object model, 482, 483-86
in RunQuery routine, 513
Connection property of PivotCache object, 352
connection string, building, 509-11
ConnectionString property, 484, 485, 513
Consecutive Delimiter parameter, 133
Consolidate_Area reserved name, 182
constants, 57
defining, 59
scope and lifetime of, 62
specifying, 59
Contacts folder, 454
containers, 53, 104, 408
Contents parameter of Protect method, 158
context-sensitive help
for an input box, 72
for a message box, 68
Continue command, 48
control characters, 195
control objects, types of CommandBar, 371
controls. See also user form controls
adding to a command bar, 371
finding hidden, 436
Controls property
of CommandBar object, 367
of CommandBarPopup object, 380
ControlTipText property, 402
conversion functions for data types, 61
converters, 131
ConvertFormula function, 123
ConvertFormula method, 179, 180
ConvertSeriesValuesToArrays procedure, 334
cookies, text of, 205
Copies parameter of PrintOut method, 148
Copy Colors From down arrow, 226
Copy method
of CommandBarControl object, 373, 375
of Sheets collection, 153
CopyFace method, 376
CopyFromExcelToNewWord-Document procedure, 461
CopyFromExcelToOpenWord-Document procedure, 460
CopyFromExcelToWord procedure, 457
CopyRows routine, 514
CopyWordToExcel procedure, 458
Corner argument, 389
corners, rounded vs. right-angled, 337
corporate profiles, 8
CorruptLoad parameter, 131
COUNT function, 206, 477
Count method, 315
Count parameter, 151
Count property
of AddIns collection, 244
of a collection, 316
of CommandBarControls collection, 372
of CommandBars collection, 366
of Dialogs collection, 384
of Errors collection, 486
of Fields collection, 494
of Parameters collection, 489
of PivotCaches collection, 352
of PivotTables collection, 349
of Sheets collection, 149
of Worksheets collection, 149
COUNTA function, 206
COUNTBLANK function, 206
counter variable in a For...Next loop, 77
COUNTIF function, 206
Create List dialog box, 9, 540
CreateBackup parameter, 136
CreateExcelChart procedure, 463
CreateItem method, 451
CreateNames sample macro, 178
CreateNewWordDoc procedure, 462
CreateObject function, 450, 451, 456, 459
CreateOneList procedure, 560-62
CreateParameter method, 488
CreatePivotTable method, 353, 358
CreateWordDocumentFromExcel procedure, 456
Creator property
of AddIn object, 245
of AddIns collection, 244
CRITBINOM function, 4
criterion operators for validation, 188
critical icon in a message box, 70
cross tabulation report, 341
CSng() conversion function, 62
CSV text file, 296
CTRL key
aiding in selection process, 413
combining with key codes, 126
key codes for, 127
CTRL+F4, 53
CTRL+F6, 53
CTRL+F8, 35
CTRL+F9, 35
CTRL+SHIFT+F8, 35
CTRL+SHIFT+F9, 35
cube, envisioning for a report, 342
Currency data type, 58, 61, 102
currency quotes on the Web, 94
current date, returning, 214
current record pointer, 492
current time, returning, 214
CurrentPage property, 354
CurrentRegion property, 162
cursor, returning to the last line of code edited, 45
CursorLocation property, 484
custom add-ins, 365
custom colors, substituting, 224
custom data formats, 217
custom data types, 72
custom dialog boxes vs. built-in, 400
custom macro toolbar, 32
Custom Menu Item command, 33
custom menus
creating for macros, 33
opening and closing, 272
Custom number formats, 195
Custom tab in Options dialog box, 224
CustomColors procedure, 225
Customize dialog box
Categories pane, 33
Commands tab, 32, 33
displaying, 49
Toolbars tab page, 32
CVar() conversion function, 62
cyan VBA color constant, 219
CycleGridlines procedure, 230
| < Day Day Up > |
|