Microsoft 365 Excel: VBA

DOWNLOAD A FREE SAMPLE

$495.00

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.

For more information, download a free sample now..

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

Choose from 81 courses available in Articulate Storyline (.story) format.

  • Social Selling for Small Businesses
  • 10 Minute Presentation
  • Successfully Managing Change
  • Performance Management: Managing Employee Performance
  • Building Better Teams
  • Anger Management: Understanding Anger
  • Conflict Resolution: Getting Along in the Workplace
  • Hiring for Success: Behavioral Interviewing Techniques
  • Creating a Top Notch Talent Management Program
  • Conducting Effective Performance Reviews
  • Stress Management
  • Managing Customer Service
  • Closing the Generation Gap in the Workplace
  • Accounting Skills for the New Supervisor
  • Onboarding: The Essential Rules for a Successful Onboarding Program
  • Employee Recognition: Appreciating Your Workforce
  • Creativity In The Workplace
  • Diversity, Equity, and Inclusion
  • Unconscious Bias
  • Becoming Management Material
  • Coaching and Mentoring
  • Motivation Training: Motivating Your Workforce
  • The ABCs of Supervising Others
  • The Professional Supervisor
  • Delegation: The Art of Delegating Effectively
  • Team Building: Developing High Performance Teams
  • Meeting Management: The Art of Making Meetings Work
  • Leadership Skills for Supervisors
  • Managing Difficult Conversations
  • Giving Effective Feedback
  • Managing the Virtual Workplace
  • Managing Across Cultures
  • Women Leadership: Owning Your Strengths and Skills
  • Dealing With Difficult People
  • Vendor Management Essentials
  • How to Become a Leader with Integrity
  • Corrective Action
  • Project Management Principles and Performance Domains
  • Advanced Writing Skills
  • Public Speaking: Presentation Survival School
  • Emotional Intelligence
  • Communication Strategies
  • Critical Elements of Customer Service
  • Skills for the New Employee
  • Business Etiquette: Gaining that Extra Edge
  • Conquering Your Fear of Speaking in Public
  • Time Management
  • Business Writing That Works
  • Critical Thinking
  • Goal Setting
  • Personal Brand: Maximizing Personal Impact
  • Self Leadership
  • Managing Pressure and Maintaining Balance
  • Active Listening
  • Fostering Innovation
  • Delivering Dynamic Virtual Presentations
  • Entrepreneurship 101
  • Workplace Harassment: What It is and What to Do About It
  • Workplace Violence: How to Manage Anger and Violence in the Workplace
  • Lean Process Improvement
  • Employee Accountability
  • Disability Awareness: Working with People with Disabilities
  • Crisis Management
  • Strategic Planning
  • Continuous Improvement with Lean
  • Creating a Positive Work Environment
  • Developing a Safety Procedures Manual
  • Beyond Workplace Politics
  • Digital Transformation
  • GDPR Readiness: Creating a Data Privacy Plan
  • Cybersecurity Fundamentals
  • Respect In The Workplace
  • Advanced Skills for the Practical Trainer
  • Facilitation Skills
  • Survival Skills for the New Trainer
  • Using Activities to Make Training Fun
  • Developing a Training Needs Analysis
  • Measuring Training Results
  • Risk Management
  • Safety in the Workplace

Download a Free Sample

Your privacy is important to us. For more information, take a look at our Privacy Policy.