25,000+ Courses Nationwide
0345 4506120

Microsoft Office - VBA Advanced

In this course 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 the Microsoft Office 2007 2019 applications. The course covers examples in Excel 2007 - 2019, Access 2007 - 2019, Word 2007 – 2019 and PowerPoint 2007 - 2019.

 

Please Note: Microsoft Excel 2016 is used as the primary application during the course for demonstration purposes.

Target audience

Delegates looking to gain enhanced skills to apply VBA to develop macros to work with Office applications. You will work with the application object models, manipulate data across applications and manage files and folders using VBA.

 

Select specific date to see price, venue and full details.

Learning Objectives

  • MS Office Object Hierarchies and the Object Browser
  • Working Within an MS Office Application
  • User Defined Types, Classes and Arrays
  • File and Folder Management Using VBA
  • Interaction Between MS Office Applications
  • Working with the Excel Range Object
  • Working with Data in Excel
  • Presenting Data in Excel
  • (Optional) Programming Access with VBA

 

Pre-Requisites

  • Have attended the QA course Introduction to VBA in Office 2007 - 2019 or have equivalent knowledge i.e.
    • Able to write macros which include variable declarations with appropriate scope, intrinsic functions, decision making structures and repeating code actions
  • Have a working knowledge of Excel 2007 - 2019, Access 2007 - 2019, Word 2007 - 2019 and PowerPoint 2007 - 2019
  • Understand how to create Charts, PivotTables and apply filters and sorting to data sets

 

Course Content

Module 1 - MS Office Object Hierarchies and the Object Browser

  • Topic A - Objects, Collections and Hierarchies
  • Topic B - Properties, Methods and Events
  • Topic C - Using the Object Browser
  • Topic D - MS Office Application Reference Libraries

Module 2 - Working Within an MS Office Application

  • Topic A - VBA or Macro Recorder
  • Topic B - Special Macros       
  • Topic C - Object Variables
  • Topic D - Binding
  • Topic E - Application Object Examples

Module 3 - User Defined Types, Classes and Arrays

  • Topic A - Introduction to User Defined Types (UDTs)
  • Topic B - Creating a User Defined Type
  • Topic C - Creating a Class
  • Topic D - Creating Array variables
  • Topic E - Using Arrays in Code

Module 4 - File and Folder Management Using VBA

  • Topic A - Using the FileSystemObject
  • Topic B - Handling Folders Using VBA
  • Topic C - Create a Folder
  • Topic D - Move, Rename, Copy or Delete a File
  • Topic E - File or Folder Properties and Attributes     

Module 5 - Interaction Between MS Office Applications

  • Topic A - Classes and References
  • Topic B - Working with Objects in another Application
  • Topic C - Editing Documents Across MS Office

Module 6 - Working with the Excel Range Object

  • Topic A - Range Objects Defined      
  • Topic B - Referencing Ranges using VBA
  • Topic C - Dynamic Range Handling
  • Topic D - Copying or Moving Range Data    

Module 7 - Working with Data in Excel

  • Topic A - Importing Data from a Delimited File or Web Page
  • Topic B - Excel and ADO
  • Topic C - Data Types or Formatting
  • Topic D - Validating Data
  • Topic E - Filtering and Sorting Data with VBA
  • Topic F - Using Worksheet Database Functions
  • Topic G - Excel Tables

Module 8 - Presenting Data in Excel

  • Topic A - Apply Conditional Formatting with VBA
  • Topic B - Working with Charts
  • Topic C - Pivot Tables

Module 9 – (Optional) Programming Access with VBA

  • Topic A - Navigating the Access Object Hierarchy
  • Topic B - Understanding Access Collections

Related Courses

Privacy Notice

In order to provide you with the service requested we will need to retain and use your contact information in accordance with our Privacy Notice. If you choose to provide us with this information you explicitly consent to us using the information as necessary to provide the requested service to you. If you do not agree please do not proceed to request the service from us.

Marketing Permissions

Would you like to receive our newsletter and other information on products and services which we think will be of interest to you by email. We will always treat your information with care and in accordance with our Privacy Notice. You are free to withdraw this permission at any time.

 

We work with the best