Microsoft Excel Level 4 - VBA

Learning Objectives

On completion of this course, you will be able to:

• Understand and create macros.
• View and modify recorded macros.
• Understand Visual Basic procedures.
• Understand and use objects.
• Understand variables.
• Understand decision making and looping.
• Use controls and dialog boxes.
• Create automatic procedures.

Pre-Requisites

To ensure your success, we recommend that you have a reasonable working knowledge of Excel or have attended the courses detailed below:

• Microsoft Excel Level 1
• Microsoft Excel Level 2
• Microsoft Excel Level 3

Course Contents

Introduction to Macros

• The Capabilities of a Macro
• Recording a Macro
• Where a Macro is Stored
• Stopping the Recorder
• Running a Macro
• Absolute and Relative Referencing
• Adding a Macro to a Menu
• Adding a Macro to a Button on a Tool Bar
• Adding a Macro to a Button on a Worksheet

 Viewing & Modifying Recorded Macros

• Displaying a Macro
• Reading the Visual Basic Code
• Getting Help in Visual Basic
• Printing a Visual Basic Module

Visual Basic Procedures

• What is a Procedure?
• Procedure Arguments
• Calling Procedures
• Visual Basic Modules
• Calling Procedures from other Modules
• Calling Procedures from another Workbook

Visual Basic Objects

• Introduction to Objects
• Using Objects
• Properties in Procedures
• Performing Actions with Methods
• Collections of Objects
• Objects as Containers
• Referencing Cells

The Object Browser

• Using the Object Browser
• Learning Objects' Properties and Methods
• Pasting Code into your Module

Declaring Variables

• Why identify Variable and Arguments in Advance
• Option Explicit helping with Errors
• Data Types
• Data Types within Arguments
• Assigning Objects to Variables

Operators, Constants & Scope

• Operators, Constants, Scope of Variables and Constants

Decision Making & Looping

• Decision Making
• If....Then Statements
• Select Case Statements
• Looping
• Do....Loop
• For....Next Loops
• Nested Control Structures
• Exiting from a Control Structure

Testing & Correcting Code

• The Tools that can Help
• Debugging Tools on the Visual Basic Toolbar
• Using the Immediate Window in the Debug Window
• Using Watch in the Debug Window
• Watch Types in the Watch Window
• Stepping Through your Code

Controls & Dialog Boxes

• Using Simple Pre-defined Dialog Boxes
• Getting Information from the User
• Displaying Information with the MsgBox Function
• Making Customised User Forms
• Setting Control Properties
• Assigning Code to Controls and User Forms
• Displaying a User Form

Creating Automatic Procedures

• Creating Automatic Procedures
• Setting Automatic Macros for Single Sheets