Excel 2010 - Visual Basic for Applications - Intensive
EXL-10-12
Duration
1 day
Who should attend?
This Excel VBA course will suit anyone who wishes to extend the functionality of Excel and develop custom routines to manage repetitive tasks.
Pre-requisites
Attendees should have a very good understanding of Excel features.
Objectives
You will use the Visual Basic editor to add functionality and logic to recorded macros. You will learn how automate a repetitive task and how to communicate with the user using input boxes, features required to build spreadsheet systems for all types of data management.
Course Content
Overview
The benefits of VBA. Examples of how VBA can enhance the use of Excel within an organisation.
The VBA window
The VBA window elements; project explorer, code window, properties window, object browser. Tools used for debugging code.
Objects
Object properties, methods and events. Use of With … End With
Variables
Variable data types, declaring local and global variables. Scope of variables.
VBA Control Structures
Conditional branching using: If... Then… Else, Select… Case. Looping through code using Do… Loop, Do While, Do Until and For Each… Next loops.
Subroutines and Functions
Create Sub() and Function() procedures. Call a procedure. Pass arguments to procedures.
Run macros
Assign shortcut keys to a macro, add a macro to a ribbon, add a macro button to a sheet.
Error checking
Handle runtime errors. Use of Err object, On Error GoTo, Resume and Resume Next to identify errors and branch code execution accordingly.