VBA Training
VBA Excel-Advanced
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