Visual Basic for Excel

Microsoft Excel VBA Programming

Two Day Course

Course Aims:

This course has been specially designed for the advanced user who wants to make use of the more powerful facilities of Microsoft Excel made available by VBA. This course explores VBA language and delegates will be encouraged to write programs to ensure that future editing, amending and updating can be carried out with the minimum of effort.Assumed Knowledge: Microsoft Excel users who are confident in their use of the commands, functions and simple Macros gained by prior attendance on a Jarrold Training "Microsoft Excel Advanced" course or via user experience.Course Outline:

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· Customising a Toolbar· Creating a Toolbar· Button Image Editor· Changing a Button’s Screen Tip

Editing Macros

· Visual Basic Editor· Project Explorer· Procedures; Inserting Comments· Printing a Visual Basic Module

The Range Object

· Cells & Range Method; Flexible Ranges· Virtual Worksheets; Range Contents

Workbooks and Sheets

· Activating a Workbook & Worksheet

Objects, Properties & Methods

· Controlling Objects with their Properties· Performing Actions with Methods· Performing Multiple Actions on an Object

Variables and Constants

· Variables, Data Types & Constants

Control Structures

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

Controls Dialogue Boxes and Forms

· Built-In & Predefined Dialogue Boxes· Custom, Worksheet & User Defined Forms· Adding Form Controls· Form Control Properties· Control & Dialogue Box Events· Displaying & Dismissing a User Form

Storing Information

· The Windows Registry

Toolbars & Display

· Abouat Toolbars & Menus· Customised Menus· Display

Debugging & Error trapping

· Errors, Break Mode; Instant Watch· Stepping & Error Handling

Worksheet & Workbook Events

· Worksheet Object Events· Workbook Object Events

Function Procedures

· User Defined Functions

Appendix A - Naming Conventions

· User Form Controls
· Memory Variables