Duration: 1 day(s)
Course Code: XLL6
Early Bird Courses
London 19th Sep 2017 £256.75 Book
London 14th Dec 2017 £256.75 Book
For dates or any further enquiries please call 01344 830100
Price: P.O.A

Microsoft Excel Level 6
Intermediate VBA Programming

Course Aims

This Microsoft Excel Level 6 Intermediate VBA Programming 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 Prerequisites

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 Level 5 course – Introduction to VBA.

Course Objectives

On completion of this Excel training 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

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 CurrentRegion Property
  • The Offset & Resize Properties
  • “Calling” Sub Procedures


Variables, Constants 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

 

Click to expand...

What previous delegates have said about our Microsoft Excel VBA Level 6 course

 

"I found Matthew to be an EXCELLENT trainer whose Dynamics style kept me and other delegates attentive and enthused. He was very informative and he made the courses extremely enjoyable"

 

Click to expand...

 

Next Learning Step

The next Excel training course in this series is: