R

Raise method, 333

RaiseEvent statement, 358

random numbers, generating, 61-62

Randomize statement, 358

Range method, 125, 153-154, 171, 175

Range object, 125

in Excel defining range names, 162 inserting data into ranges, 162-163 resizing ranges,

163-164 returning data about ranges, 163 returning with Cells method, 154 returning with

Columns method, 156 returning with Offset method, 157-158 returning with Range method, 153-154 returning with Rows method, 155-156 selecting ranges, 158-161 in Word, 153

Bold property, 126 Case property, 126 Delete method,

128-129 deleting text, 128-129 Font property, 126 formatting text, 126 InsertAfter method, 127 InsertBefore method, 127 inserting text, 127-128

Remove command (File menu) 397

InsertParagraph method, 127 InsertParagraphAfter method, 127 InsertParagraphBefore method, 127 InsertParagraphsAnd Text procedure, 127-128 Italic property, 126 reading and changing range text, 126 returning with Range method, 125 returning with Range property, 125

Range property, 125

ranges. See Range object rate function, 62-63, 365

ReadInboxData procedure,

233-234 reading application settings from

Registry, 306-307 range text (Word), 126 section settings, 309 ReadReceiptRequested property (MailItem object), 221 recalculating worksheets, 140, 319

ReceivedTime property (Mailltem object), 221 RecentFiles object, 73,

116-117 Recipients object, 226

Recipients property

(Mailltem object), 221 Record Macro command

(Macros menu), 5-7 Record Macro dialog box, 5-7

recording macros

Excel macros, 7-8 overview, 3 Word macros, 5-7

records adding, 205-206 deleting, 207-208 editing, 203-205 finding, 202-203 navigating, 199-201

Recordset object accessing recordset data, 198-199 adding records, 205-206 deleting records, 207-208 editing records, 203-205 finding records, 202-203 methods, 199

AddNew, 205-206 Delete, 207 Find, 202 GetRows, 208 Move, 200 MoveFirst, 200 MoveLast, 200 MoveNext, 200 MovePrevious, 200 Open, 193-195 Update, 204 navigating records,

199-201 opening with Open method, 194-196 opening with SELECT

statement, 196-198 opening with tables,

193-194 overview, 192-193 properties, 195

BOF property, 200 EOF property, 200 Fields property, 198

RecordsetBookmark Navigation procedure, 201

RecordsetData procedure, 199 RecordsetOpenProperties procedure, 196 RecordsetOpenSELECT procedure, 197

RecordsetOpenTable procedure, 193-194 recordsets. See also RecordSet object accessing recordset data, 198-199 adding records, 205-206 deleting records, 207-208 editing records, 203-205 exporting from Access to

Excel, 210-212 finding records, 202-203 navigating records,

199-201 opening with tables,

193-198 overview, 192-193

ReDim statement, 42, 358

referencing

Outlook folders, 214 Outlook from other applications, 231

Registry deleting settings from, 307 overview, 305 reading section settings, 309 reading settings from,

306-307

storing settings in, 306 tracking file usage,

307-309

Relationship element (XML), 265, 269

Rem statement, 358 Remove command (File menu), 301

398 Index

RemoveChapter14Setting procedure

RemoveChapter14Setting procedure, 309

Removeltem method, 253 removing attachments from forwarded messages, 229-230 modules, 301 renaming files/folders, 316 modules, 299-300 Replace function, 64, 369 Reply method, 225 ReplyAll method, 225 replying to email, 225 Reset statement, 359 ResetKey procedure, 143 resetting controls, 293-295 Resize method, 163

resizing controls, 244 Excel ranges, 163-164

Resume statement,

327-329, 359 resuming program execution, 327-329 RetrieveProducts procedure, 210-212

RetrievingEntireRecords procedure, 209 RGB function, 80, 99, 367

Ribbon customization advantages, 270 controls attributes, 276-277 buttons, 277 check boxes, 281-282 combo boxes, 288 dialog box launchers, 289

drop-down lists, 284-285

galleries, 285-287 getting and changing control values, 295-297 initializing, 290-293 menus, 278-280 resetting, 293-295 split buttons, 280-281 toggle buttons, 282-283 Custom UI Editor, 270 extensibility, 263-265 groups creating, 274 customizing existing groups, 274-275 hiding built-in Ribbon,

270-271 macro-enabled documents/templates, creating, 265-267 MyRibbon.xml file adding to document package, 268-269 creating, 267-268 renaming and opening, 269 overview, 263 RibbonX, 270 tabs creating, 271-272 customizing existing tabs, 272-273 ribbon element (XML), 271

RibbonX, 270

controls attributes, 276-277 buttons, 277 check boxes, 281-282 combo boxes, 288 dialog box launchers, 289 drop-down lists,

284-285 galleries, 285-287

getting and changing control values, 295-297 initializing, 290-293 menus, 278-280 resetting, 293-295 split buttons, 280-281 toggle buttons, 282-283 elements checkBox, 281 comboBox, 288 dialogBoxLauncher, 289

dropdown, 284 gallery, 285-287 group, 274-275 menu, 278

Relationship, 265, 269 ribbon, 271 splitButton, 280 tab, 272-273 tabs, 271

toggleButton, 282-283 groups creating, 274 customizing, 274-275 hiding built-in Ribbon,

270-271 tabs creating, 271-272 customizing, 272-273 Right function, 65, 369 Right$ function, 65, 369 RightB function, 369 RightB$ function, 369

RightToLeft property

(forms), 240 RmDir statement,

316-318, 359 Rnd function, 61, 365

Root Certificate

Program, 304 Round function, 365

Selection object 399

Row property (Range object), 163

rows exporting from Access to

Excel, 208-210 returning, 155-156

Rows method, 155-156 RowSource property (ListBox object), 252

RSet statement, 359 RTrim function, 65, 369 RTrim$ function, 65, 369 rules (Outlook), 223-224 Run method, 187-188

Run To Cursor command

(Debug menu), 343 RunJugglingSlideShow procedure, 187-188 running macros, 8, 21-22 PowerPoint slide shows,

187-188 procedures at specific times, 144 when user selects Repeat or Undo, 144-146 runtime errors, 338-339

Save method

Document object, 119 Presentation object, 168 SaveAll procedure, 149

SaveAs method

Attachment object, 229 Document object, 120 Presentation object, 168 SaveChanges_OnAction procedure, 279

Saved property

(Presentation object), 167 SaveSetting statement,

306, 359 saving

Excel workbooks, 148-149 Word documents, 118 with Save method, 119 with SaveAs method, 120

screen updating, turning off, 319

screentip attribute

(controls), 277 ScreenUpdating property (Application object), 319 ScrollBar object, 253

ScrollBars property

(forms), 241 ScrollHeight property

(forms), 242 Scrolling properties (forms), 241-242

ScrollLeft property

(forms), 242 ScrollTop property

(forms), 242 ScrollWidth property (forms), 242

SearchRecordsWithFind procedure, 202-203 Second function, 69, 363 section settings, reading, 309

security digital signatures, 304-305 macros macro security levels,

302-304 overview, 301 trusted locations, 301-302

Seek function, 364 Seek statement, 359

Select All command (Edit menu), 243 Select Case statement, 97-101, 359

FutureValue4()

example, 98 LetterGrade() example,

98-99 syntax, 97

VBAColor() example,

99-101

Select Certificate dialog box, 305 Select Database dialog box, 192 Select method, 129, 182

SELECT statement,

196-198 SelectA1OnAllSheets procedure, 158-159 SelectCurrentNamed Range procedure, 161

SelectHomeCells procedure, 159-160 selecting controls, 243 Excel cells/ranges selecting A1 on all worksheets, 158-159 selecting home cell on all worksheets, 159-160 selecting named range that contains active cell, 160-161

selection handles, 243

Selection object

Collapse method, 132 collapsing selection, 132 creating, 129

400 Index

Selection object extending selection,

131-132 MoveEnd method, 130 MoveStart method, 130 moving insertion point, 130 overview, 129 Type property, 129

SendAMessage procedure, 227 SenderEmailAddress property (MailItem object), 221

SenderName property

(MailItem object), 221 sending email messages,

226-227 SendKeys statement, 359

Sensitivity property

(MailItem object), 221 Sentences object, 133-135 SentOn property (MailItem object), 221

serial numbers, 68 sessions (Outlook)

logging off, 233-234 logging on, 232 Set statement, 79, 359 SetAttr statement, 318, 359 SetKey procedure, 143

SetUpJugglingSlides procedure, 183-185 SetUpStartPage procedure, 180-181 Sgn function, 61, 365

Shadow property (Shape object), 179 Shape objects (PowerPoint)

adding to slides, 175-178 methods, 176-178, 181-182

properties, 178-180 specifying, 174-175 Shapes property (Slide object), 173 Shell function, 364

shortcut keys assigning to Excel macros, 10-11 assigning to Word macros, 9-10 Show method, 85 ShowModal property (forms), 240

ShowType property (SlideShowSettings object), 187 ShowWithAnimation property

(SlideShowSettings object), 187 ShowWithNarration property

(SlideShowSettings object), 187 shutting down Visual Basic Editor, 30

signatures, digital, 304-305 Sin function, 61, 365 Single data type, 37

size attribute

(controls), 276 Size property (MailItem object), 221 sizing controls, 244 slash (/), 55 Slide objects adding to presentations, 171 methods, 174 properties, 172-173 Range method, 171 specifying, 170-171

slide shows (PowerPoint)

running, 187-188 settings, 187 transitions, 186 SlideMaster property (Presentation object), 167

slides (PowerPoint)

creating, 171 inserting from files, 172 methods, 174 properties, 172-173 specifying, 170-171

Slides collection

Add method, 171 InsertFromFile method, 172

Slides property

(Presentation object), 167 SlideShowSettings object, 187

SlideShowSettings property (Presentation object), 167, 187

SlideShowTransition property (Slide object), 173 SlideShowTransitions object, 186 SLN function, 63, 365

SmallChange property (ScrollBar object), 253 Sort method, 75

Source property

Err object, 330 Recordset object, 195

Space function, 65, 369 Space$ function, 65, 369 spam, filtering, 224-225 SpecialEffect property

(forms), 239 spell checking, 87

statements 401

SpellCheckTest procedure, 87

spin buttons, 253-254 SpinButton object,

253-254 split buttons (Ribbon), creating, 280-281

Split function, 370 splitButton element (XML), 280

Sqr function, 61, 366 StandardHeight property (Worksheet object), 151 StandardWidth property (Worksheet object), 152

StartingSlide property (SlideShowSettings object), 187

StartUpPosition property

(forms), 241 statements. See also functions; methods; loops; procedures AppActivate, 356 Beep, 356 Call, 356 ChDir, 315, 356 ChDrive, 315, 356 Close, 356 comments, 22 Const, 45, 356 Date, 356 Declare, 356 DefBool, 356 DefByte, 356 DefCur, 356 DefDate, 356 DefDbl, 356 definition of, 355 DefInt, 356 DefLng, 356 DefObj, 356 DefSng, 356

DefStr, 356 DefType, 39 DefVar, 356 DeleteSetting, 307, 357 Dim, 33-34, 357 Do...Loop, 105-106, 357 End, 357 entering, 22-23 Enum, 357 Erase, 357 Error, 357 Event, 357

executing in Immediate window, 350 Exit, 357 Exit Do, 110 Exit For, 110 FileCopy, 315, 357 For Each...Next, 109, 357 For...Next, 106-108, 357 Function, 38, 357 Get, 357

GetAllSettings, 309 GetSetting, 306-307 GoSub...Return, 357 GoTo, 357 If...Then

And operator, 95 block syntax, 92 example, 92-93 Or operator, 95 single-line syntax, 92 If...Then...Else, 357 example, 93-94 indenting for readability, 94 multiple If...Then...Else statements, 95-96 syntax, 93 Implements, 357 indenting for readability, 111 Input, 357 Kill, 315-316, 357

Let, 357 Line Input, 357 Load, 258, 357 Lock, 357 LSet, 358 MkDir, 316, 358 Name, 316, 358 On Error, 358 On Error GoTo, 325 On Error Resume

GoTo, 358 Open, 358

Option Base 011, 358 Option Compare TextlBinary, 358 Option Explicit, 351, 358 Option Private, 358 Print, 358 Private, 358 Property Get, 358 Property Let, 358 Property Set, 358 Public, 358 Put, 358 RaiseEvent, 358 Randomize, 358 ReDim, 42, 358 Rem, 358 Reset, 359

Resume, 327-329, 359 RmDir, 316-318, 359 RSet, 359

SaveSetting, 306, 359 Seek, 359 SELECT, 196-198 Select Case, 359 FutureValue4()

example, 98 LetterGrade()

example, 98-99 syntax, 97 VBAColor() example, 99-101 SendKeys, 359

402 Index statements

Set, 79, 359 SetAttr, 318, 359 Static, 359 Stop, 342, 359 Sub, 359 Time, 359 Type, 359 Unload, 259, 359 Unlock, 359 While...Wend, 359 Width, 359 With...End With, 359 Write, 359

Static statement, 359 status bars, displaying messages in, 82 StatusBar property (Application object), 82

Step Into command

(Debug menu), 340, 343 Step Out command

(Debug menu), 343 Step Over command (Debug menu), 343

stepping into procedures, 343 stepping out of procedures, 343 stepping over procedures, 343

stepping to cursor, 343 Stop Recording command

(Macros menu), 7-8 Stop statement, 342, 359

StoreWorksheetNames procedure, 42-43 storing application settings in

Registry, 305-306 user input in variables, 45 InputBox function,

50-51 MsgBox function, 45-49

Str function, 65, 370 Str$ function, 65, 370 StrComp function, 65, 370 StrConv function, 65 String data type, 36 String function, 65, 370 String$ function, 65, 370

strings concatenating, 157 converting into objects,

140-141 String data type, 36 string expressions, 63-66 string functions, 65, 368-370

StrReverse function, 370 Style property (ComboBox object), 252 Sub statement, 359

Subject property (MailItem object), 221 subscripts, 41

subtraction operator (-), 55

supertip attribute (controls), 277 supplementing reminders with email messages, 227-228 Switch function, 103-104, 367

SYD function, 63, 365 syntax checking, 351 syntax errors, 23, 338

0 0

Post a comment