Objectives: Building on Visual Basic for Applications in Excel, this course explores the Excel macros in more detail and consolidates Excel VBA programming skills. Participants will learn to write more ambitious and robust code.

Who should attend: Delegates who have attended the Introduction to Excel VBA course and have been using Excel VBA extensively.

Duration: 2 days

Module 1 – Overview of Introduction Course:

• Selecting ranges
• Variables
• IF and Case statements
• Formulas
• Error trapping
• Loops (For each, For, Do While)
• Forms / Dialog Boxes

Module 2 – Further work on the Excel Object Model:

• The Range Object
• The Current Region Object
• Workbooks and worksheets collections
• Chart Object
• List Object

Module 3 – Events

• Events within the application object

Module 4 – Arrays

• Dynamic arrays
• Array optimisation
• Multidimensional arrays
• Arrays and objects

Module 5 – Object Oriented Programming

• An overview of Classes and Objects
• Set and Let statements
• Creating new objects
• Creating new classes
• Error handling

Module 6 – Good Programming Techniques

• Recursive programming
• Using optional arguments
• Best practice for Excel programming

Module 7 – Advanced Procedures, parameters

• Passing parameters by reference and by value
• Optional and default parameters
• Passing an array of arguments to a procedure
• Using enumerations
• User-defined type
• Implicit and explicit conversions

Module 8 – Linking with Office Applications

• Connecting to other Office applications
• Working with other Office applications
• Understanding Active-X data objects
• The Connection string
• Manipulating data

Module 9 – Add-ins

• Creating simple add-ins
• Creating menu and toolbars in add-ins

Module 10 - (optional) Importing /Exporting from /to Access usin DAO and ADO

Content 3