Automate Excel with VBA and Office Scripts
Automating Excel (VBA and Office Scripts) is an advanced-level course of 5 lessons (about 1 h 30 min), with 15 exercises and 15 quiz questions. Prerequisites: Build and Safeguard a Reliable Excel Workbook. The course needs a Windows virtual machine with Power BI, planned for a later phase of the platform.
- Excel
- Advanced
- 5 lessons
- 1 h 30 min
- 15 exercises
- Windows virtual machine (phase 2)
The course in brief
This course teaches you to automate repetitive Excel tasks. You start from the macro recorder, learn to read and write VBA with variables, loops and conditions, then discover Office Scripts in TypeScript for Excel on the web and Power Automate. You finish with macro security, writing robust code, and choosing the right tool for the need.
What you will be able to do
- Record, read and simplify a macro, and save it in .xlsm format
- Write VBA macros with typed variables, Excel objects, loops and conditions
- Write an Office Script that reads, calculates and writes data, and call it from Power Automate
- Configure macro security and spot a suspicious file
- Add error handling that restores Excel's state and choose the right automation tool
Syllabus: 5 lessons
- Show the Developer tab and record a macro
- Choose between absolute and relative references while recording
- Read and tweak the code produced in the VBA editor, then save in the right format
- 02
VBA Basics: Objects, Variables and Cells
- Navigate the Excel object model (workbook, sheet, range)
- Declare typed variables with Option Explicit
- Read and write values and formulas, and debug step by step
- 03
Loops and Conditions in VBA
- Go through rows of variable length with For and For Each
- Make decisions with If and Select Case
- Write fast, safe loops
For, For Each, Do While loopsIf and Select CaseLast row detection
- 04
Office Scripts: Automating Excel on the Web with TypeScript
- Place Office Scripts relative to VBA
- Read and write a script with the ExcelScript API (sheets, ranges, tables)
- Trigger a script from Excel or from Power Automate
Office Scripts and the main functionExcelScript APIOffice Scripts and Power Automate
- 05
Macro Security and Robust Code
- Understand and configure macro security in Excel
- Make a macro robust against errors
- Choose the right automation tool for the need
Final project
Automate the Monthly Sales Report
Every month, the sales team of a fictional small business receives the sales export and must produce a report: calculated amounts, classified sales, a summary sheet by region and consistent formatting. Automate this report in two ways: a VBA macro launched by a button in an .xlsm workbook, and an equivalent Office Script for the version of the workbook stored online, callable by Power Automate with the region as a parameter.
Deliverables
- An .xlsm workbook with the GenererRapport macro (amounts, classes, summary by region, formatting) launched by a button
- The code of the equivalent Office Script and the description of the Power Automate flow that calls it
- Complete error handling and a prerequisite check in the macro
- A one-page note: recommended security settings and justified tool choice
Assessment criteria
- The macro adapts to the number of rows and can be rerun without error
- Correct results on the test dataset (total 3,900, 3 large sales, revenue by region)
- Readable code: Option Explicit, typed variables, qualified ranges, no needless Select
- Working Office Script with a parameter and a return value
- Security respected: no global lowering, no secret in the code, error handling that restores Excel's state
Frequently asked questions
Do I need to know how to program to follow this course?
No, the course starts from the macro recorder. It does, however, assume a good command of Excel (tables, formulas), acquired in the modeling course.
VBA or Office Scripts: which one should I learn?
VBA remains the reference for Excel installed on the desktop. Office Scripts is for Excel on the web and scheduled automation with Power Automate. The course covers both.
Are Office Scripts available with every subscription?
No, their availability depends on the Microsoft 365 license and the administrator's settings. Check that the Automate tab is present in Excel for the web.
Are macros dangerous?
A macro is a program and can act on the whole computer. The course explains the security settings and the habits to adopt: only enable macros from a known source.
This course, told for your job
Say what you want to be able to do: “Automating Excel (VBA and Office Scripts)” takes its place in your path, with examples from your field. The concepts stay those of the course.