Index_D

D

D_yyyymmdd.xls files, 545

daily reports , 545-46, 550-53

DailyRandomnumbers procedure, 548

DailyReport worksheet, 547, 550

DAO library, 646

DAO objects, 640

data. See also external data

importing without altering, 638

inserting, 214, 591

querying, 625

updating, 630-31

data analysis, 687-729

grouping data (subtotals), 688-91

internal management (PivotCache), 720-27

macros for, 44

overview, 687

pivot tables, 692-710

construction and reconstruction of, 716-20

example, 692-96

for external data, 703-7

generating and deleting, 711-15

layout options, 697-703

options for, 708-9

overview, 692

pivot charts , 709-10

syntax summary, 728-29

data channels, 278-79

Data command, 19

data fields, 96, 657-59

defining relations between, 634

what they are, 571

working with, 363, 364-65

data formatting options, 639

data management, 569-618. See also databases

balance sheet example, 602-18

components of application, 607-8

editing preexisting invoice, 616-18

event procedures, 613-14

initializing Invoice form, 612-13

opening Excel file for monthly reports, 610-11

overview, 602

printing Invoice form, 614-15

program code, 608

removing keyboard focus from listboxes, 613

running program, 603-6

saving Data from Invoice form in monthly report, 615-16

Workbook Open procedure, 609-10

consolidating tables, 599-602

data processing in VBA Code, 588-94

displaying Database form, 591

elementary database management, 588-89

filtering form, 590

form letter in Microsoft Word (example), 591-94

inserting, editing, and deleting data, 591

overview, 588

searching for data, 589-90

sorting data, 590

syntax summary, 594-95

database worksheet functions, 595-98

Excel vs. database systems, 572-75

filtering data, 582-88

advanced filters, 585-87, 585-88

autofilters, 583-85

input, alteration, and deletion of Data with Database Form, 579-80

overview, 569

sorting data, 581-82

tracking down data, 582-83

data points, 531

data presentation with graphs, 42

data records

adding, 661

creating a list with rec.Open sql, conn, 654

deleting, 661

editing, 660-61

navigation in, 659

searching and locally sorting, 660

what they are, 571

data series, 531, 848

data smoothing, 532-33

data source

ODBC, 652-53

and QueryType property, 640

selecting, 627-28

Data Source parameter, 647, 651, 652

data transfer via clipboard, 228-31

data types, 87-89

custom, 96

links between differing, 90

data validation, 498-99

data warehouses, 704-5

DataConsolidate function, 548, 599, 602

DataConvert to MS Access command, 643

DataFilterAdvanced Filter command, 585, 586, 587

DataFilterAutofilter command, 19

DataFilterAutoFilter command, 583

DataFilterShow all command, 21

DataFilterShow All command, 584, 585

DataForm command, 579

DataGet External DataData Range Properties command, 638

DataGet External DataEdit Query command, 631

DataGet External DataImport Text File command, 289, 292

DataGet External DataNew Database Query command, 52, 627

DataGet External Data command, 626

Data Group and Outline command, 691, 701

DataPivotTable and PivotChart Report command, 693, 710

DataRefreash Data command, 692

DataRefresh Data command, 630

Data Sort command, 19, 21, 581, 699

DataSubtotals command, 688

DataValidation command, 28-29, 57, 674

Database form

displaying, 591

inputting, altering, and deleting data with, 579-80

Database keyword, 653

database parameter, 595

database worksheet functions, 595-98

databases. See also literature database; Northwind database; relational databases; specific types of databases

commands, standardized language for, 636-37

construction of, 674-75

creating in Excel, 576-78

dialog for data records, 391

difference from worksheet calculational programs, 572-73

vs. Excel, 572-75

exporting to, 643

extracting data from, 52

importing external data from, 627-39

reading questionnaires into, 675-76

sharing data among several of, 620-22

survey, 679-83

templates linked to deleting linkage, 508

format of, 505-7

terminology related to, 571-72

transferring data into, 676-78

what they are, 570-71

DataBodyRange property, 716, 728

DataField range, 694

DataFields() method, 717, 728

DataLabel object, 819-20

DataLabelRange property, 716, 728

DataLabels object, 820

DataLink file, 653

DataObject object, 230-31, 820

DataRange property, 717, 729

DataSearch macro, 22

DataTable object, 820

DataTemplate sheet, 548

DataTemplate worksheet, 547

DataType parameter, 290

DATE function, 25-26

Date function, 250, 265

date, setting with spin button, 455

Date variables , 88, 95, 249

DATE worksheet function, 253, 266

DateAdd function, 251-52

DateCreated property, 272, 302

DateDiff function, 252

DateLastAccessed property, 272, 302

DateLastChanged property, 302

DateLastModified property, 272

DatePart function, 252

dates and times, 246-66

application and programming techniques, 254-57

calendar generation, 262-65

errors, 249

holidays, 257-65

internal representation of, 248-49

overview, 246

in program code, 248

syntax summary, 265-66

VBA functions, 250-53

worksheet functions, 253-54

year 2000, 246-47

DateSerial function, 248, 250, 265

DateValue function, 250, 265

DATEVALUE worksheet function, 253, 266

DAVERAGE function, 596

Day function, 250, 253, 265, 266

dayCounter function, 259

days, 255

Days360 function, 251

DAYS360 worksheet function, 253, 266

DB_Cars file, 609

DB_Functions.xls file, 597

DB_Share.xls file, 602, 603, 607

DBCars.xls file, 41

dbsurveydata table, 674

dbsurvey.mdb database, 674, 675, 679

dbsurvey.mdb file, 670

DCOUNT function, 596, 598

DCOUNTA function, 596

DDE (dynamic data exchange), 788

Deactivate event, 145, 146, 157, 158, 436

deactivating

Ask Again Later button, 37

chart objects, 541

code, 79

OK button, 37

Debug object, 76

methods and properties, 820

Print method, 376, 386, 820

Run To method, 376

Set Next method, 377

Step Into method, 376

Step Out method, 376

Step Over method, 376

Toggle Breakpoint method, 377

debugging, 7, 371-86, 372-73

debugging aids, 372-79

controlled program execution, 376-79

reaction to errors, 373-76

syntax control, 372-73

and error-tolerant behavior of programs, 380-85, 385-86

overview, 371

syntax summary, 386

Debug.Print instruction, 81, 86, 240

decimal point (.), 197

Decimal variables, 88

DecimalSeparator parameter, 291

Declare command, 755-56

Default property, 407

Default Style setting, 461

Default to Full Module View option, 73

DefaultDir attribute, 727

DefaultWebOptions object, 331, 744, 821

DefinedSize field property, 668

defining new tools, 12-13

definition operators, 178

DELETE command, 666, 668

Delete method, 62, 124, 193-94, 220, 224, 274, 302, 535, 661, 714

DeleteFile method, 301

DeleteTextFile method, 277-78

deleting

charts, 541-42

columns , 632

data, 214, 579-80

fields, 93-94

files, 274

folders, 274

new toolbars , 14

pivot tables, 711-15

sheets, 224

Design Mode tool, 439

desktop database systems, 571

Destination parameters, 63

development environment, 67-84

code input in modules, 76-80

components of, 69-76

conditional compilation, 75

editor options, 73

general options, 74

object browser, 72-73

overview, 69-70

project explorer, 71

project properties, 75

properties window, 71-72

in Excel 97, 55

executing macros, 80-82

keyboard shortcuts, 83-84

overview, 67

recording macros, 82-83

VBA components, 68-69

DGET function, 596

Diagram object, 565, 821

DiagramNode object, 565, 566, 821

DiagramNodeChildren object, 565, 821

DiagramNodes object, 822

diagrams, 49, 565-67

dialog management, 556-58

Dialog object, 822

dialogs

database form, 391

in Excel 2000, 53

positioning, 394-95

predefined

for file selection, 390-91

passing parameters to, 388-89

simulating keyboard input in, 389-90

standard Excel, 388-93

warning messages, 392

Dialogs object, 822

DialogSheets method, 219

Dialogs(xlDialogFormulaFind).Show, 23

Dictionary object, 166, 822

Dim command, 86, 88, 89, 92, 109

Dim field1(5), field2(10, 10), 96

Dim var keyword, 112

Dimfield() field, 96

Dimfield(-3 through 3) field, 96

Dimx data field, 96

Dir command, 304

[Direct]Dependents method, 191

[Direct]Precedents method, 191

directories. See folders

directory tree, 275

Discount function, 306-7

Display All Data button, 23

Display All Data macro, 21

DisplayAlerts property, 360, 368, 392

DisplayFullScreen property, 124

DisplayGridlines property, 218

DisplayHeadings property, 218

displaying Database form, 591

DisplayOutline property, 691

DisplayStatusBar property, 358-59, 368

DisplayUnitLabel object, 536, 539, 822

DisplayUnitXxx properties, 52

DLL (dynamic link libraries)

GDI library, 755

Kernel library, 755

limitations, 755

overview, 754-55

User library, 755

DLL functions

add-ins compiled to, 736

calling, 758

and character strings, 757-58

controlled via ActiveX, 761

declaring, 755-56

example, 758-59

overview, 754-55

parameter list, 757-58

"DM"* symbol, 198

DMIN/DMAX function, 596

DNS keyword, 653

Do keyword, 118

docked windows , 74

Docking page, 10

DoCmd property, 765

Document superclass, 169, 170, 171

documentation, on-line, 367

DocumentProperties object, 823

DocumentProperty object, 823

dollar ($) symbol, 198

DoLoop, 118-19

Double variables, 88, 95, 99, 116, 282

DPRODUCT function, 596

DragOver event, 158

DragPlot event, 158

drawing objects ( shapes ), 560-64

Drawing toolbar, menu of, 542

drilldown , 702-3

Drive object, 269-70, 823

Drive property, 273, 302

DriveExists method, 275, 301

Driver keyword, 653

drives

changing, 271

properties of, 269-70, 302

Drives object, 823

DriveType property, 269, 302

DropLines object, 823-24

DSTDEV function, 596

DSTDEVP function, 596

DSUM function, 596

Duplicate method, 541

DVAR function, 596

DVARP function, 596

dynamic code changing, 61

dynamic fields, 93

dynamic forms, 451-53

dynamic link libraries. See DLL (dynamic link libraries)

dynamic properties, 650