Escape Excel Busywork with AI-Powered VBA Macros

Important note: This training may demonstrate AI tools that are not approved for use with Stanford data. Inclusion in this session does not imply institutional approval. Participants should refrain from entering Stanford data into unapproved tools. An up-to-date list of approved and reviewed tools is available on the GenAI Evaluation Matrix page.

If you are tired of repetitive Excel tasks, why not discover how to automate them with VBA macros?

Visual Basic for Applications, or VBA, is a programming language inside Microsoft Office. A macro is VBA code that you can run to perform many tasks quickly, with a single click. The best part about macros is that you don't need any coding experience to get started immediately. Plus, you can create them with your favorite AI tools.

This workshop is a unique opportunity to get Excel macros under your tool belt and turn repetitive spreadsheet chores into one-click actions. It feels like magic and makes a huge difference for productivity and job satisfaction.

First, you discover how to record macros. It's the easiest and most common way to get started without programming experience. But then we'll go further and explore how to create macros using modern AI tools. Which is a whole different ball game.

Every macro we create in this workshop is valuable. You can start using it immediately in your everyday workflow to avoid busywork.

What's Included in this session:  
- An introduction to Excel macros. 
- Professional macro-recording techniques. 
- An overview of the VBA editor environment. 
- How to avoid common mistakes. 
- Tips for preventing or fixing common issues. 
- Best practices for managing your favorite macros.
- An introduction to prompt-engineering and context-engineering techniques optimized for success with macros and VBA. 
- How to create and refine macros with AI tools like ChatGPT, Microsoft Copilot, or Anthropic Claude.
- Create macros that help clean, format, and transform data in Excel. 
You should expect to pocket at least 7 (but likely more) macros that you can use right away in your spreadsheets.

Who this workshop is for: Anyone who wants to eliminate Excel busywork and focus on what's important.

Learning Outcomes:
- Record and modify basic VBA macros using the Macro Recorder and VBA editor
- Generate working VBA code using AI tools (ChatGPT, Claude, Copilot) with effective prompts
- Debug and refine macros to handle errors and optimize performance
- Build a personal macro library with at least 7 reusable automations for daily tasks

Example workplace scenarios we¿ll practice:
- Automatically format weekly sales reports with consistent styling and layout
- Clean imported data by removing duplicates, fixing inconsistent text, and standardizing formats
- Generate monthly summary sheets by copying specific data ranges from multiple tabs
 

Topics covered:
Excel Macros and Macro Recorder
- The basics of macros
- The Developer tab
- Macro security
- Recording, running, and managing macros

Visual Basic for Applications
- VBA editor
- Basic syntax and structure of VBA code
- Editing recorded macros
- Debugging VBA

Leveraging AI to Create and Improve Excel Macros
- Overview of AI tools for code generation (e.g., Copilot, ChatGPT, Claude)
- Prompt engineering and context engineering
- Generating VBA code from natural language prompts
- Validating and editing AI-generated code

Hands-On Practice: Building Macros with AI Assistance
- Step-by-step walkthrough: creating macros with AI
- Run and customize AI-generated macros
- Real-world scenarios and use cases
- Optimizing macro performance
- Integrating multiple macros for complex tasks
- AI for code refactoring and documentation

Key Benefits: 
- Save hours each week by automating routine tasks (copy/paste, formatting).
- Combine the Macro Recorder, VBA, and AI to build reliable, maintainable automations. 
- Use AI models to draft, refactor, and document VBA code. 
- Explore prompt strategies that produce accurate, usable results. 
- Debug faster with AI-generated explanations of errors and step-by-step fixes. 
- Create user-friendly tools in Excel: buttons, shortcuts, and simple forms for teammates. 
- Apply best practices for clean code: naming conventions, modular structure, comments, and robust error handling. 
- Share macros safely with colleagues; understand macro security. 
- Build a reusable library of personal macros and an AI prompt pack for common office tasks. 
- Gain confidence to tackle new automation ideas and translate business processes into repeatable scripts.