Excel VBA 24-Hour Trainer. Tom Urtis

tips, hints, tricks, and asides to the current discussion are offset and placed in italic like this.

      As for styles in the text:

      • We highlight new terms and important words when we introduce them.

      • We show filenames, URLs, and code within the text like so: persistence.properties.

      • We present code like this:

      We use a monofont type with no highlighting for most code examples.We use bold to emphasize code that's particularly important in the present context.

      • Text that you need to enter as you work through the Try It sections is written as bold code, as shown here:

      Name it cmdExit and caption it as Exit.

      Source Code

      As you work through the examples in this book, you may choose either to type in all the code manually or to use the source code files that accompany the book. All of the source code used in this book is available for download at www.wrox.com/go/excelvba24hour. The code snippets from the source code are accompanied by a download icon and note indicating the name of the program so you know it's available for download and can easily locate it in the download file. Once at the site, simply locate the book's title (either by using the Search box or by using one of the title lists) and click the Download Code link on the book's detail page to obtain all the source code for the book.

      After you download the code, just unzip the file using WinZip or a similar tool. Alternatively, you can go to the main Wrox code download page at http://www.wrox.com/dynamic/books/download.aspx to see the code available for this book and all other Wrox books.

      Errata

      We make every effort to ensure that there are no errors in the text or in the code. However, no one is perfect, and mistakes do occur. If you find an error in one of our books, like a spelling mistake or faulty piece of code, we would be very grateful for your feedback. By sending in errata you may save another reader hours of frustration and at the same time you will be helping us provide even higher quality information.

      To find the errata page for this book, go to http://www.wrox.com and locate the title using the Search box or one of the title lists. Then, on the book details page, click the Book Errata link. On this page you can view all errata that has been submitted for this book and posted by Wrox editors. A complete book list including links to each book's errata is also available at www.wrox.com/misc-pages/booklist.shtml.

      If you don't spot “your” error on the Book Errata page, go to www.wrox.com/contact/techsupport.shtml and complete the form there to send us the error you have found. We'll check the information and, if appropriate, post a message to the book's errata page and fix the problem in subsequent editions of the book.

       p2p.wrox.com

      For author and peer discussion, join the P2P forums at p2p.wrox.com. The forums are a web-based system for you to post messages relating to Wrox books and related technologies and interact with other readers and technology users. The forums offer a subscription feature to e-mail you topics of interest of your choosing when new posts are made to the forums. Wrox authors, editors, other industry experts, and your fellow readers are present on these forums.

      At http://p2p.wrox.com you will find a number of different forums that will help you not only as you read this book, but also as you develop your own applications. To join the forums, just follow these steps:

      1. Go to p2p.wrox.com and click the Register link.

      2. Read the terms of use and click Agree.

      3. Complete the required information to join as well as any optional information you wish to provide and click Submit.

      4. You will receive an e-mail with information describing how to verify your account and complete the joining process.

      NOTE You can read messages in the forums without joining P2P but in order to post your own messages, you must join.

      Once you join, you can post new messages and respond to messages other users post. You can read messages at any time on the web. If you would like to have new messages from a particular forum e-mailed to you, click the Subscribe to this Forum icon by the forum name in the forum listing.

      For more information about how to use the Wrox P2P, be sure to read the P2P FAQs for answers to questions about how the forum software works as well as many common questions specific to P2P and Wrox books. To read the FAQs, click the FAQ link on any P2P page.

Part I

      Understanding the BASICs

      Lesson 1

Introducing VBA

      Welcome to your first lesson in Excel VBA 24-Hour Trainer! A good place to start is at the beginning, where you'll find it useful to get an understanding of where Visual Basic for Applications (VBA) comes from and what VBA is today. After you get a feel for how VBA fits into the overall Excel universe, you find out how to use VBA to manipulate Excel in ways you might never have thought possible.

      What is VBA?

      VBA is a programming language created by Microsoft to automate operations in applications that support it, such as Excel. VBA is an enormously powerful tool that enables you to control Excel in countless ways that you cannot do – or would not want to do – manually.

      In fact, VBA is also the language that manipulates Microsoft Office applications in Access, Word, PowerPoint, and Outlook. For the purposes here, VBA is the tool you use to develop macros and manipulate objects to control Excel and to control other Office applications from Excel.

      You do not need to purchase anything more than the Office suite (or the individual application) to also own VBA. If you have Excel on your computer, you have VBA on your computer.

      WHAT IS A “MACRO,” ANYWAY?

      Back in the day, a programming language was often called a “macro language” if its capabilities included the automation of a sequence of commands in spreadsheet or word-processing applications. With Microsoft's release of Office 5, VBA set a new bar for how robust a programming language can be, with capabilities extending far beyond those of earlier programming languages, such as the ability to create and control objects within Excel or to have access to disk drives and networks.

      So VBA is a programming language, and it is also a macro language. Confusion of terminology arises when referring to VBA code that is a series of commands written and executed in Excel. Is it a macro, a procedure, or a program? Microsoft commonly refers to its VBA procedures as macros, so that's good enough for me to call them macros also. Outside of a few exceptions that I explain when the time comes, I refer to VBA procedures as macros.

      A Brief History of VBA

      VBA is a present-day dialect of the BASIC (Beginner's All-purpose Symbolic Instruction Code) programming language that was developed in the 1960s. BASIC became widely used in many software applications throughout the next two decades because it was easy to learn and understand.

      Over the years, BASIC has evolved and improved in response to advancing technology and increased demands by its users for greater programming flexibility. In 1985, Microsoft released a much richer version of BASIC, named QuickBASIC, which boasted the most up-to-date features found in programming languages of the day. In 1992, Microsoft released Visual Basic for Windows, designed to work within the burgeoning Windows environment.

      Meanwhile,