Index

+ (addition) operator, 103 & (ampersand) operator, 103 ' (apostrophe) in comments, 89-90

\ (backward slash) operator, 103 A (caret) operator, 103 = (equal sign) operator, 102 / (forward slash) operator, 103 \ (integer division) operator, 103 - (minus sign) operator, 103 + (plus sign) operator, 103 " (quotation marks) in object references, 55

Abs function, 123

Accelerator property (UserForm controls), 249 Activate event examples, 161-163 workbooks, 152 worksheets, 153 ActivateMicrosoftApp method, 352 activating Visual Basic Editor (VBE), 33 ActiveX Data Objects (ADO), 360-362 adaptive menus, 294 Add Watch dialog box, 192-193 adding buttons to toolbars, 295-296 columns to worksheets, 366 controls to dialog boxes, 234-235, 247-248 menu items, 315-316, 318-319 rows to worksheets, 366 VBA modules to projects, 36-37 AddinInstall event, 152 add-ins closing, 341

converting workbooks to add-ins, 341-342

creating, 14, 340, 347

defined, 339

descriptions, 346-347

distributing, 349

example, 342-345

loading, 341

modifying, 349 opening, 341, 348 testing, 346

third-party suppliers, 339 unloading, 341 unlocking, 349 uses, 340

worksheet functions, 340 XLA file extension, 340 Add-Ins dialog box, 341 AddinUninstall event, 152 addition (+) operator, 103 Address property (Range object), 113 addresses of cell ranges, 113 ADO (ActiveX Data Objects), 360-362 alert messages, 209 aligning dialog box controls, 261 ampersand (&) operator, 103 And operator, 103 animation of menu display, 295 apostrophe (') in comments, 89-90 AppActivate statement, 352 Application object ActivateMicrosoftApp method, 352 defined, 52

object hierarchy, 52-53 applications macro-driven, 14

starting other applications from Excel, 351-352 arguments descriptions, 337 functions, 64, 327-335 methods, 59 Array function, 123 arrays declaring, 104-105 defined, 104 dynamic, 105-106 multidimensional, 105 one-dimensional, 104-105 Asc function, 123 Assign Macro dialog box, 70-71 assigning shortcut keys to macros, 83 values to variables, 91

assignment (=) operator, 102 assignment statements, 101-102 asterisk (*) operator, 103 Atn function, 123 attaching toolbars to workbooks,

293, 297-298 author's Web site, 370 Auto Data Tips option (VBE), 46 Auto Indent option (VBE), 47 Auto List Members option (VBE), 46 Auto Quick Info option (VBE), 46 Auto Syntax Check option (VBE), 45 automatic calculation, 208-209 automation automation server, 352-354 defined, 12 early binding, 354 late binding, 354 Microsoft Outlook, 358-360 tasks, 13 autosensing, 293

AutoSize property (UserForm controls), 249

background color dialog box controls, 249 ranges (of cells), 114-115 BackStyle property (UserForm controls), 249 backward slash (\) operator, 103 Banfield, Colin, Excel 2003 For Dummies

Quick Reference, 2 BeforeClose event, 152, 159-160 BeforeDoubleClick event, 153, 163 BeforePrint event, 152 BeforeRightClick event, 153, 163-164 BeforeSave event, 152, 160 BeginGroup property (CommandBar controls),

301, 313 blogs, 371

Boolean data type, 93 Boolean settings, 204 breakpoints, 189-191 browsing objects, 61-62 bugs. See also errors categories of, 185-186 debugging, 186-193 defined, 185 finding, 186-187 macros, 186

reduction tips, 194 syntax errors, 186 built-in dialog boxes constants, 229-230 displaying, 228-230 error messages, 230 built-in functions Abs, 123

arguments, 64, 327-335

Array, 123

Asc, 123

Atn, 123

Choose, 123

Chr, 123

Cos, 123

creating, 14

CurDir, 123

Date, 120, 123

DateAdd, 123

DateDiff, 123

DatePart, 123

DateSerial, 124

DateValue, 124

Day, 124

defined, 119

Dir, 124

Erl, 124

Err, 124

Error, 124

Exp, 124

FileLen, 121-122, 124 Fix, 121, 124 Format, 124 GetSetting, 124 Hex, 124 Hour, 124

InputBox, 122, 124, 221-223

InStr, 124

IPmt, 124

IsArray, 124

IsDate, 124

IsEmpty, 124

IsError, 124

IsMissing, 124

IsNull, 124

IsNumeric, 124

IsObject, 125

LBound, 125

LCase, 125

Left, 125

Len, 121, 125 Log, 125 LTrim, 125 Mid, 125 Minute, 125 Month, 125

MsgBox, 125, 187-188, 216-221

#NAME errors, 365

RGB, 125

Right, 125

RTrim, 125

Second, 125

Sgn, 125

Shell, 123, 125, 351-352

Sin, 125

Space, 125

Sqr, 125

Str, 125

StrComp, 125

String, 125

Tan, 125

Time, 120, 126

Timer, 126

TimeSerial, 126

TimeValue, 126

Trim, 126

TypeName, 122, 126 UBound,126 UCase, 126 Val, 126 VarType, 126 Weekday, 126 Year, 126

BuiltIn property (CommandBar controls), 301, 313

buttons (toolbars) adding, 295-296 copying, 295 creating, 13 deleting, 296 groups, 297 hyperlinks, 297 icons, 294 images, 296-297 macros, 297 moving, 295 resetting, 293, 296 ScreenTips, 295 text, 297

Calculate event, 153 Call keyword, 71 calling

Function procedures, 72-74 Sub procedures, 65-71 captions

CommandBar controls, 301, 312, 317 UserForm controls, 249 caret (A) operator, 103 cell ranges. See ranges (of cells) Cells property (Range object), 109 Change event, 153, 164-166 changing Excel settings, 203-204 UserForm control properties, 235-236, 248-249 charts custom dialog boxes, 284-285 formatting, 207 loops, 206 macros, 205 modifying, 205 properties, 206 CheckBox control, 235, 251-252 checked menu items, 308 Choose function, 123 Chr function, 123 Clear method (Range object), 117 clearing ranges (of cells), 117 closing add-ins, 341 code

Auto Indent option (VBE), 47

breakpoints, 189-190

comments, 26-27

copying, 44

defined, 5

editing, 25-29

indenting, 47

inserting into VBA modules, 39-44 line numbers, 106 menus, 313

optimizing performance, 207-208, 366 password-protection, 365-366 stepping through code, 192 structured programming, 135 syntax errors, 45 viewing, 25-27 Code Colors option (VBE), 47

Code window UserForm objects, 236 Visual Basic Editor (VBE), 34-35 collections defined, 17, 53 loops, 149-150 methods, 60 properties, 59 Column property (Range object), 112 columns (worksheets), adding, 366 ComboBox control, 235, 252 CommandBars collection BeginGroup property, 301, 313 BuiltIn property, 301, 313 Caption property, 301, 312, 317 Enabled property, 301, 313 FaceID property, 301, 312 listing, 299-300 OnAction property, 301, 313 references, 300-301, 310-312 ToolTipText property, 301, 313 Type property, 299 types of CommandBars, 289 CommandButton control adding, 238-239 event-handler procedures, 253 what it does, 235 commands, creating, 13-14 comments apostrophe ('), 89-90 converting statements into comments, 90 defined, 89 inserting, 89 macros, 26-27 syntax, 89-90 using effectively, 91 concatenation, defined, 103 concatenation (&) operator, 103 Const statement, 98 constants declaring, 98-99 defined, 98

dialog boxes (built-in), 229-230 listing, 99

scope, 98 values, 98

xlCalculationManual,99 contiguous ranges, 108 Control objects, 310-313 controlling Microsoft Excel from Word, 355-358 Microsoft Word from Excel, 355

controls (CommandBar object) BeginGroup property, 301, 313 BuiltIn property, 301, 313 Caption property, 301, 312, 317 Enabled property, 301, 313 FaceID property, 301, 312 OnAction property, 301, 313 ToolTipText property, 301, 313 controls (custom dialog boxes). See controls

(UserForm object) controls (UserForm object) accelerator keys, 249 adding, 234-235, 247-248 aligning, 261 automatic resizing, 249 background colors, 249 background styles, 249 captions, 249

changing properties, 235-236, 248-249

CheckBox, 235, 251-252

ComboBox, 235, 252

CommandButton, 235, 238-239, 253

event-handling procedures, 241-243

Frame, 235, 253

height, 250

help, 251

hiding, 250

Image, 235, 250, 254

Label, 235, 254-255

ListBox, 235, 255-256, 272-276

moving, 261

MultiPage, 235, 256

Name property, 237

naming, 250

OptionButton, 235, 239-241, 256-257, 278

positioning, 250

RefEdit, 235, 257, 276-277

resizing, 261

ScrollBar, 235, 258

spacing, 261

SpinButton, 235, 258-259, 278-280 TabStrip, 235, 259 TextBox, 235, 259-260, 278-280 ToggleButton, 235, 260 values, 249 width, 250 ConvertFormulas macro, 29-30 converting formulas to values, 21-22 statements into comments, 90 workbooks to add-ins, 341-342

Copy method (Range object), 116-117 copying buttons (toolbars), 295 code, 44

ranges (of cells), 116-117, 196-199 Cos function, 123

Count property (Range object), 112 counting cells in a range, 112 creating add-ins, 14, 340, 347 commands, 13-14 dialog boxes, 215-216, 232-233, 238 functions, 14 menus, 313-315 toolbar buttons, 13 toolbars, 292 VBA modules, 39 CurDir function, 123 Currency data type, 93 custom dialog boxes adding controls, 247-248 aligning controls, 261 changing control properties, 248-249 charts, 284-285 checkboxes, 251-252 checklist suggestions, 286 combo boxes, 252 command buttons, 238-239, 253 creating, 215-216, 232-233, 238 design, 264 displaying, 237

event-handling procedures, 241-243 frames, 253

Get Name and Sex example, 265-271

hot keys, 263

images, 254

information, 237

InputBox function, 221-223

keyboard users, 262-263

labels, 254-255

list boxes, 255-256, 272-276

macros, 243-245

moving controls, 261

MsgBox function, 216-221

multipage, 256

option buttons, 239-241, 256-257, 278

progress indicator, 280-283

range selection, 258, 276-277

resizing controls, 261

scrollbars, 258

spacing controls, 261

spin buttons, 258-259, 278-280

tab order, 262-263 tab strips, 259 tabbed, 283-284 testing, 263

text boxes, 259-260, 278-280 toggle buttons, 260 UserForm objects, 233-237 uses, 215, 231-232 custom functions argument descriptions, 337 arguments, 327-335 categories, 336-337 debugging, 333 defined, 131-132 descriptions, 335-336 inserting, 326, 335 limitations, 326 passivity, 326 uses, 325 writing, 327 custom message boxes, 218-221 Customize dialog box Commands tab, 294 Options tab, 294-295 Toolbars tab, 292 customizing menus, 308-309 toolbars, 289-290 Visual Basic Editor (VBE), 44-49

data types Boolean, 93 Currency, 93 Date, 93, 100-101 defined, 92 Double (negative), 93 Double (positive), 93 Integer, 93 Long, 93 Object, 93 Single, 93 String, 93 User defined, 93 values, 93 Variant, 93-94 Data, Validation command, 164-165 data-entry templates, 14 Date data type, 93, 100-101 Date function, 120, 123

DateAdd function, 123 DateDiff function, 123 DatePart function, 123 DateSerial function, 124 DateValue function, 124 Day function, 124

Deactivate event, 152-153, 161-163 debugging custom functions, 333 defined, 186 debugging techniques breakpoints, 189-191 Debug.Print statements, 189 examining code, 187 MsgBox function, 187-188 stepping through code, 192 VBA Debugger, 189-193 watch expressions, 192-193 Debug.Print statements, 189 decision-making constructs Do-Until loop, 134, 148-149 Do-While loop, 134, 147-148 For-Next loop, 134, 144-147 GoTo statement, 134-135 If-Then structure, 134-139 Select Case structure, 134, 140-143 declaring arrays, 104-105 constants, 98-99 strings, 100

variable types, 210-211 variables, 94 VBA modules, 39 Default to Full Module View option (VBE), 47 Delete method (Range object), 117 deleting breakpoints, 190 buttons (toolbars), 296 menu items, 316-317 menus, 316 ranges (of cells), 117 toolbars, 292

VBA modules from projects, 37 descriptions add-ins, 346-347 arguments, 337 custom functions, 335-336 Function procedures, 74 macros, 83 designing custom dialog boxes, 264 dialog boxes (built-in) constants, 229-230 displaying, 228-230 error messages, 230

dialog boxes (custom) adding controls, 247-248 aligning controls, 261 changing control properties, 248-249 charts, 284-285 checkboxes, 251-252 checklist suggestions, 286 combo boxes, 252 command buttons, 238-239, 253 creating, 215-216, 232-233, 238 design, 264 displaying, 237

event-handling procedures, 241-243 frames, 253

Get Name and Sex example, 265-271

hot keys, 263

images, 254

information, 237

InputBox function, 221-223

keyboard users, 262-263

labels, 254-255

list boxes, 255-256, 272-276

macros, 243-245

moving controls, 261

MsgBox function, 216-221

multipage, 256

option buttons, 239-241, 256-257, 278

progress indicator, 280-283

range selection, 258, 276-277

resizing controls, 261

scrollbars, 258

spacing controls, 261

spin buttons, 258-259, 278-280

tab order, 262-263

tab strips, 259

tabbed, 283-284

testing, 263

text boxes, 259-260, 278-280 toggle buttons, 260 UserForm objects, 233-237 uses, 215, 231-232 Dim keyword, 95 Dir function, 124 disabling macros, 5-6 shortcut menus, 322 displaying dialog boxes (built-in), 228-230 dialog boxes (custom), 237 message boxes, 216-217 multiline messages in a message box, 367 system date/time, 120 toolbars, 291-292, 302-304

distributing add-ins, 349 toolbars, 297-298 division (/) operator, 103 docked toolbars, 291 Docking tab (Options dialog box), 48-49 Double (negative) data type, 93 Double (positive) data type, 93 Do-Until loop, 134, 148-149 Do-While loop, 134, 147-148 Drag-and-Drop Text Editing option (VBE), 47 dynamic arrays, 105-106

early binding, 354 Edit, Undo command, 28 editing code, 25-29 macros, 25-29 values in ranges of cells, 111 Editor Format tab (Options dialog box), 47-48 Editor tab (Options dialog box), 45-47 e-mail, 358-360 empty modules, 367 enabled menu items, 308 Enabled property (CommandBar controls),

301, 313 enabling macros, 5-6 entering worksheet functions, 129-130 equal sign (=) operator, 102 Eqv operator, 103 Erl function, 124 Err function, 124 Error function, 124 error handling On Error statements, 177-178 Resume statements, 178-180 tips, 180

error messages in dialog boxes, 230 errors. See also bugs error numbers, 181-182 examples, 172-176 ignoring, 180-181 intentional, 182-184 programming errors, 171 run-time errors, 171 subscript out of range,366 event-handler procedures BeforeClose event, 159-160 BeforeSave event, 160 custom dialog boxes, 241-243

defined, 154 Open event, 157-159 storing, 154 writing, 155, 157 events

Activate, 152-153, 161-163 AddinInstall, 152 AddinUninstall, 152 BeforeClose, 152, 159-160 BeforeDoubleClick, 153, 163 BeforePrint, 152 BeforeRightClick, 153, 163-164 BeforeSave, 152, 160 Calculate, 153 Change, 153, 164-166 Deactivate, 152-153, 161-163 defined, 60 examples, 151-152 FollowHyperlink, 153 keypress events, 168-169 NewSheet, 152 OnKey, 168-169 OnTime, 167-168 Open, 152, 157-159 SelectionChange, 153 SheetActivate, 152 SheetBefore DoubleClick, 152 SheetBefore RightClick, 152 SheetCalculate, 153 SheetChange, 153 SheetDeactivate, 153 SheetFollowHyperlink, 153 SheetSelectionChange, 153 usefulness of, 154 WindowActivate, 153 WindowDeactivate, 153 WindowResize, 153 workbook events, 152-153 worksheet events, 153 Excel blogs, 371

controlling from Microsoft Word, 355-358 controlling Microsoft Word, 355 newsgroups, 370 user groups, 371 versions, 19 Excel 5 For Windows Power Programming

Techniques, John Walkenbach, 16 Excel 2003 Bible, John Walkenbach, 2 Excel 2003 For Dummies, Greg Harvey, 2 Excel 2003 For Dummies Quick Reference, John Walkenbach and Colin Banfield, 2

Excel Object Model, 54 Excel programming advantages of learning, 3 defined, 2 terminology, 12 EXCEL11.XLB file, 290 executing Function procedures, 72-74 Sub procedures, 65-71 Exp function, 124 exponentiation (A) operator, 103 exporting objects, 37

FacelD property (CommandBar controls),

301, 312 file size, 121-122 FileLen function, 121-122, 124 filenames GetOpenFilename method, 223-227 GetSaveAsFilename method, 227-228 finding bugs, 186-187 Fix function, 121, 124 fixed-length strings, 100 floating toolbars, 291 FollowHyperlink event, 153 Font property (Range object), 114 fonts fancy font names, 295 ranges (of cells), 114 VBA modules, 48 Format function, 124 formatting charts, 207 Formula property (Range object), 115 formulas converting to values, 21-22 ranges (of cells), 113-114 For-Next loops, 134, 144-147 forward slash (/) operator, 103 Frame control, 235, 253 full menus, 294

fully qualified object references, 55-56 Function procedures calling, 72-74 defined, 17, 63 descriptions, 74 End Function statement, 64 executing, 72-74 Function keyword, 64 naming, 65 recording, 64 running, 72-74

syntax, 64 VBA modules, 39 functions (built-in)

Abs, 123

arguments, 64, 327-335

Array, 123

Asc, 123

Atn, 123

Choose, 123

Chr, 123

Cos, 123

creating, 14

CurDir, 123

Date, 120, 123

DateAdd, 123

DateDiff, 123

DatePart, 123

DateSerial, 124

DateValue, 124

Day, 124

defined, 119

Dir, 124

Erl, 124

Err, 124

Error, 124

Exp, 124

FileLen, 121-122, 124 Fix, 121, 124 Format, 124 GetSetting, 124 Hex, 124 Hour, 124

InputBox, 122, 124, 221-223

InStr, 124

IPmt, 124

IsArray, 124

IsDate, 124

IsEmpty, 124

IsError, 124

IsMissing, 124

IsNull, 124

IsNumeric, 124

IsObject, 125

LBound, 125

LCase, 125

Left, 125

Log, 125

LTrim, 125

Mid, 125

Minute, 125

Month, 125

MsgBox, 125, 187-188, 216-221

#NAME error, 365 Now, 120, 125 RGB, 125 Right, 125 Rnd, 22, 125 RTrim, 125 Second, 125 Sgn, 125

Shell, 123, 125, 351-352

Sin, 125

Space, 125

Sqr, 125

Str, 125

StrComp, 125

String, 125

Tan, 125

Time, 120, 126

Timer, 126

TimeSerial, 126

TimeValue, 126

Trim, 126

TypeName, 122, 126 UBound, 126 UCase, 126 Val, 126 VarType, 126 Weekday, 126 Year, 126 functions (custom) argument descriptions, 337 arguments, 327-335 categories, 336-337 debugging, 333 defined, 131-132 descriptions, 335-336 inserting, 326, 335 limitations, 326 passivity, 326 uses, 325 writing, 327 functions (worksheet) add-ins, 340 arguments, 327-335 entering, 129-130 LARGE, 127 listing, 130 MAX, 127 MIN, 127 MOD, 130-131 PMT, 127-128 syntax, 126-127 VLOOKUP, 128-129 WorksheetFunction object, 126

General tab (Options dialog box), 48 GetOpenFilename method, 223-227 GetSaveAsFilename method, 227-228 GetSetting function, 124 Google search engine, 371 GoTo statement, 134-135 groups of toolbar buttons, 297

handling errors On Error statements, 177-178 Resume statements, 178-180 tips, 180

Harvey, Greg, Excel 2003 For Dummies, 2 HasFormula property (Range object), 113-114 height of UserForm controls, 250 help controls (UserForm object), 251 help system, 60-61, 369 Hex function, 124 hiding dialog box controls, 250 toolbars, 291-292, 304-305 hot keys custom dialog boxes, 263 defined, 4 Hour function, 124 hyperlinks for toolbar buttons, 297

icons for toolbar buttons, 294

If-Then structure, 134-139

ignoring errors, 180-181

Image control, 235, 254

Image property (UserForm controls), 250

images dialog box controls, 250, 254 toolbar buttons, 296-297 Immediate window (VBE), 34-35, 191-192 Imp operator, 103 importing objects, 37-38 indenting code, 47 InputBox function, 122, 124, 221-223 Insert Function dialog box, 73-74, 326 inserting code into VBA modules, 39-44 comments, 89 custom functions, 326, 335

inserting (continued) labels, 106 text strings, 13 UserForm objects, 233-234 InStr function, 124 Int function, 121, 124 Integer data type, 93 integer division (\) operator, 103 intentional errors, 182-184 Interior property (Range object), 114-115 Internet newsgroups, 370 IPmt function, 124 IsArray function, 124 IsDate function, 124 IsEmpty function, 124 IsError function, 124 IsMissing function, 124 IsNull function, 124 IsNumeric function, 124 IsObject function, 125

keypress events, 168-169 keywords

Call,71 Dim, 95 Function, 64 Preserve,106 Public, 97 Static, 97 Sub, 64

Label control, 235, 254-255 labels inserting, 106 syntax, 106 LARGE worksheet function, 127 late binding, 354 LBound function, 125 LCase function, 125 Left and Top property (UserForm controls), 250 Left function, 125 Len function, 121, 125

line continuation (underscore) character, 367 line numbers, 106 ListBox control adding, 255-256 example, 272-276 what it does, 235

listing

CommandBars collection, 299-300 constants, 99 projects (VBE), 36 toolbars, 291 worksheet functions, 130 loading add-ins, 341 Log function, 125 logical operators, 103 Long data type, 93 loops charts, 206 collections, 149-150 defined, 143-144 Do-Until, 134, 148-149 Do-While, 134, 147-148 For-Next, 134, 144-147 ranges (of cells), 200-201 LTrim function, 125

Macro commands (Tools menu) Macros, 25

Record New Macro, 23 Stop Recording, 24 Visual Basic Editor, 25, 33 macro recorder absolute mode, 78-79 comparison with tape recorder, 76 default naming system, 83 efficiency, 84-86 factors for success, 78 features, 76

how it works, 27, 81-82 limitations, 77, 365 relative mode, 79-80 step-by-step, 23-24, 42-43, 76-77 Stop Recording toolbar, 79 toolbar customization, 302 macros applications, 14 assigning shortcut keys, 83 automation, 13 bugs, 186

buttons (toolbars), 297 charts, 205

ConvertFormulas, 29-30 converting formulas to values, 21-22 custom add-ins, 14 custom commands, 13-14 custom dialog boxes, 215, 243-245 custom functions, 14

custom toolbar buttons, 13 data-entry templates, 14 defined, 12 descriptions, 83 disabling, 5-6 editing, 25-29 enabling, 5-6 Excel settings, 203-204 Excel versions, 19 naming, 23, 83

prompting for a cell value, 201-202 ranges (of cells), 195-196 running, 24-25 security settings, 5-6 shortcut keys, 24 stopping recording, 24 storing, 83 testing, 24 text strings, 13 viewing, 25-27 Macros dialog box, 25 MAX worksheet function, 127 maximizing VBE windows, 38 menu bar, 308 menu items adding, 315-316, 318-319 captions, 317 checked, 308 defined, 308 deleting, 316-317 enabled, 308 separator bar, 308 shortcut menus, 321-322 menus. See also controls adaptive, 294 animations, 295 captions, 317 code, 313

CommandBar objects, 289, 310-312

Control objects, 310-313

creating, 313-315

customizing, 308-309

defined, 308

deleting, 316

full, 294

restoring, 309, 318 shortcut menus, 308, 320-322 submenus, 308 message boxes customizing, 218-221 displaying, 216-217 multiline messages, 367 responses, 217

Index 383

methods ActivateMicrosoftApp, 352 arguments, 59 collections, 60 defined, 18, 56-57, 59 GetOpenFilename, 223-227 GetSaveAsFilename, 227-228 methods (Range object) Clear, 117 Copy, 116-117 Delete, 117 Paste, 116-117 Select, 116 Microsoft Excel blogs, 371

controlling from Microsoft Word, 355-358 controlling Microsoft Word, 355 newsgroups, 370 Object Model, 54 user groups, 371 versions, 19 Microsoft Excel 2003 Power Programming with

VBA, John Walkenbach, 2, 371 Microsoft Outlook automation, 358-360 Microsoft product support, 369 Microsoft Word automation, 352-354 controlling Excel, 355-358 controlling from Excel, 355 Mid function, 125 MIN worksheet function, 127 minimizing VBE windows, 38 minus sign (-) operator, 103 Minute function, 125 mod operator, 103 MOD worksheet function, 130-131 modifying add-ins, 349 charts, 205 Module window (VBE), 35 module-only variables, 96-97 modules adding to projects, 36-37 creating, 39 declarations, 39 empty, 367 font, 48

Function procedures, 39 inserting code, 39-44 removing from projects, 37 requiring variable declarations, 45 Sub procedures, 39 vertical margin indicator bar, 48 Month function, 125

moving buttons (toolbars), 295 dialog box controls, 261 ranges (of cells), 199-200 MsgBox function alternative to custom dialog boxes, 216 customizing, 218-221 debugging, 187-188 responses, 217 what it does, 125 multidimensional arrays, 105 MultiPage control, 235, 256 multiplication (*) operator, 103

#NAME errors, 365

Name property (UserForm controls), 237, 250 naming controls (UserForm object), 250 Function procedures, 65 macros, 23, 83 Sub procedures, 65 variables, 91-92 navigating the object hierarchy, 55 newsgroups, 370 NewSheet event, 152 non-Boolean settings, 204 noncontiguous ranges, 108 Not operator, 103 Now function, 120, 125 NumberFormat property (Range object), 115

Object Browser, 61-62 Object data type, 93 object model defined, 17 diagram, 54 object-oriented programming (OOP), 51 objects

ActiveX Data Objects (ADO), 360-362 Application object, 52 browsing, 61-62 collections, 53

CommandBar object, 289, 299-301, 310-312

Control objects, 310-313

defined, 51

events, 60

exporting, 37

importing, 37-38

methods, 56-57, 59-60

navigating the object hierarchy, 55 properties, 56-59 Range object, 107-111 references, 54-56, 209-210 UserForm object, 233-237 Workbook object, 52 Worksheet object, 52-53 WorksheetFunction object, 126 Offset property (Range object), 110 On Error statements, 177-178 OnAction property (CommandBar controls), 301, 313

one-dimensional arrays, 104-105 OnKey events, 168-169 OnTime event, 167-168 OOP (object-oriented programming), 51 Open event, 152, 157-159 opening add-ins, 341, 348 VBE (Visual Basic Editor), 25 operators addition (+), 103 And, 103

assignment (=), 102 backward slash (\), 103 concatenation (&), 103 division (/), 103 Eqv, 103

exponentiation (A), 103 Imp, 103

integer division (\), 103 logical, 103

mod (modulo arithmetic), 103 multiplication (*), 103 Not, 103 Or, 103

precedence order, 104 subtraction (-), 103 XoR, 103

optimizing performance of code, 207-208, 366 Option Explicit statement, 94 OptionButton control adding, 239-241 multiple sets, 278 properties, 256-257 what it does, 235 Options dialog box Docking tab, 48-49 Editor Format tab, 47-48 Editor tab, 45-47 General tab, 48 Or operator, 103 Outlook automation, 358-360

password-protection, 365-366 Paste Function dialog box, 129-130 Paste method (Range object), 116-117 Paste Special dialog box, 21-22 pasting ranges (of cells), 116-117 performance optimization of code,

207-208, 366 plus sign (+) operator, 103 PMT worksheet function, 127-128 positioning dialog box controls, 250 precedence order of operators, 104 Preserve keyword, 106 Procedure Separator option (VBE), 47 procedure-only variables, 95-96 procedures. See also Function procedures; Sub procedures defined, 40 naming, 65 product support from Microsoft, 369 program bugs. See bugs programming advantages of learning, 3 defined, 2

structured programming, 135 programming errors. See bugs programs, defined, 12 progress indicator, 280-283 Project Explorer window (VBE), 25-26, 34-35 projects adding VBA modules, 36-37 defined, 36 exporting objects, 37 importing objects, 37-38 listing, 36

removing VBA modules, 37 properties (charts), 206 properties (CommandBars collection) BeginGroup, 301, 313 BuiltIn, 301, 313 Caption, 301, 312, 317 Enabled, 301, 313 FaceID, 301, 312 OnAction, 301, 313 ToolTipText, 301, 313 Type, 299 properties (general) changing settings, 58-59 collections, 59 defined, 18, 56-58 examining settings, 58 read-only, 59

properties (Range object) Address, 113 Cells, 109 Column, 112 Count, 112 Font, 114 Formula, 115 HasFormula, 113-114 Interior, 114-115 NumberFormat, 115 Offset, 110 read-only, 111 Row, 112 Text, 112 Value, 111 properties (UserForm controls) Accelerator, 249 AutoSize, 249 BackColor, 249 BackStyle, 249 Caption, 249

changing, 235-236, 248-249 Image, 250 Left and Top, 250 Name, 237, 250 Value, 249 Visible, 250 Width and Height, 250 Public keyword, 97 public variables, 97

quotation marks (") in object references, 55

Range object methods Clear, 117 Copy, 116-117 Delete, 117 Paste, 116-117 Select, 116 Range object properties Address, 113 Cells, 109 Column, 112 Count, 112 Font, 114 Formula, 115 HasFormula, 113-114 Interior, 114-115 NumberFormat, 115

Range object properties (continued) Offset, 110 read-only, 111 Row, 112 Text, 112 Value, 111 ranges (of cells) addresses, 113 background color, 114-115 clearing, 117 contiguous, 108 copying, 116-117, 196-199 counting cells in a range, 112 defined, 107 deleting, 117

determining selection type, 202-203 editing values, 111 font, 114

formulas, 113-114

identifying multiple selection, 203

loops, 200-201

macros, 195-196

moving, 199-200

noncontiguous, 108

pasting, 116-117

prompting for a cell value, 201-202 references, 107-110 selecting, 116, 199, 276-277, 366 text strings, 112 values, 111 Record Macro dialog box, 23, 82 recording macros absolute mode, 78-79 comparison with tape recorder, 76 default naming system, 83 efficiency, 84-86 factors for success, 78 how it works, 27, 81-82 limitations, 77, 365 relative mode, 79-80 step-by-step, 23-24, 42-43, 76-77 Stop Recording toolbar, 79 toolbar customization, 302 ReDim statement, 105-106 RefEdit control, 235, 257, 276-277 references

CommandBars collection, 300-301, 310-312 objects, 54-56, 209-210 ranges, 107-110 removing breakpoints, 190 VBA modules from projects, 37 renaming toolbars, 292

Require Variable Declaration option (VBE), 45 resetting toolbar buttons, 293, 296 toolbars, 302 resizing dialog box controls, 261 restoring menus, 309, 318 toolbars, 290, 304-305 Resume statements, 178-180 RGB function, 125 Right function, 125 Rnd function, 22, 125 routines, defined, 40 Row property (Range object), 112 rows (worksheets), adding, 366 RTrim function, 125 running Function procedures, 72-74 macros, 24-25 Sub procedures, 65-71

scope constants, 98 variables, 95-98 screen updating, 208 ScreenTips, 295 ScrollBar control, 235, 258 Second function, 125 security macros, 5-6

password-protecting code, 365-366 settings, 5-6 viruses, 5 Select Case structure, 134, 140-143 Select method (Range object), 116 selecting ranges (of cells) dialog boxes, 276-277

to the end of a row or column, 198-199, 366 Select method, 116 SelectionChange event, 153 sending e-mail, 358-360 separator bar (menu items), 308 Sgn function, 125 SheetActivate event, 152 SheetBefore DoubleClick event, 152 SheetBefore RightClick event, 152 SheetCalculate event, 153 SheetChange event, 153 SheetDeactivate event, 153 SheetFollowHyperlink event, 153 SheetSelectionChange event, 153

Shell function, 123, 125, 351-352 shortcut keys assigning to macros, 83 macros, 24 shortcut menus, 308, 320-322 Show method (UserForm object), 237 Sin function, 125 Single data type, 93 Space function, 125 spacing dialog box controls, 261 SpinButton control, 235, 258-259, 278-280 Sqr function, 125

starting another application from Excel,

351-352 statements AppActivate, 352 assignment statements, 101-102 Const, 98

converting into comments, 90 Debug.Print, 189 Do-Until loop, 134, 148-149 Do-While loop, 134, 147-148 End Function, 64 End Sub, 64

For-Next loop, 134, 144-147 GoTo, 134-135 If-Then structure, 134-139 On Error, 177-178 Option Explicit, 94 ReDim, 105-106 Resume, 178-180

Select Case structure, 134, 140-143 With-End With structure, 211 Static keyword, 97 static variables, 97-98 stepping through code, 192 Stop Recording toolbar, 79 stopping macro recording, 24 storing event-handler procedures, 154 macros, 83 Str function, 125 StrComp function, 125 String data type, 93 String function, 125 strings concatenation, 103 declaring, 100 defined, 100 fixed-length, 100 getting length, 121 ranges (of cells), 112 variable-length, 100

structured programming, 135 Sub procedures calling, 65-71 defined, 16, 63 End Sub statement, 64 event-handler procedures, 154 executing, 65-71 naming, 65 running, 65-71 Sub keyword, 64 syntax, 64 VBA modules, 39 submenus, 308

subscript out of range error, 366 subtraction (-) operator, 103 syntax comments, 89-90 errors, 45, 186 Function procedures, 64 labels, 106 Sub procedures, 64 worksheet functions, 126-127 system date/time, displaying, 120

tab order in dialog boxes, 262-263 tabbed dialog boxes, 283-284 TabStrip control, 235, 259 Tan function, 125 task automation, 13 templates for data-entry, 14 testing add-ins, 346

custom dialog boxes, 263 macros, 24 text on toolbar buttons, 297 Text property (Range object), 112 text strings concatenation, 103 declaring, 100 defined, 100 fixed-length, 100 getting length, 121 inserting, 13 ranges (of cells), 112 variable-length, 100 TextBox control, 235, 259-260, 278-280 Time function, 120, 126 Timer function, 126 TimeSerial function, 126 TimeValue function, 126

ToggleButton control, 235, 260 toolbar buttons adding, 296 copying, 295 creating, 13 deleting, 296 groups, 297 hyperlinks, 297 icons, 294 images, 296-297 inserting, 295 macros, 297 moving, 295 resetting, 293, 296 ScreenTips, 295 text, 297 toolbars attaching to workbooks, 293, 297-298 autosensing, 293

CommandBar objects, 289, 299-301 creating, 292 customizing, 289-290 deleting, 292

displaying, 291-292, 302-304 distributing, 297-298 docked, 291

EXCEL11.XLB file, 290 floating, 291

hiding, 291-292, 304-305 listing, 291 macro recorder, 302 renaming, 292 resetting, 302 restoring, 290, 304-305 rows (one or two), 294 Visual Basic Editor (VBE), 34-35 Tools, Macro menu commands Macros, 25

Record New Macro, 23 Stop Recording, 24 Visual Basic Editor, 25, 33 ToolTipText property (CommandBar controls), 301, 313 Trim function, 126 turning on/off alert messages, 209 automatic calculation, 208-209 screen updating, 208 Type property (CommandBars object), 299 TypeName function, 122, 126

UBound function, 126 UCase function, 126

underscore (line continuation) character, 367 Undo command (Edit menu), 28 unloading add-ins, 341 unlocking add-ins, 349 User defined data type, 93 user groups, 371 UserForm control properties Accelerator, 249 AutoSize, 249 BackColor, 249 BackStyle, 249 Caption, 249

changing, 235-236, 248-249 Image, 250 Left and Top, 250 Name, 237, 250 Value, 249 Visible, 250 Width and Height, 250 UserForm controls accelerator keys, 249 adding, 234-235, 247-248 aligning, 261 automatic resizing, 249 background colors, 249 background styles, 249 captions, 249

changing properties, 235-236, 248-249

CheckBox, 235, 251-252

ComboBox, 235, 252

CommandButton, 235, 238-239, 253

event-handling procedures, 241-243

Frame, 235, 253

height, 250

help, 251

hiding, 250

Image, 235, 250, 254

Label, 235, 254-255

ListBox, 235, 255-256, 272-276

moving, 261

MultiPage, 235, 256

Name property, 237

naming, 250

OptionButton, 235, 239-241, 256-257, 278 positioning, 250

RefEdit, 235, 257, 276-277 resizing, 261 ScrollBar, 235, 258 spacing, 261

SpinButton, 235, 258-259, 278-280 TabStrip, 235, 259 TextBox, 235, 259-260, 278-280 ToggleButton, 235, 260 values, 249 width, 250 UserForm objects adding controls, 234-235 Code window, 236 inserting, 233-234 Show method, 237 testing, 263

Val function, 126

Validation command (Data menu), 164-165 Value property Range object, 111 UserForm controls, 249 values assigning to variables, 91 constants, 98

converting formulas to values, 21-22 data types, 93 integer portion, 121 ranges (of cells), 111 UserForm controls, 249 variable-length strings, 100 variables arrays, 104-105 assigning values, 91 declaring, 94, 210-211 defined, 18, 91 module-only, 96-97 naming, 91-92 procedure-only, 95-96 public, 97 scope, 95-98 static, 97-98 Variant data type, 93-94 VarType function, 126 VB (Visual Basic), 11 VBA Debugger breakpoints, 189-191 Immediate window, 191-192 watch expressions, 192-193

VBA modules adding to projects, 36-37 creating, 39 declarations, 39 empty, 367 font, 48

Function procedures, 39 inserting code, 39-44 removing from projects, 37 requiring variable declarations, 45 Sub procedures, 39 vertical margin indicator bar, 48 VBA (Visual Basic for Applications) advantages, 15 defined, 11 disadvantages, 15-16 help system, 60-61, 369 Object Browser, 61-62 uses, 16-18 VBE (Visual Basic Editor) activating, 33 Auto Data Tips option, 46 Auto Indent option, 47 Auto List Members option, 46 Auto Quick Info option, 46 Auto Syntax Check option, 45 Code Colors option, 47 Code window, 34-35 customizing, 44-49

Default to Full Module View option, 47 Drag-and-Drop Text Editing option, 47 Immediate window, 34-35 listing projects, 36 maximizing windows, 38-39 menu bar, 34

minimizing windows, 38-39 Module window, 35 opening, 25

Procedure Separator option, 47 program window, 34 Project Explorer window, 25-26, 34-35 Require Variable Declaration option, 45 shortcut menus, 34 toolbars, 34-35 versions of Excel, 19 vertical margin indicator bar

(VBA modules), 48 viewing code, 25-27 macros, 25-27 viruses, 5

Visible property (UserForm controls), 250 Visual Basic Editor (VBE). See VBE

(Visual Basic Editor) Visual Basic for Applications. See VBA

(Visual Basic for Applications) Visual Basic (VB), 11 VLOOKUP worksheet function, 128-129

Walkenbach, John Excel 5 For Windows Power Programming

Techniques, 16 Excel 2003 Bible, 2

Excel 2003 For Dummies Quick Reference, 2 Microsoft Excel 2003 Power Programming with VBA, 2, 371 Web site, 370 watch expressions, 192-193 Web site for this book's author, 370 weblogs, 371 Weekday function, 126 Width and Height property

(UserForm controls), 250 width of UserForm controls, 250 WindowActivate event, 153 WindowDeactivate event, 153 WindowResize event, 153 With-End With structure, 211 Word automation, 352-354 controlling Excel, 355-358 controlling from Excel, 355 Workbook object, 52

workbooks attaching toolbars, 293, 297-298 converting to add-ins, 341-342 events, 152-153 worksheet functions add-ins, 340 arguments, 327-335 entering, 129-130 LARGE, 127 listing, 130 MAX, 127 MIN, 127 MOD, 130-131 PMT, 127-128 syntax, 126-127 VLOOKUP, 128-129 WorksheetFunction object, 126 Worksheet object, 52-53 worksheets adding rows or columns, 366 events, 153 writing custom functions, 327 event-handler procedures, 155, 157

XLA file extension, 340

xlCalculationManual constant, 99 XoR operator, 103

Year function, 126

0 0

Post a comment