Index_C

 < Day Day Up > 



C

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 > 



Microsoft Excel 2003 Programming Inside Out
Microsoft Office Excel 2003 Programming Inside Out (Inside Out (Microsoft))
ISBN: 0735619859
EAN: 2147483647
Year: 2006
Pages: 161

flylib.com © 2008-2017.
If you may any questions please contact us: flylib@qtcs.net