Advanced Excel Macros and VBA Programming

To elevate delegates programming skills to a professional level.

Course Outline
Advanced Excel Macros and VBA Programming 2 Days

Who is it for?

This course is designed for those who already have significant VBA programming experience and would like to enhance all the advanced features of VBA. At the end of the course, you will be able to optimise your code, manipulate complex data structures and create your own objects using class modules.

Review of Coding Techniques

• Control structures and their use
• Declaring and using variables
• Objects, properties and methods
• Debugging and Error Handling

Good Programming Practice

• What is Good Programming Practice?
• Elements of good programming practice
• Indentation and comments
• Correct use of control structures
• Modular Programming
• How to identify BAD programming practice
• Identifying redundant code (modules & procedures)

Arrays

• Create and populate arrays of values
• Fixed vs. dynamic arrays
• Array optimization
• Multidimensional arrays
• Arrays and objects
• Resizing arrays while keeping data

Importing Data from Databases using ADO

• Understanding Active-X data objects (ADO)
• Referencing the ADO Object Library
• The Connection string
• Opening and using Recordsets
• Manipulating data

Consolidating Data

• Importing and Consolidating Data from Other Files (Excel and Text)
• Import data
• Copy data into other sheets
• File system objects and dialogs
• Read and write text files

Charting in VBA

• Overview of chart related objects
• Create a chart sheet through VBA
• Create an embedded chart through VBA

Using the PivotTable Object

• Defining PivotTables through VBA
• Create a Pivot Cache
• Create a Pivot Table
• Define the structure of the Pivot Table using PivotFields

Object Oriented Programming

• What is a class?
• Creating a class module
• Creating properties and methods