Course Code: XLL6
Duration: 1 Day
Course Aims:
This course is aimed at people who wish to improve on their existing basic knowledge of VBA and enhance their knowledge and appreciation of the concepts, tools and techniques of VBA programming.
Course Pre-requisites:
Students who wish to attend this course need to have significant experience and ability at understanding and using Excel advanced functionality and have attended the Microsoft Excel VBA Introduction course.
Course Objectives:
On completion of this course delegates will be able to:
- Use the For loop to repeat the execution of code
- Create code that allows users to respond to prompts such as the input box and the message box
- Identify, select and manipulate worksheet cells more effectively by using appropriate objects, methods and properties
- Use storage areas in the form of variables, arrays and constants to contain, control and handle data that needs to be used in the execution of a macro
- Facilitate complex calculations in Excel worksheets with specially written mathematical functions
- Create Add-Ins to enable the distribution of macros and user defined functions to other team members
- Troubleshoot, test and correct code using Visual Basic Editor tools
Course Content:
Introduction and Objectives
Using the "For" Loop
- Using a For - Next Loop
- Using a For Each Loop
Adding Simple Interactivity to Macros
- Creating an Interactive Message Box
- Creating a Custom Input Box
Exploring the Range Object
- Referring to a Range
- The Current Region Property
- The Offset & Resize Properties
- "Calling" Sub Procedures
Variables and Arrays
- Declaring Variables
- Scope and Visibility
- Creating and Defining Arrays
- Assigning Values to an Array
- Filling Arrays Using Loops
- Dynamic Arrays
User Defined Functions
- Using Excel Worksheet Functions in VBA
- VBA Functions
- User-Defined Functions
- Declaring Variables in User Defined Functions
- Creating and Managing an Add-in for User Defined Functions
Testing and Debugging code
- Types of Error and Debugging
- Stepping Through a Procedure
- Displaying Variable Values
- Break Mode
- Testing and Debugging Code
Action planning
Review of Programme
Excel Frequently Asked Questions
