About this course

Course type Specialist
Course code QAEX07VBAA
Duration 2 Days

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 workbooks and data.

Target Audience:

Experienced Excel 2007 users who work with data and need to create tools and solutions in Excel 2007 to manage and present information

Prerequisites

  • Delegates should have attended the www.qa.com/QAVBA07INT course or have equivalent VBA knowledge
  • Experience of recording and writing macros which include control structures and the declaration of variables
  • Please note this course is not suitable for Excel 2007 users who have no VBA knowledge

Delegates will learn how to

At the end of this course you will be able to:

  • Work with Excel 2007 components using VBA
  • Manipulate and present data in Excel 2007 using VBA
  • Work with files and folders in Excel 2007 using VBA

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

  • Importing Data into Excel
  • Data Types and 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: Working with the Excel Interface

  • Working with Page and Print Options
  • Controlling Visual Elements of a Workbook
  • Workbook Sharing and Track Changes
  • Workbook Security using VBA

Lesson 6: 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
Specialist Course

2 Days

Duration
Delivery Method

Delivery method

Classroom

Face-to-face learning in the comfort of our quality nationwide centres, with free refreshments and Wi-Fi.

Find dates and prices

Online booking is currently not available for this course, to find out more please call us on 0345 074 7998 or email us at info@qa.com to discuss how we can help.

Trusted, awarded and accredited

Fully accredited to ensure we provide the highest possible standards in learning

All third party trademark rights acknowledged.