Course outline for
Microsoft Excel 2010 and Visual Basic for Applications

About This Course

Course Prerequisites

Course Overview

Course Objectives

How To Use This Book

Lesson 1: Developing Macros

TOPIC A: Create a Macro Using the Macro Recorder

Visual Basic for Applications

Object-Oriented Programming

Macros

Macro Options

The Macro Recorder

Personal Macro Workbooks

How to Create a Macro Using the Macro Recorder

Activity 1-1

TOPIC B: Edit a Macro

The Visual Basic Editor

Projects

Modules

The Modules Folder

Objects

Properties

Methods

VBA Comments

How to Edit a Macro

Activity 1-2

TOPIC C: Debug a Macro

The Debugging Process

Debugging Tools

How to Debug a Macro

Activity 1-3

TOPIC D: Customize the Quick Access Toolbar and Hotkeys

How to Customize the Quick Access Toolbar and Hotkeys

Activity 1-4

TOPIC E: Set Macro Security

Digital Certificates

Digital Signatures

Macro Security Settings

How to Set Macro Security

Activity 1-5

Summary

Review Questions

Lesson 2: Formatting Worksheets Using Macros

TOPIC A: Insert Text

The Selection Property

The ActiveSheet Property

The Name Property

The Value Property

Concatenation

How to Insert Text

Activity 2-1

TOPIC B: Format Text

How to Format Text

Activity 2-2

TOPIC C: Sort Data

The Range Object

The Select Method

The CurrentRegion Property

How to Sort Data

Activity 2-3

TOPIC D: Duplicate Data

Data Types

Variables

Variable Naming Rules

Operators

The Assignment Operator

Arithmetic Operators

Comparison Operators

Logical Operators

The For Next Loop

Do Loops

The Worksheets Object

The Count Property

The Offset Property

The Copy Method

The Paste Method

How to Duplicate Data

Activity 2-4

TOPIC E: Generate a Report

The Columns Property

The AutoFit Method

The Address Property

The Call Statement

The Font Property

The End Property

How to Generate a Report

Activity 2-5

Summary

Review Questions

Lesson 3: Creating an Interactive Worksheet

TOPIC A: Determine the Dialog Box Type

Message Boxes

Input Boxes

Activity 3-1

TOPIC B: Capture User Input

The InputBox Function

Constants

The MsgBox Function

The Code Continuation Character

The vbCrLf Constant

Decision Structures

The Select Case Structure

The If Then Structure

The Else Clause

How to Capture User Input

Activity 3-2

Summary

Review Questions

Lesson 4: Working with Multiple Worksheets

TOPIC A: Insert, Copy, and Delete Worksheets

The Add Method

The Copy Method

The Delete Method

How to Insert, Copy, and Delete Worksheets

Activity 4-1

TOPIC B: Rename Worksheets

Expressions

The DateSerial Function

The Format Function

How to Rename Worksheets

Activity 4-2

TOPIC C: Modify the Order of Worksheets

The Move Method

How to Modify the Order of Worksheets

Activity 4-3

TOPIC D: Print Worksheets

The PrintPreview Method

The PrintOut Method

How to Print Worksheets

Activity 4-4

Summary

Review Questions

Lesson 5: Performing Calculations

TOPIC A: Create User-Defined Functions

User-Defined Functions

Types of Functions

Arguments

How to Create User-Defined Functions

Activity 5-1

TOPIC B: Automate SUM Functions

Declared Range Objects

The Set Statement

Range Object Cell Addressing

The Rows Property

The Formula Property

The Columns Property

Address Property Cell Reference Settings

How to Automate SUM Functions

Activity 5-2

Summary

Review Questions

Lesson Labs

Lesson 1

Lesson Lab 1-1

Lesson Lab 1-2

Lesson 2

Lesson Lab 2-1

Lesson Lab 2-2

Lesson 3

Lesson Lab 3-1

Lesson 4

Lesson Lab 4-1

Lesson Lab 4-2

Lesson 5

Lesson Lab 5-1

Course Wrap-Up

Post-Course Assessment

Course Summary

Appendices

Keyboard Shortcut Quick Reference Sheet

Glossary

Index