Fitch Learning

Mastering VBA in Excel

Available dates

This course has no confirmed dates in the future. Subscribe to be notified when it is offered.

About the course

The purpose of this two day hands-on course is to gain an understanding of how Excel VBA can save you time through automation of repetitive Excel tasks. Day one is a core skills day, delivered with tutor-led mini exercises, designed to build up programming confidence. Here we spend a good amount of time on language structure and syntax. Day two applies the concepts to a real-life business problem, where delegates are given freedom to design a solution with tutor guidance.

Key Learning Outcomes:

Record, Write, Edit and Run Macros and Functions with confidence.

Day 1

Introduction to Macros

  • Recording absolute and relative macros (sub procedures)
  • Running Macros with Form Controls, Shapes, Shortcut Keys

Tour of the Visual Basic Editor (VBE)

  • Location and uses of the VBE windows:
    • Project Explorer
    • Properties
    • Code
    • Immediate
    • Locals
  • Customising the look and feel of the VBE, including window docking

Editing and Writing Code

  • Language Structure: Objects, Properties and Methods (and Optional Parameters)
  • Using the Intellisense List (CTRL+SPACE)
  • Testing code with the Immediate Window, including Debug.Print
  • With…End With to assign multiple properties
  • VBA Functions
  • Working with Dates
  • Using Comments / Remarks
  • Common Error-types: Syntax, Compile, Run-time
  • Getting Help: F1, Macro Recording, Web Resources

Using Variables and Data Types

  • Declaring and assigning values to 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 messages 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 (using Object Variables)
  • 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

Scoping

  • Private and Public Subs, Functions, Variables
  • Project, Module and Procedure (Local) Scope

Further IF and Select Case

  • IF, Else, Else IF
  • Select Case, Case Is, Case Else

*User Defined Functions (UDFs) *

  • Creating UDFs with Function Arguments
  • Optional Arguments
  • Testing / Calling UDFs with Breakpoints (F9)
  • Calling UDFs in Excel

Efficient Error Handling

  • On Error Resume Next
  • On Error Goto
  • Handling Error Numbers
  • Resume Next and Exit Statements

Day 2

*Class Guided Project: VBA UserForms *

This project runs in 3-4 hours. We will design a UserForm to select and import CSV files upon which we output some useful calculations.

  • Designing UserForms with check boxes, command buttons, labels etc
  • Formatting via the Properties Window
  • The Power of Hungarian Notation
  • Showing and Unloading UserForms
  • Setting File path variables
  • Toggling Automatic Calculations, Display Alerts, Screen Updating
  • Testing with Breakpoints (F9)

Event Procedures

  • Worksheet events E.g. SelectionChange,, Change
  • Workbook Events E.g. AfterSave, Open, BeforeClose, NewSheet
  • Testing with Breakpoints (F9)

Class Takeaway Project: Building Loan Schedules

This project takes 1-2 hours. Delegates are tasked with building out automated loan schedules which handle bullet /amortisation structures.

Who should attend

The course will suit anyone who finds themselves repeating Excel tasks regularly and / or wants to learn how to design their own bespoke Excel Functions. The only pre-requisite for this course is having a reasonable understanding of Microsoft Excel.

Course reviews

Downloadable files