VBA is Microsoft’s powerful development tool for creating macros in Excel and other Microsoft Office applications. VBA is both a programming language and an integrated development environment that creates small strings of code that complete specific tasks.
Microsoft 365 Excel: VBA is intended to help all intermediate to advanced users get up to speed quickly with Visual Basic for Applications. It will cover different features of the Visual Basic Editor interface; show users how to record, edit, debug, and manage macros; and learn about the syntax that VBA uses to create macros.
By the end of the course, users should be comfortable with creating, recording, editing, and debugging a macro; formatting worksheets using macros; creating an interactive worksheet; and performing custom calculations using 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
About This Course
Course Prerequisites
Course Overview
Course Objectives
How to Use This Book
Lesson 1: Analyzing Data with Logical and Lookup Functions
TOPIC A: Use Text Functions
Text Functions
Logging into Office 365
Launching Excel Online
The LEFT and RIGHT Functions
The MID Function
The LEN Function
The TRIM Function
The UPPER, LOWER, and PROPER Functions
The CONCATENATE Function
The TRANSPOSE Function
Activity 1-1: Analyzing Data Using Text Functions
TOPIC B: Use Logical Functions
Logical Functions
Logical Operators
The AND Function
The OR Function
The IF Function
Activity 1-2: Analyzing Data Using Logical Functions
TOPIC C: Use Lookup Functions
Lookup Functions
The LOOKUP Function
The VLOOKUP Function
The HLOOKUP Function
The MATCH Function
The INDEX Function
Activity 1-3: Analyzing Data Using Lookup Functions
TOPIC D: Use Date Functions
The TODAY Function
The NOW Function
Serializing Dates and Times with Functions
Activity 1-4: Analyzing Data Using Date Functions
TOPIC E: Use Financial Functions
The IPMT Function
The PPMT Function
The NPV Function
The FV Function
Activity 1-5: Using Financial Functions
Summary
Review Questions
Lesson 2: Organizing Worksheet Data with Tables
TOPIC A: Create and Modify Tables
Tables
Table Components
The Create Table Dialog Box
Table Modification Options
Activity 2-1: Creating and Modifying a Table
TOPIC B: Sort and Filter Data
The Difference Between Sorting and Filtering
Sorting Data
Filter Operators
Removing Duplicate Values
Activity 2-2: Sorting Data
Summary
Review Questions
Lesson 3: Visualizing Data with Charts
TOPIC A: Create Charts
Charts
Chart Types
Chart Insertion Methods
Resizing and Moving the Chart
Adding Additional Data
Activity 3-1: Creating Charts
TOPIC B: Modify and Format Charts
The Difference Between Modifying and Formatting
Chart Elements
Minimize Chart Elements
The Chart Tools
Activity 3-2: Modifying and Formatting Charts
Summary
Review Questions
Lesson 4: Enhancing Workbooks
TOPIC A: Customize Workbooks
Comments
Hyperlinks
Pictures
Activity 4-1: Customizing Workbooks
TOPIC B: Preparing a Workbook for Multiple Audiences
Adding Alternative Text to Objects
Modifying Worksheets Using the Accessibility Checker
Managing Fonts
Activity 4-2: Preparing a Workbook for Multiple Audiences
Summary
Review Questions
Lesson Labs
Lesson 1
Lesson Lab 1-1
Lesson Lab 1-2
Lesson 2
Lesson Lab 2-1
Lesson 3
Lesson Lab 3-1
Lesson 4
Lesson Lab 4-1
Course Wrap-Up
Post-Course Assessment
Course Summary
Appendices
Keyboard Shortcut Quick Reference Sheet
Glossary
Index
