Excel: VBA Programming (4 day)

Course Overview

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

Skills Gained

Skills Gained

• use Microsoft Excel to develop applications that aggregate, analyse and present data in a user-friendly fashion
• write procedures in VBA to control the wide variety of object types that Excel and Microsoft Office make available
• write code in ways that make the application easier to maintain
• develop a professional user interface for an Excel-based application
• protect an Excel-based application from interference by its users

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 Outline

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's Objects

• 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.