EXCEL VBA programing

488 Index Variant keyword, 38 Version property, 215 Variant variables, Battlecell program and, 186 vertical argument, 322 VBA View Code icon, 13, 211 Viruses, 3 collection objects in, 153–155 Visible property, 308 color constants in, 220 VisibleRange property, of Window object, 169, 185 common data types in, 32 Visual Basic (VB), for applications with Excel, 1–23 creating function procedures in, 65–66 Visual Basic Editor icon, from Visual Basic toolbar, 5, 11 defining custom data types in, 234–235 Visual Basic toolbar, 6, 10, 11–15 derived data types in, 233–237 designing forms with, 208–233 W enumerated types defined in, 235–237 Excel application functions in, 68 Watch Expression, 278 file access modes with, 292 Watch type, selecting, 278 For/Next loops in, 104 Watch window, 277–279 installing and enabling, 3–4 Wave Form Audio (.wav file), 203 I/O with, 40–42 logical operators with, 69–71 for Blackjack game, 242 looping with, 100–106 Wav files, playing via Windows API, 204–205 mathematical operators in, 33 Web pages, 330 object-oriented programming and, 150–151 Weekdays enumerated type, 236 saving workbook from program in, 281 what argument, outputting results of hand to Blackjack string functions, 44 XML and, 343–344 worksheet and, 265 VBA debugger, 270 While keyword, 101 VBA file I/O methods, 282–295 Width property, 59, 160, 212, 241 FileDialogFilters collection object, 284–288 FileDialog object, 283–284 of Application object, 168 FileDialogSelectedItems collection object, 284–288 Excetris program and, 460 FileSystem object, 288 Window object, 155, 161–169, 205 Open statement, 289 WindowResize() event, of Workbook object, 169, 182, random access files, 294–295 sequential access files, 292–294 183, 185 VBA help, accessing, 16–17 Windows API (Application Programming Interface), VBA IDE (Integrated Design Environment), 1, 5–10 accessing from Visual Basic toolbar, 6 203–205 in break mode, 274–275 WindowState property, 168 components of, 5–10 With/End With structure, 173–175, 205, 388 getting help with, 15–17 Wizards, 220 Object Browser in, 155 Word Find program, 269, 296–326 VBA library, 156 VBA program, sound added to, 203–205 designing, 298–301 VBAProject library, 156 form design, 298–300 VBA shapes, 427–472 requirements for, 296–298 activating Shape objects, 435 worksheet design, 300–301 OLEObjects collection, 436–438 writing code for, 301–326 ShapeRange collection object, 432–434 writing code for Userform module, 302–314 Shapes collection and Shape objects, 428–432 writing code for Worksheet module, 315–326 VbDayOfWeek enumerated type, 235 Wordfind worksheets, 269, 270 VB.net, 151 Word search puzzles, 296, 324 Verisign, 4 WordToList() sub procedure, 323 WordWrap property, 299 Workbook object, 150, 153, 154, 161–169, 162, 175, 205, 279 Charts property of, 380 Close() method of, 164, 165 object module for, in Battlecell program, 181

Index 489 Open() event of, 167 X SaveAs() method of, 281, 283, 343 viewing worksheets property of, 158 xlFileFormat constants, 282 WindowResize() event of, 169 .xls files, 280 Workbook_Open event procedure, 170 XML Data file type, 343 Workbooks, 8, 9 XML editor, 338 centering, 166 XML (eXtensible Markup Language), 329 closing, 165–166 opening, 281 description of, 331–332 saving, 15, 281–282 Excel and, 338–343 Workbooks collection object, 163 Workbooks objects, 155 data saved as XML spreadsheet, 342–343 Workbook windows, resizing, 168, 169 opening/importing XML documents into Excel Worksheet_Activate() event procedure, 170 worksheet, 338–341 Worksheet cells, programming formulas into, 120–123 worksheet saved as XML data, 343 WorksheetFunction property, 161 worksheets saved to XML files, 341 Worksheet interface, for Battlecell program, 180 overview of, 330–331 Worksheet module, writing code for, 315–326 sample, 332–333 Worksheet object, 150, 153, 159, 162, 169–170, 174, 175, schemas, 337–338 syntax, 334–336 205, 386 case-sensitive tags, 335 Cells property of, 172 closing tags, 334 Change() event of, 368 element names, 336 object module for, in Battlecell program, 181 nesting of tags, 335 PrintOut() method of, 325 quotes around attributes, 335–336 Select() method of, 170 root elements, 334 Shapes property of, 428 validation, 338 Worksheets VBA and, 343–344 altering properties of, in Excel using VBA, 7–9 saving and opening XML documents, 343 for Blackjack game, 242, 243 XmlMap object, 345–349 controls on, 11–12 ListObject object, 348–349 drawing layers in, 428 XML files, in new Math Game program, 353, 355 embedded chart on, 381 XmlImport() method, 347, 348, 360 hidden, 299, 303, 304, 309 XmlImportXml() method, 347, 348 for Magic Squares, 34–37 XmlMap object, 343, 345–348, 358, 374 for Math Game program 125 XML Source Task Pane, 339, 340, 341, 344 for new Math Game program, 350, 351 XML spreadsheet, saving data as, 342–343 saving as text files, 283 .xsd file extension, 339 saving as XML data, 343 saving to XML files, 341 Y shapes added to, 429 triggering Change() event of, 143 y-axis, in chart sheet, 389 for Word Find program, 269, 270, 300–301 Year() function, 53 word search puzzle created on, 300 Worksheets collection object, 379 Z Worksheets objects, 155 World Wide Web, 151, 329 z-axis, in chart sheet, 389 World Wide Web Consortium, 332 Zero, divide by error, 270 Write #, 293, 294 Zero-length string, 41 WriteRemainingProblems() sub procedure, 369 ZoomGameBoard() sub procedure, 183, 185 WriteWord() sub procedure, 318, 322 Zoom property, of Window object, 169, 181 ZOrder() method, of Shape object, 431

Professional ■ Trade ■ Reference RISE TO THE TOP OF YOUR GAME WITH COURSE PTR! Check out the Beginning series from Course PTR—full of tips and techniques for the game developers of tomorrow! Perfect your programming skills and create eye-catching art for your games to keep players coming back for more. Beginning C++ Beginning DirectX 9 Beginning OpenGL Beginning Illustration Game Programming Game Programming and Storyboarding ISBN: 1-59200-349-4 for Games ISBN: 1-59200-205-6 $29.99 ISBN: 1-59200-369-9 $29.99 $29.99 ISBN: 1-59200-495-4 $29.99 Check out advanced books and the full Game Development series at WWW.COURSEPTR.COM/GAMEDEV Call 1.800.354.9706 to order Order online at www.courseptr.com

Professional ■ Trade ■ Reference Let’s face it. Microsoft® Excel VBA Programming for the Absolute Beginner, Second Edition C++, ASP, and Java can be a little intimidating. ISBN: 1-59200-729-5 ■ Coming March 2005 But now they don’t have to be. The for the absolute Microsoft® Visual Basic.NET beginner series gives you Programming for the Absolute Beginner a fun, non-intimidating introduction to the world ISBN 1-59200-002-9 of programming. Each book in this series teaches a Palm™ Programming specific programming language using simple for the Absolute Beginner game programming as a teaching aid. If you are new to programming, want to learn, and ISBN 0-7615-3524-1 want to have fun, then Premier Press’s for the absolute beginner series is just what Python Programming you’ve been waiting for! for the Absolute Beginner ASP Programming ISBN 1-59200-073-8 for the Absolute Beginner Microsoft® Windows® Shell Script ISBN 0-7615-3620-5 Programming for the Absolute Beginner C Programming ISBN 1-59200-085-1 for the Absolute Beginner ISBN 1-931841-52-7 C++® Programming for the Absolute Beginner ISBN 0-7615-3523-3 Java™ Programming for the Absolute Beginner ISBN 0-7615-3522-5 Microsoft® Access VBA Programming for the Absolute Beginner, Second Edition ISBN: 1-59200-723-6 ■ Coming March 2005 Call 1.800.354.9706 to order Order online at www.courseptr.com

License Agreement/Notice of Limited Warranty By opening the sealed disc container in this book, you agree to the following terms and conditions. If, upon reading the following license agreement and notice of limited warranty, you cannot agree to the terms and conditions set forth, return the unused book with unopened disc to the place where you purchased it for a refund. License: The enclosed software is copyrighted by the copyright holder(s) indicated on the software disc. You are licensed to copy the software onto a single computer for use by a single user and to a backup disc. You may not reproduce, make copies, or distribute copies or rent or lease the soft- ware in whole or in part, except with written permission of the copyright holder(s). You may transfer the enclosed disc only together with this license, and only if you destroy all other copies of the software and the transferee agrees to the terms of the license. You may not decom- pile, reverse assemble, or reverse engineer the software. Notice of Limited Warranty: The enclosed disc is warranted by Thomson Course Technology PTR to be free of physical defects in materials and workmanship for a period of sixty (60) days from end user’s purchase of the book/disc combination. During the sixty-day term of the limited warranty, Thomson Course Technology PTR will provide a replacement disc upon the return of a defective disc. Limited Liability: THE SOLE REMEDY FOR BREACH OF THIS LIMITED WARRANTY SHALL CONSIST ENTIRELY OF REPLACEMENT OF THE DEFECTIVE DISC. IN NO EVENT SHALL THOMSON COURSE TECHNOLOGY PTR OR THE AUTHOR BE LIABLE FOR ANY OTHER DAMAGES, INCLUDING LOSS OR CORRUPTION OF DATA, CHANGES IN THE FUNCTIONAL CHARACTERISTICS OF THE HARDWARE OR OPERATING SYSTEM, DELETERIOUS INTERACTION WITH OTHER SOFTWARE, OR ANY OTHER SPECIAL, INCIDENTAL, OR CONSEQUENTIAL DAMAGES THAT MAY ARISE, EVEN IF THOMSON COURSE TECHNOLOGY PTR AND/OR THE AUTHOR HAS PREVIOUSLY BEEN NOTIFIED THAT THE POSSIBILITY OF SUCH DAMAGES EXISTS. Disclaimer of Warranties: THOMSON COURSE TECHNOLOGY PTR AND THE AUTHORS SPECIFICALLY DISCLAIM ANY AND ALL OTHER WARRANTIES, EITHER EXPRESS OR IMPLIED, INCLUDING WARRANTIES OF MERCHANTABILITY, SUITABILITY TO A PARTICULAR TASK OR PURPOSE, OR FREEDOM FROM ERRORS. SOME STATES DO NOT ALLOW FOR EXCLUSION OF IMPLIED WARRANTIES OR LIMI- TATION OF INCIDENTAL OR CONSEQUENTIAL DAMAGES, SO THESE LIMITATIONS MIGHT NOT APPLY TO YOU. Other: This Agreement is governed by the laws of the State of Massachusetts without regard to choice of law principles. The United Convention of Contracts for the International Sale of Goods is specifically disclaimed. This Agreement constitutes the entire agreement between you and Thomson Course Technology PTR regarding use of the software.