Programming: An Introduction using VBA (2 day)

Course Overview

Many people find that they need to be able to do a little customisation of Microsoft Office applications, such as Excel and Access, to make them work optimally in their environment; others may wish to become "fully-blown" Visual Basic developers. we have a range of courses available to assist with acquiring the knowledge and skills, however experience has shown that "you cannot make a programmer overnight" and this short course has been designed to introduce programming fundamentals, as a lead-in to those other courses. This course introduces the process of specifying and designing programs, then looks in a very practical way at the features of the "Visual Basic for Applications" (VBA) language.

Those attending will learn how to write simple, self-contained programs using various aspects of the language, and also how to call one program from another. The course uses the Visual Basic editor built-in to Excel, as this is commonly available at work or at home but please note that the course does NOT look at any Excel-specific programming features. The instructor-led course will use a mixture of lecture material and practicals, some of which will be paper-based but mostly involve writing small programs at the computer.

Course Outline

Program definition and design

• What is a program?
• Layers of software in a computer
• Task Breakdown - Identifying Start, Middle and End
• Flowcharts
• Pseudo-code.

The VBA editing environment

• Visual Basic Editor
• Projects, Modules &Procedures
• Setting the editor options
• Navigating
• Line breaks and comments

Simple Output

MsgBox statement

Variables and Data Types

• What are variables?
• Variable declaration and data types
• Arrays.

Using built-in functions

• Text manipulation
• Date functions
• Type-conversion

Simple user input

InputBox function

Program control, Branching and Looping

• IF constructs
• Case construct
• For & Do Loops
• Msgbox function for decision-making

Types of procedure

Features of Sub and Function procedures.

Calling one procedure from another

• Differences in ways of calling subs and functions
• Passing information between procedures.

Use of external facilities

• Setting references
• Simple file handling
• Use of the Windows Registry.

Debugging tools and handling errors

• Debug Toolbar
• Break Points
• Stepping through code
• Immediate Window
• Locals Window
• Watch Window
• choices for Error Handling.Program definition and design

What is a program? Layers of software in a computer; Task Breakdown - Identifying Start, Middle and End; Flowcharts; Pseudo-code.

The VBA editing environment

Visual Basic Editor; Projects, Modules & Procedures; Setting the editor options; Navigating; Line breaks and comments;

Simple Output

MsgBox statement

Variables and Data Types

What are variables? Variable declaration and data types; Arrays.

Using built-in functions

Text manipulation; Date functions; Type-conversion;

Simple user input

InputBox function

Program control, Branching and Looping

IF constructs; Case construct; For &Do Loops; Msgbox function for decision-making

Types of procedure

Features of Sub and Function procedures.

Calling one procedure from another

Differences in ways of calling subs and functions; Passing information between procedures.

Use of external facilities

Setting references; Simple file handling; Use of the Windows Registry.

Debugging tools and handling errors

Debug Toolbar; Break Points; Stepping through code; Immediate Window; Locals Window; Watch Window; choices for Error Handling.

Programming: An Introduction using VBA (2 day) / VBAINT