MS Excel - Visual BASIC for Applications (VBA)

Duration: TWO DAYS Training Course for Non-Programmers

Overview and Goals:

The course is aimed at those with little or no previous programming background, although experienced programmers wishing to learn VBA will find it a quick and valuable introduction to the language. Previous experience of recording Excel macros is desirable though not essential. Throughout the course the theme of adopting good programming practices will be encouraged.

Course Objectives:

The MS Excel - Visual BASIC for Applications (VBA) course has been specifically designed for the advanced Excel user who wishes to make use of the more powerful facilities of MS Excel made available from VBA. This course explores the VBA language and delegates will develop a small project to ensure that future editing, amending, updating and data extractions can be carried out with the minimum of effort.

Who should attend:

Excel users who have a strong working knowledge of the product including the development of macros.

Course Content:

Introduction:
What is a Macro
Recording & Running a Macro
Relative References

Assigning Macros to Buttons and Objects:
Assigning a Macro to a Button or a Drawn Object on a Sheet
Assigning a Macro to a Button on a Toolbar

Editing Macros:
Visual Basic Editor
Project Explorer
Procedures; Inserting Comments
Printing a Visual Basic Module

Improving macro performance:
Aesthetic improvements
Increasing speed

Creating VBA Projects:
Structuring an application
Identifying Modules and procedures
Writing and testing tight and concise VBA code
Adopting good programming practices

Developing the course project:
Collecting user input
Verifying success of each procedure
Understanding an application’s requirements

The Range Object:
Cells & Range Method; Flexible Ranges
Virtual Worksheets; Range Contents

Workbooks and Sheets:
Activating a Workbook & Worksheet
This Workbook

Objects, Properties & Methods:
Controlling Objects with their Properties
Performing Actions with Methods
Performing Multiple Actions on an Object

Variables:
Variables and associated Data Types
Object Variables

Control Structures:
Conditional & Looping Control Structures
Non-Structured Statements
Sub Procedures

Using Controls:
Adding Worksheet Controls
Worksheet Control Properties

Debugging & Error trapping:
Errors, Break Mode; Instant Watch
Stepping & Error Handling

Function Procedures:
User Defined Functions

Course Summary

What you will be able to do:

On completion of the above you will be equipped with the skills to handle the following situations automatically (i.e. programmatically with VBA)

Record and Edit Excel macros
Make informed decisions on when to Record macros and when to write VBA programs
Run macros or VBA programs from a worksheet using command and/or toolbar buttons
Open, Close, Save Workbook(s)
Insert, Delete, Rename Worksheet(s)
Close/save all Open workbooks and Quit Excel.
Interact with the PC user through messages and keyboard input including selections from dropdown lists
Construct efficient and easily maintainable VBA Programs
Identify beginning and end of a range of worksheet cells
Extract user specified data from a range of cells (similar to but beyond filtering)
Build multiple worksheets from data extracted from other sheets/workbooks
Format ranges of cells
Carry out arithmetic within VBA or insert Excel functions into a sheet
Trap and handle user generated errors
Develop your own functions similar to those offered by Excel