Microsoft Office Excel 2010: VBA

Course Objectives

You will automate your job tasks in Microsoft� Office Excel� 2010.

Prerequisites

Knowledge of the basics of Excel, including how to create, edit, format, and print worksheets that include charts and sorted and filtered data. Students are recommended to first take the following courses or have equivalent knowledge:

Microsoft� Office Excel� 2010: Level 1
Microsoft� Office Excel� 2010: Level 2
Microsoft� Office Excel� 2010: Level 3
Microsoft� Office Excel� 2010: Level 4

Target Student

This course is intended for advanced Microsoft Excel professionals that need to automate Excel spreadsheet tasks using Visual Basic for Applications (VBA).

Hardware Requirements

For this course, you need one computer for each student and one for the instructor. Each computer will need the following minimum hardware configuration:

A PC with a Pentium� processor, at least 300 MHz.
10 GB of hard disk space or larger. You should have at least 1 GB of free hard disk space available for Office installation.
A minimum of 64 MB of RAM with 1 GB of free hard disk space.
A CD-ROM drive.
Keyboard and mouse or other pointing device.
1024 x 768 resolution monitor is recommended.
Network cards and cabling for local network access.
Internet access (contact your local network administrator).
A printer (optional) or an installed printer driver.
A projection system to display the instructor�s computer screen.

Software Requirements

Each computer requires the following software:

Microsoft� Office Professional Plus 2010 Edition
Microsoft� Windows 7 (Professional Edition)
Windows Rights Management Services (RMS) Client with Service Pack 2.

Performance-Based Objectives

Upon successful completion of this course, students will be able to:

develop a macro.
format worksheets using macros.
create an interactive worksheet.
work with multiple worksheets.
perform calculations.

Course duration

3 Days

Course outline

Lesson 1: Developing Macros

Topic1A: Create a Macro Using the Macro Recorder
Topic1B: Edit a Macro
Topic1C: Debug a Macro
Topic1D: Customize the Quick Access Toolbar and Hotkeys
Topic1E: Set Macro Security

Lesson 2: Formatting Worksheets Using Macros

Topic2A: Insert Text
Topic2B: Format Text
Topic2C: Sort Data
Topic2D: Duplicate Data
Topic2E: Generate a Report

Lesson 3: Creating an Interactive Worksheet

Topic3A: Determine the Dialog Box Type
Topic3B: Capture User Input

Lesson 4: Working with Multiple Worksheets

Topic4A: Insert, Copy, and Delete Worksheets
Topic4B: Rename Worksheets
Topic4C: Modify the Order of Worksheets
Topic4D: Print Worksheets

Lesson 5: Performing Calculations

Topic5A: Create User-Defined Functions
Topic5B: Automate SUM Functions