Excel 2010 - Visual Basic for Applications
EXL-10-11
Duration
2 days
Who should attend?
The Excel VBA course will suit anyone who wishes to extend the functionality of Excel and develop custom routines in response to organisational requirements.
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 dialog 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. Create global subroutines and functions. Call a procedure. Pass arguments to procedures.
Dialog boxes
Use of dialog boxes, input boxes and message boxes to interact with the user. Userform objects: text boxes, combo boxes, options buttons, check boxes, command buttons.
Ribbon, customise
Customise the ribbon to add a new tab and add a macro. Time permitting: Use the CustomUIEditor tool to embed Ribbon and VBA code in a workbook or XLA add-in.
Error checking
Handle runtime errors. Use of Err object, On Error GoTo, Resume and Resume Next to identify errors and branch code execution accordingly.
