Duration

3 days

Overview

Experience learning made easy, and quickly teach yourself Microsoft Office Excel 2007 Visual Basic for Applications

(VBA)—one step at a time! Work at your own pace through the easy numbered steps and practice files on CD, and

master the fundamentals for using VBA to create custom software solutions and automating tasks in Excel 2007—

no developer experience required! You'll learn how to automate spreadsheets, write your own functions and

procedures, and customize menus and toolbars. You’ll discover Excel Objects, including workbooks, range objects,

graphics, and PivotTables®. Plus, you’ll learn how to chart data from databases and other information sources.

With STEP BY STEP, you can take just the lessons you need or work from cover to cover. Either way, you drive the

instruction—building and practicing the skills you need, just when you need them! Includes a companion CD with

hands-on practice files.

Prerequisites

Must have a working knowledge of Excel. Power users of Excel and end-user specialist who need to develop macros for their departments.

User level

Beginner/Intermediate

Features

- Covers all of the fundamentals
- Includes coverage of all the fundamentals, including how to automate spreadsheets, customizing menus, and

writing functions and procedures.

- Features easy-to-follow lessons
- Features easy-to-follow lessons and hands-on skill-building exercises
- Includes practice files and resources
- Includes a companion CD with practice files and other resources.

Chapter 1. Make a Macro Do Simple Tasks

What’s the Difference Between VBA and a Macro?

Creating a Simple Macro
Format Currency by Using a Built-In Tool
Record a Macro to Format Currency
Run the Macro
Assign a Shortcut Key to the Macro
Look at the Macro
Save the Macro Workbook
Changing Multiple Properties at Once
Create Sidebar Headings with a Command
Record a Macro to Merge Cells Vertically
Eliminate Unnecessary Lines from the Macro
Manipulating Recorded Properties
Record a Macro to Remove Window Elements
Run the Macro from the Visual Basic Editor
Use a Macro to Toggle the Value of a Property
Eliminate Repeated Objects in a Recorded Macro
Run a Macro from the Quick Access Toolbar
Recording Methods in a Macro
Convert a Formula to a Value by Using Menu Commands
Convert a Formula to a Value by Using a Macro
Make a Long Statement More Readable
Trusting Macro-Enabled Workbooks
Designate a Trusted Location for Macros
Designate a Trusted Publisher for Macros
Key Points

Chapter 2. Make a Macro Do Complex Tasks

Task One: Opening the Report File
Open a Text File
Watch a Macro Run by Stepping Through It
Select a File While Running a Macro
Task Two: Filling In Missing Labels
Select Only the Blank Cells
Fill the Selection with Values
Record Filling In the Missing Values
Watch the FillLabels Macro Run
Task Three: Adding a Column of Dates
Add a Constant Date
Step Through the Macro
Prompt for the Date
Task Four: Appending to the Database
Append Data to a Master List
Step Through the AppendData Macro
Record a Relative Movement
Choose Whether to Save Changes While Closing a File
Task Five: Deleting the Worksheet
Create a Macro to Delete the Active Worksheet
Make the Macro Operate Quietly
Assembling the Pieces
Record a Macro That Runs Other Macros
Simplify the Subroutine Statements

Chapter 3. Explore Workbooks and Worksheets

What Is an Object?
Objects Come in Collections
Objects Have Properties
Objects Have Methods
Methods Can Change Properties
Properties Can Involve Actions
Understanding Workbooks
Add a New Workbook
Count the Workbooks
Close the Workbooks
Refer to a Single Workbook
Refer to a Workbook by Name
Refer to a Workbook by Pointing
Change a Workbook Property Value
Understanding Worksheets
Add a New Worksheet
Rename and Delete a Worksheet
Look at the Return Value of the Delete Method
Look at the Result of the Add Method
Copy a Worksheet
Manipulate Multiple Worksheets
Declare Variables to Enable Auto Lists

Chapter 4. Explore Range Objects

Referring to a Range
Refer to a Range by Using an Address
Refer to a Range as a Collection of Cells
Refer to a Range as a Collection of Rows or Columns
Refer to a Range Based on the Active Cell
Refer to Subsets of a Range
Refer to a Relative Range
Enhancing Recorded Selections
Simplify Select...Selection Pairs
Simplify Select Groups
Entering Values and Formulas into a Range
Relative References
Absolute References
R1C1 Reference Style
Put Values and Formulas into a Range
Construct Formulas to Fill a Grid
Formatting a Range
Add Borders to a Range
Format the Interior of a Range

Chapter 5. Explore Data Objects

Working with Excel Tables
Create a New File from an Existing Worksheet
Create a Table from an Internal Source
Create a Table from an External Source
Record a Macro to Manipulate a Table
Manipulate Table Columns
Manipulate Table Totals and Filters
Working with PivotTable Reports
Create a PivotTable Report from an Internal Source
Create a PivotTable Report from an External Source
Record a Macro to Set the PivotTable Structure
Set the PivotTable Structure
Record a Macro to Customize a PivotTable Layout
Customize a PivotTable Layout
Record a Macro to Customize a PivotTable Style
Customize a PivotTable Style
Key Points

Chapter 6. Explore Graphical Objects

Exploring Graphical Objects
Use Worksheet Cells as a Drawing Grid
Add a Gradient Fill to a Cell
Add a Gradient-Filled Shape
Reference a Selected Shape
Use an AutoShape to Create a Logo
Use Grouped Shapes to Create Macro Buttons
Exploring Chart Objects
Create a Chart
Synchronize Two Charts
Format the Plot Area of a Chart

Chapter 7. Control Visual Basic

Using Conditionals
Make a Decision
Make a Double Decision
Ask Yourself a Question
Test for a Valid Entry
Ask with a Message
Creating Loops
Loop Through a Collection by Using a For Each Loop
Loop with a Counter by Using a For Loop
Loop Indefinitely by Using a Do Loop
Managing Large Loops
Set a Breakpoint
Set a Temporary Breakpoint
Show Progress in a Loop
Key Points

Chapter 8. Extend Excel and Visual Basic

Creating Custom Functions
Use a Custom Function from a Worksheet
Add Arguments to a Custom Function
Make a Function Volatile
Make Arguments Optional
Use a Custom Function from a Macro
Handling Errors
Syntax Errors
Compiler Errors
Logic Errors
Run-Time Errors
Ignore an Error
Ignore an Error Safely by Using a Subroutine
Add Arguments to Generalize a Subroutine
Check for an Error
Loop Until an Error Goes Away
Trap an Error

Chapter 9. Launch Macros with Events

Creating Custom Command Buttons
Try the ZoomIn and ZoomOut Macros
Enable the Developer Tab in the Ribbon
Create a Custom Command Button
Link a Command Button to a Macro
Create an Event Handler on Your Own
Make a Button Respond to Mouse Movements
Explore the Visual Basic Project
Handling Worksheet and Workbook Events
Run a Procedure When the Selection Changes
Handle an Event on Any Worksheet
Suppress a Workbook Event
Cancel an Event

Chapter 10. Use Dialog Box Controls on a Worksheet

Using a Loan Payment Calculator
Create a Loan Payment Model
Use the Loan Payment Model
Creating an Error-Resistant Loan Payment Calculator
Restrict the Years to a Valid Range
Restrict the Down Payment to Valid Values
Restrict the Interest Rate to Valid Values
Retrieving a Value from a List
Prepare a List of Cars
Retrieve the Price from the List
Set the Column Widths
Protecting the Worksheet
Create an Event Handler for the Combo Box
Protect the Worksheet

Chapter 11. Create a Custom Form

Creating a Form’s User Interface
Create the Form
Add Option Buttons
Add a Check Box with a Related Text Box
Initialize the Text Box
Add Command Buttons
Set the Tab Order for Controls
Preparing a Form’s Functionality
Create Custom Views on a Worksheet
Create a Macro to Switch Views
Dynamically Hide Columns
Implementing a Form
Implement Option Buttons
Implement a Check Box
Check for Errors in an Edit Box
Print the Report
Launch the Form

Programme scope, delivery arrangements and suitability are confirmed during enquiry.

VBA07SBS - Microsoft Excel 2007 Visual Basic for Applications Step by Step