Microsoft Excel 2007: Advanced VBA

Course Overview

You use Excel 2007 to work with data and need to improve the processes involved. This course looks at how you can work directly with Excel 2007 and its components to manipulate the application to help you work with your data.

Delegates that attend a Microsoft training course are twice as motivated than those that don't?

Related Courses

• Microsoft Excel 2007: Upgrade New Features
• Microsoft Excel 2007: Introduction for Beginners (1 day)
• Microsoft Excel 2007: Visual Basic for Applications (VBA) Programming (2 day)
• Microsoft Excel 2007: Advanced (1 day)
• Microsoft Excel 2007: Intermediate (1 day)
• Microsoft Excel 2007: Intermediate (2 day)
• Microsoft Excel 2007: Advanced
• Microsoft Excel 2007: Formulae & Functions
• Microsoft Excel 2007: Charts, Pivot Tables & Macros
• Microsoft Excel 2007: Intermediate and Advanced Training Course

Skills Gained

• Work with Excel 2007 components using VBA
• Manipulate and present data in Excel 2007 using VBA
• Create an Application in Excel 2007 using VBA

Prerequisites

Before attending this course, delegates should have attended our Introduction to VBA course or have equivalent VBA knowledge Experience of recording and writing simple macros Please note this course is not suitable for Excel 2007 users who have no VBA knowledge

• Experience of recording and writing simple macros
• Please note this course is not suitable for Excel 2007 users who have no VBA knowledge

Course Outline

Lesson 1: Excel 2007 Object Model

• The Excel Component Object Model
• Using Methods and Events
• Object Variables and Binding
• Creating User Defined Types and Classes

Lesson 2: Working with the Range Object

• Range Objects Defined
• Referencing Ranges using VBA
• Dynamic Range Handling
• Copying or Moving Range Data
• Creating Array Variables

Lesson 3: Working With Data in Excel

• Data Types v Formatting
• Validating Data
• Filtering and Sorting Data with VBA
• Using Database Functions
• Excel Tables

Lesson 4: Presenting Data in Excel

• Apply Conditional Formatting with VBA
• Working with Charts
• Pivot Tables

Lesson 5: Importing Data into Excel

• Creating Connections
• Managing Connections
• Excel and ADO

Lesson 6: Working with the Excel Interface

• Excel Interface Options
• Controlling the Excel Interface
• Using Events
• Creating Excel Add-Ins

Lesson 7: Managing Files and Folders from Excel

• FileSystemObject
• Managing Folders from Excel
• File Management Tasks using Excel
• Working with File Properties and Attributes from Excel
• Reading a Text File from Excel