Excel VBA – Course suitable for all versions

5.0
1 review
  • .
    |

Course

In London

£ 595 + VAT

Description

  • Type

    Course

  • Location

    London

  • Duration

    2 Days

This Excel VBA training course 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.

This training course is trained on 2013 but applicable to all versions of Excel 2007 – 2016.

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

Facilities

Location

Start date

London
See map
99 Bishopsgate, EC2M 3XD

Start date

AprilEnrolment now closed
MayEnrolment now closed
FebruaryEnrolment now closed
MarchEnrolment now closed

About this course

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.

Our aim is that you leave the course as a confident, albeit inexperienced, Excel programmer and can start automating your spreadsheets as soon as your return to your office.

create recorded macros in Excel
understand the Excel object model and VBA concepts
work effectively with the main features ofthe VBA Editor window
create procedures in VBA
create and use variables
create and work with user-definedfunctions in VBA
write code to manipulate Excel objects
use a range of common programmingtechniques
create a custom form complete with anassortment of controls
create code to drive a user form
create procedures that start automatically

Our courses run from 9.30 am to approximately 4.15pm. While you are with us we are focused on offering you a relaxed and productive learning environment. When you book a course with use you get the following:

A full colour training manual/book.
The exercises that you have worked on during your course to allow you to practice afterwards.
USB stick to take the exercises away.
Certificate of attendance
Freshly prepared lunch.
Refreshments throughout the day.
Training in fully air conditioned facilities from an experienced instructor.

This Excel VBA training course requires knowledge of Excel upto Advanced level. We would recommend you have attended our Advanced Excel course first. No prior knowledge of Excel macros or Visual Basic for Applications (VBA) is required. If delegates already have such knowledge then the course could be suitably tailored to meet requirements, by concentrating on more advanced topics.

Note: This course is designed to concentrate on the programming aspects of Excel, notably macros and Visual Basic for Applications (VBA).

Our courses run from 9.30 am to approximately 4.15pm. While you are with us we are focused on offering you a relaxed and productive learning environment. When you book a course with use you get the following:

A full colour training manual/book.
The exercises that you have worked on during your course to allow you to practice afterwards.
USB stick to take the exercises away.
Certificate of attendance
Freshly prepared lunch.
Refreshments throughout the day.
Training in fully air conditioned facilities from an experienced instructor.

Questions & Answers

Add your question

Our advisors and other users will be able to reply to you

Fill in your details to get a reply

We will only publish your name and question

Reviews

5.0
  • .
    |
100%
4.9
excellent

Course rating

Recommended

Centre rating

Tyron van Rooyen

5.0
14/01/2018
About the course: .
What I would highlight: The course were excellent and so were the teachers who taught the courses. The courses were professionally designed keeping in mind need of every type of student.
What could be improved: Nothing
Would you recommend this course?: Yes
*All reviews collected by Emagister & iAgora have been verified

This centre's achievements

2019

All courses are up to date

The average rating is higher than 3.7

More than 50 reviews in the last 12 months

This centre has featured on Emagister for 10 years

Subjects

  • Excel
  • Excel for Accounting
  • Computing
  • Microsoft Excel
  • Programming
  • Excel Formula
  • Excel Sheet
  • Microsoft excel training
  • MS Excel
  • Skills and Training

Course programme

Course detailsRecorded Macros

Macros allow non-programmers to automate repetitive tasks in Excel. The macro recorder allows you to automatically generate Excel VBA code. You simply carry out the specific task that you would like to automate and the macro recorder will convert that into Excel VBA code. This lesson introduces macros and shows you how to record a macro.

  • Understanding Excel Macros
  • Setting Macro Security
  • Saving a Document as Macro Enabled
  • Recording a Simple Macro
  • Running a Recorded Macro
  • Relative Cell References
  • Running a Macro With Relative References
  • Viewing a Macro
  • Editing a Macro
  • Assigning a Macro to the Toolbar
  • Running a Macro From the Toolbar
  • Assigning a Macro to the Ribbon
  • Assigning a Keyboard Shortcut to a Macro
  • Deleting a Macro
  • Copying a Macro
  • Practice Exercise
  • Practice Exercise Sample
Understanding Excel VBA

Now that you are comfortable with macros the course moves on to dealing with Excel VBA directly. This session covers the key concepts including terminology, ways to work in the VBA editor in Excel and the object model that is key to programming in VBA.

  • Programming in Microsoft Excel
  • VBA Terminology
  • Displaying the DEVELOPER Tab
  • The VBA Editor Screen
  • Opening and Closing the Editor
  • Understanding Objects
  • Viewing the Excel Object Model
  • Using the Immediate Window
  • Working With Object Collections
  • Setting Property Values
  • Working With Worksheets
  • Using the Object Browser
  • Programming With the Object Browser
  • Accessing Help
  • Code Glossary
Starting With Excel VBA
  • Using the Project Explorer
  • Working With the Properties Window
  • Using the Work Area
  • Viewing Other Panes
  • Working With Toolbars
  • Working With a Code Module
  • Running Code From the Editor
  • Setting Breakpoints in Code
  • Stepping Through Code
Procedures

Now that you have had a thorough introduction to Excel VBA and the code editor, this session delves further into how you write code. In it you will learn how to write comments in your code and how to clearly lay out your code so that others can understand what it does.

  • Understanding Procedures
  • Where to Write Procedures
  • Creating a New Sub Routine
  • Using IntelliSense
  • Using the Edit Toolbar
  • Commenting Statements
  • Indenting Code
  • Bookmarking in Procedures
  • Code Glossary
Using Variables

Variables are a key concept in all programming. This session covers their use in Excel VBA. So how to create one, how to use one, when and where you can use a variable that you have created and how you change it’s value.

  • Understanding Variables
  • Creating and Using Variables
  • Explicit Declarations
  • The Scope of Variables
  • Procedure Level Scoping
  • Module Level Scoping
  • Understanding Passing Variables
  • Passing Variables by Reference
  • Passing Variables by Value
  • Understanding Data Types for Variables
  • Declaring Data Types
  • Using Arrays
  • Code Glossary
Functions in VBA

Advanced users of Excel will know that it contains huge numbers of functions. They make carrying out the specific tasks they are designed for very fast. VBA allows users to create their own custom functions and also insert them into the ribbon.

  • Understanding Functions
  • Creating User-Defined Functions
  • Using a User-Defined Function in a Worksheet
  • Setting Function Data Types
  • Using Multiple Arguments
  • Modifying a User-Defined Function
  • Creating a Function Library
  • Referencing a Function Library
  • Importing a VBA Module
  • Using a Function in VBA Code
  • Code Glossary
Using Excel Objects

This lesson takes a far more detailed look at methods, applications, objects and properties.

  • The Application Object
  • The Workbook Objects
  • Program Testing With the Editor
  • Using Workbook Objects
  • The Worksheets Object
  • Using the Worksheets Object
  • The Range Object
  • Using Range Objects
  • Using Objects in a Procedure
  • Code Glossary
Programming Techniques

This lesson covers two key concepts. It starts with a detailed look at how to use message boxes to communicate with users. Either informing them of something or asking them to give input. It then moves to covering IF statements. These allow programs to carry one of two or more pieces of code depending on if a condition, or conditions, have been met.

  • The MsgBox Function
  • Using MsgBox
  • InputBox Techniques
  • Using the InputBox Function
  • Using the InputBox Method
  • The IF Statement
  • Using IF for Single Conditions
  • Using IF for Multiple Conditions
  • The Select Case Statement
  • Using the Select Case Statement
  • For Loops
  • Looping With Specified Iterations
  • The Do Loop Statement
  • Looping With Unknown Iterations
  • Code Glossary
Creating Custom Forms

This session focuses on forms. Generally users interact with Excel VBA programs via forms. This means that it is very important to set up and design your forms correctly to make it straightforward for a user to work with your program.

  • Understanding VBA Forms
  • Creating a Custom Form
  • Adding Text Boxes to a Form
  • Changing Text Box Control Properties
  • Adding Label Controls to a Form
  • Adding a Combo Box Control
  • Adding Option Buttons
  • Adding Command Buttons
  • Running a Custom Form
Programming UserForms

Having learned how to create your form in the previous session, this session shows you how to trigger a form in your program, how to transfer the data that the user has input into the form in your code and how to error check user input.

  • Handling Form Events
  • Initialising a Form
  • Closing a Form
  • Transferring Data From a Form
  • Running Form Procedures
  • Creating Error Checking Procedures
  • Running a Form From a Procedure
  • Running a Form From the Toolbar
  • Code Glossary
Automatic Startup

This final session in the two day course focuses on automation. Now that you have learned how to create your custom VBA program this will show you how to get the most from it.

  • Programming Automatic Procedures
  • Running Automatic Procedures
  • Automatically Starting A Workbook
Error Handling
  • Understanding Error Types
  • The on Error Statement
  • Simple Error Trapping
  • Using the Resume Statement
  • Using Decision Structures in ErrorHandlers
  • Working With Err Object
  • Error Handling in Forms
  • Coding Error Handling in Forms
  • Defining Custom Errors
  • Code Glossary

Excel VBA – Course suitable for all versions

£ 595 + VAT