Excel宏与VBA自动化办公实战：拿来就用的代码库

Excel宏与VBA自动化办公实战：拿来就用的代码库零基础可改·全部代码附注释·10个脚本覆盖80%重复劳动致每一位被重复操作折磨的职场人，你是否有过这样的时刻：每周一要合并8个部门的报表，逐个打开、复制、粘贴，两个小时过去了，只做了一件毫无价值的事；或者月底要给200个客户发对账单，一封封创建邮件、添加附件、修改标题，手酸眼花还怕发错。你听说过“宏”和“VBA”能解决这一切，但打开编辑器看到英文字母就头疼，网上搜的代码要么跑不通，要么不敢用。这份手册，就是为你准备的。我不讲计算机原理，不讲你暂时用不上的复杂语法。我会带你录制第一个宏，读懂并修改它，然后把10个经过上千次验证的自动化脚本交到你手里——每一个都附有详细注释，你只需要改一个文件路径、一个邮箱地址，就能直接运行。在开始之前，请务必记住这条铁律：⚠️先备份原文件，再运行任何宏。将你的重要Excel文件复制一份到“测试文件夹”，所有练习都在副本上进行。这是保护数据的第一原则，也是职业素养。目录认识VBA——30分钟从陌生到写出第一段代码录制宏与修改宏——让Excel帮你写代码一键合并多个工作簿/工作表批量拆分工作表为独立文件自动发送邮件（基于Outlook）批量导出PDF并智能命名用户窗体设计——简易录入界面错误处理与调试技巧安全性设置与分发10个常用自动化脚本速查库附录：常见报错与排查1.认识VBA——30分钟从陌生到写出第一段代码1.1打开那扇门：VBA编辑器打开Excel，按Alt+F11。

一个全新的窗口出现了——这就是VBA编辑器，所有魔法发生的地方。左侧是“工程资源管理器”，显示你打开的所有工作簿和工作表。如果没看到，按Ctrl+R。在左侧右键你的工作簿名称→插入→模块。右侧出现一个空白编辑区。此刻，你已站在自动化的大门前。1.2第一段代码：让Excel跟你对话在空白模块中，输入以下代码（你可以直接复制）：Sub我的第一个宏()

'这是一条注释，VBA不执行它，只是给人类看的说明

MsgBox"你好，自动化世界！"&vbCrLf&"现在时间是："&Now

EndSubSub和EndSub之间是过程体，宏的内容。单引号'开头的行是注释，写给人看。MsgBox是弹窗函数。vbCrLf是换行符，Now是当前系统时间。运行它：点击代码任意位置，按F5。一个弹窗瞬间出现。恭喜，你的第一个宏已成功执行。1.3三个最核心的概念变量——数据的容器Dim姓名AsString'定义一个装文字的变量

Dim数量AsInteger'定义一个装整数的变量

Dim金额AsDouble'定义一个装小数的变量

姓名="张三"

数量=100

MsgBox姓名&"买了"&数量&"件商品"'&是连接符循环——让程序重复干活DimiAsInteger

Fori=1To10

Cells(i,1).Value="第"&i&"行"'在第1列的第1到10行填内容

Nexti判断——让程序有脑子Dim库存AsInteger

库存=Cells(2,2).Value

If库存<10Then

MsgBox"库存不足，请补货！"

Else

MsgBox"库存正常"

EndIf变量、循环、判断——这三个掌握了，你就学会了VBA的一半。2.录制宏与修改宏——让Excel帮你写代码你不需要从零写代码，Excel可以为你代笔。2.1录制你的第一个宏视图→宏→录制宏。

名称输入格式化报表，快捷键可设为Ctrl+Shift+F。现在你做的每一步操作都会被录制。

例如：选中A1单元格→输入“销售报表”→加粗→字体调为16号→给A1:D1填充蓝色。停止录制。2.2看看Excel为你写了什么按Alt+F11，在模块里你会看到一段自动生成的代码，类似：Sub格式化报表()

Range("A1").Select

ActiveCell.FormulaR1C1="销售报表"

Selection.Font.Bold=True

Selection.Font.Size=16

Range("A1:D1").Select

Selection.Interior.Color=RGB(0,112,192)

EndSub2.3修改录制的宏：去掉冗余，让它更聪明录制的宏包含大量Select和Selection，这是冗余的。我们可以把它精简为：Sub格式化报表()

WithRange("A1")

.Value="销售报表"

.Font.Bold=True

.Font.Size=16

EndWith

Range("A1:D1").Interior.Color=RGB(0,112,192)

EndSubWith...EndWith让你对一个对象进行多项操作时不用反复写它的名字。代码立刻清爽。你的成长路径：录制→看懂→修改→自己写。这条路径无数人走过，你同样可以。3.一键合并多个工作簿/工作表场景：你有一个文件夹，里面放着30个分公司的Excel报表，你需要把它们合并到一个总表里。3.1合并同一文件夹下所有工作簿操作前准备：新建一个Excel文件作为“汇总表”，保存到与待合并文件相同的文件夹中。按Alt+F11插入模块，粘贴以下代码。Sub合并所有工作簿()

Dim文件夹路径AsString

Dim文件名AsString

Dim汇总表AsWorkbook

Dim源工作簿AsWorkbook

Dim最后行AsLong

'关闭屏幕刷新，大幅提速

Application.ScreenUpdating=False

'获取当前文件夹路径

文件夹路径=ThisWorkbook.Path&"\"

文件名=Dir(文件夹路径&"*.xlsx")'只找.xlsx文件

'设置汇总表

Set汇总表=ThisWorkbook

'循环每一个文件

DoWhile文件名<>""

'跳过汇总表自身

If文件名<>ThisWorkbook.NameThen

Set源工作簿=Workbooks.Open(文件夹路径&文件名)

'找到汇总表最后有数据的行

最后行=汇总表.Sheets(1).Cells(Rows.Count,1).End(xlUp).Row+1

'复制源文件第一个工作表的所有数据

源工作簿.Sheets(1).UsedRange.Copy

汇总表.Sheets(1).Cells(最后行,1).PasteSpecialxlPasteValues

'关闭源文件，不保存

源工作簿.CloseFalse

EndIf

'找下一个文件

文件名=Dir

Loop

'恢复屏幕刷新

Application.ScreenUpdating=True

MsgBox"所有工作簿已合并完成！"

EndSub代码解析（逐行看懂）：Dir()函数遍历文件夹，像点名一样一个个叫出文件名。DoWhile...Loop是循环结构，直到没有下一个文件为止。End(xlUp)从表格最底部向上找最后一个有数据的单元格，确保新数据粘贴时不覆盖旧数据。PasteSpecialxlPasteValues只粘贴数值，保留你的汇总表格式。修改指南：如果文件是.xls格式，把"*.xlsx"改为"*.xls"。如果要合并的是第二个工作表，把Sheets(1)改为Sheets(2)或Sheets("数据")。4.批量拆分工作表为独立文件场景：你有一个总表，里面“北京”“上海”“广州”各一张工作表，需要把它们拆成独立的Excel文件发给各地负责人。Sub拆分工作表为独立文件()

Dim原始工作簿AsWorkbook

Dim新工作簿AsWorkbook

Dim工作表AsWorksheet

Dim保存路径AsString

'关闭屏幕刷新和警告

Application.ScreenUpdating=False

Application.DisplayAlerts=False

Set原始工作簿=ThisWorkbook

保存路径=原始工作簿.Path&"\拆分结果\"

'如果文件夹不存在则创建

IfDir(保存路径,vbDirectory)=""ThenMkDir保存路径

'遍历每一个工作表

ForEach工作表In原始工作簿.Worksheets

工作表.Copy'这行会创建一个新工作簿，只含当前工作表

Set新工作簿=ActiveWorkbook

新工作簿.SaveAs保存路径&工作表.Name&".xlsx"

新工作簿.Close

Next工作表

Application.DisplayAlerts=True

Application.ScreenUpdating=True

MsgBox"拆分完成！文件已保存到："&保存路径

EndSub关键点：Worksheets.Copy不带参数时，会自动创建新工作簿，只包含被复制的工作表。MkDir创建文件夹，Dir(vbDirectory)检查文件夹是否已存在。拆分后的文件以工作表名称命名。5.自动发送邮件（基于Outlook）场景：你需要给供应商列表里的每一家发送采购订单，附件是对应的PDF文件。前提：你的电脑安装了Outlook并已配置邮箱。Sub批量发送邮件带附件()

DimOutlookAppAsObject

Dim邮件AsObject

Dim最后行AsLong

DimiAsLong

'假设数据在Sheet1：A列姓名，B列邮箱，C列附件路径

WithThisWorkbook.Sheets("Sheet1")

最后行=.Cells(.Rows.Count,1).End(xlUp).Row

Fori=2To最后行'假设第1行是标题

SetOutlookApp=CreateObject("Outlook.Application")

Set邮件=OutlookApp.CreateItem(0)

With邮件

.To=.Cells(i,2).Value'收件人

.Subject="采购订单-"&.Cells(i,1).Value

.Body="尊敬的"&.Cells(i,1).Value&"："&vbCrLf&_

"请查收附件中的采购订单，谢谢。"

.Attachments.Add.Cells(i,3).Value'添加附件

.Display'先用.Display预览，确认无误后改为.Send自动发送

'.Send'取消上面.Display的注释，注释掉.Display，即可自动发送

EndWith

Nexti

EndWith

Set邮件=Nothing

SetOutlookApp=Nothing

MsgBox"邮件创建完成！"

EndSub安全提醒：强烈建议先用.Display测试，确认每一封邮件的收件人、附件都正确。测试无误后，将.Display替换为.Send即可自动发送。大批量发送前，请确认你的邮箱每日发送限制。6.批量导出PDF并智能命名场景：你需要把总表里的每个工作表都导出为PDF，文件名用工作表名+日期。Sub批量导出PDF()

Dim保存路径AsString

Dim工作表AsWorksheet

保存路径=ThisWorkbook.Path&"\PDF输出\"

IfDir(保存路径,vbDirectory)=""ThenMkDir保存路径

Application.ScreenUpdating=False

ForEach工作表InThisWorkbook.Worksheets

工作表.ExportAsFixedFormat_

Type:=xlTypePDF,_

Filename:=保存路径&工作表.Name&"_"&Format(Date,"YYYYMMDD")&".pdf",_

Quality:=xlQualityStandard

Next工作表

Application.ScreenUpdating=True

MsgBox"PDF已全部导出到："&保存路径

EndSub代码说明：ExportAsFixedFormat是Excel内置的导出方法，无需额外安装。Format(Date,"YYYYMMDD")将当天日期格式化为20260603这样的字符串。导出的PDF保持工作表的打印设置和页面布局。7.用户窗体设计——简易录入界面7.1创建你的第一个窗体在VBA编辑器中，右键你的工程→插入→用户窗体。弹出一个空白窗体和工具箱。从工具箱拖入：标签：显示“物料名称”文本框：用于输入物料名称标签：显示“数量”文本框：用于输入数量命令按钮：显示“确定录入”双击命令按钮，输入代码：PrivateSubCommandButton1_Click()

Dim最后行AsLong

'找到工作表最后一行

最后行=ThisWorkbook.Sheets("Sheet1").Cells(Rows.Count,1).End(xlUp).Row+1

'写入数据

ThisWorkbook.Sheets("Sheet1").Cells(最后行,1).Value=TextBox1.Text

ThisWorkbook.Sheets("Sheet1").Cells(最后行,2).Value=TextBox2.Text

'清空文本框，准备下一条

TextBox1.Text=""

TextBox2.Text=""

TextBox1.SetFocus'光标回到第一个文本框

EndSub在模块中写一个宏来显示窗体：Sub打开录入界面()

UserForm1.Show

EndSub效果：运行这个宏，一个干净的录入窗口弹出。输入数据、点击确定，数据就乖乖写入工作表。对于需要高频录入的场景，窗体比直接在Excel里敲要高效、舒适得多。8.错误处理与调试技巧8.1让程序不崩溃：OnError的两种用法Sub安全删除工作表()

OnErrorResumeNext'如果出错，跳过继续执行下一行

Application.DisplayAlerts=False

Sheets("临时表").Delete

Application.DisplayAlerts=True

OnErrorGoTo0'恢复正常错误处理

EndSubOnErrorResumeNext适合处理预料之中的错误（比如要删除的表可能不存在）。8.2调试三板斧第一招：逐句运行(F8)

在代码中点击任意位置，反复按F8，程序会一行一行执行。你将看到代码的实际跳转逻辑。第二招：断点(F9)

在行号左侧灰色区域点击，会出现一个红点。运行宏时，程序会在这里暂停，等待你的指令。此时将鼠标悬停在变量上，可以看到它的当前值。第三招：立即窗口(Ctrl+G)

在VBA编辑器底部打开“立即窗口”，输入?变量名回车，即可查看变量的值。也可以在代码中插入Debug.Print变量名，让程序运行时把值打印到这里。9.安全性设置与分发9.1启用宏将包含宏的工作簿保存为.xlsm（启用宏的工作簿）格式。收到别人发来的宏文件时，Excel会默认禁用。需要在黄色警告栏点击“启用内容”。长期使用：将该文件所在文件夹添加到Excel信任位置（文件→选项→信任中心→信任位置）。9.2数字签名（进阶）若在公司内部推广你的宏工具，可以使用自签名证书：在Office安装目录找到SelfCert.exe，创建自签名证书。在VBA编辑器→工具→数字签名，选择你的证书。同事首次打开时选择“信任来自此发布者的所有文档”，后续不再弹警告。9.3分发提醒向同事分发你的宏文件时，请附上简要说明：文件用途使用前请备份数据遇到安全警告时