Excel 365 VBA Including Your VBA Projects

17 Ratings

Course Number: VBA-137
Duration: 4 days (26 hours)
Format: Live, hands-on

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: This 4-day course includes a day of time spent working on your actual VBA projects, existing and under development. We also offer a 3-day "off-the-shelf" version without the extra day to work on your specific projects.

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.
  • Receive assistance with your organization's own specific needs and goals. We work with you to design, develop, and debug your company’s projects.

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
Advanced Excel Functions
  • Index
  • Match
  • XLookup
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
Your Own Project Topics/Advanced Topics
  • Process data quickly using Arrays
  • Access data from external sources such as Access, Oracle, SQL Server, etc.
  • Interface with other products, such as Word, Outlook, PowerPoint
  • Retrieve and parse data in external files
  • Troubleshoot existing logic failures
  • Designing macros for speed, reducing errors, and maintainability
  • Any other projects with which you would like help
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.


Learn faster

Our live, instructor-led lectures are far more effective than pre-recorded classes

Satisfaction guarantee

If your team is not 100% satisfied with your training, we do what's necessary to make it right

Learn online from anywhere

Whether you are at home or in the office, we make learning interactive and engaging

Multiple Payment Options

We accept check, ACH/EFT, major credit cards, and most purchase orders



Recent Training Locations

Alabama

Birmingham

Huntsville

Montgomery

Alaska

Anchorage

Arizona

Phoenix

Tucson

Arkansas

Fayetteville

Little Rock

California

Los Angeles

Oakland

Orange County

Sacramento

San Diego

San Francisco

San Jose

Colorado

Boulder

Colorado Springs

Denver

Connecticut

Hartford

DC

Washington

Florida

Fort Lauderdale

Jacksonville

Miami

Orlando

Tampa

Georgia

Atlanta

Augusta

Savannah

Hawaii

Honolulu

Idaho

Boise

Illinois

Chicago

Indiana

Indianapolis

Iowa

Cedar Rapids

Des Moines

Kansas

Wichita

Kentucky

Lexington

Louisville

Louisiana

New Orleans

Maine

Portland

Maryland

Annapolis

Baltimore

Frederick

Hagerstown

Massachusetts

Boston

Cambridge

Springfield

Michigan

Ann Arbor

Detroit

Grand Rapids

Minnesota

Minneapolis

Saint Paul

Mississippi

Jackson

Missouri

Kansas City

St. Louis

Nebraska

Lincoln

Omaha

Nevada

Las Vegas

Reno

New Jersey

Princeton

New Mexico

Albuquerque

New York

Albany

Buffalo

New York City

White Plains

North Carolina

Charlotte

Durham

Raleigh

Ohio

Akron

Canton

Cincinnati

Cleveland

Columbus

Dayton

Oklahoma

Oklahoma City

Tulsa

Oregon

Portland

Pennsylvania

Philadelphia

Pittsburgh

Rhode Island

Providence

South Carolina

Charleston

Columbia

Greenville

Tennessee

Knoxville

Memphis

Nashville

Texas

Austin

Dallas

El Paso

Houston

San Antonio

Utah

Salt Lake City

Virginia

Alexandria

Arlington

Norfolk

Richmond

Washington

Seattle

Tacoma

West Virginia

Charleston

Wisconsin

Madison

Milwaukee

Alberta

Calgary

Edmonton

British Columbia

Vancouver

Manitoba

Winnipeg

Nova Scotia

Halifax

Ontario

Ottawa

Toronto

Quebec

Montreal

Puerto Rico

San Juan