Course outline for
Microsoft 365 Excel: VBA

About This Course

Course Prerequisites

Course Overview

Course Objectives

How To Use This Book

Lesson 1: Developing Macros

TOPIC A: Get to Know Macros

Visual Basic for Applications

Macros

Object-Oriented Programming

Macro Options

Activity 1-1: Getting to Know Macros

TOPIC B: Create a Macro

The Macro Recorder

Personal Macro Workbooks

How to Create a Macro Using the Macro Recorder

Activity 1-2: Creating a Macro Using the Macro Recorder

TOPIC C: Edit a Macro

The Visual Basic Editor

Projects

Modules

The Modules Folder

Objects

Properties

Methods

VBA Comments

How to Edit a Macro

Activity 1-3: Editing a Macro

TOPIC D: Debug a Macro

The Debugging Process

Debugging Tools

How to Debug a Macro

Activity 1-4: Debugging a Macro

TOPIC E: Customize the Quick Access Toolbar and Hotkeys

Add a Button to the Quick Access Toolbar to Run a Macro

Assign a Keyboard Shortcut to a Macro

Activity 1-5: Customizing the Quick Access Toolbar and Hotkeys

TOPIC F: Set Macro Security

Digital Certificates

Digital Signatures

Macro Security Settings

Activity 1-6: Setting Macro Security

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: Inserting Text

TOPIC B: Format Text

How to Format Text

Activity 2-2: Formatting Text

TOPIC C: Sort Data

The Range Object

The Select Method

The CurrentRegion Property

How to Sort Data

Activity 2-3: Sorting Data

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: Duplicating Data

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: Generating a Report

Summary

Review Questions

Lesson 3: Creating an Interactive Worksheet

TOPIC A: Determine the Dialog Box Type

Message Boxes

Input Boxes

Activity 3-1: Determining the Dialog Box Type

TOPIC B: Capture User Input

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: Capturing User Input

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: Inserting and Deleting Worksheets

TOPIC B: Rename Worksheets

Expressions

The DateSerial Function

The Format Function

How to Rename Worksheets

Activity 4-2: Renaming Worksheets

TOPIC C: Modify the Order of Worksheets

The Move Method

How to Modify the Order of Worksheets

Activity 4-3: Moving Worksheets within a Workbook

TOPIC D: Print Worksheets

The PrintPreview Method

The PrintOut Method

How to Print Worksheets

Activity 4-4: Printing a Workbook

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: Creating and Applying a User-Defined Function

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: Automating SUM Functions

Summary

Review Questions

Lesson Labs

Lesson 1

Lesson Lab 1-1

Lesson Lab 1-2

Lesson Lab 1-3

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