Microsoft Excel: Visual Basic for Applications
(VBA) Macros | Introduction

Microsoft Excel Training Series | Level 4

Course Details

Duration: 2 days (9 am – 4 pm)

Microsoft® Excel® Versions: 2016 | 2019 | 2021 | 365 (Windows)

Delivery Methods (Instructor-led): In-person (Live classroom) | Online in virtual classroom

Course Dates: June 26/27, 2024 (Online in virtual classroom or In-person) | View schedule Course schedule

Course Fee: $495 CAD per person + HST (Virtual classroom), $545 CAD per person + HST (Bring your own device for in-person courses only) or $595 CAD per person + HST (Avantix Learning provides device for in-person courses only)*

Timing: Public scheduled courses run from 9:00 am to 4:00 pm (Eastern Time).

Virtual classroom courses: Our instructor-led virtual classroom courses are delivered in a virtual classroom environment. Students will be sent a virtual classroom invitation prior to the course.

In-person classroom courses: Our instructor-led, live classroom (in-person) courses are held in downtown Toronto at 18 King Street East, Suite 1400, Toronto, Ontario, Canada. Some courses are also held at an alternate downtown Toronto location.

Custom training: This course may be delivered at your site or ours as an instructor-led virtual classroom or in-person solution. Contact us at info@avantixlearning.ca for more information including savings for custom group training.

Some public courses and / or formats and may be subject to a minimum enrollment requirement.

Register for this training course.

Course Overview

In this course, students will learn to automate Excel by writing VBA (Visual Basic for Applications) macros. Designed for the novice or non-programmer, students will start by recording VBA macros and then use the Visual Basic Editor (VBE) window to write and edit code for sub procedures in modules. Basic syntax logic will be reviewed and students will work with projects, modules, procedures, variables and constants within the VBE window. If statements and loops will also be created using various examples. Students will run macros using different strategies including  keyboard shortcuts. A simple user-defined function will also be written to automate a calculation. Throughout the course, the instructor will include numerous tips, tricks and shortcuts. Each student will also receive a full course manual.

Prerequisite: Microsoft Excel: Intermediate / Advanced or equivalent knowledge and skills.

Location and timing: Public scheduled courses are held online in virtual classroom format or in downtown Toronto and run from 9:00 am to 4:00 pm (Eastern Time).

Related training: View all Microsoft Excel courses >

INCLUDED IN THIS COURSE

  • Comprehensive course manual or published book
  • Keyboard shortcuts quick reference
  • Sample and exercise files
  • Refreshments (for classes conducted in Avantix Learning classrooms)
  • Certificate of completion
  • Follow-up email support

Course Topics

Overview of Macro Concepts

  • What is a macro?
  • Displaying the Developer tab in Excel
  • Creating macros using the Macro Recorder
  • Assigning a keyboard shortcut
  • Viewing a list of macros
  • Viewing the VBA code for a recorded macro
  • Absolute vs relative referencing
  • Limitations of recorded macros

Saving and Opening Files with Macros

  • Deciding where to save macros
  • Saving workbooks with macros
  • Opening workbooks with macros

Macro Security and Trust Centre Settings

  • Specifying macro security settings
  • Creating a trusted folder

Working in the VBE (Visual Basic Editor)

  • Overview of VBE menus and toolbars
  • Displaying different parts of the window
  • Working with the Project Explorer
  • Viewing the iVBA Code window
  • Setting properties
  • Displaying other windows
  • Returning to Excel

Getting Started Writing Code using VBA

  • Inserting a module
  • Basic VBA syntax logic
  • Creating sub procedures or macros
  • Naming sub procedures or macros
  • Controlling the display of code
  • Adding comments
  • Referencing objects
  • Using methods and properties
  • Getting help

Working with Modules

  • Different ways of Inserting new modules
  • Creating multiple sub procedures in a module
  • Renaming modules
  • Deleting modules
  • Saving a backup of modules

Running Macros

  • Using the Macros dialog to run macros
  • Running macros using keyboard shortcuts
  • Running macros from within the VBA window
  • Displaying buttons on the Quick Access Toolbar or the Ribbon to run macros
  • Running macros by creating buttons on a worksheet

Using Variables and Constants

  • Using constants and variables in VBA code
  • Types of variables including integer, long and string
  • Declaring variables
  • Benefits of declaring variables

Changing Scope

  • Procedure level scope, module level scope or public scope

Displaying Input Boxes and Message Boxes

  • Displaying messages
  • Using input boxes for entry
  • Using message boxes and input boxes with variables
  • Displaying different types of buttons

Using If Statements in VBA

  • Benefits of If … Then statements
  • Syntax for  If  … Then … Else statements
  • Using If statements with message boxes or input boxes

Using For … Next Loops

  • Using loops
  • Using For … Next loops
  • Ending a loop
  • Changing loop step values

Basic Debugging

  • Common errors and break mode
  • Using Reset
  • Commenting out problem lines of code

*Prices subject to change

NOTE: This course is designed for students who have never used VBA. For those users who have been using VBA, we recommend the Intermediate Excel VBA course.

Our instructor-led courses are delivered in virtual classroom format or at our downtown Toronto location at 18 King Street East, Suite 1400, Toronto, Ontario, Canada (some in-person classroom courses may also be delivered at an alternate downtown Toronto location). Contact us at info@avantixlearning.ca if you'd like to arrange custom instructor-led virtual classroom or onsite training on a date that's convenient for you.

Copyright 2024 Avantix® Learning

Microsoft Excel training man pointing to icons.

You may like

What is Power Query in Excel?

What is Power Query in Excel?

What is Power Query in Excel and Why is It So Useful? by Avantix Learning Team | Updated November 18, 2023 Applies to: Microsoft® Excel® 2016, 2019, 2021 and 365 (Windows) Power Query in Excel is a powerful data transformation tool that allows you to import data from...

How to Stop or Control Green Error Checking Markers in Excel

How to Stop or Control Green Error Checking Markers in Excel

Turn Off or Customize Error Checking Options (and Markers) in Excel by Avantix Learning Team | Updated September 15, 2023 Applies to: Microsoft® Excel® 2010, 2013, 2016, 2019, 2021 and 365 (Windows) In Microsoft Excel, errors are flagged by default with a small green...

How to Use Flash Fill in Excel (4 Ways with Shortcuts)

How to Use Flash Fill in Excel (4 Ways with Shortcuts)

4 Ways to Use Flash Fill in Excel to Extract, Combine, Clean or Format Data by Avantix Learning Team | Updated September 15, 2023 Applies to: Microsoft® Excel® 2013, 2016, 2019, 2021 or 365 (Windows) You can use Flash Fill in Excel to extract, combine, clean or format...

How to Convert Text to Numbers in Excel (5 Ways)

How to Convert Text to Numbers in Excel (5 Ways)

5 Ways to Convert Numbers Stored as Text to Numbers in Microsoft Excel Worksheets by Avantix Learning Team | Updated September 15, 2023 Applies to: Microsoft® Excel® 2010, 2013, 2016, 2019, 2021 and 365 (Windows) There are several ways to convert text to numbers in...

How to Insert Multiple Rows in Excel (4 Fast Ways with Shortcuts)

How to Insert Multiple Rows in Excel (4 Fast Ways with Shortcuts)

Insert Multiple Rows Quickly in an Excel Worksheet by Avantix Learning Team | Updated September 15, 2023 Applies to: Microsoft® Excel® 2013, 2016, 2019, 2021 and 365 (Windows) You can quickly insert multiple rows in Excel using a drop-down menu, keyboard shortcuts or...

You may also like

How to Freeze Rows in Excel (One or Multiple Rows)

How to Freeze Rows in Excel (One or Multiple Rows)

Freeze One or More Rows in an Excel Worksheet by Avantix Learning Team | Updated October 25, 2023 Applies to: Microsoft® Excel® 2010, 2013, 2016, 2019, 2021 and 365 (Windows) You can freeze one or more rows in an Excel worksheet using the Freeze Panes command. If you...

How to Show or Hide Gridlines in Excel

How to Show or Hide Gridlines in Excel

Show or Hide Gridlines in Excel Worksheets (+ Shortcuts) by Avantix Learning Team | Updated October 15, 2023 Applies to: Microsoft® Excel® 2013, 2016, 2019, 2021 and 365 (Windows) You can remove or hide gridlines in Excel worksheets to simplify worksheet design. By...

Microsoft, the Microsoft logo, Microsoft Office and related Microsoft applications and logos are registered trademarks of Microsoft Corporation in Canada, US and other countries. All other trademarks are the property of the registered owners.

Avantix Learning |18 King Street East, Suite 1400, Toronto, Ontario, Canada M5C 1C4 | Contact us at info@avantixlearning.ca

Upcoming Training
Course
Microsoft Excel: Visual Basic for Applications course | Introduction (Online in virtual classroom or in-person in Toronto)
Location
Avantix Learning, 18 King Street East, Suite 1400,Toronto, Ontario, Canada,
Next course starts
June 26, 2024
Next course ends
June 27, 2024

Pin It on Pinterest