Excel

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.

Prerequisites

Delegates should have attended the Intermediate VBA course or have the equivalent knowledge and experience of programming in VBA.

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

Tags: Microsoft Excel

About the Trainers

Qualifications - European Computing Driving License (ECDL) Microsoft Office Specialists (MOS) Microsoft Office User Specialist (MOUS), Microsoft Certified Applications Specialist (MCAS), 2 of our trainers have taken the Institute of Learning Management Train the Trainer Master Class Award. Every trainer has a minimum of 5 years training experience.

mpn

NC Member logo

Latest News

Windows 8 and 10

Windows 8 courses now available.

New courses for Microsoft Windows 10 will be available soon – be one of the first to see it with us.

MS Office Training

Microsoft Office weekend training and Microsoft Office 2013 upgrade training now available.

Testimonials

"I have used NC Training Services for a number of years now to provide a range of training for staff at 2 different companies. I have always been very impressed by the flexibility of the company and the consistency of the training. The feedback from all staff who have attended the courses has been extremely positive and has definitely suited the needs of the people. Due to the ability to tailor make a course, I have been able to provide clear objectives (which can vary depending on the company/people being trained) and this is something I had not been able to do before and it certainly proved extremely beneficial for the people attending the course. I would certainly recommend NC Training Services."

Caroline Holt, Customer Service Manager

"After taking part in the intermediate and advanced excel courses I soon booked onto the word training. Nicola was both helpful and technical. The courses were just the right length and I would strongly recommend."

James Harris, Critchleys Chartered Accountants