Download PDF copy
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
Download PDF copy
Excel Frequently Asked Questions
Pricing
Dedicated Courses
Virtual - £795 per day for up to 8 delegates
Onsite - £845 per day for up to 10 delegates
Public Schedule Courses - N/A
Notes
Onsite prices are for courses in London. For other locations, trainer expenses will apply.
All prices displayed are exclusive of VAT.
.svg)