Mastering Excel VBA

This Excel VBA training will show you how to make the most of the programming possibilities in Excel. It aims to give advanced excel uses a thorough introduction to Excel VBA programming.

The key topics that it covers include recorded macros, the macro editor, using and defining variables, creating custom forms and working with the various objects within Microsoft Excel.

Excel VBA is very widely used to automate repetitive tasks in Excel.  Typical examples include creating charts from a standard data input or creating and formatting reports from a raw unformatted set of data, but given how widely Excel is used it is used in business it is used in a huge number of applications.

The course is instructor-led and involves working on a number of practical, hands-on examples and exercises in a workshop environment. Led by a highly experienced Excel VBA trainer with many years of experience our course ensures that you will leave with lots of practical Excel VBA experience as well as useful tips and tricks.

PRE REQUISITES

This Excel VBA training requires knowledge of Excel up to Advanced level.

No prior knowledge of Excel macros or Visual Basic for Applications (VBA) is required.

Objectives

The aim of this course is to give delegates a thorough grounding in Excel VBA. It uses multiple exercises to ensure that delegates can practice the key techniques that are taught and also ask any questions that they may have. Rest assured that you will leave the course as a confident, albeit inexperienced, Excel programmer and can start automating your spreadsheets as soon as your return to your office.

Certifications

Attending delegates will be presented with a Certificate of Attendance upon completion of training.

Training Methodology

We believe that learning is most effective when presented in a relevant context so that the skills, strategy, and knowledge are meaningful to our delegates and can be applied directly in the training. Training sessions are conducted as hands-on workshops.

Each delegate will be given a digital courseware manual to refer to after the course.

Course Outline

Introduction to Excel VBA

  • Creating a Macro
  • Running the Macro
  • Examining the code behind the macro

Introducing the Visual Basic Editor (VBE)

  • Opening the Visual Basic Editor
  • Recognizing Parts of the Visual Basic Editor
  • Menus – View, Insert, Format, Debug, Run & Tools Menu

Exploring Visual Basic for Applications

  • Objects and Collections
  • Workbook and Worksheet Object
  • Range Object and Cells Property
  • Methods and Property
  • Assigning Object Variables and Using Named Argument

Declaring Variables, Constants and Data Types

  • Declaring Variables
  • Defining Data Types
  • Defining Constants
  • Assigning Values to Variables
  • Handling Strings
  • Handling Dates and Times
  • Handling Variants and Data Type Conversions

Modules and Procedures

  • Modules and Procedures and Their Scope
  • Calling Sub Procedures and Function Procedures
  • Understanding Variable Scope and Lifetimes
  • Exercises

Writing Your First VBA Function in Excel

  • About User-Defined Functions
  • A Function to Calculate the Area of a Rectangle
  • A Function to Calculate Fuel Consumption
  • Accessing Your Custom Functions

Decision Structures

  • IF … Then Statement
  • IF … Then … Else
  • Exercise: A Function to Calculate the Area of a Rectangle – (IF)
  • Select Case
  • Exercise: A Function That Gives the Name of the Day

Loop Structures

  • For … Next
  • For … Next Loop With Step
  • Do While … Loop
  • Do Until … Loop
  • Do … Loop While
  • Exercises 0..8

Complex Exercises

  • Adding Up the Coloured Numbers

Date

Thursday 16th May 2019

Time

09:00 - 16:00

CPE Hours

6

Location

PwC's Academy @ The Hub

Price

€250.00

Book your place today!