Course outline for
Microsoft Excel 2016 and VBA

About This Course

Course Prerequisites

Course Overview

Course Objectives

How To Use This Book

Lesson 1:
Developing Macros

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

The Visual Basic Editor

Projects

Modules

The Modules Folder

Objects

Properties

Methods

VBA Comments

How to Edit a Macro

Activity 1-2

The Debugging Process

Debugging Tools

How to Debug a Macro

Activity 1-3

How to Customize the Quick Access Toolbar and Hotkeys

Activity 1-4

Digital Certificates

Digital Signatures

Macro Security Settings

How to Set Macro Security

Activity 1-5

Summary

Review Questions

Lesson 2:
Formatting Worksheets Using Macros

The Selection Property

The ActiveSheet Property

The Name Property

The Value Property

Concatenation

How to Insert Text

Activity 2-1

How to Format Text

Activity 2-2

The Range Object

The Select Method

The CurrentRegion Property

How to Sort Data

Activity 2-3

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

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

Message Boxes

Input Boxes

Activity 3-1

The InputBox Function

Constants

The MsgBox Function

The Code Continuation Character

The vbCrLf Constant

Decision Structures

The Select Case Statement

The If Then Structure

The Else Clause

How to Capture User Input

Activity 3-2

Summary

Review Questions

Lesson 4:
Working with Multiple Worksheets

The Add Method

The Copy Method

The Delete Method

How to Insert, Copy, and Delete Worksheets

Activity 4-1

Expressions

The DateSerial Function

The Format Function

How to Rename Worksheets

Activity 4-2

The Move Method

How to Modify the Order of Worksheets

Activity 4-3

The PrintPreview Method

The PrintOut Method

How to Print Worksheets

Activity 4-4

Summary

Review Questions

Lesson 5:
Performing Calculations

User-Defined Functions

Types of Functions

Arguments

How to Create User-Defined Functions

Activity 5-1

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 Lab 1-1

Lesson Lab 1-2

Lesson Lab 2-1

Lesson Lab 2-2

Lesson Lab 3-1

Lesson Lab 4-1

Lesson Lab 4-2

Lesson Lab 5-1

Appendices

Keyboard Shortcut Quick Reference Sheet

Glossary

Index