VBA & Microsoft Excel

Who is this course for?

This course is for you if you are an experienced user of Excel and wish to use Excel VBA to automate repetitive tasks. You must have experience of using Excel to an advanced level, recorded and edited macros.

What are the benefits

By the end of the course you will learn how to use Visual Basic to control Excel, and to automate processes to speed up and simplify your work with Excel data. You will understand what VBA is, understand the Excel object model, and make use of its properties, methods and events. You will know how to manipulate the values in a spreadsheet, declare and manipulate variables, write code which looks at a condition and carries out different actions accordingly, write code which repeats an action a specified number of times, create functions to reuse frequently used pieces of code, trap errors to avoid users seeing problems and to find problems in code.

Enquire about this course

Course Enquiry

Please type your full name.
Invalid email address.
Invalid email address.
Invalid Input

Course Duration

2 Days

Course Content

VBA Terminology

  • Exploring the Visual Basic Editor
  • The Project Explorer / Properties Window
  • The Code Window
  • Modules and Procedures
  • Editing macros / inserting comments
  • Printing a visual basic module
  • Attach code to events and buttons

Using Objects

  • The Excel Object Model
  • Discussion of Properties, Methods and Events
  • Using the Object Browser
  • Object Properties
  • Methods
  • Immediate Window

The Range Object

  • What is the Range Object?
  • The Cells Property
  • The Range Property
  • Flexible Ranges

Control Structures I

  • Branching with ‘If’, ‘Select Case’ and ‘Do Loop’

Control Structures II

  • Looping with ‘For Each’ and ‘For Next’

Variables and Data Types

  • Understanding expressions and statements
  • Declaring variables
  • Understanding data types
  • Working with variable scope
  • Inputbox and Msgbox functions

Error Trapping

  • Understanding error handling
  • Understanding VBA's error trapping options
  • Trapping errors with the on error statement
  • Understanding the error object

Controls, Dialog Boxes and Forms

  • Built-in dialog boxes
  • Custom forms
  • Setting the properties of a control
  • User-defined forms
  • Adding form controls
  • Form control properties
  • Control and dialog box events
  • Displaying and dismissing a user form

What our users say

 

  • A very good course, trainer had good knowledge and delivered well
  • I like the fact that you have selected topics/functions on the basis of their interest/possible use - course is much more than pertinent and a better use of time
  • Very friendly and helpful trainer
  • Ray is the only IT Trainer who has ever inspired me with enough confidence to learn effectively
  • It was very good, I really enjoyed it and learnt a lot
  • I will definitely use the Pivot Tables at work in the future! Thank you for such a great training day!
  • Will ensure my team all attend (they all work remotely)
  • Exactly what I was hoping to learn, thanks

  • Contact Us
  • 44 (0) 208 681 1485
  • 44 (0) 7794 0689 6985

E Mail Us