Course Icon VBA Training

VBA Excel-Introduction

Designed for VBA versions 2016,2013,2010,2007

0800 1337 401

Course Objectives

This course aims to give users an introduction to Visual Basic Programming within the Excel application. By the end of the course, users will be able to create and edit their own automated procedures. They will have an enhanced understanding of Objects, Variables, Control Of Flow structures, producing User Forms and creating the code for Pivot tables.

In addition, while creating and executing the code, the course introduces you to debugging code techniques and how to handle errors to minimise time spent trying to identify errors.

Who Should Attend

This course is for people who have advanced knowledge of Excel and use Excel extensively. In addition, anyone attending this course will be competent in recording and running macros using the macro recorder and the Macros dialogue box.

Course Syllabus

INTRODUCTION

  • Overview of recording Macros
  • Introducing Visual Basic for Applications
  • Opening and Closing the Visual Basic Editor

INTRODUCING OBJECTS

  • What is Object Orientated Programming (OOP)
  • The Excel Object Hierarchy
  • What is an Object
  • What is a Collection
  • Manipulating Objects and Collections
  • How to work with properties
  • How to work with methods

INTRODUCING VARIABLES

  • How to declare Variables and explicitly define them and their data types
  • Public v Private Variables
  • Harnessing intrinsic functions
  • Adding message boxes and using input boxes

VISUAL BASIC EDITOR WINDOW

  • Project Explorer pane
  • VBA Projects
  • Modules
  • Procedures
  • Properties pane
  • Procedure window
  • Object selection
  • Procedure selection

DEVELOPING WITH PROCEDURES AND FUNCTIONS

  • Editing a recorded macro
  • Creating modules
  • Defining and creating Sub Procedures
  • Calling procedures
  • Introduction to the Immediate window
  • Introduction to the Locals window
  • Making and naming a function procedure
  • Utilising the object browser

PROCEDURE CODE STRUCTURES

  • Defining control-of-flow structures
  • Using Boolean expressions
  • IF…END IF logical procedures
  • Select Case...End Select structure
  • Do...Loop structure
  • For...Next structure
  • For Each...Next structure

FORMS AND CONTROLS

  • Understanding controls
  • Setting control properties in the properties window
  • Creating UserForms using the Controls Toolbox
  • UserForm properties, events and methods
  • Using Labels, Text Box, Combo, Checkbox and Command Button controls
  • View the from by using code

USING THE PIVOT TABLE OBJECT

  • Defining PivotTables
  • Making a PivotTable
  • Using the PivotTable wizard method
  • Using PivotFields

HANDLING ERRORS

  • Creating an error handling routine
  • Capturing errors with the on error statement
  • Determining and using the error object
  • Using inline error handling
  • Breaking code
  • Step through code to identify errors
See all of the available Seven Institute Microsoft Courses