🤖 AI 跟我学 新手入门

AI 写 VBA 脚本：Excel 自动化保姆教程

AI 写 VBA 保姆教程：用大白话描述需求，AI 直接生成可运行的 VBA 宏脚本。含 3 套通用 prompt、批量处理示例和 VBA 报错救援

发布 2026/05/19 📎 参考官方文档

你是不是也遇到过这种情况：每周一上午都要把 5 个分公司的销售明细表合并到一张总表，每张表 3000 行，手动复制粘贴 1 小时起步，眼睛都看花了。听说 VBA 能自动化，但翻了 3 篇教程都是 100 行代码看不懂，学习成本高得离谱。这时候请 AI 写 VBA 出马，是最省事的解法。

这篇就教你怎么用 AI 写 VBA。从最基础的「自动合并 5 张表」，到带条件判断的进阶玩法，再到怎么让生成的脚本能稳定跑半年不出错，3 个 prompt 模板直接抄，10 分钟跑通一个能用的脚本。

为什么是 AI 而不是录制宏

Excel 自带「录制宏」功能，但它有 3 个硬伤：

  • 只能录死操作：你录的是「点 A1、复制、点 C1、粘贴」，下次表结构变了就崩
  • 不会循环和判断：要处理 5 张表必须手动重复录 5 次，无法自动遍历
  • 生成代码超啰嗦：录一个简单操作能生成 50 行代码，可读性极差

AI 大模型能解决这 3 个问题。实测下来：

  • Claude（Sonnet 4.5 / Opus 4.5）：代码质量最高，注释清晰，逻辑严谨。本文以它为主，看 Claude 是什么。
  • ChatGPT（GPT-5.1 及以上）：调试报错最强，把报错信息丢进去能精准修复。
  • Kimi / 豆包 / DeepSeek：国内免登录，VBA 这种通用任务都能写。看 国产 AI 对比 选一个。

选哪个的标准很简单：写新脚本用 Claude，改错用 ChatGPT。如果你想要系统的 Excel AI 思路，可以先看 AI 写 Excel 公式 配合阅读。

3 个 prompt 模板，复制就能用

写 VBA 一般是三种场景：批量处理、定时任务、跨表交互。每种给你一个直接能用的 prompt。

模板 1：批量处理脚本（最常用）

适用：要对多张表、多个 sheet、多个文件做同样操作。

📋 Prompt 模板 
你是一位资深 Excel VBA 开发专家，帮我写一个批量处理脚本。
我的任务描述：
[用大白话写清楚你要干什么，比如「把当前文件夹下所有 Excel 文件的第 1 个 sheet 合并到一张总表」]
环境信息：
    

  • Office 版本：[如「Office 365 / Office 2019 / WPS」]
    • 

  • 数据规模：[如「5 个文件，每个文件 1 张 sheet，每张 sheet 约 3000 行」]
    • 

  • 表结构：[列出表头字段，如「列 A: 日期、列 B: 地区、列 C: 产品、列 D: 销售额」]
    • 

我希望脚本做到：
    

  1. [第 1 个明确需求，如「跳过表头，只复制数据行」]
    2. 

  2. [第 2 个需求]
    3. 

  3. [第 3 个需求]
    4. 

请按以下格式输出：
    

  1. 

    完整 VBA 代码
    

      

    • 加详细中文注释
      • 

    • 关键变量用有意义的名字（不要用 a、b、c）
      • 

    • 处理异常（如文件打不开、sheet 不存在）
      • 

    

    2. 

  2. 

    怎么把代码放到 Excel 里
    

      

    • 第 1 步：按 Alt+F11 打开 VBA 编辑器
      • 

    • 第 2 步：插入 → 模块
      • 

    • 第 3 步：粘贴代码
      • 

    • 第 4 步：按 F5 运行（或回到 Excel 用按钮触发）
      • 

    

    3. 

  3. 

    运行前要做什么准备
    

      

    • 是否需要把所有源文件放在固定文件夹
      • 

    • 是否需要先备份原数据
      • 

    • Office 是否需要打开「信任宏」设置
      • 

    

    4. 

  4. 

    可能的报错和解决方案
列出 3 个最常见的报错 + 怎么改。
    

    5. 

要求：
    

  • 代码必须能直接复制到 VBA 编辑器运行
    • 

  • 不要用过时语法（如 Excel 4.0 宏函数）
    • 

  • 不要建议安装第三方插件
    •

用法：把方括号内容换成你的实际任务，整段发给 AI。30 秒出脚本。

模板 2：带条件判断的脚本（进阶）

适用：需要根据数据情况做不同处理，不是简单的「全部复制」。

📋 Prompt 模板 
你是一位资深 VBA 开发专家，帮我写一个带条件逻辑的脚本。
业务场景：
[详细描述，如「整理客户回款表，根据回款金额自动标记客户等级：>10 万标 A、5-10 万标 B、<5 万标 C，并按等级填充不同颜色」]
数据情况：
    

  • 表结构：[列出每列字段名和示例值]
    • 

  • 数据量：[约多少行]
    • 

  • 数据来源：[如「每月人工导出，格式可能有变化」]
    • 

业务规则（必须严格按规则执行）：
    

  • 规则 1：[如「金额大于 100000 标 A 级，单元格背景填绿色」]
    • 

  • 规则 2：[如「金额介于 50000-100000 标 B 级，背景黄色」]
    • 

  • 规则 3：[如「金额小于 50000 标 C 级，背景红色」]
    • 

  • 规则 4：[如「金额为空或负数标’异常’，背景灰色，并在备注列写’需复核’」]
    • 

边界情况怎么处理：
    

  • 如果某行金额是文本不是数字 → [怎么办]
    • 

  • 如果某行整行为空 → [跳过 / 删除 / 标记]
    • 

  • 如果发现重复客户 → [合并 / 保留第一行 / 标记]
    • 

请按以下格式输出：
    

  1. 

    完整 VBA 代码
    

      

    • 用 Select Case 或 If-ElseIf 实现规则判断
      • 

    • 颜色用 RGB 函数写，方便后续调整
      • 

    • 处理所有边界情况，不要让脚本崩溃
      • 

    

    2. 

  2. 

    测试用例
给我 5 行测试数据（含正常和异常），让我先在小数据上验证脚本是否正确。
    

    3. 

  3. 

    怎么扩展规则
如果以后要加新规则（如「VIP 客户单独标紫色」），代码里改哪一段、加几行。
    

    4. 

要求：
    

  • 规则判断逻辑要清晰，方便后续修改
    • 

  • 给的颜色 RGB 值要醒目（不要给浅得看不见的颜色）
    • 

  • 边界情况必须全覆盖
    •

这个 prompt 的关键是把业务规则写得跟法律条文一样清楚。规则越含糊，AI 写的代码越容易漏。

模板 3：定时任务 + 邮件通知（高难度）

适用：脚本要定时跑，跑完自动发邮件通知。

📋 Prompt 模板 
你是一位资深 VBA 自动化专家，帮我写一个定时任务脚本。
任务场景：
[详细描述，如「每天上午 9 点自动打开销售总表，刷新数据，检查异常，邮件发给销售总监」]
环境：
    

  • Office 版本：Office 365
    • 

  • 邮件客户端：Outlook（已绑定企业邮箱）
    • 

  • 触发方式：[Windows 任务计划程序 / Excel 启动时自动跑]
    • 

脚本要做的事（按顺序）：
    

  1. [步骤 1，如「打开 D:/sales/total.xlsx」]
    2. 

  2. [步骤 2，如「刷新所有数据连接」]
    3. 

  3. [步骤 3，如「检查 J 列有没有红色单元格（异常标记）」]
    4. 

  4. [步骤 4，如「如果有红色单元格，邮件发给 [email protected]」]
    5. 

  5. [步骤 5，如「关闭文件并保存」]
    6. 

邮件要求：
    

  • 主题：[如「{日期} 销售异常预警」，日期自动替换]
    • 

  • 正文：[如「附件是今日异常数据明细，共 {N} 条，请查看」]
    • 

  • 附件：[要不要附上 Excel 文件本身]
    • 

请按以下格式输出：
    

  1. 

    完整 VBA 代码
    

      

    • 含错误处理（如文件打不开、Outlook 没装、网络断了）
      • 

    • 关键步骤打 log 到一个日志文件，方便排查
      • 

    

    2. 

  2. 

    怎么设置定时触发
    

      

    • 在 Windows 任务计划程序里怎么配
      • 

    • 触发命令是什么（如 excel.exe /e D:/sales/total.xlsx）
      • 

    

    3. 

  3. 

    怎么测试
    

      

    • 第 1 次手动跑，看是否正常
      • 

    • 第 2 次改触发时间到 5 分钟后，验证定时是否生效
      • 

    • 第 3 次故意制造一个异常数据，验证邮件是否发送
      • 

    

    4. 

  4. 

    安全提醒
    

      

    • 启用宏的安全风险
      • 

    • 邮件密码不要写在代码里
      • 

    

    5. 

要求：
    

  • 代码必须能脱机运行（不要依赖网络资源）
    • 

  • 日志要详细，方便出问题时排查
    • 

  • 异常时不要崩溃，要发邮件通知有人
    •

跑完你就有一个能 7x24 自动跑的脚本。第 1 次跑要人工监督几天，跑稳定后基本不用管。

手把手 4 步走

光有 prompt 不够，下面是从打开 Excel 到脚本跑通的完整流程。

第 1 步：把需求写成「输入 → 处理 → 输出」三段

写 VBA 前必须想清楚 3 件事：

  • 输入：脚本读什么数据（文件路径、sheet 名、列名）
  • 处理：要做什么操作（合并、筛选、排序、计算、标记）
  • 输出：结果放哪（新文件、新 sheet、覆盖原数据、发邮件）

错误示范：

帮我写个 VBA 整理销售表

正确示范：

输入：D:/sales/ 下所有 .xlsx 文件，每个文件第 1 个 sheet 处理：跳过表头，把每行数据按「地区、产品、金额」3 列复制到总表 输出：D:/sales/total.xlsx 的 sheet1，A 列从第 2 行开始

第 2 步：用模板 1 出基础脚本

把需求三段式喂给 AI，按模板 1 出代码。

第一次出来的代码大概率能跑 80% 的场景，但会漏一些边界情况（如空文件、合并单元格）。先跑跑看。

第 3 步：跑一次，把报错丢回 AI

VBA 代码 80% 的报错是路径不对、sheet 名不对、数据类型不匹配。

把报错信息（含错误号 + 错误描述）丢给 AI，让它修。例如：

跑你刚才给的代码报错：「运行时错误 ‘1004’：应用程序定义的或对象定义的错误」，行号 35，那一行是 ws.Range("A2:D" & lastRow).Copy。怎么改？

AI 会精准定位问题。改 1-2 轮基本能跑通。

第 4 步：跑通后做 3 件事

  • 保存为 .xlsm 文件：.xlsx 不能存宏
  • 加密码保护代码：VBA 编辑器 → 工具 → VBAProject 属性 → 保护
  • 写一个简短说明：哪里点按钮触发、出错找谁、备份在哪

[此处放截图：VBA 编辑器界面，展示代码窗口、模块面板、运行按钮的位置]

AI 输出示例 + 5 个让它写得更好的技巧

下面是用模板 1 写「合并文件夹下所有 Excel」的真实输出片段，给你参考。

Sub MergeAllExcelFiles()
    ' 把指定文件夹下所有 .xlsx 文件的 sheet1 合并到当前工作簿的「总表」sheet
    
    Dim folderPath As String
    Dim fileName As String
    Dim sourceBook As Workbook
    Dim sourceSheet As Worksheet
    Dim targetSheet As Worksheet
    Dim lastRow As Long, targetRow As Long
    
    ' 1. 配置参数
    folderPath = "D:\sales\"  ' 修改为你的文件夹路径
    Set targetSheet = ThisWorkbook.Sheets("总表")
    targetRow = 2  ' 从第 2 行开始写（保留表头）
    
    ' 2. 遍历文件夹下所有 .xlsx 文件
    fileName = Dir(folderPath & "*.xlsx")
    Do While fileName <> ""
        ' 跳过当前文件本身
        If fileName <> ThisWorkbook.Name Then
            Set sourceBook = Workbooks.Open(folderPath & fileName, ReadOnly:=True)
            Set sourceSheet = sourceBook.Sheets(1)
            
            ' 3. 找到数据范围（跳过表头）
            lastRow = sourceSheet.Cells(sourceSheet.Rows.Count, 1).End(xlUp).Row
            If lastRow > 1 Then
                sourceSheet.Range("A2:D" & lastRow).Copy _
                    targetSheet.Cells(targetRow, 1)
                targetRow = targetRow + (lastRow - 1)
            End If
            
            sourceBook.Close SaveChanges:=False
        End If
        fileName = Dir
    Loop
    
    MsgBox "合并完成，共写入 " & (targetRow - 2) & " 行数据"
End Sub

照这个建议跑一次，5 个文件 5 秒合并完成。

5 个让 AI 写得更好的技巧

技巧 1：先描述输入输出再描述处理

prompt 里先说「输入是什么、输出要什么」，再说中间怎么处理。AI 能更准确判断要操作哪些对象。

技巧 2：让 AI 加详细注释

明确写「代码必须加中文注释，每个关键步骤都说明在干什么」。注释完整的代码，半年后你回来改还能看懂。

技巧 3：让 AI 处理异常

prompt 加一句「考虑文件不存在、sheet 名错、数据类型不匹配等异常情况，遇到异常不要崩溃」。能避免脚本跑一半挂掉。

技巧 4：先小数据测试再上大数据

让 AI 在代码末尾加一段「测试模式」开关。测试时只处理前 10 行，确认逻辑对了再去掉开关跑全量。

技巧 5：让 AI 解释代码

代码出来后，问一句「用大白话解释这段代码每一行在干嘛」。能帮你理解逻辑，下次改的时候不慌。

一个常见失败案例 + 怎么补救

失败场景：你只描述了「正常情况」，没考虑异常。结果跑到第 3 个文件时卡死。

报错大概长这样：

运行时错误 ‘9’：下标越界

原因：第 3 个文件没有 sheet1，可能 sheet 名叫「数据」。

补救方法：

  1. 加防御性代码：让 AI 在打开每个文件前检查 sheet 是否存在
  2. 打 log：每处理一个文件就 print 一行 log，跑错了能知道卡在哪
  3. 加 On Error Resume Next：遇到异常跳过当前文件继续处理下一个，最后统一报告哪些文件出错

记住：AI 写 VBA 不是替你做架构师，是替你做编码。需求越清楚、边界越明确，AI 写的代码越稳定。

进阶玩法 + 类似场景

学会用 AI 写 VBA，同一套思路可以做很多事。

进阶玩法 1：让 AI 帮你重构旧代码

手里有一份 5 年前同事写的 200 行 VBA，看不懂还要改。把代码丢给 AI，让它「用大白话解释每一段功能 + 重写一份更清晰的版本」。能省一周学习成本。

进阶玩法 2：让 AI 生成 Office 365 的新方案

新的 Office 365 支持 Office Scripts（TypeScript 写宏），云端可跑。让 AI 同时给「VBA 版本」和「Office Scripts 版本」，按你的环境选。

进阶玩法 3：跨 Office 应用联动

VBA 不只能写 Excel，还能控制 Word、Outlook、PowerPoint。让 AI 写一个「Excel 数据 → 自动生成 Word 报告 → 邮件发送」的全自动脚本。

类似场景：这套方法还能做什么

同样的「描述需求 + 给数据样例 + 让 AI 写代码」流程，可以套用到：

常见问题

Q：我从来没写过代码，能用 AI 写 VBA 吗？ A：能。VBA 是最入门级的编程语言，AI 写出来的代码加上详细注释，普通用户能照着改。建议先从「批量处理」类的简单脚本入手，跑通几个再尝试复杂任务。

Q：宏会不会被杀毒软件拦截？ A：自己写的 VBA 不会被拦，但从网上下载的 .xlsm 文件可能被拦。把 AI 给的代码复制到自己的 Excel 里，让 Excel 自带的「信任此宏」机制确认就行。

Q：免费版 AI 能写 VBA 吗？ A：能。VBA 是通用任务，免费版 ChatGPT、Claude、Kimi 都能写。复杂脚本（500 行以上）建议用付费版 Claude 或 ChatGPT。

Q：脚本跑得很慢怎么办？ A：3 个常见优化方向：① 关掉 Application.ScreenUpdating；② 用数组一次性读写而不是逐单元格操作；③ 关闭自动计算（Application.Calculation = xlManual）。把脚本丢给 AI，让它「优化性能，目标 10 倍加速」。

Q：WPS 能跑 VBA 吗？ A：WPS 个人版默认不带 VBA，需要单独安装 VBA 7.1 插件。WPS 专业版自带。AI 写 VBA 时告诉它「环境是 WPS」，它会避开 Office 独有的 API。