2 - day course
Print this page
Course Code: S163Ven
Who should attend?
This course is intended for participants who are familiar with Microsoft Excel 2016.
Prerequisites
Microsoft Excel 2016 - Level 2
Course Objectives
Upon completion of the course, participants will be able to accomplish the following:
  • Understand key concepts that will be used to build custom solutions using Microsoft Excel VBA Macros.
  • Become familiar with basic macro techniques for recording, writing, running, and maintaining macros.
  • Understand how Visual Basic for Applications is incorporated, used, and modified in Microsoft Excel.
  • Learn how to design and layout a user form and controls to create a customized dialog box to simplify data entry.
  • Understand how to prepare an Excel Workbook containing macros and toolbars for deployment to other users and machines.
  • Learn to customize macros with VBA source code to automate data entry tasks and enable source code protection.
  • Learn to enhance source code with variables, constants, and logical operators.
  • Learn to handle the three types of errors that can be generated when working with source code.
  • Learn to access external data by using ActiveX Data Objects.
  • Learn how to use Excel as an automation server to control Excel objects externally outside of the application.
Course Content  
    Building Solutions
    Course Overview ; Solution Concepts ; Data Entry Concepts ; Macro Concepts ; Programming Concepts ;
    Macro Basics
    Planning Macros ; Recording Macros ; Executing Macros ; Maintaining Macros ;
    VBA Programming Basics
    Understanding Objects ; Using the Visual Basic Editor ; Understanding VBA Code ; Executing VBA Code ; VBA Help Reference ;
    VBA User Form Basics
    Planning User Forms ; Designing User Forms ;
    Deployment Basics
    Preparing for Deployment ; Code Security ; Adding Macros to the Quick Access Toolbar ; Modifying Quick Access Toolbar Buttons ;
    Customizing Macros
    Prompting for User Input ; Automating Data Entry ; Recording Absolute vs. Relative References ; Protecting VBA Source Code ;
    Enhancing VBA Code
    Writing Source Code ; Variables and Constants ; Writing Tips ; Logical Operators ; Flow Control ;
    Handling Errors
    Handling Errors ; Syntax Errors ; Logical Errors ; Run-Time Errors ;
    Accessing External Data
    External References ; ActiveX Data Objects Concepts ; Connecting to a Database ; Retrieving Records from a Database ;
    Excel Automation
    Automation Concepts ; Working with Object Models ; Excel’s Object Model ; Automating Excel with VBScript ;