Excel 2007 Power Programming with VBA (Mr. Spreadsheets Bookshelf)

Abs function, 982

absolute cell references, 49

macro recorder, 159-162

absolute value of number, 982

Activate event, 603, 640, 654

Activate method, 319, 578

ActivateMicrosoftApp method, 674-675

activating

application windows , 977

charts , 578

ranges, selecting and, 950

sheets, ListBoxes and, 476-479

active chart, 572

active objects, 137

active sheet, 16

active workbook, 16

ActiveCell property, 178

ActiveChart property, 178, 572

ActiveSheet property, 178

ActiveWindow property, 178

ActiveWorkbook property, 178

ActiveX component can't create object error, 991

ActiveX controls

drawing layer, 118

Form controls comparison, 120

UserForms, 118

worksheets, 118-120

AddButtonAndCode procedure, 870

Add100Buttons procedure, 874-875

AddChart method, 575-576

AddComment method, 177

AddControl event, 661

AddDataField method, 558

Add-In Manager, 702-703, 706

AddIn object

Comments property, 717

events, 719-720

FullName property, 716

Installed property, 717

Name property, 716

Path property, 716

Title property, 717

AddInInstall event, 719-720

add-ins

accessing as workbook, 719

checklist for creating, 709

creating, 703-704, 706-707

custom functions and, 310

description, 699-700

descriptive information, 705-706

distributing, 708

Excel version, detecting, 724

files, 90-91

installation, 538, 707-708

installation, ensuring, 721-723

introduction, 43

ListAllAddIns procedure, 718

modifying, 708

optimization, 720-721

password-protected, 124

procedures, accessing, 712-714

protected, 713

reasons for, 701-702

referencing files, 723

testing, 708

workbook setup, 705

workbooks and, 700

worksheet functions, 105

AddIns collection

adding items, 715

introduction, 714

removing items, 716

Add-Ins dialog box, 702-703

Add-Ins tab (Ribbon), 728

AddInstall event, 637

AddInUninstall event, 719-720

AddPresetGradient procedure, 927

AddSubmenu procedure, 774-775

AddToShortCut procedure, 770

AddUninstall event, 637

ADO (ActiveX Data Objects)

ADO_Demo procedure, 696-697

developers and, 13

ADO_Demo procedure, 696-697

aesthetics, 124-125

AfterCalculate event, 656

AfterXMLExport event, 637

AfterXMLImport event, 637

album title extended property, 839

alignment, ChartObjects, 584-585

AllBold function, 362

ambiguously named procedures, 246

Analysis Toolpak

add-ins and, 699

description, 42

analysis tools

Analysis ToolPak, 42

outlines, 42

pivot tables, 42

Solver, 42

XML files, 42

AnimateChart procedure, 618, 620-621

animating charts

AnimateChart procedure, 618

clock chart, 622-624

hypocycloid charts, 621-622

scrolling, 618-621

animating labels, 482-485

annuities

future value, 983

interest payment, 984

interest rate per period, 985

number of periods, 985

payment amount, 985

present value, 985

AppActivate statement, 674, 977

application events

AfterCalculate, 656

description, 630

monitoring, 659-660

NewWorkbook, 656

SheetActivate, 656

SheetBeforeDoubleClick, 656

SheetBeforeRightClick, 656

SheetCalculate, 656

SheetChange, 656

SheetDeactivate, 656

SheetFollowHyperlink, 656

SheetPivotTableUpdate, 656

SheetSelectionChange, 656

WindowActivate, 656

WindowDeactivate, 656

WindowResize, 656

WorkbookActivate, 656

WorkbookAddinInstall, 656

WorkbookAddinUninstall, 657

WorkbookAfterXMLExport, 657

WorkbookAfterXMLImport, 657

WorkbookBeforeClose, 657

WorkbookBeforePrint, 657

WorkbookBeforeSave, 657

WorkbookBeforeXMLExport, 657

WorkbookBeforeXMLImport, 657

WorkbookDeactivate, 657

WorkbookNewSheet, 657

WorkbookOpen, 657

WorkbookPivotTableCloseConnection, 657

WorkbookPivotTableOpenConnection, 657

WorkbookRowSetComplete, 657

WorkbookSync, 657

Application object

ActiveCell property, 178

ActiveChart property, 178

ActiveSheet property, 178

ActiveWindow property, 178

ActiveWorkbook property, 178

objects, 165-166

Run method, 244

Selection property, 178

ThisWorkbook property, 178

application windows, activating, 977

Application-defined or object-defined error, 992

applications. See also international applications; spreadsheet applications; user -oriented applications

Access, 106

activating with Excel, 674-675

aesthetics, 124-125

associating Help files, 798

audience for, 101

beta testing, 122

cells , locking, 123

characteristics of, 98

data storage, 106

database front ends, 107

development concepts, 813-814

distributing, 127

introduction, 97-98

intuitiveness, 124-125

multilanguage, 823-824

objects and, 16

planning, 111-114

single-block budgets , 106

single-user, 104

spaghetti, 104-105

starting from Excel, 670-674

testing, 121-122

turnkey applications, 107

updates, 127-128

user needs and, 111-114

utility applications, 105

what-if models, 106

Apply Names dialog box, 54

ApplyLayout method, 589

arctangent of number, 982

Areas method, 327

AreaType function, 327-328

Argument not optional error, 992

arguments

array arguments, 293-294

event handler procedures, 635-637

functions, 285-286

optional, functions, 294-295

passing to procedures, 251-255

procedures, 238

SERIES formula, 592-593

array arguments, 293-294

array formulas

calendar, 62-63

example, 61-62

introduction, 48

pros/cons, 63

Array function, 296-298, 982

ArrayFillRange function, 338-339

arrays

declaring, 210

dimensions, 980

dynamic, declaring, 211

introduction, 209

limits, 980

local, declaring, 980

multidimensional, declaring, 210-211

one-dimensional, transferring, 339

public, declaring, 980

reinitializing, 979

sorting, 352-354

strings, combining, 984

artist extended property, 839

Asc function, 982

ASCII value, converting strings to, 982

assets

depreciation, 983

depreciation, straight-line, 986

sum-of- years ' digits, 986

assignment statements

= (equal sign) and, 207

description, 190

expressions, 207

functions and, 207

operators, 208

Assistance Platform Help, 794

associating Help files with application, 798

associating help topics with functions, 798-799

Atn function, 982

attributes extended property, 839

audio sample rate extended property, 839

audio sample size extended property, 839

author extended property, 839

Auto Data Tips option (VBE), 154

Auto Indent option (VBE), 154

Auto List Members option (VBE), 153

Auto Quick Info option (VBE), 154

Auto Syntax Check option (VBE), 152-155

automation

developers and, 13

introduction, 676-677

Automation error, 992

Automation object does not have a default value error, 992