Microsoft Excel Automation

Introduction
Course Objectives
Command Reference (2003 - 2007)

Time Saving Techniques
Shortcuts & Quick Tips
Auto Correct Options

Validation & Protection
Introducing Data Validation
Creating a List with Validation
Setting up Protection

Named Ranges
Defining a Name
Editing & Using Names
Name Manager

Security
Adjusting Security Levels
Using Macro Extension

Introduction to Macros
Tools Menu vs. Developer Toolbar
Storing Macros
Working with a Startup Folder

Macros
Getting Started
Absolute vs. Relative Reference

Practice with Macros
Creating a Routine
Automating Formats
Creating a Template for Headers/Footers

Understanding Macro Code
Editing Macros with VBA
Introduction to Reading Code

Buttons vs. Command Buttons
Creating a Button
Creating a Command Button

The VBA Screen
The Explorer & Properties
Objects & User Forms

Creating a Routine
Creating an Object pt. 1
Creating an Object pt. 2
Adding Code to an Object

Adding Controls
Creating Dropdown Lists
Viewing the Code

Designing a Pop-Up Calendar
Creating the Calendar Form
Writing the Code

Writing Code
Coding with VBA
Examples

Formulas & Functions
Formulas vs. Functions
Tips for Formulas

Three Dimensional Formulas
Formulas from Other Worksheets
Linking External Workbooks

Popular Functions
Conditional Statements
Nested Statements
Introduction to V-Lookup
Tips for Working with V-Lookup

VBA & Functions
Using Functions
Creating a Custom Function

Using an Add-In
Explaining Add-In
Creating an Add-In for a Custom
Using a Function with Goal Seek

Pivot Tables
Using Pivot Tables
Working with Outside Data
Formats & Features of Pivots

Automating Pivot Tables
Adding a Pivot Chart
Macros & Code for Automation

Advanced Charts & Graphs
Single Point Charts
Specialty Charts
Combination Charts

Automating Charts & Graphs
Creating a Dynamic Chart
The Offset Command

Pivots & Charts in Legacy Versions
Creating Pivots in Earlier Versions
Charts & Graphs in Prior Versions

Working with Other Programs
Linking to PowerPoint
Dressing up Your Spreadsheets

Importing/Exporting Data
Working with External Data
Exporting from Excel

Analysis Tools
Understanding the Add-In Tools
Descriptive Statistics
Histogram

Looking for Help
The Help Menu for Excel & VBA
The Microsoft Interactive Tool

Wrap Up
Conclusion
About the Author