VBA Training
VBA Excel-Introduction
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