Strategic Business IT Planning, Deployment & Management Courses

Microsoft Office with VBA Programing

Targeted Audience:

This course is aimed at individuals with a basic grounding in Excel VBA and an advanced knowledge of Microsoft Excel to develop skills with introducing more sophisticated automation into their workflows.

Course Objective:

  • Variable types
  • Using object variables to represent worksheets and workbooks
  • Using count-based (For-Next) and conditional based (Do-Until, Do-While) loops
  • If-Then-Else-End If and Select Case statements
  • With blocks
  • Using Range and Cells objects
  • Benefits
  • Upon completion of the course delegates will have a good understanding of the major components of VBA.
  • Delegates will expand their reach by being able to use VBA to communicate with other Office applications, such as Word.
  • In addition, the course also covers working with data from database applications such as Access, and manipulating and presenting the data.

Course Outline:

Unit 1: The Excel Object Model

  • Exploring the Range object in detail
  • The versatile Current Region object
  • Working with collections of Workbooks and Worksheets
  • Manipulating Charts through VBA
  • Manipulating PivotTables through VBA

Unit 2: Arrays:

  • Efficient variable storage with arrays
  • Array optimization
  • Dynamic arrays
  • The Array function

Unit 3: Triggers and Events:

  • Running macros automatically
  • Executing macros on a timer
  • Associating macros with other Workbook events

Unit 4: Advanced Parameters:

  • Passing parameters by reference and by value

Unit 5: Working with Text Files:

  • Importing text files
  • Exporting text files
  • Using the FileStream object

Unit 6: Linking with Office:

  • Connecting to other Office applications
  • Working with other Office applications

Unit 7: Linking to data sources using ADO:

  • Understanding ActiveX Data Objects
  • Using Excel to communicate with other data sources
  • Understanding the connection string
  • Adding, reading, modifying and deleting data

Unit 8: Add-Ins:

  • How macro security works
  • Password protecting your code
  • Distributing macros via an Add-In

Strategic Business IT Planning, Deployment & Management Courses
Microsoft Office with VBA Programing (B)


Mercury dynamic schedule is constantly reviewed and updated to ensure that every category is being addressed at least once a month, if not once every week. Please check the training courses listed below and if you do not find the subject you are interested in, email us or give us a call and we will do our best to assist.