Excel VBA

Learn to program


Overview

What does the course cover?

The course provides a solid foundation in Excel VBA. Using Excel VBA will extend your knowledge of recorded Macros and provide you powerful skills to automate Excel. This course explains how and where Macros can be useful in Excel and how to use Excel for as much of the a�?heavy liftinga�? as possible.

Aims

Why take this course?

If you are performing repeated actions on workbooks or sheets, it may be possible to automate that work. Importing data, cleaning that data, archiving existing information and similar activities are good candidates for automation in Excel. VBA provides extra options for developers looking to automate Excel.

Audience

Who should attend?

Users familiar with recording and editing basic macros in Excel who want to go further in automating and customising Excel using VBA – Visual Basic for Applications. No previous programming experience is required but an expert level of Excel use is assumed.


Course preview

Each course contains high quality, professional, Live demo’s, Downloadable exercise files and Real-world templates for you to use.


Live demo’s

Downloadable files

Real-world templates

Course detail and sample lessons

Section 1: Introduction

Lesson 1: Introduction
View Demo
Lesson 2: Excel Overview
View Demo
Lesson 3: Recording A Macro
Demo Not Available
Lesson 4: Reviewing Macro Code
Demo Not Available
Lesson 5: Introduction to Excel
Demo Not Available
Lesson 6: Objects, Methods And Properties
Demo Not Available
Lesson 7: Differences Between Office 2003, 2007, 2010 And 2013
Demo Not Available
Lesson 8: Creating A Macro Enabled Workbook
Demo Not Available

Section 2: Macro Recorder Part 1

Lesson 1: Introduction
View Demo
Lesson 2: Recording A Macro To Format Cells
View Demo
Lesson 3: Recording A Macro To Format Cells As Currency
Demo Not Available
Lesson 4: Recording A Macro To Swap Two Columns
Demo Not Available
Lesson 5: Recording A Macro Using Relative References Rather Than Absolute
Demo Not Available
Lesson 6: Recording A Macro To Turn Gridlines On And Off
Demo Not Available
Lesson 7: Tweaking Macros To Change Their Functionality
Demo Not Available

Section 3: Macro Recorder Part 2, A Larger Example

Lesson 1: Introduction
View Demo
Lesson 2: Some Useful Functionality In Excel For Cell Selection
View Demo
Lesson 3: Importing A Text File
Demo Not Available
Lesson 4: Filling Blank Cells With Data
Demo Not Available
Lesson 5: Adding A Column With The Date
Demo Not Available
Lesson 6: Creating A Pivot
Demo Not Available
Lesson 7: Creating A Chart
Demo Not Available
Lesson 8: Deleting Sheets And Charts
Demo Not Available
Lesson 9: Putting Them All Together
Demo Not Available

Section 4: Forms

Lesson 1: Introduction
View Demo
Lesson 2: Creating A Simple Switchboard Form To Call Our Previous Macros
View Demo
Lesson 3: Adding A Button To The Ribbon To Display The Form
Demo Not Available
Lesson 4: Using The Auto Open Events To Display The Form
Demo Not Available
Lesson 5: The Name List Form, Dealing With Events
Demo Not Available
Lesson 6: Using String Functions To Manipulate Text
Demo Not Available
Lesson 7: Creating A Form To Interact With Data On A Sheet
Demo Not Available

Section 5: Loops

Lesson 1: Introduction
View Demo
Lesson 2: Looping Through Data In A Column With The For Loop
View Demo
Lesson 3: The While Loop
Demo Not Available
Lesson 4: Using ‘Cells’ To Access A Cell
Demo Not Available
Lesson 5: Looping Through Variable Length Lists
Demo Not Available
Lesson 6: Looping Through Data In A Column With Spaces
Demo Not Available
Lesson 7: Calling Formula To Determine When To Stop A Loop
Demo Not Available
Lesson 8: Creating Reusable VBA Code
Demo Not Available
Lesson 9: Finding And Formatting Data Using Loops
Demo Not Available

Section 6: User Defined Functions

Lesson 1: Introduction
View Demo
Lesson 2: Creating A User Defined Function (UDF)
View Demo
Lesson 3: Creating A Variable Discount Calculation UDF
Demo Not Available
Lesson 4: Error Handling In VBA
Demo Not Available
Lesson 5: Debugging In VBA
Demo Not Available

Section 7: Automation

Lesson 1: Introduction
View Demo
Lesson 2: Automating Word From Excel
View Demo
Lesson 3: Automating Powerpoint From Excel
Demo Not Available
Lesson 4: Automating Outlook From Excel
Demo Not Available
Lesson 5: Getting Data From Access Into Excel Using VBA
Demo Not Available

Section 8: Security

Lesson 1: Introduction
View Demo
Lesson 2: Personal Macro Workbook
Demo Not Available
Lesson 3: Macro Security Levels
Demo Not Available
Lesson 4: Using A Digital Signature
Demo Not Available

Section 9: VBA Language Review

Lesson 1: VBA Language Review
View Demo


Skills you’ll learn

What you’ll be able to do after the course

    • Record and edit a macro’s VBA code
    • Understand object oriented structure of Excel
    • Creating custom forms in Excel
    • Macro security and protection of macro code
    • Foundations of programming using VBA
    • Pump data from databases, CSV files and similar
    • Cleanup imported data to remove extra spaces and lines

How you can apply these skills

What you’ll be able to use these skills for

  • Create a custom menu structure for Excel users
  • Simplify repetitive tasks using Macros and VBA code
  • Edit and update existing Excel Macros
  • Print specific separate areas of a workbook
  • Create custom menu structures and fill-in forms in Excel
  • Creating your own functions in Excel
  • Export data into other Office applications

Certification

When you pass that you will receive an internationally recognised accreditation certificate like this:

Excel_Apprentice_Certificate


What’s next?

Upon completion, you can move onto Excel Level 2, then 3. After you have completed the 3 levels you will be ready to take the Expert or Specialist courses in Excel for advanced Certification and for preparation for the Microsoft Certification exams.


Careers that require these skills at this level?

Excel is used in practically every business and in nearly all departments in those businesses – ranging from sales to accounting and administration. Any office role will require you to be proficient to this level.


Want to start learning today?

Joining is simple, easy and risk free with our 7 day money back guarantee. Or sign up for our 7 Day free trial.