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

Microsoft Excel Training Series | Level 4

Course Details

Duration: 2 days

Microsoft® Excel® Versions: 2010 | 2013 | 2016 | 365 (Windows)

Delivery Methods: Instructor-led classroom (ILC) | Virtual classroom (VC) on request

Course Dates: November 19/20, 2018 | February 25/26, 2019 | View schedule Course schedule

Course Fee: $595 CDN per person + HST or BYOD (bring your own device): $545 CDN person + HST*

Note: Public scheduled dates for this course are delivered as live instructor-led classroom training.

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 in downtown Toronto and run from 9:00 am to 4:00 pm (Eastern Standard Time).

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

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

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.

Register Now for a public course at our downtown Toronto location at 1 Yonge Street, Suite 1801 (Toronto Star Building), Toronto, Ontario, Canada.

Do you have any other questions? Contact us!

If you are looking for more information regarding versions, check out our Microsoft Excel versions overview.

To request this page in an alternate format, contact our staff.

*Prices subject to change.

Copyright 2018 Avantix® Learning Inc.

Instructor writing on virtual graph in Microsoft Excel VBA training course in Toronto.

You may like

How to Freeze Row and Column Headings in Microsoft Excel

As many Excel worksheets can become quite large, it can be useful to freeze row and column headings so that they are locked in place when you scroll through your worksheet. In Excel, you can freeze both row headings and column headings or just one.

10 Excel Tips for Working with the Subtotal Feature

You can insert subtotals in Microsoft Excel data sets or lists using the Subtotal feature. Check out these 10 great tips which include showing and hiding subtotals, formatting subtotals, copying only subtotals and grand totals and more …

5 Awesome Excel Chart Shortcuts

If you create a lot of charts in Excel, you’re probably looking for shortcuts to speed up creation and formatting. Here are 5 useful shortcuts you can use with your Excel charts.

You may also like

How to Change the Measurement System in Microsoft Word

You can easily change the measurement system units in Microsoft Word for all of your documents. If you’d prefer to work in inches rather than centimeters (or vice versa), simply change the measurement system in Word’s Options.

How to Add Notes in PowerPoint Files

You can add notes or speaker notes in your PowerPoint files using the Notes pane in Normal view. Notes may be viewed by a presenter during a presentation and may be printed as well. When you create your PowerPoint slides, you can add, edit and format notes.

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 | 1 Yonge Street, Suite 1801 (Toronto Star Building), Toronto, Ontario, Canada M5E 1W7 | info@avantixlearning.ca

Upcoming Training
Course
Microsoft Excel: Visual Basic for Applications | Introduction (Toronto course)
Location
Avantix Learning, 1 Yonge Street, Suite 1801 (Toronto Star Building),Toronto, Ontario,Canada
Next course starts
November 19, 2018
Next course ends
November 20, 2018

Pin It on Pinterest