Excel with VBA for Microsoft 365
Excel with VBA for Microsoft 365 is an intermediate VBA course that builds on basic macros, covering arrays, dictionaries, RegExp, user-defined functions, combining files from many folders, PivotTables and charts in code, and PDF and email output. It suits people who already write basic macros and staff who report on large data sets, and you leave able to build a multi-file sales summary system.
- Duration12 hours · 2 days
- FormatOnsite / live online
- Next roundOn request
- CertificateIncluded
Course overview
Once you can write basic macros, real work soon gets harder. Data runs to tens of thousands of rows, so macros that loop cell by cell become slow; files have to be combined from many folders; totals must be summarised by unique customer code; PivotTables and charts are rebuilt every month; and reports go out as PDFs to each department. Code written as one long block in a single module is hard to change and breaks easily when the data layout changes.
This course builds on basic VBA. It starts with structuring code for reuse, reading and writing whole ranges at once with arrays, grouping data with collections and dictionaries, handling text and dates and using the new RegExp built into VBA, writing intermediate user-defined functions, and finding and filtering large data sets. You then manage files and folders, build PivotTables and charts in code, create a UserForm to search and edit records, export PDFs and send email, and make your code fast and robust. It finishes with a workshop that builds a sales summary system from many files. (2 days, 6 hours per day, 12 hours in total, Intermediate level.)
What you’ll gain
- Structure code as subs and functions that pass values and can be reused
- Use arrays, collections and dictionaries to process large data sets quickly
- Handle text, dates and data formats with VBA functions and RegExp
- Write user-defined functions that accept flexible inputs and work on the worksheet
- Manage files and folders and combine data from several sources in code
- Create and update PivotTables, charts and data refreshes with VBA
- Build a UserForm to search, edit and delete records in an Excel table
- Export reports to PDF, send email, and write code that is fast and tolerant of errors
Who this course is for
- People who have completed Basic VBA Macros in Microsoft Excel or can already write basic macros
- Data, accounting and finance staff who report on large data sets
- People who regularly combine files from several branches or departments
- IT staff who look after shared macro workbooks in their organisation
Prerequisites
- Able to record macros and edit code in the VBA Editor
- Able to write subs using variables, If, Select Case, and For and Do loops
- Able to use Range, Cells, Worksheet and Workbook in code
- Able to use PivotTables and Excel tables on the worksheet
- A Windows laptop with Excel from Microsoft 365 or Excel 2024
Curriculum
Course Details
This is the 2026 edition of the Excel with VBA course. It runs for 2 days, 6 hours per day (12 hours in total, 09:00-16:00), as lectures with hands-on coding labs in every topic, at Intermediate level. This edition is now an intermediate course that follows Basic VBA Macros in Microsoft Excel and leads into Advanced VBA macros in Microsoft Excel. It adds
arrays, dictionaries, the new RegExp built into VBA in Microsoft 365, file and folder handling, PivotTables and charts in code, a UserForm to search and edit records, and PDF and email output. Learners use their own or their company's Excel for Windows; sending email from code needs classic Outlook. They take home the course handbook, practice files, sample code and the sales summary system from the workshop.
Day 1 Code Structure and Processing Large Data Sets
Section 1: Lab: Structuring Code for Reuse
- Splitting work into small subs and functions with readable names
- Passing values ByVal and ByRef, and Optional arguments
- Public, Private and constants shared across the project
- Lab: split a long recorded macro into reusable steps
Section 2: Lab: Arrays and Reading or Writing Whole Ranges
- Fixed and dynamic arrays, ReDim and ReDim Preserve
- Reading Range.Value into an array and writing it back in one go
- LBound, UBound and two-dimensional arrays from the worksheet
- Lab: compare the speed of cell-by-cell loops with arrays
Section 3: Lab: Collections and Dictionaries
- Collections: adding, removing and looping through items
- Scripting.Dictionary: Add, Exists, Keys and Items
- Finding unique values and totalling by code with a dictionary
- Lab: summarise sales by customer from tens of thousands of rows
Section 4: Lab: Text, Dates and RegExp
- Split, Join, InStr, Replace and Format
- DateSerial, DateAdd, DateDiff and dates stored as text by other systems
- The RegExp built into VBA in Microsoft 365 in place of VBScript.RegExp
- Lab: extract product codes and phone numbers from mixed text
Section 5: Lab: Intermediate User-Defined Functions
- Functions that take ranges, Optional and ParamArray arguments
- Returning an array that spills into several cells (dynamic arrays)
- WorksheetFunction versus Application, and handling error values
- Lab: a UDF that calculates tiered commission from a rate table
Section 6: Lab: Finding and Filtering Large Data Sets
- Find and FindNext to locate every matching record
- AutoFilter with several criteria and SpecialCells(xlCellTypeVisible)
- AdvancedFilter to copy matching data to another sheet
- Lab: split each branch's sales onto its own sheet automatically
Day 2 Files, Reports, Forms and Workshop
Section 7: Lab: Managing Files and Folders
- FileSystemObject: checking, creating, copying and moving files
- Looping through files in every level of subfolders
- FileDialog for choosing files or folders, and caveats when files sit in OneDrive
- Lab: combine sales files from every branch folder into one table
Section 8: Lab: PivotTables, Charts and Data Refresh in Code
- Creating a PivotCache and PivotTable from an Excel table
- Setting row, column, data and filter fields in code
- Creating and formatting charts with Shapes.AddChart2
- Refreshing PivotTables and Power Query queries with RefreshAll
- Lab: build a summary report with charts from a single button
Section 9: Lab: A UserForm to Search and Edit Records
- Multi-column ListBoxes and search as you type
- Loading the selected record for editing and saving it back to an Excel table
- Validating data before saving and preventing accidental deletion
- Lab: a form to search, edit and delete customer records
Section 10: Lab: Exporting PDFs and Sending Email
- ExportAsFixedFormat to export a sheet or range to PDF
- Naming files and folders automatically by month and branch
- Sending email with attachments through classic Outlook
- Lab: create a PDF for each branch and send it to each manager
Section 11: Lab: Speed and Error Handling
- ScreenUpdating, Calculation and EnableEvents, restored when an error occurs
- Timing code with Timer and finding what makes it slow
- A central error handler and logging errors to a sheet
- Lab: make the file-combining macro faster and able to skip broken files
Section 12: Workshop: A Sales Summary System from Many Files
- Workshop: choose a folder, combine the files and clean the data
- Summarise with a dictionary and a PivotTable with charts
- Export PDFs by branch and send email from a single menu sheet
- A checklist before sharing the workbook, and next steps towards add-ins
Schedule & training options
For individuals — public rounds
No public rounds are open right now. Join the waiting list and we will contact you first when the next round opens, or ask us on LINE. Or call 02-570-8449 or 088-807-9770
For organisations — in-house / private
- Tailor the content to your team’s tools and projects
- Your dates, at your office or live online
- Quotation with tax ID for procurement
Instructors
Frequently asked questions
What is the difference between a macro and VBA in Excel?
A macro is a set of instructions that automates repetitive work, and VBA (Visual Basic for Applications) is the language those instructions are written in. Recording gives you starting code, but intermediate jobs such as combining files from many folders, totalling by code with a dictionary or rebuilding a PivotTable every month need code you write yourself in the VBA Editor (Alt+F11), because recording cannot produce conditions or loops.
Why are macros that loop cell by cell slow, and how do you fix them?
Every time code reads or writes a cell, VBA has to talk to the worksheet, which is very slow across tens of thousands of rows. The effective fix is to read the whole Range.Value into an array, process it in memory and write it back in one go, while turning off ScreenUpdating and setting Calculation to manual during the run, and always restoring the original settings even when an error occurs.
With VBScript being deprecated, do RegExp and Dictionary still work in VBA?
Microsoft says the VBScript deprecation affects code that references VBScript libraries, such as VBScript.RegExp, and running .vbs files from VBA. Scripting.Dictionary and FileSystemObject live in the separate Scripting Runtime. For regular expressions, move to the RegExp now built into VBA in recent Microsoft 365 builds, and check the Office versions in your organisation before sharing files.
Can I send email from Excel VBA through the new Outlook?
No. The new Outlook for Windows does not support VBA or COM automation, so code using CreateObject("Outlook.Application") has to run against classic Outlook, which you can still switch back to. If your organisation moves fully to the new Outlook, consider Power Automate for sending email instead. This course practises with classic Outlook and outlines the alternatives.
Who is the Excel with VBA for Microsoft 365 course for, and what should I know beforehand?
It suits people who have completed Basic VBA Macros in Microsoft Excel or can already write basic macros, and data, accounting and finance staff who report on large data sets. You should be able to write subs with variables, conditions and loops, use Range and Worksheet in code, and bring a Windows laptop with Excel from Microsoft 365 or Excel 2024.
Can the Excel with VBA for Microsoft 365 course be run in-house for our team?
Yes. It can be run in-house for your team, with examples adapted to your own work, such as combining report files from every branch, monthly summaries or sending PDFs to each department. You can request a quotation from this page, or contact us on LINE @itgenius or by phone on 02-570-8449.
Related articles
Articles are published in Thai.