Unit 1: What Is VBA?
What Is VBA?
What Can You Do with VBA?
- Inserting a bunch of text
- Automating a task you perform frequently
- Automating repetitive operations
- Creating a custom command
- Creating a custom button
- Developing new worksheet functions
- Creating custom add-ins for Excel
- Creating complete, macro-driven applications
- Advantages and Disadvantages of VBA
- VBA in a Nutshell
An Excursion into Versions
Unit 2: Jumping Right In
- Taking the First Steps
- Recording the Macro
- Testing the Macro
- Examining the Macro
- Modifying the Macro
- Saving Workbooks That Contain Macros
- Understanding Macro Security
More about the NameAndTime Macro
Unit 3: Working in the Visual Basic Editor
What Is the Visual Basic Editor?
- Activating the VBE
- Understanding VBE components
Working with the Project Window
- Adding a new VBA module
- Removing a VBA module
- Exporting and importing objects
Working with a Code Window
- Minimizing and maximizing windows
- Creating a module
- Getting VBA code into a module
- Entering code directly
- Using the macro recorder
- Copying VBA code
Customizing the VBA Environment
- Using the Editor tab
- Using the Editor Format tab
- Using the General tab
- Using the Docking tab
Unit 4: Introducing the Excel Object Model
Excel Is an Object?
Climbing the Object Hierarchy
Wrapping Your Mind around Collections
Referring to Objects
- Navigating through the hierarchy
- Simplifying object references
Diving into Object Properties and Methods
- Object properties
- Object methods
- Object events
Finding Out More
- Using VBA’s Help system
- Using the Object Browser
Automatically listing properties and methods
Unit 5: VBA Sub and Function Procedures
Subs versus Functions
- Looking at Sub procedures
- Looking at Function procedures
- Naming Subs and Functions
Executing Sub procedures
Executing the Sub procedure directly
- Executing the procedure from the Macro dialog box
- Executing a macro by using a shortcut key
- Executing the procedure from a button or shape
- Executing the procedure from another procedure
- Executing Function procedures
- Calling the function from a Sub procedure
Calling a function from a worksheet formula
Unit 6: Using the Excel Macro Recorder
- Is It Live, or Is It VBA?
- Recording Basics
- Preparing to Record
- Relative or Absolute?
- Recording in absolute mode
- Recording in relative mode
- What Gets Recorded?
- Recording Options
- Macro name
- Shortcut key
- Store Macro In
- Description
- Is This Thing Efficient?
Unit 7: Essential VBA Language Elements
Using Comments in Your VBA Code
Using Variables, Constants, and Data Types
- Understanding variables
- What are VBA’s data types?
- Declaring and scoping variables .
- Working with constants
- Pre-made constants
- Working with strings
- Working with dates
Using Assignment Statements
- Assignment statement examples
- About that equal sign
- Smooth operators
Working with Arrays
- Declaring arrays
- Multidimensional arrays
- Dynamic arrays
Using Labels
Unit 8: Working with Range Objects
A Quick Review
Other Ways to Refer to a Range
- The Cells property
- The Offset property
Some Useful Range Object Properties
- The Value property
- The Text property
- The Count property
- The Column and Row properties
- The Address property
- The HasFormula property
- The Font property
- The Interior property
- The Formula property
- The NumberFormat property
Some Useful Range Object Methods
- The Select method
- The Copy and Paste methods
- The Clear method
The Delete method
Unit 9: Using VBA and Functions
What Is a Function?
Using Built-In VBA Functions
VBA function examples
VBA functions that do more than return a value
Discovering VBA functions
Using Worksheet Functions in VBA
- Worksheet function examples
- Entering worksheet functions
- More about using worksheet functions
Using Custom Functions
Unit10: Controlling Program Flow and Making Decisions
Going with the Flow, Dude
The GoTo Statement
Decisions, Decisions
- The If-Then structure
- The Select Case structure
Knocking Your Code for a Loop
- For-Next loops
- Do-While loop
- Do-Until loop
Using For Each-Next Loops With Collections
Unit 11: Automatic Procedures and Events
Preparing for the Big Event
- Are events useful?
- Programming event-handler procedures
Where Does the VBA Code Go?
Writing an Event-Handler Procedure
Introductory Examples
- The Open event for a workbook
- The BeforeClose event for a workbook
- The BeforeSave event for a workbook
Examples of Activation Events
- Activate and deactivate events in a sheet
- Activate and deactivate events in a workbook
- Workbook activation events
Other Worksheet-Related Events
- The BeforeDoubleClick event
- The BeforeRightClick event
- The Change event
Events Not Associated with Objects
Keypress events
Unit 12: Error-Handling Techniques
Types of Errors
An Erroneous Example
- The macro’s not quite perfect
- The macro is still not perfect
- Is the macro perfect yet?
- Giving up on perfection
Handling Errors Another Way
- Revisiting the EnterSquareRoot procedure
- About the On Error statement
Handling Errors: The Details
- Resuming after an error
- Error handling in a nutshell
- Knowing when to ignore errors
- Identifying specific errors
An Intentional Error
Unit 13: Bug Extermination Techniques
Species of Bugs
Identifying Bugs
Debugging Techniques
- Examining your code
- Using the MsgBox function
- Inserting Debug.Print statements
- Using the VBA debugger
About the Debugger
- Setting breakpoints in your code
- Using the Watch window
- Using the Locals window
Bug Reduction Tips
Unit 14: VBA Programming Examples
Working with Ranges
- Copying a range
- Copying a variable-sized range
- Selecting to the end of a row or column
- Selecting a row or column
- Moving a range
- Looping through a range efficiently
- Looping through a range efficiently (Part II)
- Prompting for a cell value
- Determining the selection type
- Identifying a multiple selection
Changing Excel Settings
- Changing Boolean settings
- Changing non-Boolean settings
Working with Charts
- AddChart versus AddChart2
- Modifying the chart type
- Looping through the ChartObjects collection
- Modifying chart properties
- Applying chart formatting
VBA Speed Tips
- Turning off screen updating
- Turning off automatic calculation
- Eliminating those pesky alert messages
- Simplifying object references
- Declaring variable types
Using the With-End With structure
Unit 15: Simple Dialog Boxes
UserForm Alternatives
The MsgBox Function
- Displaying a simple message box
- Getting a response from a message box
- Customizing message boxes
The InputBox Function
- InputBox syntax
- An InputBox example
- Another type of InputBox
The GetOpenFilename Method
- The syntax for the GetOpenFilename method
- A GetOpenFilename example
The GetSaveAsFilename Method
Getting a Folder Name
Displaying Excel’s Built-in Dialog Boxes