Excel 2000 Programming in 21 days

Sams Teach Yourself Microsoft Excel 2000 Programming in 21 Days gives you the skills you need to get up and running efficiently. With this complete tutorial, you'll master the basics and move on to the more advanced features and concepts. Learn to add new functions and capabilities to your Excel files and applications. Explore built-in functions, arrays, subroutines, error handling, string operators and expressions, variables, and constants--all in detail. Step-by-step lessons show you how to use Visual Basic to control different applications from withing Excel, and more.

• Delivers the most important aspects of VBA development
• Learn how to increase your development time by using VBA's Object Browser
• Apply modular techniques to make big jobs more understandable and reusable

TABLE OF CONTENTS

• INTRODUCTION
  

WEEK 1 - AT A GLANCE

• CHAPTER 1 - GETTING STARTED
  
  • Macros and Programming Languages
    
  • Why Learn Visual Basic for Applications?
    
  • Recording A New Macro
    
  • Putting It Together: Recording Your First Macro
    
  • Macro Source Code
    
  • Running Macros
    
  • Summary
    
  • Workshop
    
  
  
• CHAPTER 2 - WRITING AND EDITING SIMPLE MACROS
  
  • Understanding the Visual Basic for Applications Environment
    
  • Editing Macros
    
  • Writing New Macros and Procedures
    
  • Running a Procedure While Editing
    
  • Displaying Messages to a Procedure's User
    
  • Understanding Error Messages While Writing, Editing, or Running a Procedure
    
  • Printing Your Source Code
    
  • Summary
    
  • Workshop
    
  
  
• CHAPTER 3 - UNDERSTANDING DATA TYPES, VARIABLES, AND CONSTANTS
  
  • Examining Visual Basic Data Types
    
  • Understanding Variables
    
  • Understanding Constants
    
  • Getting Data from Your Procedure's User
    
  • Summary
    
  • Workshop
    
  
  
• CHAPTER 4 - OPERATORS AND EXPRESSIONS
  
  • Understanding Operators and Expressions
    
  • Data Type Compatibility
    
  • Visual Basic's Automatic Data Conversions
    
  • The Assignment Operator (=)
    
  • Arithmetic Operators
    
  • Comparison Operators
    
  • Logical Operators
    
  • String Concatenation Operators
    
  • Understanding Operator Precedence and Complex Expression Evaluation
    
  • Summary
    
  • Workshop
    
  
  
• CHAPTER 5 - USING VISUAL BASIC AND EXCEL FUNCTIONS
  
  • Understanding Functions
    
  • Using Visual Basic for Application's Functions
    
  • Using Excel's Functions
    
  • Using the Object Browser to Insert Function Calls
    
  • Using Functions to Manipulate Strings
    
  • Summary
    
  • Workshop
    
  
  
• CHAPTER 6 - CREATING AND USING VBA FUNCTION PROCEDURES AND EXCEL USER-DEFINED
  FUNCTIONS
  
  • Understanding Function Procedures and User-Defined Functions
    
  • Creating Function Procedures
    
  • Using Function Procedures in Visual Basic for Applications
    
  • Using the Object Browser to Find Your Function Procedures
    
  • Using User-Defined Functions in Excel Worksheets
    
  • Designing Function Procedures
    
  • Understanding Recursion
    
  • Summary
    
  • Workshop
    
  
  
• CHAPTER 7 - UNDERSTANDING OBJECTS AND COLLECTIONS
  
  • Understanding Objects
    
  • Using Objects
    
  • Working with Object Collections and Object Containers
    
  • Using the Object Browser with Objects, Methods, and Properties
    
  • Summary
    
  • Workshop
    
  
  

WEEK 1 - IN REVIEW: A BASIC WORKBOOK CREATION UTILITY

WEEK 2 - AT A GLANCE

• CHAPTER 8 - MAKING DECISIONS IN VISUAL BASIC FOR APPLICATIONS
  
  • Understanding VBA's Decision-Making Commands
    
  • Making Simple Choices
    
  • Making Complex Choices
    
  • Unconditional Branching
    
  • Ending Procedures, Functions, and Entire Programs Early
    
  • Using MsgBox to Let Users Make Choices
    
  • Summary
    
  • Workshop
    
  • Exercises
    
  
  
• CHAPTER 9 - REPEATING ACTIONS IN VISUAL BASIC: LOOPS
  
  • Understanding Looping Commands
    
  • Fixed Repetition: The For Loops
    
  • Flexible Repetition: The Do Loops
    
  • Ending Loops Early
    
  • Nesting Loops
    
  • Summary
    
  • Workshop
    
  
  
• CHAPTER 10 - ADVANCED TECHNIQUES FOR USING DATA TYPES AND VARIABLES
  
  • Getting Information About Variables and Expressions
    
  • Defensive Programming: Preventing Errors Before They Happen
    
  • Preserving Variables Between Function and Procedure Calls
    
  • Summary
    
  • Workshop
    
  
  
• CHAPTER 11 - CREATING YOUR OWN DATA TYPES AND OBJECT CLASSES
  
  • Creating Your Own Data Types
    
  • Defining a User-Defined Data Type
    
  • Creating Your Own Program Objects
    
  • Summary
    
  • Workshop
    
  
  
• CHAPTER 12 - CREATING LIBRARIES AND WHOLE PROGRAMS: MODULAR PROGRAMMING TECHNIQUES
  
  • Using Modules More Effectively
    
  • Advanced Scope Rules for Cross-Module Programming
    
  • Understanding Structured Programming Techniques
    
  • Using Procedure Arguments to Communicate Data Between Procedures
    
  • Summary
    
  • Workshop
    
  
  
• CHAPTER 13 - MANAGING FILES WITH VISUAL BASIC FOR APPLICATIONS
  
  • Understanding File Management
    
  • Working with File Attributes
    
  • Getting or Finding Filenames
    
  • Using Excel's Built-In Dialogs to Get Filenames
    
  • Working with Disk Drives and Folders
    
  • Copying and Deleting Files
    
  • Renaming or Moving Files
    
  • Getting Information about Files
    
  • Summary
    
  • Workshop
    
  
  
• CHAPTER 14 - ARRAYS
  
  • The Option Base Statement
    
  • Declaring Arrays
    
  • Using Arrays
    
  • Using ReDim with Dynamic Arrays
    
  • The LBound and UBound Functions
    
  • Using Erase to Clear or Remove Arrays
    
  • Using Arrays as Arguments to Procedures and Functions
    
  • Sorting Arrays
    
  • Searching Arrays
    
  • Summary
    
  • Workshop
    
  
  

WEEK 2 - IN REVIEW: ENHANCING THE WORKBOOK CREATION UTILITY

WEEK 3 - AT A GLANCE

• CHAPTER 15 - DEBUGGING AND TESTING VBA CODE
  
  • Basic Types of Program Bugs
    
  • Using the Break Mode
    
  • Using the Step Into Command
    
  • Using the Step Over Command
    
  • Using the Step Out and Run To Cursor Commands
    
  • Understanding and Using Watched Variables
    
  • Tracing Procedure Calls
    
  • Using the Immediate Window
    
  • Summary
    
  • Workshop
    
  
  
• CHAPTER 16 - CREATING CUSTOM DIALOG BOXES
  
  • Understanding User Forms
    
  • Understanding Controls
    
  • Using Forms to Create Custom Dialog Boxes
    
  • Displaying User Forms with VBA
    
  • Using VBA with Controls on a Form
    
  • Summary
    
  • Workshop
    
  
  
• CHAPTER 17 - MENUS AND TOOLBARS
  
  • Understanding Command Bars
    
  • Command Bar Object Methods and Properties
    
  • Understanding Built-In and Custom Command Bars
    
  • Command Bar Control Objects, Methods, and Properties
    
  • Managing Custom and Built-In Command Bars
    
  • Managing Command Bar Controls
    
  • Putting It Together: A Menu-type Command Bar
    
  • Putting It Together: A Toolbar-type Command Bar
    
  • Summary
    
  • Workshop
    
  
  
• CHAPTER 18 - ERROR HANDLING
  
  • Strategies for Error Handling
    
  • The On Error GoTo Statement
    
  • The Resume Statement
    
  • Finding the Runtime Error's Type, Message, and Location
    
  • Forcing Runtime Errors and Creating User-Defined Error Codes: The Error Statement
    
  • Using the Err Object
    
  • Putting It Together: Examples of Error Handling
    
  • Summary
    
  • Workshop
    
  
  
• CHAPTER 19 - CONTROLLING EXCEL WITH VBA
  
  • Working with Workbook Objects
    
  • Working with Worksheet Objects
    
  • Returning a Worksheet Object
    
  • Methods That Return Range Objects
    
  • Working with Cells and Ranges
    
  • Summary
    
  • Workshop
    
  
  
• CHAPTER 20 - WORKING WITH OTHER APPLICATIONS
  
  • What Is Automation and OLE?
    
  • Looking Up an Object's Class Type
    
  • Adding Linked and Embedded Objects
    
  • Working with Linked and Embedded Objects
    
  • Using Automation
    
  • Accessing DLLs from Visual Basic for Applications
    
  • Working with Applications that Don't Support Automation or OLE
    
  • Summary
    
  • Workshop
    
  
  
• CHAPTER 21 - USING EVENT PROCEDURES AND ADD-INS
  
  • What Are Events and Event Procedures?
    
  • Working with Application Object Events
    
  • Working with Excel Object Events
    
  • Working with Excel's Event-Related Properties and Methods
    
  • Working with Add-In Applications
    
  • Summary
    
  • Workshop
    
  
  

WEEK 3 - IN REVIEW: UTILITIES ADD-IN MODULE AND CUSTOMIZED WORKBOOK CLASS

• APPENDIX A - ANSWERS
  
• INDEX