By using our website you agree to our use of cookies.

VBA Programming with Microsoft Excel

4 Day Course
Hands On
Code WNXLPG6

This course has been superseded by the Microsoft Office - VBA Introduction and Microsoft Office - VBA Advanced courses.

Modules

Collapse all

Introduction (2 topics)

  • Using the Excel macro recorder and the Visual Basic Editor to create and modify macros
  • Customising menus and toolbars to run macros

The VBA Language (4 topics)

  • Simple input and output
  • Declaring and using variables of appropriate data types
  • Controlling the flow of the program
  • Debugging techniques and aids

Using Excel (7 topics)

  • Objects, properties and methods
  • Collections of Objects
  • Excel's Object Hierarchy
  • Object browser and online help
  • Useful properties and methods of the Application Object
  • Working with workbooks and sheets
  • Special VBA statements for working with Objects

Using Procedures (7 topics)

  • Program Design
  • Scope and lifetime of variables
  • Passing parameters to procedures
  • Event procedures
  • Functions
  • Writing functions to be used in worksheet formulas
  • Error handling

Working with Worksheet Ranges (5 topics)

  • Identifying a range
  • Range object properties and methods
  • Formatting
  • Navigating around a worksheet
  • Improving on the macro recorder

Designing User Forms (4 topics)

  • Uses of different control types
  • Displaying a user form
  • Validating user input
  • Using built-in dialogs

The User Interface (7 topics)

  • Using controls on worksheets
  • Creating and modifying CommandBars by program
  • Using Event Procedures to set up the desired environment
  • Protection
  • Hiding Excel's interface
  • Providing Help
  • Making an Add-In

Getting Data into Excel (6 topics)

  • Using Microsoft Query
  • QueryTables and Parameters
  • Use of ADO objects to access databases
  • Importing from text files
  • Importing data from Web Pages
  • Using links

Analysing Data (4 topics)

  • Sorting
  • Filtering
  • Excel's data analysis functions
  • Pivot Tables

Presenting Results (7 topics)

  • Charting
  • Chart object model
  • Creating generic charts
  • PivotCharts
  • Report generation by controlling Microsoft Word
  • Sending results by email
  • Publishing to the Web

Prerequisites

Delegates must have used Excel for at least three months. An understanding of programming concepts, such as looping, conditional statements and variables, is essential. Required programming skills can be obtained by attending the 2-day course "Introduction to Programming with Visual Basic for Applications" (QAVBAINT). Programmers, systems analysts and support professionals who want to create and support custom spreadsheet applications using Microsoft's Visual Basic for Applications. This course is not suitable for Excel users without programming experience (see Prerequisites section). This course is relevant for any version of Microsoft Office from 97 to 2003

User Microsoft Office Training Courses

Course PDF

Print

Sections