In this class you will learn the skills to enable you to use the Visual Basic for Applications (VBA) programming language to simplify or automate activities and tasks using Microsoft Excel 2013. (Two day, 12-hour class.)
In Excel 2013 Advanced VBA we look at the implementation of real-life applications, including automation, database interactions (cloud-based), on-the-fly model creation, and other practical examples. In addition, you will take home custom templates, functions, class modules, and other code snippets that you can integrate into your own projects.
Objectives
Students looking to gain enhanced skills to apply VBA to develop macros to work with Office 2013 applications. You will work with the application object models, manipulate data across applications and manage files and folders using VBA.
Prerequisites
Students should have attended Excel 2013 Introduction to VBA or possess the equivalent knowledge.You should also be proficient in using advanced features in Excel, including how to create Charts, define PivotTables, and apply Filters and Sorting to data sets.
- Two day, 12-hour class (offered approximately once per month)
$1,195$1,095 ($100 discount)
Topics
Office 2013 Object Hierarchies and the Object Browser
- Object Hierarchies and Collections
- Properties, Methods and Events
- Using the Object Browser
- Office 2013 Application Reference Libraries
Working within an Application
- Concepts
- VBA or Macro Recorder
- Binding
- Object Variables
- Application Object Examples
Interaction between Office 2013 Applications
- Classes and References
- Working with Objects in another Application
- Editing Documents across Office 2013 Applications
Working with Data in Office 2013
- Data and Terminologies
- Manipulating Data in an Application
- ADO and Connections – Working with Recordsets
- Using Data between Applications
- Mail Merge
- Charting Data in Office 2013
User Defined Types and Arrays
- Creating a User Defined Type
- Creating a Class
- Creating Array Variables
- Using Arrays in Code
File and Folder Management using VBA
- Using the FileSystemObject
- Handling Folders using VBA
- Move, Rename, Copy or Delete a File using VBA
- Access File or Folder Attributes and Properties
Real-World Applications and Examples
- String and Message Resource Handlers
- CornerStone Tools and Methodology
- Event Handling (Excel App and VB Editor)
- Cloud-Based Database Connections
- Data Manipulation using SQL (Select, Insert, Update, Delete)
- Designing Modeless UserForms
