Start a conversation ↗

Description

Intermediate Excel VBA: Working with Objects, Handling Errors and Building Forms

Move beyond the macro recorder and write code that holds up against real working files

  • 2 days — 14 h
  • In-person or virtual
  • Intermediate
  • Up to 6 participants

After a few recorded macros, the limits appear quickly: the code works on this month's file but breaks as soon as the number of rows changes or a sheet is renamed. Routines stay tied to one specific workbook, cannot be reused, and the smallest data anomaly halts execution with an incomprehensible message.

These two days bridge the gap between recording and structured development: the Excel object model, parameterised procedures, error trapping, looping through multiple files and building data-entry forms. Exercises are based on real monthly routines brought in by participants.

Learning objectives

  • Use the Excel object model to act on workbooks and ranges
  • Write parameterised procedures that can be reused from one file to another
  • Handle run-time errors and inform the user clearly
  • Loop through several files in a folder within a single routine
  • Build a data-entry form and connect it to a data sheet

What makes this programme different

Every concept is applied to a real monthly routine brought in by a participant
Macros are written to be independent of sheet names and row counts
A code review checklist is used at the end of each exercise

Programme

1The Excel Object Model

Speaking to Excel in its own logic

  • Hierarchy of workbooks, worksheets, ranges and cells
  • The most frequently used properties and methods
  • Dynamic range referencing based on the data present
  • Moving from one workbook to another within a single routine

2Procedures and Control Structures

Writing reusable code

  • Procedures and functions with arguments
  • Variables and arrays to hold working data
  • Nested conditions and case selection
  • Loops suited to iterating through rows and collections

3Robustness and File Processing

Standing up to real-world data

  • Trapping run-time errors and exiting cleanly
  • Checking that a file or a sheet exists
  • Looping through the files in a folder and consolidating them
  • Displaying progress while the routine runs

4Forms and User Interaction

Guiding the user through data entry

  • Creating a form and selecting the right controls
  • Loading drop-down lists from a worksheet
  • Checking mandatory fields before validation
  • Writing the entered data into the target table

Who is it for

Excel users who have already worked with macros, business managers, management controllers, analysts and executive assistants.

Prerequisites

Having recorded and edited simple macros and read VBA code.

Dates & locations

36 scheduled dates between November 2026 and December 2027. Seats are confirmed in the order enquiries are received.

November 2026

December 2026

January 2027

February 2027

March 2027

April 2027

May 2027

June 2027

September 2027

October 2027

November 2027

December 2027

None of these dates suit you? We open additional sessions on request, and any programme can be run privately for your team.

Practical details

Before the programme
Online positioning questionnaire. Your development objectives are shared with the trainer, who tailors the practical case studies to your context.
Teaching methods
Theoretical input, workshops and practical case studies. Digital course materials and method sheets provided.
Assessment
Multiple-choice tests and role-play exercises. Assessment of learning at the start and end of the programme, with immediate and 60-day follow-up evaluations.
After the programme
One year of access to the e-learning platform. Self-assessment of the skills acquired and a 30-day follow-up session with your trainer.
How to register
Registration online or on the basis of a quotation.
Lead time
11 working days after confirmation of registration.
Accessibility
Accessible to people of determination. Contact our accessibility coordinator to design a suitable solution: contact@mpf-academy.ae
Start dates
Rolling intake: in addition to the scheduled sessions, this programme can start on request.