Workshop 3

Automate Excel with AI (VBA)

Excel is everywhere in enterprises. In this hands-on workshop, you will describe tasks, let AI generate VBA macros, and produce a shareable .xlsm file. No manual coding — just logic and results.

Why This Workshop?

Excel remains one of the most widely used tools in enterprises worldwide. From finance to logistics, HR to marketing — teams rely on spreadsheets daily.

You will learn how to automate real Excel tasks using AI, becoming more efficient and impactful at work.

New to these terms? Quick definitions:
  • Macro = A saved sequence of actions Excel can repeat automatically
  • VBA = Visual Basic for Applications — Excel's built-in programming language for creating macros
  • .xlsm file = An Excel file with macro support (regular .xlsx files cannot run macros)
  • Copilot = An AI assistant that writes code based on your descriptions

From Ideas to Apps: No Manual Coding

Like all workshops in this course, you won't write code manually. You'll describe what you want, let AI generate the automation code, and review & iterate until it's perfect.

The AI does the coding — you focus on the logic and results.

Final Deliverable

A single .xlsm file containing your data and macros. Ready to run and share at work.

Understanding Macros & VBA

What are Macros?

Recorded sequences of actions in Excel. Excel generates VBA behind the scenes.

What is VBA?

Excel's automation language. Supports logic, loops, conditions, and more.

Why Master VBA?

For complex macros beyond recording — dynamic ranges, error handling, multi-step automation.

Editing Beats Re-recording

Update code instead of re-recording. Faster, reliable, scalable.

Try It Yourself: See VBA Code Behind Any Macro

Want to peek behind the curtain? You can view the VBA code of any macro in Excel:

1
Open the VBA Editor

Press Alt + F11 on Windows. On Mac, use the menu: Tools → Macro → Visual Basic Editor (or Option + F11 if your Excel version supports it).

2
Find Your Modules

In the left panel, expand VBAProject → Modules. Double-click any module to see its code.

3
Read the Code

Each Sub is a macro. Comments start with ' and explain what the code does.

Pro tip: If you've recorded a macro, open the VBA Editor to see exactly what Excel generated. It's a great way to learn!
Example: See What VBA Code Looks Like Click to expand
The Scenario

You have a list of employee salaries in column B (rows 2 to 10). You want a macro that calculates the total, writes it below the data, and shows a confirmation message. Here's exactly what that looks like in VBA — with every single line explained:

CalculateTotal.bas VBA 
' --------------------------------------------------
' Macro Name: CalculateTotal
' What it does: Adds up all salaries and shows the result
' --------------------------------------------------

Sub CalculateTotal()                         ' "Sub" starts a macro. The name appears in Excel's macro list.

    Dim total As Double                      ' "Dim" creates a variable. "total" will store our sum.
    
    ' Use Excel's built-in SUM function to add B2:B10 — works for any number of rows!
    total = Application.WorksheetFunction.Sum(Range("B2:B10"))
    
    Range("A12").Value = "TOTAL:"            ' Write the label "TOTAL:" in cell A12
    Range("B12").Value = total               ' Write the calculated total into cell B12
    Range("B12").Font.Bold = True            ' Make the total bold so it stands out
    
    MsgBox "Done! The total is: " & total    ' Show a popup message with the result

End Sub                                      ' "End Sub" marks the end of this macro
VBA Vocabulary (for the curious):
  • Sub / End Sub — Marks the start and end of a macro
  • Dim — Creates a variable (a container to store data)
  • Range("B2") — Refers to a cell or range of cells
  • MsgBox — Shows a popup message to the user

Remember: You won't write this yourself! You'll simply describe "add up all salaries in B2 to B10, write the total in B12, make it bold, show a message" — and Copilot generates all of this code for you, including the comments.

Recording vs. Writing VBA

Recording a Macro Writing/Editing VBA (with AI)
Simple repetitive tasksComplex logic (if/else, loops)
Fixed actions onlyDynamic behavior
Must re-record to changeEdit the code
Limited capabilitiesUnlimited possibilities

Real-World Use Cases

Here are common scenarios where Excel automation saves hours of manual work — exactly what you'll learn to build:

Financial Reports

Automatically consolidate monthly data from multiple sheets, calculate KPIs, and format the final report — every month, in seconds.

Data Cleaning

Remove duplicates, standardize date formats, trim whitespace, and flag invalid entries across thousands of rows.

Automated Emails

Generate and send personalized emails from Excel data — order confirmations, reminders, or status updates.

Dynamic Charts

Create charts that update automatically when new data is added — no manual selection or reformatting needed.

Why AI Changes the Game

Before AI: The Traditional Struggle

Learning VBA used to take months. You had to memorize syntax, understand Excel's object model (Workbooks, Worksheets, Ranges, Cells...), debug cryptic errors, and search Stack Overflow endlessly.

Most people gave up. The barrier was too high for non-programmers.

With AI: Describe, Generate, Done

Now you simply describe what you want in plain language. Copilot understands context and generates working VBA code. You don't need to memorize anything.

If it doesn't work perfectly? Just ask AI to fix it. Iterate until it's right.

What You'll Be Able To Do

After this workshop, you'll have the skills to automate tasks like these:

Process hundreds of files automatically (rename, merge, extract data)
Build one-click report generators that format and calculate everything
Create data validation tools that check for errors before submission
Automate repetitive copy-paste tasks between workbooks
Generate PDFs or exports on a schedule
Share your automation with colleagues — they just open the file and click a button

Our Tech Stack & Workflow

Here's how we combine tools you already know into a smooth workflow:

1

VS Code

The editor you already know. You'll write .bas files here.

2

GitHub Copilot

Generates VBA from your descriptions in comments.

3

VBA Modules (.bas)

Automation logic saved as text files. Excel understands these.

4

Python (Build Step)

Injects .bas modules into Excel and saves as .xlsm. (Windows only; macOS uses manual import.)

✓

Excel (.xlsm)

The final product! A single file with data + macros. Share it, run it, use it at work.

Ready to Share!

Prerequisites

Click on each item to see the setup guide:

Microsoft Excel

Microsoft 365 or desktop version
1
Check if you have Excel

Open Excel from your Start menu (Windows) or Applications folder (Mac). If it opens, you're set!

2
Don't have Excel?

Get Microsoft 365 at microsoft.com/microsoft-365. Free trial available, or use your company/school account.

3
Verify macro support

Open Excel → File → Options → Trust Center → Trust Center Settings → Macro Settings → Select "Disable all macros with notification" (recommended) or "Enable all macros" (only for development, not recommended for daily use).

VS Code with GitHub Copilot

Your AI-powered code editor
1
Download VS Code

Go to code.visualstudio.com and download for your OS. Install with default settings.

2
Install GitHub Copilot extension

Open VS Code → Extensions (Ctrl+Shift+X) → Search "GitHub Copilot" → Click Install.

3
Sign in to GitHub

Click the Copilot icon in the sidebar → Sign in with your GitHub account. Need a subscription? Start a free trial at github.com/features/copilot.

4
Install VBA extension (optional)

Search "VBA" in Extensions → Install for syntax highlighting in .bas files.

Python 3.10+

Windows recommended for automated build
1
Download Python

Go to python.org/downloads and download Python 3.10 or later.

2
Install with PATH

Run the installer. Important: Check "Add Python to PATH" at the bottom before clicking Install.

3
Verify installation

Open Terminal/Command Prompt → Type python --version → Should show "Python 3.10.x" or higher.

4
Install pywin32 (Windows only)

Run: pip install pywin32. This allows Python to control Excel.

macOS users: Python is optional. You can import .bas files manually into Excel instead.

Excel Trust Center

Enable VBA project access
1
Open Excel Options

Open Excel → File → Options (bottom left).

2
Go to Trust Center

Click "Trust Center" in the left sidebar → Click "Trust Center Settings..." button.

3
Enable Macro Settings

Click "Macro Settings" → Select "Enable all macros" (or at minimum "Disable with notification").

4
Enable VBA Project Access

Check ✅ "Trust access to the VBA project object model" → Click OK → Click OK again.

Security note: Only enable these settings for trusted workbooks. Consider reverting after the workshop if you don't need macros regularly.

Setup: Prepare Your Workspace

Before we start the automation loop, let's set up your project folder.

1. Create a New Folder & Open in VS Code

Create a new folder anywhere on your computer (e.g., my-excel-automation). Open VS Code → File → Open Folder → Select your new folder.

2. Add Your Excel File (If You Have One)

You have an existing Excel file

Simply copy your Excel file (e.g., employee-data.xlsx) into this folder. That's it!

You don't have an Excel file yet

No problem! In the next step, Copilot will create one for you based on your description.

3. Tell Copilot to Create the Project Structure

Open Copilot Chat and describe what you want:

You say:

"I have an Excel file called employee-data.xlsx with names in column A, salaries in column B, departments in column C. Create a complete project structure with: macros folder for VBA files, a Python build.py script that injects VBA modules into Excel and saves as .xlsm, and requirements.txt with needed packages."

Copilot Creates:
my-excel-automation/
employee-data.xlsx ← Your file (already here)
build.py ← AI-generated build script
requirements.txt ← AI-generated
macros/
← VBA files go here (next step)
Your Action:

Save the files Copilot generates. Create the folder structure if it asks you to. Install requirements: pip install -r requirements.txt

Setup complete! You're ready to start the automation loop.

The Loop: Generate → Deploy → Test → Iterate

This is where the magic happens. You describe, AI generates, you test, you refine. Repeat until perfect.

Describe
Generate
Deploy
Test
1

Describe: Tell Copilot What You Want

Open Copilot Chat. Describe what automation you need in plain English:

Example: Data Cleaning

"Create a VBA macro that removes duplicate rows from column A, formats dates in column B to DD/MM/YYYY, and highlights cells with invalid emails in column C in red."

Example: Report Generation

"Create a macro that calculates the total and average of salaries in column B, groups by department in column C, and writes a summary table at the bottom."

2

Generate: AI Creates VBA + Python

Copilot generates all the code. Save each file:

Copilot Generates:
  • VBA macro file (.bas) — save to macros/ folder
  • Updated build.py if needed
  • All code with comments explaining what it does

Tip: Ask Copilot to explain any part of the code if you're curious. You don't need to understand it to use it!

3

Deploy: Build the .xlsm

Tell Copilot to run the build script. Python injects all VBA macros into your Excel file:

You say:

"Run the build script"

Done! output.xlsm created with all your macros embedded.

macOS users: Open your Excel file → Go to Tools → Macro → Visual Basic Editor → File → Import File... → Select your .bas files. No Python needed.

4

Test: Run Your Macro in Excel

Open the generated .xlsm file and run your macro:

1 Open output.xlsm in Excel
2 Click "Enable Macros" when prompted
3 Press Alt+F8 → Select your macro → Click Run
Want a Clickable Button Instead?

Pressing Alt+F8 every time can be tedious. You can ask AI to create a button directly in your spreadsheet — just describe where you want it!

Example prompt:

"Add a green button labeled 'Generate Report' in cell E1 that runs the GenerateReport macro when clicked."

Another example:

"Create two buttons at the top of the sheet: one blue button 'Clean Data' and one orange button 'Export PDF', each running their respective macros."

VBA and Python can both create buttons programmatically. Just tell AI the position, size, color, and label you want — it generates the code. Your users then just click the button to run the automation!

Perfect!

Great! You're done. Share your .xlsm file with your team.

Needs changes?

Go back to step 1. Tell Copilot what to fix or add.

Iteration Examples

Something not right? Just describe the problem to Copilot:

Issue:

"It only processes the first 100 rows but I have 5000 rows."

You say:

"Update the macro to process all rows with data, not just 100."

Add feature:

"I want to also export the results as a PDF."

You say:

"Add code to export the active sheet as a PDF after running the macro."

Style change:

"I want yellow highlighting instead of red for values below 50."

You say:

"Change the highlighting color from red to yellow for values below 50."

The Loop:

Describe → AI generates code → Tell Copilot "run the build" → Test in Excel → Repeat until perfect!

Pro tip: Keep Excel open with your .xlsm file during the loop! Each time you run python build.py, it creates a fresh output file. Just close and reopen the file in Excel to see the updated macros.

Share Your Automation

Your automation is complete. Time to share it!

output.xlsm

A single file containing your data + all macros. No installation, no dependencies. Anyone can use it!

For Your Users:

  1. Open the .xlsm file
  2. Click "Enable Macros" when prompted
  3. Press Alt+F8 → Select macro → Click Run
  4. Done! Their task is automated.

FAQ & Limitations

Can VBA run inside VS Code?

No. VBA executes only inside Excel. We use VS Code for authoring and Python to inject modules into the workbook.

Do I need to write code manually?

No! You describe tasks in comments; Copilot generates VBA. You review and iterate.

Is Python required?

It simplifies building the final .xlsm on Windows. On macOS, you can import modules manually — no Python needed.

Security considerations?

Enable macros only from trusted sources. Some enterprises require digitally signed macros.

What if I Don't Have an Excel File Yet?

Nothing changes! The workflow is identical. Just describe your data structure and automation needs to AI, and ask it to generate a blank Excel template along with the VBA macros.

Example: "Create an Excel file with columns for Name, Age, and Score. Add a macro to calculate the average score and highlight scores above 80 in green." AI generates everything, including the starting Excel file.
? Shortcuts

Keyboard Shortcuts

Next section J
Previous section K
Change language L
Toggle theme T
Scroll to top Home
Close modal Esc