Excel VBA Intro

Excel VBA - Intro Course Outline

This 2 day Excel VBA course is suitable for Advanced users of Microsoft Excel responsible for very large and variable amounts of data, or teams, who want to learn how to program features and functions to improve the accessibility and usability of their data.

Prerequisites

A high proficiency of Microsoft Excel is essential, or completion of our 2 day Advanced MS Excel course.

Benefits

At the end of the course the delegate will have covered the fundamentals of VBA, including:

• working with procedures and functions
• understanding objects
• using expressions, variables and intrinsic functions.

Course Content

Introduction

• What is a Macro
• Recording a Macro
• Relative References
• Running A Macro
• Running A Macro From The Macros Dialog Box
• Editing A Macro With Visual Basic

Assigning Macros to Buttons and Objects

• Assigning a Macro to a Button on a Sheet
• Assigning a Macro to a Drawn Object on a Sheet
• Assigning a Macro to a Button on a Toolbar
• Creating A Short-Cut Key To Run A Macro

Visual Basic Editor

• Objects
• Methods
• Properties
• Programming Tools
• The Menu Bar

Editing Macros

• The Visual Basic Editor
• The Project Explorer
• The Code Window
• Procedures
• Comments
• Printing a Visual Basic Module

The Range Object

• The Cells Property
• The Range Property
• Flexible Ranges
• Range Contents

Workbooks and Sheets

• Activating a Workbook
• Activating a Worksheet
• ThisWorkBook

Running Code

• Run Mode And Design Mode
• Running Code From The Development Environment

The Project Explorer

• Using The Project Explorer

The Properties Window

• Changing A Property

Using Constants

• Excel Constants
• Variable Constants

Objects, Properties and Methods

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

Excel Objects

• Objects, Properties And Methods
• Getting & Setting Properties

Variables

• Using Variables In Routines

Using Visual Basic Functions

• Inputbox Function
• Msgbox Function
• Using A Set Statement

Building Formulae Control Structures

• If...Then Decision Structures
• Logical Operators
• For...Loops
• Do Loops

Event Handling

• Vba Editing & Debugging
• Auto Macros
• Error Handling

Controls, Dialogue Boxes and Forms

• Built-In Dialogue Boxes
• Predefined Dialogue Boxes
• Custom Forms
• Worksheet Forms
• User-Defined Forms
• Adding Form Controls
• Form Control Properties
• Control and Dialogue Box Events
• Displaying and Dismissing a User Form
• Handling Form Controls

Control Structures

• Conditional Control Structures
• Looping Control Structures
• Non-Structured Statements
• Subprocedures