Compare courses
Register
Fitch Learning

Mastering VBA in Excel

Next dates

Jul 8—9
2 days
London, United Kingdom
GBP 1295 ≈USD 1669
GBP 647 per day
Jul 16—17
2 days
Singapore, Singapore
USD 1495
USD 747 per day

Description

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.

Files

Detailed Description
Detailed Description
Show more