Microsoft 365 - Excel VBA and Macros - Introduction

Overview

This hands-on 1-day course is based on the current Microsoft 365 Desktop version of Excel. Participants using Excel 2016 or later will be able to follow along, although some of the newest features may not be available in older versions.

This advanced course is designed for learners who have solid previous experience with Excel, equivalent to On-Track Excel Level 2. Experienced, advanced, self-taught users, may find this course a valuable refresher.

Excel Macros are automated sequences of commands and actions that perform repetitive tasks in Microsoft Excel with a single click. They are created using the Visual Basic for Applications (VBA) programming language, which allows users to record or write instructions that can manipulate data, format worksheets, generate reports, or perform complex calculations automatically. By using macros, Excel users can dramatically improve efficiency and accuracy, especially when working with routine processes.

Prerequisites

• Solid familiarity with Microsoft Excel Desktop application - equivalent to Level 2
• Access to Microsoft Excel 365 Desktop application
• If using at work - IT permission to use Macros might be needed

Learning Objectives

Through the use of hands-on exercises participants will learn to:

• Record and Edit Simple Macros
• Understand the Difference between Relative and Absolute references
• Store Macros - Understand the Trust Center
• Begin to understand the VBA editor
• Access Macros through customization of the Quick Access Toolbar, the Ribbon and on worksheets

Course Modules

Module 1 - What are Macros

Understanding the Macro processes, working with the Trust Center and setting up the Developer Tab.

Module 2 - Creating and Recording Macros

Record and Run Macros, naming Macros, storing Macros, absolute and relative references

Module 3 - The VBA Editor

VBA, the editor, introduction to VB/Macro code, copying/pasting, creating a User-Defined Function, and working with Auto-Executable Macros

Module 4 - Dialog Boxes

VBA objects, built-in and user defined Input and Message Dialog Boxes

Module 5 - Understanding Loops

With/End With, For/Next, If/Then/Else, Case

Module 6 - Introduction to Controls

User forms, assigning Macros to the Quick Access Toolbar, the Ribbon and Worksheet Controls

Module 7 - Tips and Tricks

Learn to be more efficient in Excel and what resources are available

Using AI tools (CoPilot, ChatGPT etc.)

ON-TRACK Corporate Training has a diverse and flexible schedule that can be customized to your needs. We offer group courses (both public and corporate), one-on-one training, virtual (on-line) classroom (Zoom etc.) and on-site training options. We also have easily accessible on-line training videos on OnTrackTV.