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.
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.
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.
Attending delegates will be presented with a Certificate of Attendance upon completion of training.
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.
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
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
- 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
- For … Next
- For … Next Loop With Step
- Do While … Loop
- Do Until … Loop
- Do … Loop While
- Exercises 0..8
- Adding Up the Coloured Numbers