Course Icon Excel Training

Microsoft Excel VBA - Advanced

0800 1337 401

Course Objectives

At the end of this course Microsoft Excel VBA Advanced course you should have an understanding of the following:

  • An enhanced knowledge of how to use Arrays efficiently
  • Understanding and creating Class modules
  • Using ADO objects to import from MS Access
  • How to apply Macro security
  • Creating and using Class Modules
  • How to distribute your macro by saving as an Add-In
  • Write code to interact with other office application such as MS Word
  • Write code to connect to Database sources such as MS Access and analyse the data within the Excel application

Who Should Attend

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

Course Syllabus

THE EXCEL OBJECT MODEL

  • Detailed examination of the Range Object
  • Why is the CurrentRegion Object so versatile?
  • Using Workbook and Worksheet Collections

CLASS MODULES

  • What is a Class Module
  • Create a Class Module
  • Use the Class Module

TIMERS AND EVENTS

  • Running macros automatically
  • Executing macros on a timer
  • Attaching macros to Workbook events

ENHANCED ARRAY MANIPULATION

  • Optimising the array so the code executes faster in memory
  • Dynamic arrays
  • The Array function

PARAMETERS

  • Passing parameters by reference and by value
  • Working with other MS Office applications
  • Importing and Exporting from MS Word
  • Use ADO to import data sets from MS Access

WHAT IS ADO?

  • Using Excel to communicate with other data sources
  • Learn how to connect to databases using the connection string
  • Using properties and methods to add and delete data from Recordsets

WORKING WITH TEXT FILES

  • Importing and Exporting text files
  • FileStream object

SECURITY AND CREATING AN ADD-IN

  • What do the Security options allow you to do
  • Passwords
  • Distributing macros via an Add-In
See all of the available Seven Institute Excel Courses