Wilmar AI Cafe
← Back to menu

Week 2 · AI-Assisted VBA for Beginners

Programming for Everybody — a hands-on session where absolute beginners write real Excel VBA using AI as their coding assistant, one small challenge at a time. No prior programming needed.

📅 Fri 24 Jul · 4–5 PM
📍 Wilmar office
🧭 9 hands-on challenges
⏱️ 60 min
Host · Xinran Liu

📦 Class materials

Download these before we start. Open the practice workbook and save your own copy as a macro-enabled file (.xlsm) so your code is kept. The answer workbook has every finished macro if you want to check your work.

📊Practice workbookxlsx ✅Answer workbookxlsm 📑Slidespptx

Menu

Warm up on the ideas, then work through the challenges using the practice workbook. Each challenge has a prompt you can copy straight into your AI assistant.

1
10 min

Foundations: What VBA Is & Coding with AI

Where VBA fits next to Excel formulas and Python, what it can automate, and how to write code by describing what you want to an AI assistant.

What can VBA do?
  • Automate repetitive Excel tasks — formatting, copy-pasting, filtering, splitting, merging.
  • Build custom logic and buttons, and link to other Office apps like Outlook.
  • Run the same steps the same way every time, on demand.
Excel formula vs VBA
  • Formulas calculate directly in cells, are beginner-friendly, and recalculate automatically.
  • VBA automates tasks, adds buttons and message boxes, and is highly flexible — but takes more effort to write and debug.
  • One is not better than the other — use formulas for quick calculations, VBA for repeatable automation.
VBA vs Python
  • VBA is embedded in Excel, great for simple repetitive Office tasks, and familiar to Excel users.
  • Python needs a separate install but scales to big data and AI; the language reads closer to English.
  • Start with VBA here; a separate AI-Assisted Python for Beginners course covers the rest.
How coding has changed

Traditional way: problem → search StackOverflow → write code → solution. New way: problem → prompt AI → trial and error → solution.

Give an accurate prompt and let AI do about 80% of the work. Then read through the code and comments, run it, and debug with AI again. Be specific about what you want.
2
5 min

Enable the Developer Tab & Save as .xlsm

Turn on the Developer tab, open the VBA editor, and save your workbook as macro-enabled so your code is preserved.

Enable the Developer tab
  • Go to File > Options > Customize Ribbon.
  • Tick Developer in the right-hand list and click OK.
  • Open the editor with Developer > Visual Basic (or press Alt + F11).
The VBA editor
  • In the editor, use Insert > Module to create a place for your code.
  • A procedure is a block of code starting with Sub and ending with End Sub.
  • Run it with the green Run button (or F5), or assign it to a button on the sheet.
Save your work

Save as Excel Macro-Enabled Workbook (.xlsm). A normal .xlsx will not keep your macros.

Working from the Practice workbook above? Use Save As and choose the .xlsm format first, before you write any code.
3
Sheet1

Challenge 1 · Print a Welcome Message

Your first macro: read a name from a cell and write a greeting next to it. Learn variables, referencing a sheet, and setting a cell value.

Prompt to your AI assistant
Write a VBA code that reads my name in A1 of Sheet1 and writes Welcome + my name in B1.
What it teaches
  • Create variables to hold values (the name, the sheet).
  • Define which sheet to work on and reference cells like Range("A1").
  • Set a cell value, then assign the macro to a button.
Reference code
Sub GreetUser() Dim userName As String Dim ws As Worksheet Set ws = ThisWorkbook.Sheets("Sheet1") userName = ws.Range("A1").Value ws.Range("B1").Value = "Welcome " & userName End Sub
4
Sheet1

Challenge 2 · Repeat with a For Loop

Write the same welcome message ten times using a loop instead of ten lines of code.

Prompt to your AI assistant
Write a VBA code that reads my name in A1 of Sheet1 and writes Welcome + my name in B1 to B10.
What it teaches
  • A For loop repeats an action a set number of times.
  • Range("B" & i) means column B, row i — the loop walks B1, B2, B3 … to B10.
Reference code
Sub GreetUserMultiple() Dim userName As String Dim ws As Worksheet Dim i As Integer Set ws = ThisWorkbook.Sheets("Sheet1") userName = ws.Range("A1").Value For i = 1 To 10 ws.Range("B" & i).Value = "Welcome " & userName Next i End Sub
5
Sheet2

Challenge 3 · Personalized Messages

Read a whole list of names and greet each one, looping only as far as there is data.

Prompt to your AI assistant
Write a VBA code that reads a list of names in column A of Sheet2 and writes Welcome + person name in column B.
What it teaches
  • Find the last row of data so the loop stops at the right place.
  • Cells(i, 1) means column 1, row i — the loop goes A1, A2 … to the last row.
  • Use an If to skip empty cells.
Reference code
Sub GreetUserCustom() Dim ws As Worksheet Dim lastRow As Integer Dim i As Integer Set ws = ThisWorkbook.Sheets("Sheet2") lastRow = ws.Cells(ws.Rows.Count, "A").End(xlUp).Row For i = 1 To lastRow If ws.Cells(i, 1).Value <> "" Then ws.Cells(i, 2).Value = "Welcome " & ws.Cells(i, 1).Value End If Next i End Sub
6
Sheet3

Challenge 4 · Calculated Field (CFR Price)

Add two columns together into a new one, keeping the headers untouched — a calculated field in VBA.

Prompt to your AI assistant
In Sheet3, add FOB Price in column A with Freight in column B to put into CFR Price in column C. Do not change the column name. Write VBA code.
What it teaches
  • Start the loop at row 2 to preserve the header row.
  • Each row calculates C = A + B, using a comment to explain the step.
Reference code
Sub AddPriceFreightSheet3() Dim ws As Worksheet Dim i As Long, lastRow As Long Set ws = ThisWorkbook.Sheets("Sheet3") lastRow = ws.Cells(ws.Rows.Count, "A").End(xlUp).Row ' Start from row 2 to preserve headers For i = 2 To lastRow ws.Range("C" & i).Value = ws.Range("A" & i).Value + ws.Range("B" & i).Value Next i End Sub
7
Sheet4

Challenge 5 · If-Else Decision

Make the macro decide between outcomes and show a message box based on a cell's value.

Prompt to your AI assistant
Write a VBA code that checks the value in cell A1 in Sheet4. If value > 10, display ">10"; if value = 10, display "=10"; otherwise, display "<10".
What it teaches
  • If / ElseIf / Else lets the macro choose a path.
  • MsgBox shows a pop-up message to the user.
Reference code
Sub CheckValueInSheet4() Dim ws As Worksheet Dim cellValue As Variant Set ws = ThisWorkbook.Sheets("Sheet4") cellValue = ws.Range("A1").Value If cellValue > 10 Then MsgBox ">10" ElseIf cellValue = 10 Then MsgBox "=10" Else MsgBox "<10" End If End Sub
8
Open Sales · 10 min

Challenge 6 · Pickup Overdue & Goods Value

On real-looking sales data, flag overdue pickups by comparing dates, and calculate the total goods value per row.

Prompt to your AI assistant
In sheet "Open Sales", provide VBA code to calculate Pickup Overdue Status in column 5, comparing today's date and the Pickup due date in column B: if today's date is bigger then status is Overdue, otherwise Not Overdue. Calculate Total Goods Value in column 6 which is Quantity (column 3) * Price (column 4). The first row is for headers.
What it teaches
  • Use Date for today and CDate to read a cell as a date.
  • Cells(i, 2) means column 2, row i — combine dates and multiplication in one loop.
Reference code
Sub CalculateDetails() Dim ws As Worksheet Dim lastRow As Long, i As Long Dim todayDate As Date, dueDate As Date Set ws = ThisWorkbook.Worksheets("Open Sales") lastRow = ws.Cells(ws.Rows.Count, 1).End(xlUp).Row todayDate = Date For i = 2 To lastRow dueDate = CDate(ws.Cells(i, 2).Value) ws.Cells(i, 5).Value = IIf(dueDate < todayDate, "Overdue", "Not Overdue") ws.Cells(i, 6).Value = ws.Cells(i, 3).Value * ws.Cells(i, 4).Value Next i MsgBox "Details Calculated", vbInformation End Sub
9
Open Sales

Challenge 7 · Split Data into Sheets

Filter the sales rows into two new sheets — Overdue and NotOverdue — based on the status column.

Prompt to your AI assistant
Write a VBA macro that splits data from the "Open Sales" sheet into two new sheets: "Overdue" and "NotOverdue", based on the value in column E. The macro should: delete the "Overdue" and "NotOverdue" sheets if they already exist; copy headers to both new sheets; copy each row to the appropriate sheet depending on whether column E says "Overdue" or not.
What it teaches
  • Create and delete sheets safely with On Error Resume Next and DisplayAlerts.
  • Copy rows conditionally to the right destination sheet.
Reference code
Sub SplitByOverdueStatus() Dim wsOriginal As Worksheet Dim wsOverdue As Worksheet Dim wsNotOverdue As Worksheet Dim lastRow As Long, i As Long Set wsOriginal = ThisWorkbook.Worksheets("Open Sales") ' Delete existing sheets if they exist On Error Resume Next Application.DisplayAlerts = False ThisWorkbook.Worksheets("Overdue").Delete ThisWorkbook.Worksheets("NotOverdue").Delete Application.DisplayAlerts = True On Error GoTo 0 ' Create new sheets Set wsOverdue = ThisWorkbook.Worksheets.Add(After:=wsOriginal) wsOverdue.Name = "Overdue" Set wsNotOverdue = ThisWorkbook.Worksheets.Add(After:=wsOverdue) wsNotOverdue.Name = "NotOverdue" ' Copy headers wsOriginal.Rows(1).Copy Destination:=wsOverdue.Rows(1) wsOriginal.Rows(1).Copy Destination:=wsNotOverdue.Rows(1) ' Filter and copy data (status is column E) lastRow = wsOriginal.Cells(wsOriginal.Rows.Count, "E").End(xlUp).Row For i = 2 To lastRow If wsOriginal.Cells(i, "E").Value = "Overdue" Then wsOriginal.Rows(i).Copy Destination:=wsOverdue.Cells(wsOverdue.Rows.Count, 1).End(xlUp).Offset(1) Else wsOriginal.Rows(i).Copy Destination:=wsNotOverdue.Cells(wsNotOverdue.Rows.Count, 1).End(xlUp).Offset(1) End If Next i wsOverdue.Columns.AutoFit wsNotOverdue.Columns.AutoFit MsgBox "Data split into Overdue and NotOverdue sheets successfully!", vbInformation End Sub
10
Overdue · Map

Challenge 8 · Merge Datasets with VLOOKUP

Pull each customer's email address from a lookup sheet into the Overdue sheet — no nested loops.

Prompt to your AI assistant
Write a VBA macro that fills column G in the "Overdue" sheet with email addresses from the "Map" sheet. In "Overdue", customer names are in column A. In "Map", customer names are in column A and email addresses are in column C. Add the header "Email" to column G if missing. Use VLOOKUP (no nested loops). Show a completion message when done.
What it teaches
  • Use VLOOKUP inside VBA to match records across two sheets.
  • Add a header only if it is missing, and confirm with a message box.
Compare your macro against the finished version in the Answer workbook.
11
Optional

Challenge 9 · Email Generator

Optional stretch: draft Outlook overdue-pickup emails automatically, one per customer, from the Overdue sheet.

Prompt to your AI assistant
Write a VBA macro for Excel that drafts overdue pickup notification emails using data from the "Overdue" sheet. Columns: A Customer Name, B Pickup Due Date, C Quantity, D Unit Price, G Total Goods Value, H Email Address. For each row (starting from row 2), create an Outlook email to the customer using HTML formatting. The subject should be "URGENT: Overdue Pickup Notification - [Customer Name]". The body should show Due Date, Days Overdue, Quantity, Unit Price, Total Value, and an urgent action message. Show a message box when all emails have been generated.
What it teaches
  • VBA can reach beyond Excel and drive Outlook.
  • Build an HTML email body from row data — always review drafts before sending.
This creates draft emails for you to review. Do not auto-send — check each one first.
12
Wrap-up

Mindset & More Resources

How to keep learning after the session, and the one challenge to take back to your own work.

The right mindset
  • It is normal to make mistakes, and completely fine not to understand every line of code.
  • Let AI do about 80% of the work, then read the code and comments and debug with AI.
  • AI is your assistant — ask it "stupid" questions freely, and tell it when an answer is wrong or needs changing. Your job is to describe what you want in as much detail as possible.
Your take-home challenge

Find something in your own work that could be optimized, or something analytical you could build with VBA — and try it with your AI assistant.

Get verified

Send Xinran a screenshot of your Developer tab with your VBA code to get your AI-Assisted VBA verification. Have fun exploring VBA with the help of AI.