How To Take This Class

Course Overview

In this Microsoft Excel 2013 VBA Training course, students will learn to use VBA in the Excel 2013 environment to create interactive worksheets and automate common worksheet tasks.

Course Prerequisites

Students should have completed, or have experience with, topics covered in the Microsoft Excel 2013 Introduction, Intermediate and Advanced Training courses.

Course Audience

This course is intended for advanced Microsoft Excel professionals that need to automate Excel spreadsheet tasks using Visual Basic for Applications (VBA).

What You'll Learn

Upon successful completion of this course, students will be able to:
  • Create, record, edit and debug a macro.
  • Format worksheets using VBA.
  • Create an interactive worksheet with VBA.
  • Perform custom calculations using VBA.

Course Duration

2 Days

Course Outline

  1. Generating VBA from Macros
    • Create a Macro Using the Macro Recorder
      • Visual Basic for Applications
      • Object-Oriented Programming
      • Macros
      • Macro Options
      • The Macro Recorder
      • Personal Macro Workbooks
      • How to Create a Macro Using the Macro Recorder
    • Edit a Macro
      • The Visual Basic Editor
      • Projects
      • Modules
      • The Modules Folder
      • Objects
      • Properties
      • Methods
      • VBA Comments
      • How to Edit a Macro
    • Debug a Macro
      • The Debugging Process
      • Debugging Tools
      • How to Debug a Macro
    • Customize the Quick Access Toolbar and Hotkeys
      • How to Customize the Quick Access Toolbar and Hotkeys
    • Set Macro Security
      • Digital Certificates
      • Digital Signatures
      • Macro Security Settings
      • How to Set Macro Security
  2. Formatting Worksheets Using VBA
    • Insert Text
      • The Selection Property
      • The ActiveSheet Property
      • The Name Property
      • The Value Property
      • Concatenation
      • How to Insert Text
    • Format Text
      • How to Format Text
    • Sort Data
      • The Range Object
      • The Select Method
      • The CurrentRegion Property
      • How to Sort Data
    • Duplicate Data
      • Data Types
      • Variables
      • Variable Naming Rules
      • Operators
      • The Assignment Operator
      • Arithmetic Operators
      • Comparison Operators
      • Logical Operators
      • The For Next Loop
      • Do Loops
      • The Worksheets Object
      • The Count Property
      • The Offset Property
      • The Copy Method
      • The Paste Method
      • How to Duplicate Data
    • Generate a Report
      • The Columns Property
      • The AutoFit Method
      • The Address Property
      • The Call Statement
      • The Font Property
      • The End Property
      • How to Generate a Report
  3. Creating an Interactive Worksheet with VBA
    • Determine the Dialog Box Type
      • Message Boxes
      • Input Boxes
    • Capture User Input
      • The InputBox Function
      • Constants
      • The MsgBox Function
      • The Code Continuation Character
      • The vbCrLf Constant
      • Decision Structures
      • The Select Case Statement
      • The If Then Structure
      • The Else Clause
      • How to Capture User Input
  4. Working with Multiple Worksheets in VBA
    • Insert, Copy, and Delete Worksheets
      • The Add Method
      • The Copy Method
      • The Delete Method
      • How to Insert, Copy, and Delete Worksheets
    • Rename Worksheets
      • Expressions
      • The DateSerial Function
      • The Format Function
      • How to Rename Worksheets
    • Modify the Order of Worksheets
      • The Move Method
      • How to Modify the Order of Worksheets
    • Print Worksheets
      • The PrintPreview Method
      • The PrintOut Method
      • How to Print Worksheets
  5. Performing Calculations with VBA
    • Create User-Defined Functions
      • User-Defined Functions
      • Types of Functions
      • Arguments
      • How to Create User-Defined Functions
    • Automate SUM Functions
      • Declared Range Objects
      • The Set Statement
      • Range Object Cell Addressing
      • The Rows Property
      • The Formula Property
      • The Columns Property
      • Address Property Cell Reference Settings
      • How to Automate SUM Functions

What Students Receive

Each student will receive:
  • A printed courseware manual for use during and after class.
  • Access to a searchable, electronic version of the courseware manual for reference after class (available for 12 months after class.)
  • A PC headset for communicating with your instructor and fellow students during your Microsoft Excel 2013 Training Class
  • A course completion certificate upon successful completion of your Microsoft Excel 2016 Training course
  • Six months of post-class email support from a Microsoft Excel Instructor