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

CalcComm procedure, 291

Calculate event, 603, 645, 654

Calculation mode, 48

Calculation property, 933

Call keyword, 244

Call statement, 977

callback procedures

CheckPageBreakDisplay, 744

description, 736

GetEnabled, 744

GetPressed, 744

Initialize, 743-744

RibbonX code, 739-740

TogglePageBreakDisplay, 744

CallByName function, 982

calling procedures, 238

camera model extended property, 839

Can't create necessary temporary file error, 991

Can't perform requested operation error, 990

Can't rename with different drive error, 990

Can't save file to TEMP error, 992

Caption property, 431

cash flows, internal rate of return, 984-985

categories, functions, 307-308

category extended property, 839

CBool function, 982

CByte function, 982

CCur function, 982

CDate function, 982

CDbl function, 982

CDec function, 982

CD-ROM

files/software on, 994-1008

installing items, 994

troubleshooting, 1009

cell references

absolute, 49

column absolute, 49

other sheets, 51-53

other workbooks, 51-53

R1C1 notation, 50

relative, 49

row absolute, 49

Cell shortcut menu, new item, 770-772

cells

activating, 639

comments, 176

comments, color , 935

comments, font, 935

comments, Help system, 784-785

counting cells between values, 365

counting visible in range, 366

data type, determining, 335-336

drop-down lists, 932

formatting, 361-362

last non-empty, 366-368

locking, 123

merged, 180

naming, 53-54

scope, 56

selected, counting, 326-327

selecting all, 932

selecting by value, 340-342

speaking contents, 933

values, prompting for, 321-323

Cells property, 182-183

CellType function, 335-336

Change event, 645-646

ChangeSeriesColor procedure, 926

channels extended property, 839

characters , repeating, 986

chart events

Activate, 654

BeforeDoubleClick, 654

BeforeRightClick, 654

Calculate, 654

Deactivate, 654

description, 630

MouseDown, 654

MouseMove, 654

MouseUp, 654

Resize, 654

Select, 654

SeriesChange, 654

Chart object, model, 574-575

chart sheets

activating, 603

charts , 18

introduction, 16

XLAM files, 711

XLSM files, 711

chart tips, MouseOver event and, 615

Chart_Activate procedure, 605

Chart_Deactivate procedure, 605

ChartObjects

alignment, 584-585

sizing, 584-585

ChartObjects collection, deleting from, 581-582

charts

Activate event, 603

activating, 578

active chart, 572

AddPresetGradient procedure, 927

animating, 617-624

BeforeDoubleClick event, 603

BeforeRightClick event, 603

bevels, 591-592

Calculate event, 603

ChangeSeriesColor procedure, 926

color, modifying, 925-928

columns , hiding, 612-613

ConvertChartToPicture procedure, 614

copying as picture, 613

creating on chart sheet, 577

creating, unlinked , 613-615

3-D effects, 591-592

data, changing, 592-598

data labels, displaying, 598-600

data points, value change, 603

Deactivate event, 603

deactivating , 580, 603

displaying in UserForms, 524-526, 600-602

DragOver event, 603

DragPlot event, 603

elements, select, 603

embedded, creating, 575-577

as embedded objects, 572

embedded, printing, 610-611

events, Chart object, 603

events, example, 604-607

exporting, 585-586

formatting, 587-589

interactive, 624-627

introduction, 37-38

location, 572

looping through, 582-584

macro recorder and, 572-574

MouseDown event, 603

MouseMove event, 603

MouseUp event, 603

moving, 578-580

names , 579

objects, connecting with chart, 607

overview, 571-572

pivot charts, 38

plotting new data, 603

public chart object, declaring, 607

range references, converting to arrays, 613

ranges used, 595-598

RecolorChartAndPlotArea procedure, 927

resize, 603

Resize event, 603

saving as GIF files, 525, 944

scrolling, 618-621

Select event, 603

in separate chart sheet, 572

SERIES formula, 592-593

series, hiding, 612-613

SeriesChange event, 603

shadows, 590-591

UseRandomColors procedure, 928

viewing as grayscale, 908

Charts collection

Add method, 577

deleting from, 581-582

Chart_Select procedure, 606

ChartSlideShow procedure, 611

ChartStyle property, 589

ChartTitle property, 575

ChartType property, 589

ChDir command, 830

ChDir statement, 977

ChDrive command, 830

ChDrive statement, 977

check boxes, Ribbon, 22-23

CheckBox control, UserForms, 416

CheckPageBreakDisplay callback procedure, 744

Choose function, 982

Chr function, 982

CInt function, 982

Class doesn't support Automation or doesn't support expected interface error, 991

class modules

code, adding, 888-890

creating, 607

events, 894-895

inserting, 887

NumLockClass class, 890-891

object class, naming, 892

overview, 885-886

Class not registered on local machine error, 992

ClearToMatchStyle method, 589

Click event, 661

client application, 677

CLng function, 982

clock chart, 622-624

Close button, UserForms, disabling, 456

Close statement, 977

CloseAllWorkbooks function, 344

CloseMode argument, 456

code

adding to class modules, 888-890

disappearing , 931

entering by copying, 152

entering manually, 145-148

entering using macro recorder, 148-152

examples, 931

introduction, 135

storing, 145

writing code, 869-871

Code Colors option (VBE), 156

Code resource lock error, 992

Code resource not found error, 992

Code window (VBE)

introduction, 141

maximizing, 144-145

minimizing, 144-145

collections

description, 166, 949

introduction, 136

membership, testing for, 359

color

background, shapes , 917-919

code, 938

codes, RGB (red/green/blue), 985

converting colors, 904-906

converting to gray, 907

decimal color values, 904-906

document themes, 911-917

examples, 903

grayscale, 906-908

HSL color system, 903-904

hue, 904

luminance, 904

modifying charts, 925-928

RGB color system, 902-903

saturation, 904

selecting in UserForms, 523-524

tabs, 935

ColorNegative function, 329-332

column absolute cell references, 49

columns

charts, hiding, 612-613

hiding, 123, 642-643

increasing number, 933

last non-empty cell, 366-368

naming, 55

COM (Component Object Model), 701

ComboBox control

UserForms, 416

UserForms, Help system, 790-792

Command functions, category, 308

command line switches, 74-75

CommandBar object

BeginGroup property, 766

BuiltIn property, 766

Caption property, 766

controls, 764-766

Enabled property, 766

FaceID property, 766

ID property, 766

menu bar, 729

OnAction property, 766

Picture property, 766

referencing, 764

shortcut menu, 729

toolbar, 729

ToolTipText property, 766

Type property, 762, 766

Visible property, 766

CommandButton control

macros and, 119

properties, 432

UserForms, 417, 450

commands

ChDir, 830

ChDrive, 830

Dir, 830

Error Checking, 31

FileCopy, 830

FileDateTime, 830

FileLen, 830

GetAttr, 830

Kill, 830

MkDir, 830

Name , 830

Ribbon, executing, 408-410

RmDir, 830

SetAttr, 830

Comment object

AddComment method, 177

Application property, 172

Author property, 172

Delete method, 173

Help, 171

methods , 172-173

Next method, 173

objects within, 175-176

Parent property, 172

Previous method, 173

properties, 171-172

Shape property, 172

Text method, 173

Visible property, 172

Comment property, 174-175

comments

apostrophes , 192

cells, 176

description, 192

instructions and, 193

Rem keyword, 192

specifying, 980

tips for using, 192-193

Comments collection, 173-174

comments extended property, 839

compatibility

file format issues, 818

international issues, 818

Microsoft issues, 818

Microsoft Office Compatibility Pack, 819

new features, 818, 819-820

overview, 817-818

problems, 818-819

Windows versus Macintosh, 818, 820-821

concatenation operator, 939

Conditional Formatting feature, 941

conditions, branches, 980

Connection to type library object library for remote process has been lost error, 992

Const statement, 977

constants

declaring, 204, 977

introduction, 203

naming, 56-57

predefined, 204-205

context-sensitive shortcut menus , 777-779

contextual tabs, Ribbon, 21

continuation character, 939

ContractAllSeries procedure, 597-598

Control Panel, dialog boxes, running, 675-676

controls

ActiveX controls, 118

CommandBar object, 764-766

developers and, 13

dynamicMenu (Ribbon), 752-754

Form Controls, 119

properties, 422-427

RefEdit, 452-453

Ribbon, accessing, 732

tab order, 425

UserForms, 118

UserForms, adding at design time, 872-875

UserForms, adding to, 415-416

UserForms, adjusting, 421-422

UserForms, aligning, 421

UserForms, referencing, 444-445

worksheets and, 419-420

Controls collection, 444

ConvertChartToPicture procedure, 614

converted strings, 986

CopyCurrentRegion function, 318-319

copying

code into module, 152

files, 830, 979

ranges, 316-317

ranges, noncontiguous, 342-344

ranges, variably sized , 318-319

CopyMultipleSelection function, 342-343

CopyRange function, 317

copyright extended property, 839

CopySheetFromAddin procedure, 711

Cos function, 982

cosine of number, 982