Microsoft Excel: Visual Basic for Applications
(VBA) Macros | Introduction
Microsoft Excel Training Series | Level 4
Duration: 2 days (9 am – 4 pm)
Microsoft® Excel® Versions: 2013 | 2016 | 2019 | 365 (Windows)
Delivery Methods (Instructor-led): In-person (Live classroom) | Virtual classroom
Course Dates: September 27/28, 2021 (Virtual classroom) | December 2/3, 2021 (Virtual classroom) | View schedule
Course Fee: $495 CAD per person (Virtual classroom), $545 CAD per person (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.
Live classroom courses: Our instructor-led live classroom (in-person) courses are held in downtown Toronto at the Toronto Star Building, 1 Yonge Street, Suite 1801. 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 or virtual classroom solution. Contact us at email@example.com for more information including savings for custom group training.
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
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
- 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
- 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
- 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.
Our instructor-led courses are delivered in virtual classroom format or at our downtown Toronto location at 1 Yonge Street, Suite 1801 (Toronto Star Building), Toronto, Ontario, Canada (some live classroom courses may also be delivered at an alternate downtown Toronto location). Contact us at firstname.lastname@example.org if you'd like to arrange custom training on a date that's convenient for you.
Copyright 2021 Avantix® Learning
You may like
Convert Pounds to Kilograms or Vice Versa in Excel Worksheets by Avantix Learning Team | Updated August 14, 2021 Applies to: Microsoft® Excel® 2010, 2013, 2016, 2019 and 365 (Windows) You can convert kilograms to pounds (kg to lb) or pounds to kilograms using formulas...
Apply or Remove Strikethrough Using Shortcuts in Excel by Avantix Learning Team | Updated July 20, 2021 Applies to: Microsoft® Excel® 2013, 2016, 2019 and 365 (Windows) You can apply strikethrough to text or values in Excel to cross out or create a line through a cell...
Lock the Position of a Slicer in an Excel Worksheet So It Can’t Be Moved by Avantix Learning Team | Updated June 14, 2021 Applies to: Microsoft® Excel® 2013, 2016, 2019 and 365 (Windows) Slicers are a great tool in Excel to filter tables or pivot tables. You can lock...
Easily Expand or Collapse the Formula Bar in Excel by Avantix Learning Team | Updated May 27, 2021 Applies to: Microsoft® Excel® 2010, 2013, 2016, 2019 and 365 (Windows) In Microsoft Excel, the Formula Bar appears below the Ribbon by default. When you enter data or a...
Quickly Fill Blanks with Zeros or Other Values in Excel Worksheets (0, -, N/A, Null or Other Text) by Avantix Learning Team | Updated August 7, 2021 Applies to: Microsoft® Excel® 2010, 2013, 2016, 2019 and 365 (Windows) You can quickly replace blank cells in Excel...
Using a Keyboard to Navigate and Select in Task Panes by Avantix Learning Team | Updated August 26, 2021 Applies to: Microsoft® Word® 2010, 2013, 2016, 2019 and 365 (Windows) This article is the third in a series of navigating and choosing commands in Word, Excel and...
You may also like
Try These Useful PowerPoint Keyboard Shortcuts in Outline View by Avantix Learning Team | September 18, 2021 Applies to: Microsoft® PowerPoint® 2013, 2016, 2019 and 365 (Windows) The outline has been an integral part of Microsoft PowerPoint for many years. It is...
Insert the Degree Symbol in PowerPoint Presentations by Avantix Learning Team | Updated September 17, 2021 Applies to: Microsoft® PowerPoint® 2013, 2016, 2019 and 365 (Windows) You can insert or type the degree symbol in PowerPoint presentations using built-in...
Change the Font on All Slides Using Themes, Masters, Layouts or Replace Fonts by Avantix Learning Team | Updated September 18, 2021 Applies to: Microsoft® PowerPoint® 2013, 2016, 2019 and 365 (Windows) If you want to change the font on all slides in a PowerPoint...
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 | email@example.com