Programming in VBA Using Excel

Introductory Level Course

Description

This course aims to increase your productivity by providing the skills and knowledge to program in VBA using Excel 2002

Learning Outcomes

Upon completion of this course the participant will be able to:

• Record macros using the recorder
• Create an application using the recorder
• Create user-defined functions
• Work with the VBA editor
• Understand VBA concepts and features
• Work with variables
• Work with Excel Objects
• Use standard programming techniques and concepts
• Create custom forms
• Program custom forms
• Modify the Excel menu system to show your macros
• Make macros start automatically

Course Content

Recorded Macros

Recording A Simple Macro

Running A Recorded Macro

Relative Recording

Running A Relative Recording

Viewing the Module

Modifying A Recorded Macro

Recorder Workshop

Creating a Recorded Application

Examining The Data

Recording A Summation Macro

Recording An Averaging Macro

Recording The Maximum Macro

Recording The Minimum Macro

Recording Divisional Macros

Testing Macros

Creating Objects to Run Macros

Assigning A Macro To An Object

User-Defined Functions

Creating A User Defined Function

Using A User Defined Function

Creating Another Function

Passing Multiple Arguments

Modifying A Function

Creating Function Library

Using The VBA Editor

Opening And Closing The VBA Editor

Working With The Project Explorer

Working With The Properties Window

Using The Work Area

Working With A Code Module

Running Procedures From The Editor

Setting Break Points In The Editor

Stepping Through A Procedure

Understanding VBA

Using The Immediate Window

Working With Object Collections

Setting Property Values

Working With Worksheets

Using The Object Browser

Programming With The Object Browser

Procedures

Creating A Command Procedure

Making Sense of IntelliSense

Using The Edit Toolbar

Commenting Statements

Indenting Text

Bookmarking In Procedures

Using Variables

Creating And Using Variables

Implicit And Explicit Declarations

The Scope of Variables

Procedure Level Scoping

Module Level Scoping

Public Scoping Of Variables

Passing Variables By Reference

Passing Variables By Value

Avoiding Variant Data Types

Using Arrays

Using Excel Objects

Splitting The Screen

Using Workbook Objects

Using Worksheet Objects

Using Range Objects

Using Objects In A Procedure

Programming Techniques

Understanding Programming Conventions

Communicating With The User

Prompting For User Input

Using The Input Method

Using IF To Make Decisions

Testing For Multiple Conditions

Looping A Fixed Number Of Times

Looping A Specified Number Of Times

Looping An Unknown Number Of Times

Creating Custom Forms

Creating A Custom Form

Changing The Form Properties

Adding Text Boxes To The Form

Moving Controls

Adding Label Controls To The Form

Changing Text Box Control Properties

Changing Label Control Properties

Adding A Combo Box

Adding Option Buttons

Adding Command Buttons

Running A Form

Programming Custom Forms

Initialising The Form

Closing The Form

Updating The List

Creating Error Checking Procedures

Running A Form From A Procedure

Adjusting Control Properties

Custom Menus

Creating A New Menu

Creating A Cascade Menu

Adding Menu Commands

Assigning Macros To Menu Commands

Removing A Menu

Automatic Startup

Programming Automatic Events

Running Automatic Procedures

Automatically Starting The Workbook