Home / Non-Accredited / Microsoft Office & Computer Skills / Writing Excel Macros with VBA (3 days)
Quick Look Course Summary:Writing Excel Macros with VBA (3 days)
-
Length: 3 day(s)
-
Price (at your venue): 1 Person R 21,125 EX VAT 3 Person R 13,658 EX VAT 10 Person R 9,155 EX VAT
-
Certification Type:Non-Accredited
-
Locations & Venues: Off-site or in-house. We train in all major city centres throughout South Africa.
Get Free & personalised
Training Advice
Writing Excel Macros with VBA (3 days)
Delegates automate the repetitive Excel work that eats their week, so a report that took half a day runs in seconds with the same result every time.
In most finance, operations and admin teams, Excel drives month-end reporting, reconciliations and management packs – and much of that work is the same manual steps repeated every week. This Excel VBA course teaches staff who already use Excel well to automate those steps with macros and VBA (Visual Basic for Applications) in Microsoft 365 and Excel 2021.
Day one covers recording macros, macro security, the Visual Basic Editor and the building blocks of the language. Day two builds real programming skill: decisions and loops, subroutines and functions, the Excel object model, and working with ranges, workbooks and worksheets in code. Day three covers debugging and error handling, events, user forms, automating complete reports and distributing tools as add-ins, ending with a capstone project on a real monthly report.
No programming experience is needed – VBA is taught from the ground up, with an exercise after every topic.
Why this course
For your organisation
- Routine reports produced in seconds instead of hours
- Consistent, repeatable results with fewer manual errors
- Skilled staff moved from copy-and-paste work to analysis
- Shared tools and add-ins that standardise reporting across teams
- More capacity without buying new software
For your delegates
- Record, read and edit macros with confidence
- Write your own VBA procedures, decisions and loops
- Build user forms that colleagues can use safely
- Leave with an automated version of a real report
What delegates will be able to do
- Record, run and edit macros, and store them in the right workbook
- Apply macro security settings and save macro-enabled files safely
- Use the Visual Basic Editor and write procedures with variables, constants and data types
- Control code with If, Select Case and For, For Each and Do loops
- Write Sub and Function procedures, including user-defined worksheet functions
- Work with the Application, Workbook, Worksheet and Range objects
- Debug code and handle run-time errors so macros stop cleanly
- Use workbook and worksheet events to run code automatically
- Build a UserForm that validates input and writes it to a worksheet
- Automate a complete report and distribute it as an add-in
Who should attend
Finance personnel and supervisors, information administrators and managers, project managers, engineers, analysts and anyone who spends hours on the same Excel tasks every week or month. Delegates should already be comfortable with Excel formulas and worksheets – ideally at Microsoft Excel Advanced level; no programming experience is needed.
Course outline
Day 1 – From recorded macros to VBA
Introduction to Excel macros and VBA
- What macros and VBA can automate in finance, operations and admin work
- Recording a macro compared with writing VBA, and when to use which
- Showing the Developer tab
- The skills this course builds, step by step
Recording macros
- Recording, running and editing a recorded macro
- Absolute and relative references
- Storing macros in the workbook or the Personal Macro Workbook
- Saving macro-enabled workbooks (.xlsm)
- Running macros from buttons, shapes and the Quick Access Toolbar
Macro security
- Trust Center macro settings
- Trusted locations and trusted documents
- Why macros in files from the internet are blocked, and how to unblock them safely
- An overview of digital signatures
The Visual Basic Editor
- The Project, Properties, Code and Immediate windows
- Inserting modules and creating procedures
- Help and the Object Browser
- Design time, run time and break mode
Constants, data types and variables
- Good programming style: comments, line continuation, indentation and naming
- Variables, data types and Option Explicit
- Constants and variable scope
- VBA operators
Day 2 – Programming Excel
Control statements
- If…Then…Else and ElseIf
- Select Case
- For…Next and For Each loops
- Do While and Do Until loops, and exiting a loop
Subroutines and functions
- Sub and Function procedures
- Parameters and arguments: ByVal, ByRef and Optional
- Public and private procedures
- User-defined worksheet functions
- Project references
Built-in functions and statements
- MsgBox and InputBox
- VBA string, date and conversion functions
- Using worksheet functions in VBA
- Application.InputBox for selecting ranges
The Excel object model
- Objects, properties, methods and collections
- The Application, Workbook and Worksheet objects
- Excel enumerations and the Object Browser
- Object variables, Set and With…End With
The Range object
- Defining a range with Range and Cells
- Offset, Resize, CurrentRegion and End
- Finding the last row and column of data
- Reading and writing values and formulas, and selecting special cells
- Copying, pasting and formatting with code
Workbooks and worksheets in code
- Opening, saving and closing workbooks
- Adding, deleting, renaming and sorting sheets
- Worksheet protection
- Printing, page setup and exporting to PDF
Day 3 – Robust tools and automated reports
Debugging and error handling
- Compile, run-time and logic errors
- Stepping through code, breakpoints, and the Watch and Locals windows
- Debug.Print and the Immediate window
- On Error statements and the Err object
Excel events
- Workbook events: Open, BeforeSave and BeforeClose
- Worksheet events: Change and SelectionChange
- An overview of chart, application and query refresh events
- EnableEvents and avoiding event loops
Built-in and custom dialog boxes
- Built-in dialog boxes
- Creating a UserForm
- Labels, text boxes, combo boxes, list boxes, option buttons and command buttons
- Validating input and writing it to a worksheet
- Form controls and ActiveX controls on worksheets
Automating reports
- Looping through files in a folder and consolidating data
- Creating pivot tables and charts with VBA
- Labelling chart data points from code
- Emailing a finished report through Outlook
Storing and distributing Excel tools
- Creating and installing Excel add-ins (.xlam)
- Adding macros to a custom ribbon tab
- Documenting and maintaining code
- Where VBA fits alongside Power Query, Office Scripts and Python in Excel
Capstone project
- Automating a real monthly report from raw data to finished output
- Code review and good practice
- Wrap-up and questions
How it is delivered
Three days, 08:30-16:00. In person at your offices anywhere in South Africa, at a BOTI venue, or live online. One delegate or a group of up to 20; every delegate codes hands-on in Excel for Windows. Laptops with the software can be supplied on request. Course notes, sample code and practice workbooks included. We deliver it at your offices in Johannesburg, Pretoria, Durban, Cape Town and anywhere else in South Africa, at a BOTI venue in Johannesburg, or live online for teams in different places.
What our clients say
“Business Optimization Training Institute facilitated Microsoft Excel Training for our company. The course was efficiently scheduled and planned around our timetable. We were impressed by the quality of training.”
“The training was very informative and practical, leaving our employees feeling confident in their ability to leverage Excel more effectively.”
“The facilitator effectively encouraged participation and used practical examples, discussions and interactive activities to ensure that the concepts were understood and could be applied in the workplace.”
Frequently asked questions
Do delegates need programming experience?
No. VBA is taught from the beginning. Delegates do need solid everyday Excel: formulas, worksheets and tables.
Will the macros work on a Mac or in Excel for the web?
The course uses Excel for Windows. Excel for the web does not run VBA macros, and Excel for Mac runs most VBA code but some features differ, so we recommend Windows machines for the course.
Can delegates automate one of our own reports?
Yes. The capstone on day three is designed for it – bring a sanitised copy of a report your team produces regularly.
Is VBA still worth learning?
Yes. It remains the most direct way to automate desktop Excel, and existing workbooks in most organisations depend on it. The course also shows where Power Query, Office Scripts and Python in Excel are the better choice.
Can you run this course at our offices?
Yes – on-site anywhere in South Africa, at a BOTI venue or live online, for one delegate up to groups of about 20. Laptops with the software can be supplied on request.
Is it accredited?
No – it is a non-accredited short course and delegates receive a BOTI certificate of attendance. If you need an accredited route, ask us about BOTI's End User Computing skills programmes (QCTO accreditation pending).
Related courses
Get your price – the more delegates, the less each pays
Change the options to see your price per person, then email yourself the quote and course outline.
Contact us now – we reply within 15 minutes during office hours
Monday to Friday, 08:00–17:00. After hours, we reply on the next working day – or call 011 882 8853.