Excel 2007 Power Programming with VBA (Mr. Spreadsheets Bookshelf)
F4 function key, 933
File already exists error, 990
File already open error, 990
file associations, Windows API, 377-378
File not found error, 990
file structure, developers and, 13
file types
accdb, 79
accde, 79
database files, 79
dbf, 79
dif, 79
htm (Hypertext Markup Language), 80
mdb, 79
mde, 79
mht (Single File Web Page), 80
pdf (Portable Document Format), 80
slk, 79
text files, 78-79
txt, 79
xla (Excel 97-Excel 2003 add-in), 77
xlam (add-in), 77
xls (Excel 5.0/95 workbook), 78
xls (Excel 97-Excel 2003 workbook), 77
xlsb (binary workbook), 77
xlsm (macro-enabled workbook), 77
xlsx (workbook), 77
xlt (Excel 97-Excel 2003 template), 77
xltx (template), 77
xltxm (macro-enabled template), 77
xml (XML data), 78
xml (XML spreadsheet), 78
xps (XML Paper Specification), 80
FileAttr function, 983
FileCopy command, 830
FileCopy statement, 979
FileDateTime command, 830
FileDateTime function, 983
FileDialog object
directory selection, 406-407
introduction, 403
FileExists function, 356, 831, 836
FileInfo procedure, 839-840
FileLen command, 830
FileLen function, 983
Filename or class name not found during Automation operation error, 991
filename switch, 74
FileNameOnly function, 356-357
files. See also text files
add-ins, 90-91
attribute information, 981
attributes, 984
attributes, changing, 830
commands for, 830
copying, 830, 979
date/time last modified, 830
deleting from disk, 830, 979
FileExists function, 831
formats, 44
formats, importance of, 88
listing in directory, 831-833
number of bytes in, 983
PathExists function, 831
pattern matching name, 983
printing, date, 363-364
processing times, 354-355
QAT, 89-90
renaming, 830, 980
saving, date, 363-364
sequential, writing to, 980
size in bytes, 830
unzipping, 858
workspace files, 80
XLB, 90
zipping, 855-858
FileSystemObject object
Drive object, 835
File object, 835
FileExists function, 836
Folder object, 835
introduction, 835-836
PathExists function, 837
ShowDriveInfo procedure, 837-838
TextStream object, 835
FillColor function, 362
FillDown method, 333-334
fills, shapes , 919-925
Filter function, 983
FilterFile procedure, 849
filtering, text files, 848-849
Financial functions, category, 308
FindControl method, 765
Finish button, wizards, 505-506
Fix function, 983
fixed-length strings, 205
folders, windows, displaying, 671
FollowHyperlink event, 645
Font option (VBE), 156
fonts
listing, 351-352
tabs, 935
workbook, 934
footers, updating, 642
For Each-Next construct, 218-220
For Each-Next statement, 979
For loop not initialized error, 991
Form controls, 119, 120
Format function, 349-350, 983
FormatAllCharts function, 582
FormatChart procedure, 587-588
FormatCurrency function, 983
FormatCurrentRegion function, 320
FormatDateTime function, 983
FormatNumber function, 983
FormatPercent function, 983
formatting
cells , 361-362
charts , 587-589
numeric, 33
stylistic, 34
forms, generating, 102
Formula property, 595
formulas
array formulas, 48
bold, 647
calculating, 48-49
cell references, 49-53
counting formulas, 64-65
errors, 59-60
hiding, 123
introduction, 30-32
megaformulas , 30, 69-71
naming, 57-59
overview, 48
range references, 49-53
SERIES, 592-593
summing formulas, 66
Formulas tab (Ribbon), 728
For-Next loops , 229-232
For-Next statement, 979
for-your-eyes-only spreadsheets, 104
Frame control
properties, 431
RefEdit controls, 453
UserForms, 417
FreeFile function, 983
Function keyword, 279
Function procedures
arguments, 285-286
executing from Immediate window, 284-285
executing from procedure, 283-284
executing in worksheet formula, 284
Sub procedures and, 276
when to use, 276
Function statement, 979
functions
Abs, 982
AllBold, 362
analyzing, 279-281
AreaType, 327-328
arguments, 285-286
arguments instead of cell references, 292
Array, 296-298, 982
array argument, 293-294
ArrayFillRange, 338-339
Asc, 982
associating help topics, 798-799
Atn, 982
BatchProcess, 354-355
BUBBLESIZE_FROM_SERIES, 596
built-in, 213-216, 948
CallByName, 982
categories, 307-308, 946-947
CBool, 982
CByte, 982
CCur, 982
CDate, 982
CDbl, 982
CDec, 982
CellType, 335-336
Choose, 982
Chr, 982
CInt, 982
CLng, 982
CloseAllWorkbooks, 344
ColorNegative, 329-332
CopyCurrentRegion function, 318-319
CopyMultipleSelection, 342-343
CopyRange, 317
Cos, 982
CountBetween, 365
CountVisible, 366
CreateChart, 576
CreateChartSheet, 577
CreateObject, 679, 982
CSng, 982
CStr, 982
CurDir, 982
custom, 277
custom, add-ins and, 310
custom, limitations, 280
custom, reasons to create, 276-277
CVar, 982
CVDate, 982
CVErr, 982
Date, 349-350, 983
DateAdd, 983
DateAndTime, 349-350
DateDiff, 983
DatePart, 983
DateSerial, 983
DateValue, 983
Day, 983
DDB, 983
debugging, 305-306
DECIMAL2RGB, 904
declaring, 281-283
DeleteEmptyRows, 332
description, 145
descriptions, Insert Function dialog box, 308-310
developers and, 13
Dir, 832-833, 983
DoEvents, 983
DupeRows, 333
EntryIsValid, 650
Environ, 983
EOF, 983
EraseRange, 393-394
Error, 256, 983
error values, 299-300
Exp, 983
ExtractElement, 369-371
FileDateTime, 983
FileExists, 356, 831, 836
FileLen, 983
FileNameOnly, 356-357
FillColor, 362
FilleAttr, 983
Filter, 983
Fix, 983
Format, 349-350, 983
FormatAllCharts, 582
FormatCurrency, 983
FormatCurrentRegion, 320
FormatDateTime, 983
FormatNumber, 983
FormatPercent, 983
FreeFile, 983
FV, 983
GetAColor, 523-524
GetAFolder, 406-407
GetAllSettings, 983
GetAttr, 984
GetDate, 323
GetDirectory, 404-406
GetImportFileName, 400-401
GetObject, 679, 984
GetOption, 878-881
GetSetting, 984
GetUserRange, 325
GetValue, 360
GetValue function, 321
GetWord, 391
Grayscale, 907
Hex, 984
HideRowsAndColumns, 345-346
Hour, 984
IIf, 984
indefinite number of arguments, 300-301
Input, 984
InputBox, 984
InRange, 334-335
InStr, 984
InStrRev, 984
Int, 984
introduction, 30-32
IPmt, 984
IRR, 984
IsArray, 984
IsBold, 362
IsDate, 984
IsEmpty, 984
IsLike, 368-369
IsMissing, 984
IsNull, 984
ISNUMBER, 293
IsNumeric, 984
IsObject, 984
Join, 984
LastInColumn, 366-368
LastInRow, 366-368
LastPrinted, 363
LastSaved, 363
LBound, 267, 984
LCase, 984
Left, 984
Len, 984
LoadPicture, 524-526
Loc, 985
LOF, 985
Log, 985
LoopFillRange, 337-338
LTrim, 985
MakePivotTables, 566-568
MaxAllSheets, 373-374
Mid, 985
Minute, 985
MIRR, 985
Month, 985
MonthName, 985
MonthNames, 296
MoveChart, 578-579
MoveRange, 318
MsgBox, 215-216, 305, 394-399, 985
MySum, 301-304
#NAME, 945
no argument, 286-289
Now, 985
NPer, 985
NPV, 985
Oct, 985
one argument, 289-292
optional arguments, 294-295
PageCount, 348
Partition, 985
PathExists, 357, 831, 837
Pmt, 985
Ppmt, 985
private, 283
in procedures, 278-279
ProcessFiles, 355
public, 283
PV, 985
QBColor, 985
RandomColor, 928
RandomIntegers, 374-375
RangeNameExists, 357-358
RangeRandomize, 375-377
RangeToVariant, 340
Rate, 985
recalculation, controlling, 288
RemoveVowels, 299-300
Replace, 985
RGB, 902, 985
RGB2DECIMAL, 904
Right, 985
Rnd, 985
Round, 985
RTrim, 985
SaveAllWorkbooks, 344
SayIt, 363
scope, 283
Second, 985
Seek, 986
SelectByValue, 341
SERIESNAME_FROM_SERIES, 596
Sgn, 986
SheetExists, 358
SheetOffset, 372
Shell, 670-672, 986
Sin, 986
SLN, 986
Space, 986
Spc, 986
Split, 370-371, 986
Sqr, 986
StatFunction, 371-372
Str, 986
StrComp, 986
StrConv, 986
String, 986
StrReverse, 986
SUM, emulating, 301-304
SumArray, 294
Switch, 986
SYD, 986
SynchSheets, 346-347
Tab, 986
Tan, 986
Time, 986
Timer, 986
TimeSerial, 986
TimeValue, 986
ToggleWrapText, 347-348
TRANSPOSE, 296, 339
Trim, 986
two arguments, 292-293
TypeName, 986
UBound, 267, 986
UCase, 271, 987
Val, 987
VALUES_FROM_SERIES, 596
VarType, 987
VBA arrays and, 296-298
Weekday, 987
WeekdayName, 987
Windows API, 311-314
WorkbookIsOpen, 358
WorksheetFunction object, 215
worksheets, 278
wrapper functions, 287
XVALUES_FROM_SERIES, 596
Year, 987
future value of annuities, 983
FV function, 983