You are previewing Microsoft® Excel® VBA Programming for the Absolute Beginner, Third Edition.
O'Reilly logo
Microsoft® Excel® VBA Programming for the Absolute Beginner, Third Edition

Book Description

If you are new to programming with Microsoft Excel VBA and are looking for a solid introduction, this is the book for you. Developed by computer science professors, books in the "for the absolute beginner" series teach the principles of programming through simple game creation. Microsoft Excel VBA Programming for the Absolute Beginner, Third Edition provides you with the skills that you need for more practical Excel VBA programming applications and shows you how to put these skills to use in real-world scenarios. Best of all, by the time you finish the book, you will be able to apply the basic principles you've learned to the next programming language you tackle.

Table of Contents

  1. Copyright
    1. Dedication
  2. Acknowledgments
  3. About the Authors
  4. Introduction
    1. Why VBA?
    2. Who Should Read This Book?
    3. What’s in This Book and What Is Required?
  5. 1. Visual Basic for Applications with Excel
    1. Project: Colorful Stats
    2. Installing and Enabling VBA
    3. The VBA Integrated Development Environment (IDE)
      1. Getting to the IDE from Excel
      2. Components of the IDE
    4. Programming Components within Excel
      1. Additional Development Tools
    5. Getting Help with VBA
    6. Constructing the Colorful Stats Program
      1. Requirements of the Colorful Stats Program
      2. Designing the Colorful Stats Program
      3. Coding the Colorful Stats Program
    7. Summary
  6. 2. Beginning Programming with VBA
    1. Project: Biorhythms and the Time of Your Life
    2. Variables, Data Types, and Constants
      1. Declaring Variables
      2. Object and Standard Modules
      3. Variable Scope
      4. Data Types
      5. Numerical Data Types
      6. String Data Types
      7. Variant Data Types
      8. Boolean Data Types
      9. Date Data Types
      10. Constants
    3. Simple Input and Output with VBA
      1. Collecting User Input with InputBox()
      2. Output with MsgBox()
    4. Manipulating Strings with VBA Functions
      1. Fun with Strings
    5. Constructing the Biorhythms and the Time of Your Life Program
      1. Requirements for Biorhythms and the Time of Your Life
      2. Designing Biorhythms and the Time of Your Life
      3. Coding Biorhythms and the Time of Your Life
    6. Summary
  7. 3. Procedures and Conditions
    1. Project: Poker Dice
    2. Event Procedures
      1. Parameters with Event Procedures
      2. Private, Public, and Procedure Scope
    3. Sub Procedures
      1. ByVal and ByRef
    4. Function Procedures
      1. Creating Your Own VBA Functions
      2. Using Excel Application Functions in VBA
    5. Logical Operators with VBA
      1. AND, OR, and NOT Operators
    6. Conditionals and Branching
      1. If/Then/Else
      2. Select/Case
    7. Constructing the Poker Dice Program
      1. Requirements for Poker Dice
      2. Designing Poker Dice
        1. The Image Control
        2. The Check Box Control
        3. Locating the Code for Poker Dice
      3. Coding Poker Dice
        1. Selecting the Dice
        2. Resetting the Game Board
        3. Rolling the Dice
        4. Scoring the Hand
    8. Summary
  8. 4. Loops and Arrays
    1. Project: Math Game
    2. Looping with VBA
      1. Do Loops
      2. For Loops
    3. Input Validation
      1. Validation with the InputBox() Function
      2. Validation with a Spreadsheet Cell
    4. Arrays
      1. One-Dimensional Arrays
      2. Multi-Dimensional Arrays
      3. Dynamic Arrays
    5. Programming Formulas into Worksheet Cells
      1. A1 Style References
      2. R1C1-Style References
    6. Constructing the Math Game
      1. Requirements for the Math Game
      2. Designing the Math Game
        1. Recording Macros
        2. The Forms Toolbar
      3. Coding the Math Game Program
        1. Adding the ActiveX Controls
        2. Starting and Initializing the Math Game Program
        3. Generating Random Questions and Operators
        4. Starting the Timer
        5. Collecting Answers
        6. Scoring the Answers
    7. Summary
  9. 5. Excel Objects
    1. Project: BattleCell
    2. VBA and Object-Oriented Programming
    3. Objects Defined
    4. VBA Collection Objects
    5. The Object Browser
    6. Top-Level Excel Objects
      1. The Application Object
      2. The Workbook and Window Objects
      3. The Worksheet Object
    7. The Range Object
      1. Using the Cells Property
    8. Working with Objects
      1. The With/End With Structure
      2. The Object Data Type
      3. For/Each and Looping through a Range
    9. Constructing BattleCell
      1. Requirements for BattleCell
      2. Designing BattleCell
      3. Coding BattleCell
        1. Opening and Closing the BattleCell Workbook
        2. Initializing BattleCell and Starting the Game
        3. Player Selections: Placing Ships and Firing at the Computer
        4. Computer Selections: Placing Ships and Firing at the Player
        5. Validating Selections
      4. Adding Sound to Your VBA Program
        1. The Windows API
        2. Playing Wav Files via the Windows API
    10. Summary
  10. 6. VBA UserForms and Additional Controls
    1. Project: Blackjack
    2. Designing Forms with VBA
      1. Adding a Form to a Project
      2. Components of the UserForm Object
      3. Adding ActiveX Controls to a Form
      4. Showing and Hiding Forms
      5. Modal Forms
    3. Designing Custom Dialog Boxes Using Forms
      1. The Option Button Control
      2. The Scroll Bar Control
      3. The Frame Control
      4. The RefEdit Control
      5. The MultiPage Control
      6. The List Box and Combo Box Controls
      7. A Custom Dialog for Quick Stats
    4. Derived Data Types in VBA
      1. Defining Custom Data Types in VBA
      2. Defining Enumerated Types in VBA
    5. Chapter Project: Blackjack
      1. Requirements for Blackjack
      2. Designing Blackjack
      3. Writing the Code for Blackjack
        1. General Purpose Public Procedures
        2. Public Procedures and Variables for the Blackjack Program
        3. Shuffling the Deck for the Blackjack Program
        4. Playing a Hand of Blackjack
    6. Summary
  11. 7. Error Handling, Debugging, and Basic File I/O
    1. Project: Word Find
    2. Error Handling
      1. Using the On Error Statement
    3. Debugging
      1. Break Mode
      2. The Immediate Window
      3. The Watch Window
      4. The Locals Window
    4. File Input and Output (I/O)
      1. File I/O Using Workbook and Worksheet Objects
      2. Opening and Saving Workbooks
      3. Using VBA File I/O Methods
      4. The FileDialog Object
      5. The FileDialogFilters and FileDialogSelectedItems Collection Objects
      6. The FileSystem Object
      7. The Open Statement
      8. Sequential Access Files
      9. Random Access Files
    5. Chapter Project: Word Find
      1. Requirements for Word Find
      2. Designing Word Find
        1. Designing the Form
        2. Designing the Worksheet
      3. Writing the Code for Word Find
        1. Writing the Code for the UserForm Module
        2. Writing the Code for the Worksheet Module
    6. Summary
  12. 8. Using XML With Excel VBA Projects
    1. Project: Revisiting the Math Game
    2. Introduction to XML
      1. What Is XML?
      2. An XML Sample
      3. XML Documents Must Have a Root Element
      4. XML Elements Must Have a Closing Tag
      5. XML Tags Are Case-Sensitive
      6. XML Tags Must Be Properly Nested
      7. XML Attributes Must Be Enclosed in Quotation Marks
      8. XML Element Names
      9. XML Schemas
      10. XML Validation
    3. XML and Excel
      1. Opening and Importing XML Documents into an Excel Worksheet
      2. Saving Worksheets to XML Files
      3. Saving Data as an XML Spreadsheet
      4. Saving a Worksheet as XML Data
    4. XML and VBA
      1. Saving and Opening XML Documents
      2. The XmlMap Object
      3. The ListObject Object
    5. Chapter Project: The Math Game
      1. Requirements for the Math Game Program
      2. Designing the Math Game
        1. Taking a Test
        2. Creating Tests
        3. Maintaining Student Tables and Viewing Test Results
      3. Coding the Math Game Program
        1. Writing Tests
        2. Maintaining the Student Table and Viewing Results
        3. Taking a Test
    6. Summary
  13. 9. Excel Charts
    1. Project: The Alienated Game
    2. The Chart Object
      1. Accessing Existing Charts
      2. Chart Sheets
      3. Embedded Charts
      4. Manipulating Charts
      5. Creating Charts
      6. Creating a Chart Sheet
      7. Creating an Embedded Chart
      8. Chart Events
      9. Chart Sheets
    3. Chapter Project: The Alienated Game
      1. Requirements for the Alienated Game
      2. Designing the Alienated Game
        1. The Chart Sheet Interface
        2. Capturing User Selections
        3. Mapping the Images
        4. Program Outline
      3. Coding the Alienated Game
        1. Initializing the Chart Sheet
        2. Scanning the Chart
        3. Playing the Game
    4. Summary
  14. 10. VBA Shapes
    1. Project: Excetris
    2. The Shapes Collection and Shape Objects
      1. Manipulating a Shape Object
      2. Looping through a Collection of Shapes
    3. The ShapeRange Collection Object
    4. Activating Shape Objects
    5. The OLEObjects Collection
    6. Chapter Project: Excetris
      1. Requirements for Excetris
      2. Designing Excetris
        1. Creating Excetris Shapes
        2. Moving Excetris Shapes
        3. Removing Shapes
        4. Program Outline
      3. Coding Excetris
        1. Starting the Game and Initializing the Worksheet
        2. Adding New Shapes
        3. Moving the Shapes
        4. Removing Shapes and Scoring Filled Rows
    7. Summary
  15. A. Common Character Codes
  16. B. Keyboard Shortcuts for the Code Window
  17. C. Trappable Errors
  18. D. Visual Basic Environment Options