Excel 365 VBA Training Overview
Accelebrate's Excel 365 VBA training course teaches attendees how to automate Excel 365 using Visual Basic for Applications. Students take their skills to the next level with advanced topics including querying databases, interfacing with other Office products, troubleshooting, and more.
Important Note: We also offer a 4-day course that course includes a day of time spent working on your actual VBA projects, existing and under development
Location and Pricing
Accelebrate offers instructor-led enterprise training for groups of 3 or more online or at your site. Most Accelebrate classes can be flexibly scheduled for your group, including delivery in half-day segments across a week or set of weeks. To receive a customized proposal and price quote for private corporate training on-site or online, please contact us.
In addition, some courses are available as live, instructor-led training from one of our partners.
Objectives
- Use the main features of the VBA Editor window and learn core VBA concepts.
- Build sub procedures and user-defined functions.
- Learn the Excel object model and write code to control Excel objects.
- Create and use variables.
- Use a wide array of standard programming techniques.
- Create a user interface (a custom form complete with a variety of controls and code to drive the user form).
- Create PivotTables programmatically.
- Learn and use multiple troubleshooting and debugging features.
- Incorporate error handlers to deal with unforeseen issues.
- Explore additional advanced areas such as querying databases, interfacing with other Office products, reading external files, and various design, performance, and troubleshooting tips.
Prerequisites
All attendees must have prior knowledge of Excel. Prior scripting or programming knowledge is very helpful but not required.
Outline
Expand All | Collapse All
Getting Started
- Introducing Visual Basic for Applications
- Displaying the Developer Tab in the Ribbon
- Recording a Macro
- Saving a Macro-Enabled Workbook
- Running a Macro
- Editing a Macro in the Visual Basic Editor
- Understanding the Development Environment
- Using Visual Basic Help
- Closing the Visual Basic Editor
- Understanding Macro Security
Working with Procedures and Functions
- Understanding Modules
- Creating a Standard Module
- Understanding Procedures
- Creating a Sub Procedure
- Calling Procedures
- Using the Immediate Window to Call Procedures
- Creating a Function Procedure
- Naming Procedures
- Working with the Code Editor
Understanding Objects
- Understanding Objects
- Navigating the Excel Object Hierarchy
- Understanding Collections
- Using the Object Browser
- Working with Properties
- Using the With Statement
- Working with Methods
- Creating an Event Procedure
Using Expressions, Variables, and Intrinsic Functions
- Understanding Expressions and Statements
- Declaring Variables
- Understanding Data Types
- Working with Variable Scope
- Using Intrinsic Functions
- Understanding Constants
- Using Intrinsic Constants
- Using Message Boxes
- Using Input Boxes
- Declaring and Using Object Variables
Controlling Program Execution
- Understanding Control-of-Flow Structures
- Working with Boolean Expressions
- Using the If...End If Decision Structures
- Using the Select Case...End Select Structure
- Using the Do...Loop Structure
- Using the For...To...Next Structure
- Using the For Each...Next Structure
- Guidelines for use of Control-of-Flow Structures
Working with Forms and Controls
- Understanding UserForms
- Using the Toolbox
- Working with UserForm Properties, Events, and Methods
- Understanding Controls
- Setting Control Properties in the Properties Window
- Working with the Label Control
- Working with the Text Box Control
- Working with the Command Button Control
- Working with the Combo Box Control
- Working with the Frame Control
- Working with Option Button Controls
- Working with Control Appearance
- Setting the Tab Order
- Populating a Control
- Adding Code to Controls
- Launching a Form in Code
Debugging Code
- Understanding Errors
- Using Debugging Tools
- Setting Breakpoints
- Stepping through Code
- Using Break Mode during Run mode
- Determining the Value of Expressions
Handling Errors
- Understanding Error Handling
- Understanding VBA's Error Trapping Options
- Trapping Errors with the On Error Statement
- Understanding the Err Object
- Writing an Error-Handling Routine
- Working with Inline Error Handling
Conclusion
Training Materials
All VBA training attendees receive comprehensive courseware.
Software Requirements
- A full installation of Microsoft Office 365 (or at least Excel and Access), including Visual Basic support and Visual Basic Help.
- If you have purchased this course, please contact us for detailed setup instructions.
- For classes delivered online, all participants need either dual monitors or a separate device logged into the online session so that they can do their work on one screen and watch the instructor on the other. A separate computer connected to a projector or large screen TV would be another way for students to see the instructor's screen simultaneously with working on their own.
