microsoft logo vba

Microsoft VBA courses

The following courses run between one and two times per month in the following locations: London, Birmingham, Bristol, Edinburgh, Glasgow, Manchester, Milton Keynes and Leeds.

We can also deliver these courses for you at your work address anywhere within the UK. 

 

Prices start from just £85 + VAT per person when a 1 day bespoke closed course is booked for up to six delegates attending.

 

 

    Course Duration           

Course Guide   

Microsoft VBA

3 days

BSG Training - delivering ICT Training Solutions for you

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.

Prerequisites: Have attended Introduction to VBA in Office 2007 - 2016 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, Access, Word and PowerPoint in any of the versions 2007 to 2016. Understand how to create Charts, PivotTables and apply filters and sorting to data sets

In this 3 day course, Delegates will learn to:
  • Understand MS Office Object Hierarchies and the Object Browser
  • Work within an MS Office Application
  • Use User Defined Types, Classes and Arrays
  • File and Folder Management using VBA
  • Interaction between MS Office Applications
  • Work with the Excel Range Object
  • Work with Data in Excel
  • Present Data in Excel
  • Programming in Access with VBA
Module 1: MS Office Object Hierarchies and the Object Browser
  • Objects, Collections and Hierarchies
  • Properties, Methods and Events
  • Using the Object Browser
  • MS Office Application Reference Libraries
Module 2: Working within an MS Office Application
  • VBA or Macro Recorder
  • Special Macros
  • Object Variables
  • Binding
  • Application Object Examples
Module 3: User Defined Types, Classes and Arrays
  • Introduction to User Defined Types (UDTs)
  • Creating a User Defined Type
  • Creating a Class
  • Creating Array Variables
  • Using Arrays in Code
Module 4: File and Folder Management using VBA
  • Using the FileSystemObject
  • Handling Folders using VBA
  • Create a Folder
  • Move, Rename, Copy or Delete a File
  • File or Folder Properties and Attributes
Module 5: Interaction between MS Office Applications
  • Classes and References
  • Working with Objects in another Application
  • Editing Documents across MS Office
Module 6: Working with the Excel Range Object
  • Range Objects Defined
  • Referencing Ranges using VBA
  • Dynamic Range Handling
  • Copying or Moving Range Data
Module 7: Working with Data in Excel
  • Importing Data from a Delimited File or Web Page
  • Excel and ADO
  • Data Types or Formatting
  • Validating Data
  • Filtering and Sorting Data with VBA
  • Using Worksheet Database Functions
  • Excel Tables
Module 8 - Presenting Data in Excel
  • Apply Conditional Formatting with VBA
  • Working with Charts
  • Pivot Tables
Module 9 - Programming Access with VBA
  • Topic A - Navigating the Access Object Hierarchy
  • Topic B - Understanding Access Collections

close
 

 

 Book a Course