Engram 9 VBA Scripts

Magic Squares Spreadsheet Selectionchange Procedure

Page object, 224

Pages collection object, MultiPage control and, 223 PageSetup object, PrintArea property of, 324 Parameters, 40, 41

with event procedures, 57-59 Parent class, 151

Parentheses, order of operator execution and, 92 PassByRef() procedure, 64 PassByVal() procedure, 64 PasswordChar property, 299 Paste() method, 121

Path property, of Workbook object, 281 Period (.), avoiding in XML element names, 336

Perl, 151

Picture property, 13, 79, 241, 317 Pie chart, 384 PieClock.xls workbook, 388 PlaceComputerShips() sub procedure, 193, 196 PlaceWord sub procedure, 317, 318, 322 PlayerFire() sub procedure, 191, 192, 197 PlayWav() sub procedure, 204, 246 Point object, 394, 395, 406, 420, 422 Points, 228

Poker Dice program, 55, 77-97 Check Box control in, 80 coding, 81-96 designing, 78-81 Image control in, 78-80 locating code for, 80-81 property settings of check box controls in, 81 requirements for, 77 resetting game board in, 82-84 rolling the dice in, 84-87 scoring hand in, 87-96 selecting dice in, 81-82 PowerDB() function, 66, 67, 68 .ppt files, 280 Preserve keyword, 117, 144 PrintArea property, of PageSetup object, 324 Print() method, of Debug object, 276 PrintOut() method of Range object, 373 of Worksheet object, 325 Print Puzzle control, 301 Private keyword, 15, 60, 65, 112, 211 custom data types and, 234 variables declared with, 31 Procedural level variable, 30 Procedure parameters, 141 Procedures event, 56-59, 97 function, 64-65, 97 passing variables to, 235 recursive, 105 VBA, 56-68 ProcessBoard() sub procedure, 462, 464, 466 ProcessChart() sub procedure, 408, 409, 414, 415, 421, 423

ProcessRow() sub procedure, 462 Programming, event-driven, 2 Programming components within Excel, 10-15 macro selection, 10 Visual Basic toolbar, 11-15 Programming procedures, 56

Programs

Alienated Game, 377, 396-425 Battlecell, 149-150, 246

Biorhythms and the Time of Your Life, 25, 47-53 Blackjack, 208 Colorful Stats, 17-22 Excetris, 427, 439-471 Fun with Strings, 43, 45-47 Math Game, 100, 123-147, 329-330, 349-372 Poker Dice, 55, 77-97 Word Find, 269, 296-326 Project Explorer window, 7, 8 Projects, forms added to, 209 Properties, 150, 162 Properties window, 8

of Command Button control, 13 Proprietary files, 280 Public keyword, 60, 65, 112, 211 custom dta types and, 234 variables declared with, 31 Public procedures for Blackjack program, 247-248 general purpose, 246-247 Put statement, 295 PuzzleList custom data type, 302 Puzzles, word search, 296

QueryClose() event, 211, 212, 313

of UserForm object, 232, 245, 266 Quotes, around XML attributes, 335-336

RAD. See Rapid application development Random access files, 294-295, 302 Randomize() function, 85 Random numbers, 72

Random questions, generating in Math Game program, 138-140

Range object, 110, 115, 122, 150, 153, 159, 161, 169, 170-172, 175, 176, 195, 199, 348, 409, 442 in Battlecell program, 195, 199, 205 Cells property of, 172-173 ClearContents() method of, 248 PrintOut() method of, 373 Row property of, 265 Select() method of, 174

Range property, 106, 159, 171, 174 of ListObject object, 348 of Shapes collection object, 433, 434 Ranges, For/Each loops and looping through, 175-177 RangeValid() function procedure, 190 Rapid application development (RAD), 5 rcInc array, 459 ReadSeqFile() procedure, 294 recNum integer variable, 302 recNum variable, 295 Recorded macros chart types and, 391 With/End With structure in, 173-174 Record Macro tool, 10, 11 Records, 294-295 numbers of, 295 updating, 311 Rectangle, adding to worksheet, 430 Recursive procedures, 105 Re-dimensioning dynamic arrays, 116, 117, 119 ReDim keyword, 116, 117 ReDim statement, 408 RefEdit control, 220-222, 226, 230, 266 Refresh control, 300

Refresh() method, of Xml-DataBinding object, 346 Remarks, 20

RemoveImages() sub procedure, 408, 415 RemoveItem() method, List Box control, Combo Box control and, 226 Requirements for Alienated Game program, 397 for Battlecell program, 177-180 for Blackjack program, 237-239 for Colorful Stats program, 17, 18 for Excetris program, 439-441 for Math Game program, 123-124 for new Math Game program, 350-351 for Poker Dice program, 77 for Word Find program, 296-298 Resizing windows, 168, 169

Resizing workbook window, in Battlecell program, 181, 182

Resume Next clause, 271 Return data type, 66 Reusability, objects and, 151 RGB() function, 220, 431, 448 Rnd function, 72

RootElementName property, Xml Map object and, 346 Root elements, in XML documents, 334 RotateCC() sub procedure, 454 Row property, of Range object, 195, 265

Rows, in spreadsheets, 109 Rows property, 169, 248 of Range object, 195 Run Macro, 11 Run time, 9

Runtime error dialog box, 274 Runtime errors, 270, 271, 273, 326, 452

SaveAs() method, of Workbook object, 281, 283, 343 SaveAsXmlData() method, 347 Save() method, of Workbooks collection, 281 ScanBoard() procedure, 469 ScanImages() function, 408, 409, 410, 411, 423 Scanning charts, in Alienated Game program, 408-420 ScanRange() sub procedure, 461, 462 ScanRowOrCol() function procedure, 409, 411 Scatter chart, 384, 391

in Biorhythms and the Time of Your Life program, 49 data series plotted in, 395 Schedule parameter, 141 Schemas, XML, 337-338

Schemas property, Xml Map object and, 346, 347 Scope, 30

ScoreAnswers() function, 370

ScoreAnswers() sub procedure, 142, 145

Scored images, removing from chart in Alienated Game program, 415, 416 Score sequences, in Alienated Game program, 396 Scoring filled rows, in Excetris program, 462, 465 Scoring hand, in Poker Dice program, 87-96 Screen object, 168 ScreenUpdating property, 161 Scroll Bar control, 216-218, 266

selected properties of, 217 Scroll bars, 216

Scroll() event, Scroll Bar control and, 217 Search engines, 38 Security, 3

Select/Case structure, 75-76, 97, 99 Selectedltem property, 224

Select() event procedure, of Chart object, 394, 395, 396,

399, 402, 420 SelectionChange() event procedure, 56, 169, 181 of Magic Squares spreadsheet, 34-35 of Worksheet object, 188, 189, 190, 191 Selection property, of Window object, 174 SelectionValid() function, 318, 319, 321 SelectLines() procedure, 434, 435

Select() method of Range object, 174 of Worksheet object, 170 Semicolon (;), between widths in ColumnWidths property, 228 SendResult() sub procedure, 62 Sequential access files, 292-294 SeriesCollection collection object, 387 Series Collection object, 405, 406 Series object, 387, 389, 391, 394, 395, 406, 420, 422 SetActiveRange() sub procedure, 452, 461, 465 SetChartType() sub procedure, 385 SetColumnWidth() macro, 460 SetFirstCell() sub procedure, 193, 195 SetKeys() sub procedure, 447 Set keyword, 175

SetTargetCell() function procedure, 197 ShapeDemos.xls workbook, 432, 433 Shape objects, 428-432, 442, 471 activating, 435

looping collection of, 431-432 manipulating, 430-432 ShapeRange collection object, 432-434, 465 ShapeRange object, 452

ShapeRange property, of Selection object, 432 Shapes adding to worksheets, 429 creating in Excetris program, 441-442 moving in Excetris program, 442-443 removing in Excetris program, 444 Shapes collection, 428-432, 469 Shapes collection object, 471 Shapes property, of Worksheet object, 428 Sheets collection object, 170, 379 Shift variable, 59

Ship locations, in Battlecell program, 188-198 ShowFileDialog() sub procedure, 288 Show() method, 213

of FileDialog object, 284, 287 form loading and, 214 modal forms and, 214, 215 shuffling deck for Blackjack program and, 248 ShuffleDeck() sub procedure, shuffling deck for

Blackjack program and, 249, 251 Shuffling deck of cards, in Blackjack game, 244, 248-252 Shuffling form, in Blackjack game, 242, 244 Single data type, 31, 32 Sinusoidal cycles, biorhythms in, 48 sndPlaySoundA() function, 204 Sound, adding to Battlecell program, 182, 203-205

Sound files in Blackjack game, 242

shuffling deck for Blackjack program and, 249 Spaces, removing in strings, 108 Spaghetti code, 271 SpecialEffect property, 211, 299 Splitting, in Blackjack game, 267 Spreadsheet applications, charts used in, 378 Spreadsheet cell, input validation with, 109 Spreadsheets

Biorhythms and the Time of Your Life, 26 Magic Squares, 34 Poker Dice, 56 SQL. See Structured Query Language Stacked chart type, 391

standalone document declaration, in Excel, 354

Standard code window, 10

Standard module, 29

Standard toolbar, 7

Static keyword, 30

Statistics, custom dialog for, 226-233

STDEV() function, 65, 68

STDEVP() worksheet function, 271

Stepping through code, break mode and, 275

Stop Recording button, 128

StoreQuestions() sub procedure, 143, 144

StoreResults() sub procedure, 370

StrConv() function, 47

String concatenation, 47, 145

String data types, 37-38, 47

String functions, VBA, 44

String keyword, 37

String manipulation, with VBA functions, 42-47

Strings, character locations in, 46

String variables, in Biorhythms and the Time of Your Life program, 52 StrReverse() function, 47 Structured Query Language (SQL), 296 Student lists, maintaining in new Math Game program,

356-357, 361-363 Style property of Combo Box control, 228, 351 MultiPage control and, 223 Sub keyword, 15, 57 Sub procedures, 56, 60-63, 97 Subroutine procedures, 60 Subtraction operator (-), 33 SUM() function, 65, 121

Summary Statistics dialog, 228 Stats page on, 233 Summary page on, 233 Super-computers, 178 Symbols +, 33 &, 47 =, 33

:, 162, 271, 336 <, 69 >, 69 <=, 69 >=, 69 -, 33, 336 /, 33, 334 *, 33, 145 33

., 235, 295, 336 $, 120 _ , 89, 92 :=, 122 ", 335-336 ;, 228 Syntax, 2

for addressing objects in VBA, 154 errors, 270