Microsoft Excel VBA and Macros Course

Recorded Macros

Understanding Excel Macros

Setting Macro Security

Saving a Document As Macro Enabled

Recording a Simple Macro

Running a Recorded Macro

Relative Cell References

Running a Macro with Relative References

Viewing a Macro

Editing a Macro

Assigning a Macro to the Toolbar

Running a Macro From the Toolbar

Assigning a Keyboard Shortcut to a Macro

Deleting a Macro

Copying a Macro

Tips for Developing Macros

Recorder Workshop

Preparing Data for an Application

Recording a Summation Macro

Recording Consolidations

Recording Divisional Macros

Testing Macros

Creating Objects to Run Macros

Assigning a Macro to an Object

Understanding Excel VBA

VBA Terminology

About Objects

Accessing the Excel Object Model

Using the Immediate Window

Working with Object Collections

Setting Property Values

Working with Worksheets

Using the Object Browser

Programming with the Object Browser

Using the VBA Editor

Starting With Excel VBA

Using the Project Explorer

Working With the Properties Window

Using the Work Area

Viewing Other Panes

Working With Toolbars

Working With a Code Module

Running Code From the Editor

Setting Breakpoints in Code

Stepping Through Code

Procedures

About Procedures

Creating a Command Procedure

Making Sense of IntelliSense

Using the Edit Toolbar

Commenting Statements

Indenting Code

Bookmarking in Procedures

Using Variables

Understanding Variables

Creating And Using Variables

Implicit And Explicit Declarations

The Scope of Variables

Procedure Level Scoping

Module Level Scoping

Passing Variables By Reference

Passing Variables By Value

Data Types

Declaring Data Types

Using Arrays

Functions in VBA

Understanding Functions

Creating User-Defined Functions

Using a User-Defined Function in a Worksheet

Setting Function Data Types

Using Multiple Arguments

Modifying a User-Defined Function

Creating a Function Library

Referencing a Function Library

Importing a VBA Module

Using a Function in VBA Code

Code Glossary

The VBA Editor

Opening And Closing the Editor

Working with the Project Explorer

Working with the Properties

Window

Working with the Work Area

Working with a Code Module

Setting Breakpoints in the Editor

Stepping through a Procedure

User Defined Functions

About Functions

Creating Simple User-Defined Functions

Using User-Defined Functions

Using Multiple Arguments

Creating a Function Library

Modifying a Function

Using Excel Objects

The Application Object

The Workbook Objects

Program Testing With the Editor

Using Workbook Objects

The Worksheets Object

Using the Worksheets Object

The Range Object

Using Range Objects

Using Objects in a Procedure

Code Glossary

Programming Techniques

The MsgBox Function

Using MsgBox

InputBox Techniques

Using the InputBox Function

Using the InputBox Method

The IF Statement

Using IF for Single Conditions

Using IF for Multiple Conditions

The Select Case Statement

Using the Select Case Statement

For Loops

Looping With Specified Iterations

The Do Loop Statement

Looping With Unknown Iterations

Creating Custom Forms

Understanding VBA Forms

Creating a Custom Form

Adding Text Boxes to a Form

Changing Text Box Control Properties

Adding Label Controls to a Form

Adding a Combo Box Control

Adding Option Buttons

Adding Command Buttons

Running a Custom Form

Programming User Forms

Handling Form Events

Initialising a Form

Closing a Form

Transferring Data From a Form

Running Form Procedures

Creating Error Checking Procedures

Running a Form From a Procedure

Running a Form From the Toolbar

Automatic Startup

Programming Automatic Procedures

Running Automatic Procedures

Automatically Starting a Workbook

Error Handling

Error Types

The on Error Statement

Creating a Simple Error Handler

Using the Resume Statement

Using Decision Structures in Error

Handlers

Using the Err Object

Error Handling in Event Procedures

Defining Custom Errors