London

Download Calendar

Who should attend the course?

The course would suit anyone with a reasonable understanding of Excel (e.g. how to format, use functions and keyboard shortcuts), who is interested in learning about how VBA can save them extensive amounts of time in their role. It is light on mathematical content, so to be accessible and suitable for attendees from many backgrounds, who have an interest in learning what VBA can do for them. No prior coding experience is required.

Course details

Overview

As the natural programming language embedded within Microsoft Office, VBA solutions continue to be widely implemented and supported by global institutions. The purpose of this active 2-day program is to gain a solid understanding of how Excel VBA can save valuable time, through automation of repetitive Excel tasks. The first day of the course focusses on core skills, designed to build up programming confidence, where we spend a good amount of time on best practice language structure and syntax. The second day applies the acquired skill set (and much more) to a real-life data project, where we automate the extraction and processing of mutual fund data.


Learning Outcomes

  • Record, write, edit, test and run VBA Sub Procedures (macros) with confidence, including working with Data & Object Variables, Conditionals & Loops, Message / Input Boxes, and Error Handling
  • Write and apply User Defined Functions (UDFs) to solve business problems
  • Implement useful Event Procedures
  • Build UserForms to enhance usability

Day 1 - Learning the Language

Introduction to Macros

  • Recording Sub Procedures (macros)
  • Running macros with form controls, shapes, shortcut keys

Tour of the Visual Basic Editor (VBE)

  • Uses of the VBE windows: Project Explorer, Properties, Code, Immediate, Locals
  • Customizing the look and feel of the VBE, including window docking

Best Practice Writing and Editing of Code

  • Language structure: Objects, properties and methods (and optional parameters)
  • Code Commenting
  • Using the Intellisense list (CTRL+SPACE)
  • Testing code with the immediate window, including Debug.Print
  • VBA functions
  • Getting Help: F1, macro recording, web resources

Variables and Data Types

  • Declaring and assigning values to data variables and constants
  • Data types, e.g. Integer, Double, String, Boolean
  • Using Option Explicit, Intellisense list to avoid spelling errors and speed up the coding process

User Interaction

  • Message boxes – to display information and progress
  • Input boxes – to collect data or obtain user feedback

Controlling Flow and Making Decisions

  • Loop structures
  • For…Next
  • Do While/Until…Loop
  • For Each…Next (for Collections)
  • If, Then, Else construction
  • Counters and flags
  • Calling Excel functions
  • Debugging and testing code with step through (F8), breakpoints (F9), locals window
  • Halting procedures with CTRL+BREAK/PAUSE (or ALT + ESCAPE)

Code Efficiency

  • Managing fully qualified references
  • With…End with to assign multiple properties
  • Use of Object Variables

Error Handling

  • Common error types: Syntax, Compile, Run-time
  • On Error Resume Next (and its dangers)
  • On Error Goto [Label]
  • Handling Error Numbers
  • Resume Next and Exit Statements

Event Procedures

  • Worksheet events, e.g. SelectionChange, Change
  • Workbook events, e.g. Open, AfterSave, BeforeClose
  • Application events, e.g. OnTime


Day 2 - Data Project

User Defined Functions (UDFs)

  • Specifying Function Arguments, including Optional Arguments
  • Calling UDFs from Excel and sub procedures

Nested IF and Select Case

  • IF, Else, Else IF structures
  • Select Case, Case Is, Case Else Structures
  • Building these into more complex UDFs

Guided Project: Data Importing and Processing

The focus of this real-world project is on importing mutual fund data from external files and processing it. We will consider at least:

  • Importing
    • Hungarian Notation (three-letter prefixes) for more robust object references
    • Working with dynamic file paths
    • Importing CSV files
    • Basic User Form Design with check boxes, command buttons, labels, etc.
    • Variable scoping: Procedure, module, project
  • Processing
    • Handling dynamic ranges and locating the last row in a spreadsheet
    • Outputting and formatting internal VBA calculations
    • Optimizing the code, e.g. turning off calculations, alerts, screen updates, etc.
    • Testing with breakpoints (F9)

"I like that the public courses are supported by online training. I can learn at my own pace and time, which has helped increase my confidence in the work that I do." ~ research analyst, global financial institution

Course materials

While this is a face to face training course, a blended learning approach is taken and delegates will be provided with access to DELTA. Our study materials contain both the knowledge and practice materials required to assist with the learning process and help you in your job role. Course materials include:

 

  • e-binder
  • case studies
  • 24/7 access to DELTA online learning environment
  • online videos
  • quizzes

Prerequisite

This course is open to all and no prior coding experience is required. However, to maximize the benefit of this course we recommend an understanding of Microsoft Excel.