MS Excel – VBA and Macros

Visual Basic for Applications (VBA) programming language can extend the functionality of Excel. This course is aimed at delegates looking to gain the skills necessary to apply VBA to develop macros, format worksheets, create user interactive macros for automating repetitive tasks, work with multiple worksheets, and perform important calculations.

You will learn how to write macros to accept user input, read and write from cells and manipulate data. You will also learn to write custom functions and create user forms. Exercises will be given to delegates to gain a greater understanding of the topics being covered.

This short course is ideal for those with a good knowledge of MS Excel and would like to add additional functionality. No programming knowledge is required.

The skill level for the Excel VBA is beginner to intermediate assuming no prior knowledge.

‍

Course content

Introducing Visual Basic for Applications

·        Displaying the Developer Tab in the Ribbon

·        Recording a Macro

·        Saving a Macro-Enabled Workbook

·        Running a Macro

·        Editing a Macro in the Visual Basic Editor

·        Understanding the Development Environment

·        Using Visual Basic Help

·        Closing the Visual Basic Editor

·        Understanding Macro Security

Working with Procedures and Functions:

·        Understanding Modules

·        Creating a Standard Module

·        Understanding Procedures

·        Creating a Sub Procedure

·        Calling Procedures

·        Using the Immediate Window to Call Procedures

·        Creating a Function Procedure

·        Naming Procedures

·        Working with the Code Editor

Understanding Objects:

·        Navigating the Excel Object Hierarchy

·        Understanding Using the Object Browser

·        Working with Properties

·        Using the With Statement

·        Working with Methods

·        Creating an Event Procedure

·        Using Expressions, Variables, and Intrinsic Functions:

·        Understanding Expressions and Statements

·        Declaring Variables

·        Understanding Data Types

·        Working with Variable Scope

·        Using Intrinsic Functions

·        Understanding Constants

·        Using Intrinsic Constants

·        Using Message Boxes

·        Using Input Boxes

·        Declaring and Using Object Variable

Controlling Program Execution:

·        Understanding Control-of-Flow Structures

·        Working with Boolean Expressions

·        Using the If…End If Decision Structures

·        Using the Select Case…End Select Structure

·        Using the Do…Loop Structure

·        Using the For…To…Next Structure

·        Using the For Each…Next Structure

·        Guidelines for use of Control-of-Flow Structures

Working with Forms and Controls:

·        Understanding User Forms

·        Using the Toolbox

·        Working with User Form Properties, Events,and Methods

·        Understanding Controls

·        Setting Control Properties in the Pro

‍

Who Should Attend?

This course has been designed for users familiar with recording and editing basic macros in Excel who want to go further in automating and customising Excel using VBA Visual Basic for Applications. No previous programming experience is required but an expert level of Excel use is assumed.