Microsoft Excel Level 6 Intermediate VBA Programming

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

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 CurrentRegion 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

• 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

Action planning

Review of Programme

Related Courses

Excel Level 7 – Advanced VBA Programming