Microsoft Excel Specialist - Macros and VBA

Course subjects

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