EXCEL VBA

Excel VBA Basics with AI: From Your First Macro to Real Automation

Record, read and write your own Excel macros, and use AI assistants such as Copilot and Claude as your VBA tutor without trusting them blindly. Practice files, an assessment and quiz for every module and a real capstone.

6h 12mBeginner82 lessons

Created by Priyendra Kumar (PK) · NextGenTemplates Academy

What you’ll learn

Record macros, read the code the recorder writes and clean it up into short, fast code
Write your own Subs in the VBA editor and step through them with F8
Point at any cell, range, sheet or workbook from VBA and find the last row the right way
Use variables, data types, If and Select Case to make your macros think
Process hundreds of rows in seconds with For, For Each and Do loops
Build friendly tools with buttons, MsgBox and InputBox that check what the user types
Create, split, copy and save sheets and workbooks automatically
Find and fix errors with the debugging tools and proper error handling
Write strong prompts for VBA and test every AI answer before you trust it
Build a complete monthly report cleaner as your capstone project

A practical route to better work.

A complete beginner VBA course in 11 modules, built around Pine Valley Office Supply, a fictional US business with real-looking sales data. Start with the macro recorder and read the code it writes, then use the VBA editor to write your own Subs, step through code and clean up recorded macros. Point at any cell, range, sheet or workbook, find the last row the right way and write values, formulas and formatting by code. Use variables and data types, make decisions with If and Select Case, and process hundreds of rows with For, For Each and Do loops. Build friendly tools with buttons, MsgBox and InputBox that check what the user types, create, split, copy and save sheets and workbooks automatically, and find and fix errors with F8, the Immediate and Locals windows and proper error handling. Every module ends with an AI Corner: the exact prompt, a real answer from an AI assistant, the mistake AI usually makes on that topic and how to prove it right or wrong in Excel. Module 10 turns AI into your VBA tutor with the C-T-C-T prompt formula, a fair side-by-side test and a personal prompt library. Modules 1-9 each end with a hands-on assessment on a new business, a 10-question quiz and a full solution walkthrough, and the course finishes with a capstone: a monthly report cleaner that turns half a day of manual clean-up into one click.

Inside the course

12 modules · 82 lessons

MODULE 01Start Here+

Welcome: what you will automate and how AI fits in3 minPreview

Set up: course files, Developer tab and a macro-ready Excel5 minPreview

MODULE 02Module 1: Macros and the Macro Recorder+

What VBA is: macros, the .xlsm file and macro security5 minPreview

Record your first macro: format the weekly sales report6 min

Absolute vs relative recording5 min

Run macros the easy way: buttons, shapes, shortcuts and the Personal Macro Workbook5 min

AI Corner: ask AI to explain a recorded macro line by line4 min

Module 1 assessment: Bean Street Coffee weekly cafe report45 min

Module 1 quiz: check your understanding10 min

Module 1 assessment: full solution walkthrough6 min

MODULE 03Module 2: The VBA Editor and Your First Code+

Tour of the VBA Editor: Project Explorer, modules, Properties and Immediate window6 min

Write your first Sub from scratch5 min

Clean up recorded code: remove Select and Activate7 min

Step through code: F8, breakpoints and Debug.Print5 min

AI Corner: let AI clean recorded code - then check it5 min

Module 2 assessment: Redwood Bike Rentals log45 min

Module 2 quiz: check your understanding10 min

Module 2 assessment: full solution walkthrough7 min

MODULE 04Module 3: Excel Objects - Workbooks, Sheets and Ranges+

The Excel object model: Application, Workbooks, Worksheets, Range6 min

Range and Cells: point at any cell or block7 min

Find the last row and column the right way6 min

Values, formulas and formatting by code7 min

With...End With and object variables5 min

AI Corner: ask AI 'how do I refer to...' and test it in the Immediate window5 min

Module 3 assessment: Prairie Health Clinics visit list45 min

Module 3 quiz: check your understanding10 min

Module 3 assessment: full solution walkthrough8 min

MODULE 05Module 4: Variables, Data Types and Calculations+

Variables: store values with Dim6 min

Data types: Long, Double, Currency, String, Date, Boolean, Variant7 min

Constants and scope: procedure, module and public5 min

Calculate in VBA: maths, text and date functions8 min

AI Corner: ask AI to pick data types and explain a Type mismatch5 min

Module 4 assessment: Bluegate Logistics shipments45 min

Module 4 quiz: check your understanding10 min

Module 4 assessment: full solution walkthrough7 min

MODULE 06Module 5: Making Decisions - If and Select Case+

If...Then...Else: make the macro decide7 min

And, Or, Not and nested If6 min

Select Case: clean multi-way decisions6 min

AI Corner: turn a business rule into If / Select Case with AI4 min

Module 5 assessment: Granite Peak Hotels bookings45 min

Module 5 quiz: check your understanding10 min

Module 5 assessment: full solution walkthrough7 min

MODULE 07Module 6: Loops - Repeat Work Automatically+

For...Next: loop through rows6 min

For Each: loop through cells and sheets6 min

Do While and Do Until; Exit For and Exit Do6 min

Loop + If: flag, color and count rows6 min

AI Corner: ask AI to explain or speed up a slow loop4 min

Module 6 assessment: Evergreen Grocers inventory45 min

Module 6 quiz: check your understanding10 min

Module 6 assessment: full solution walkthrough7 min

MODULE 08Module 7: Talking to the User - MsgBox, InputBox and Buttons+

MsgBox: messages, icons and Yes/No questions6 min

InputBox and Application.InputBox: ask for values and ranges7 min

Friendly macros: buttons, status bar and screen updating6 min

AI Corner: AI writes user-friendly messages and input checks4 min

Module 7 assessment: Harborline Insurance claims desk45 min

Module 7 quiz: check your understanding10 min

Module 7 assessment: full solution walkthrough6 min

MODULE 09Module 8: Working with Sheets and Workbooks+

Add, rename, copy, move and delete sheets6 min

Loop through sheets: build an index with hyperlinks6 min

Open, save and close workbooks; ThisWorkbook vs ActiveWorkbook8 min

Split data into one sheet per region7 min

AI Corner: review an AI-generated 'one sheet per region' macro5 min

Module 8 assessment: Lumen Electronics regional sales45 min

Module 8 quiz: check your understanding10 min

Module 8 assessment: full solution walkthrough8 min

MODULE 10Module 9: Debugging and Error Handling+

Know your errors: compile, run-time and logic6 min

Debug like a pro: Locals, Watches and the Immediate window7 min

Error handling: On Error GoTo done right6 min

AI Corner: paste the error into AI - and the 5-point AI code checklist5 min

Module 9 assessment: Maple & Main Bakery broken macros45 min

Module 9 quiz: check your understanding10 min

Module 9 assessment: full solution walkthrough8 min

MODULE 11Module 10: AI as Your VBA Tutor+

The VBA prompt formula: context, task, constraints, test6 min

Copilot and Claude side by side: one prompt, a fair test8 min

AI to comment, document and test your code4 min

Stay safe: data privacy and macros from the internet5 min

Build your personal VBA prompt library4 min

Module 10 quiz: AI as your VBA tutor10 min

MODULE 12Module 11: Capstone - The Monthly Report Cleaner+

Capstone brief: Pine Valley's monthly report cleaner4 min

Capstone brief: build Pine Valley's monthly report cleaner180 min

Submit your capstone10 min

Capstone solution walkthrough19 min

Practice files

25 downloadable practice workbooks to follow along with the lessons.

Video lessons

61 step-by-step video lessons (6h 12m), streamed securely.

A practical assignment

Apply your learning, submit your work and explain your approach.

A knowledge check

Test your understanding with clear feedback and explanations.

Certificate of completion

Complete the course to download a certificate of completion with a unique certificate ID.

Is this course for you?

Analysts, accountants and admin staff who repeat the same Excel steps every week
Sales and operations teams who build recurring reports by hand
Excel users who want to understand the code AI writes for them
Anyone who wants to start automating Excel from zero

Before you start

Excel for Windows (Microsoft 365, 2021, 2019 or 2016)
Basic Excel skills: opening files, sheets and simple formulas
No programming experience needed; a free AI assistant account is helpful for the AI Corners (optional)

About your instructor

Priyendra Kumar (PK)

Founder, NextGenTemplates · Creator of PK: An Excel Expert

A business intelligence consultant, educator and template creator with more than 20 years of real-world experience in Excel, Power BI, Google Sheets, dashboards and business reporting.

PK has shared Excel tutorials on YouTube since 2017. In this course he teaches the same practical way: understand the idea, work through a business example, and build something you can use.

More about PK

Good to know

How do I buy a course, and which payment methods can I use?+

Choose a course, select Add to cart and complete checkout. With an Indian billing address you pay in INR through Razorpay, using UPI, cards or net banking. From any other country you pay in USD through PayPal, including debit and credit cards. It is a one-time payment, and the course appears in My Learning as soon as payment is confirmed.

Can I learn at my own pace?+

Yes. Lessons are organized into modules, and signed-in learners can save their progress and resume where they left off.

Are practice files included?+

Yes. Practice files are included with each paid course and can be downloaded from the course once you are enrolled. They are for your personal learning and your own internal work.

Will I receive a certificate?+

Yes. When you complete all required lessons (and any knowledge check or assignment in the course), you can download a certificate of completion with a unique certificate ID. It confirms course completion; it is not an academic or professional accreditation.

How long will I have course access?+

You get lifetime access: you can use the course for as long as NextGenTemplates Academy operates and offers it, as described in the Course Access Policy. There is no subscription or renewal fee.

Can I get a refund?+

Courses are digital content with immediate access, so purchases are generally non-refundable. Limited exceptions are explained in the Refund Policy.

FREE EXCEL LESSONS BY EMAIL

Not ready to enroll yet?

Join the Academy email list for practical Excel and AI tips, free preview lessons and news when a new course opens. Unsubscribe at any time.