VBA Programming with Microsoft Excel

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

Course Description:

Many companies have made a major investment in Excel spreadsheets to support their business. However, few organisations realise the potential for improved efficiency and effectiveness that can be realised by programming with Excel and Visual Basic for Applications (VBA). This combination allows for the development of custom spreadsheet applications, which can extend the functionality of Excel and its user interface.

Through the mixture of lectures and practical sessions you will practice all the significant aspects of Excel-based application development with Visual Basic for Applications. Delegates will learn how to create complete, robust applications.

This course includes the following modules:

Introduction

• 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

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

Using Excel

• 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

• 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

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

Designing User Forms

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

The User Interface

• 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

• 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

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

Presenting Results

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