1. 被重复劳动逼上梁山我的第一个VBA宏1.1 一个周五下午的崩溃2018年冬天我在一家公司做运营每周五下午都要处理一份从后台导出的数据报表。那会儿公司用的还是老掉牙的流程把系统里导出的CSV文件打开手动复制到Excel模板里然后删掉空白列调整格式把某些单元格标黄最后另存为xlsx发到群里。整个流程看起来不复杂但数据量一大就让人崩溃——有一回表格里有两千多行我光是调整列宽、刷格式就刷了四十分钟手腕都酸了。真正让我决定学VBA的是那次我连续弄错了三遍第一遍忘了标黄第二遍把公式列删错了第三遍另存的时候格式全乱了因为CSV打开的时候中文编码直接乱码。那天晚上我回家之后脑子里全是为什么我要做这么蠢的事的念头。第二天上班我打开Excel点开开发工具选项卡找到那个录制宏按钮然后开始记录我每一步操作。是的我的VBA之路就是从一个录制宏开始的。现在回想起来录制宏这件事其实特别有意思。它把人的手动操作翻译成VBA代码虽然代码啰嗦得像老太太的裹脚布但它让我第一次意识到原来我每天在表格里做的那些点来点去本质上都是一条条指令可以被记录下来再被批量执行。我当时看着那些Range(A1).Select、Selection.Interior.Color之类的代码完全看不懂但内心里已经隐隐约约感觉这玩意儿能救我的命。1.2 打开VBA编辑器后的第一课录制完我那个标黄调列宽的宏之后我按了一下AltF11第一次看到了VBA编辑器。左边是工程资源管理器右边是代码窗口中间还有一堆属性面板。说实话第一眼非常劝退满屏的英文术语什么Worksheet、Module、Procedure我全都不认识。但好在我录制的宏代码就摆在那里我能把代码和刚才的操作一一对照——Range(A1).Select就是选中A1ActiveCell.Font.Bold True就是把字体加粗。这种录制—回放—修改的学习方式特别适合零基础的人。我不需要先去背语法只需要把操作录下来然后对着代码猜意思。猜错了就运行一下看哪里报错再去看帮助文档。用这种方式我在两周之内搞懂了最常用的几个对象Workbook工作簿、Worksheet工作表、Range单元格区域、Cells单元格、ActiveSheet当前活动表。也明白了VBA本质上就是一套挂在Office/WPS宿主上的编程语言它用的语法和VB6同源但最大的价值不在于语言本身而在于它能直接操控你桌面上的表格软件。这里我想多说一句很多人一上来就买厚厚的VBA教材从数据类型、运算符、循环结构开始啃结果往往坚持不了三天。我的经验反过来了先有真实需求再带着需求去抄代码、改代码才是成年人自学的正确姿势。你不需要知道什么叫过程级变量声明的最佳实践你只需要知道我这段代码为什么红的搜索vba 变量未定义就能搞懂。1.3 第一次写出自己的代码录制宏用了大概一个月之后我开始不满足于单纯录制了因为录制出来的代码实在太啰嗦。有一次我要把C列的空行删除录制出来的代码有几十行里面全是Select和Selection运行得还很慢。后来我搜了一下发现原来可以直接写Range(C1:C1000).SpecialCells(xlCellTypeBlanks).EntireRow.Delete一行搞定。那一刻我才明白为什么别人说VBA要学代码而不是学录制。录制的代码是给电脑看的而手写的代码是给你自己看的。从那以后我开始尝试手写一些小东西先写一个遍历数据的For Each循环再写一个判断条件的If...Then...Else然后配合MsgBox弹个提示框。虽然写得丑但真是养活了我自己。我记得我写的第一个稍微有点复杂的代码是把某个工作表里所有大于100的单元格标红。那个代码大约长这样Sub MarkLargeValues() Dim rng As Range Dim cell As Range Set rng ThisWorkbook.Sheets(数据).Range(A1:Z1000) For Each cell In rng If IsNumeric(cell.Value) And cell.Value 100 Then cell.Interior.Color RGB(255, 0, 0) End If Next cell End Sub虽然现在看这段代码问题一大堆没加On Error、没限定使用区域、没处理空值但对于当时的我来说这已经是神器了。运行完看到所有大于100的格子唰唰变红那种成就感不亚于第一次独立做出一道硬菜。2. 那些让我效率翻倍的VBA实战场景2.1 批量把图片转换为嵌入格式后来我进入了一家做数据分析的公司VBA用得越来越频繁。有一个需求让我印象特别深业务部门每个月都会提交一堆带截图的Excel表但那些截图大多是浮动在表格上方的Shape对象而不是嵌入到单元格里的图片。一旦表格被转发、合并、或者从WPS切换到Excel浮动图片经常乱跑、丢失特别讨厌。我当时的任务是把一个文件夹里几十个工作簿里的浮动图片全部变成嵌入单元格内的图片。传统做法是手动一张张插入显然是干不完的。我研究了一下发现VBA里Shape对象有个Copy方法可以把图片复制到剪贴板然后通过Worksheet.Paste再粘贴回工作表。但关键是要把它嵌入单元格而不是作为浮动对象回来。具体做法是先定位图片所在的单元格区域复制图片然后选中目标单元格用Paste方式粘贴并且把图片的Placement属性设置为xlMoveAndSizeWithCells。我的核心代码大概长这样Sub EmbedImages() Dim ws As Worksheet Dim shp As Shape Dim targetCell As Range For Each ws In ThisWorkbook.Worksheets For Each shp In ws.Shapes 先把图片复制到剪贴板 shp.Copy 找到图片左上角对应的单元格 Set targetCell ws.Cells(shp.TopLeftCell.Row, shp.TopLeftCell.Column) 在目标单元格粘贴并调整为嵌入模式 ws.Paste targetCell With ws.Shapes(ws.Shapes.Count) .Placement xlMoveAndSizeWithCells .LockAspectRatio msoTrue End With 删除原来的浮动图片 shp.Delete Next shp Next ws End Sub这里面有一个坑就是Shape集合在循环过程中是动态变化的如果你一边遍历一边删除索引会乱套。我的做法是先从后往前遍历或者先收集所有Shape的名字到数组里再统一处理。后来我发现从后往前遍历最稳妥因为删除后面的元素不影响前面元素的位置。这个经验是实测出来的网上很多教程没仔细讲很多人写完一运行就下标越界。2.2 CSV转XLSX与AdvancedFilter去重再有一个特别高频的场景从系统导出的数据永远是CSV而CSV在中文环境下动不动就乱码而且不能保存公式、不能保存格式。于是我自己写了一个小工具一键把指定文件夹里的所有CSV文件转换成xlsx并且顺手做一次去重。CSV转xlsx的核心其实是用Workbooks.Open打开CSV时指定编码格式。我吃过大亏如果文件是UTF-8编码直接双击打开很容易乱码用VBA打开时需要把Local参数设为True或者指定Format。后来我干脆不用Open而是用QueryTables或者Workbooks.OpenText把编码指定为936GBK或65001UTF-8。下面这段我用了很久Sub CsvToXlsx() Dim fso As Object, fld As Object, f As Object Dim wb As Workbook, ws As Worksheet Dim pathStr As String pathStr D:\data\csv_folder\ 改成你的路径 Set fso CreateObject(Scripting.FileSystemObject) Set fld fso.GetFolder(pathStr) For Each f In fld.Files If LCase(f.Name) Like *.csv Then Set wb Workbooks.Open(FileName:f.Path, Local:True) Set ws wb.Sheets(1) 清理掉最后可能的空行 ws.UsedRange.AutoFilter ws.UsedRange.SpecialCells(xlCellTypeVisible).Copy wb.SaveAs Replace(f.Path, .csv, .xlsx), FileFormat:51 wb.Close False End If Next f End Sub至于AdvancedFilter这个是Excel自带的高级筛选功能VBA里可以一行代码完成去除重复项、只保留唯一值的操作。比如你要把A列的所有唯一值提取到D列可以用ws.Range(A1:A1000).AdvancedFilter Action:xlFilterCopy, CopyToRange:ws.Range(D1), Unique:True这个比用字典循环写代码快得多而且不用动原始数据。如果说字典是内存里的人力筛选那么AdvancedFilter就是Excel官方的自动筛选机。根据我的经验如果数据行数在几万行以内优先用AdvancedFilter又快又省内存如果数据量很大或者要顺便统计次数那才需要字典。2.3 用Shape绘制矩形与窗口状态控制有人可能会觉得VBA只适合处理数据平时在Excel里画图、画表头这种事也能用代码做还真能。有一回我需要给一张大宽表添加统一的表头色块和标签同时把Excel窗口最大化还要让用户看到自定义窗口状态。我查了很久发现Shape对象里AddShape方法可以画矩形、圆角矩形甚至可以画箭头和流程图。当时热词里不是有个excel vba绘制矩形吗我最初就是为了给报表封面画一个Logo占位写下了这段Sub DrawRectangle() Dim ws As Worksheet Set ws ThisWorkbook.Sheets(封面) Dim shp As Shape Set shp ws.Shapes.AddShape(msoShapeRoundedRectangle, 10, 10, 200, 50) With shp .Fill.ForeColor.RGB RGB(0, 112, 192) .Line.Visible msoFalse .TextFrame.Characters.Text 月度运营报表 .TextFrame.Characters.Font.Color RGB(255, 255, 255) .TextFrame.Characters.Font.Size 16 End With shp.TextFrame.HorizontalAlignment xlCenter shp.TextFrame.VerticalAlignment xlCenter End Sub在写这段之前我一直不知道msoShapeRoundedRectangle这种常量是什么。后来查了对象模型才知道msoShape*系列都是MsoAutoShapeType的枚举值。类似的知识点用的时候查一次下次就能记住。至于窗口状态热词里那个vba application.windowstate其实很简单Application.WindowState xlMaximized 最大化 Application.WindowState xlMinimized 最小化 Application.WindowState xlNormal 还原这个在自动化报表导出时特别有用尤其是需要给客户展示全屏效果的驾驶舱式表格。我做过一个报表自动化模板打开工作簿时自动隐藏Excel的菜单栏、最大化窗口然后展示一个全屏的Dashboard页等用户点某个按钮再恢复。这个其实用Application.DisplayFullScreen True也可以但有时会有兼容性问题相比之下WindowState更稳。2.4 日期比较与下拉框级联日期处理是Excel用户的阿喀琉斯之踵。因为Excel里的日期本质上是序列值1900年1月1日对应1往后每天加1。VBA里DateDiff、DateAdd、Format这些函数用起来很方便但真正坑的是日期比较大小。举个例子Dim d1 As Date, d2 As Date d1 2024-01-15 d2 2024-01-20 If d2 d1 Then MsgBox d2晚于d1看起来很简单但是如果你从单元格里取日期往往取出来的是Double或String直接比较就会出岔子。我在写报表自动核对时就曾经因为日期列里有文本型日期吃了大亏。后来我学乖了比较之前统一做一次转换Function NormalizeDate(v As Variant) As Date If IsDate(v) Then NormalizeDate CDate(v) Else NormalizeDate CDate(1900/1/1) End If End Function另一个高频需求是下拉框级联。比如省份下拉框选完城市下拉框只显示该省的城市大类选完子类跟着变。很多人一上来就写一堆If判断代码能写半屏。其实最优雅的做法是用一个辅助区域配合Worksheet_Change事件动态重置另一个下拉框。大致思路是这样的Private Sub Worksheet_Change(ByVal Target As Range) If Target.Address $B$2 Then B2是省份下拉框根据它的值刷新B3城市下拉框 With Range(B3).Validation .Delete .Add Type:xlValidateList, AlertStyle:xlValidAlertStop, _ Formula1:GenerateCityList(Target.Value) End With End If End Sub这里的GenerateCityList函数会扫描辅助区域返回一串逗号分隔的城市名。用Validation.Add动态设置数据验证列表是最稳妥的级联写法。注意在事件里改Validation时一定要先.Delete否则第二次运行会提示不能在数据验证上使用联合之类的错误。这些都是我踩过的坑写出来大家少走弯路。3. VBA字典与代码架构从随手写脚本到认真设计3.1 字典Dictionary到底解决了什么问题我大概在写VBA半年之后才真正理解字典这个热词为什么这么火。当时遇到一个场景有两张表一张是三万行的销售明细一张是一万行的客户归属表我需要根据客户ID把归属表里的地区批量填到销售明细里。用VLookup公式的话三万行跑起来慢得想砸电脑用循环单元格访问更是能跑到天荒地老。后来我搜到vba字典才发现原来可以把客户归属表先读进内存里的一个Dictionary对象中然后在一瞬间完成查找。字典的核心思想就是键值对。你往字典里放数据时把客户ID当key把地区当item之后再用客户ID去查地区速度是毫秒级的因为它内部用的是哈希表而不是像VLookup那样一个格子一个格子去比。一个我经常用的例子是统计重复次数Sub CountWithDictionary() Dim dict As Object Set dict CreateObject(Scripting.Dictionary) Dim cell As Range Dim key As String For Each cell In ThisWorkbook.Sheets(数据).Range(A1:A20000) key CStr(cell.Value) If dict.Exists(key) Then dict(key) dict(key) 1 Else dict.Add key, 1 End If Next cell 输出结果 Dim k As Variant For Each k In dict.Keys Debug.Print k, dict(k) Next k End Sub这段代码写得有点赘余其实用dict(key) dict(key) 1时如果key不存在在VBA里直接这样写会报错所以要先Exists判断。还有个小技巧如果只是想判断某个key是否存在用dict.Exists(key)判断速度很快。如果你用dict.CompareMode vbTextCompare还可以让key不区分大小写这在处理英文字段时特别方便。3.2 全局变量与模块规划当你的VBA代码超过几百行之后一片平铺的Sub/Function堆在一起就很难维护了。这时候就要考虑全局变量和模块化。所谓全局变量就是写在模块顶部的Public变量它在整个工程的所有过程之间共享。比如我在报表工具里就定义了一个全局的startTime记录宏运行开始的时间最后弹窗显示耗时多少秒——Public gStartTime As Double Sub StartTask() gStartTime Timer 调用其他过程 Call DoSomething MsgBox 总耗时 Format(Timer - gStartTime, 0.0) 秒 End Sub全局变量用起来方便但也要节制。如果到处都是Public temp代码会变得一团乱麻你不知道哪个过程改了它。我的建议是只有在多个过程之间共享状态时才用全局变量比如工作簿路径、当前工作簿对象、字典对象、日志集合等。反之单个过程内部用Dim定义局部变量外面不要随便暴露。模块规划上我习惯把代码按功能拆成不同的模块Module。比如数据清理.bas、报表生成.bas、界面交互.bas、工具函数.bas。这样改一个功能时心里有数去哪里找。很多初学者喜欢把所有代码都塞进Sheet的代码窗口里虽然能运行但一旦工作表被复制或删除代码也跟着混乱。正确的做法是与特定工作表强相关的事件代码放Sheet里通用业务代码放Module里这样结构清晰得多。3.3 异常处理与调试技巧聊到代码架构就不能不提错误处理。VBA默认的报错方式是弹出一个红色的对话框上面是英文的Run-time error 1004对新手极不友好而且程序直接中断什么都不保存。后来我学会了写On Error语句Sub SafeRun() On Error GoTo ErrorHandler 你的核心代码 Exit Sub ErrorHandler: MsgBox 出错了 Err.Description, vbCritical End SubOn Error GoTo是一种标准的错误跳转模式但要注意Exit Sub必须在ErrorHandler:之前否则错误处理代码会被穿透执行。这是我早期经常范的低级错误一运行总是弹出错误提示翻来覆去查不到原因。还有一种做法是On Error Resume Next意思是出错也继续下一行适合某些查找不到就算了的场景但一定要谨慎否则会把真正的错误也吞掉。调试方面VBA编辑器里最实用的三个工具是F8单步执行、本地窗口、立即窗口。F8可以一行一行看代码执行过程配合鼠标悬停在变量上就能看当前值本地窗口可以查看所有变量在这条语句时的值排查循环越界很有用立即窗口里可以输入?变量名回车直接打印结果。这套调试技巧比看一百篇文章都管用。4. WPS环境下的VBA生存指南4.1 WPS VBA插件与免费启用方法说到国产办公软件WPS现在用的人非常多但大家最头疼的问题就是WPS个人版默认不带VBA只有购买商业版或专业版才内置VBA模块。于是网上搜wps vba宏插件下载的人特别多。我在公司里有一段时间就遇到这个问题家里电脑装的是WPS单位是Excel两者代码兼容性还算好但WPS必须装一个VBA for WPS插件才能跑宏。关于这个插件我要说清楚几点首先WPS官方确实提供过VBA插件安装包最新版WPS特别是Office兼容模式中在开发工具—宏里如果提示未安装会引导你下载其次很多第三方网站的所谓绿色版破解版插件我不建议下载因为宏本身就能执行任意代码如果再扛着一个来路不明的插件等于把钥匙交给了陌生人。安软件和代码都只走官方渠道。启用宏的免费方法其实在WPS里有个功能叫宏安全性在开发工具选项卡最右侧。具体操作是打开WPS依次点开发工具—宏安全性—选择启用所有宏。但启用所有宏有风险日常办公里更推荐禁用所有宏并发出通知这样打开带宏的文件时会提示你你自己决定要不要启用。在Excel里也是类似文件—选项—信任中心—信任中心设置—宏设置。不过说实话如果只是自己用且宏的来源是自己写的可以直接启用所有宏如果是打开别人发的文件先看一眼代码再决定。4.2 宏安全性与密码管理因为宏能做的事情实在太多了它完全可以删除文件、读取通讯录、把数据发到某个网址。所以安全性怎么强调都不过分。我处理的一个原则是不是自己写的宏一律先看代码。在VBA编辑器里把每个模块打开翻一下有没有CreateObject(MSXML2.XMLHTTP)这种网络请求有没有Kill File这种删除文件命令。如果没有异常再运行。至于VBA密码忘记了怎么解锁——这是很多人在网上搜的热词。这类需求通常是这样的别人发来一个带宏的Excel里面有加密的项目工程你想看它的VBA代码但提示工程不可查看或者你自己N年前写的宏加了密码现在忘了。我的看法是不要尝试用破解工具去暴力解密。一是这些工具往往来自灰色渠道本身带着木马二是在公司环境下绕过别人宏密码去查看代码可能涉及合规问题。最稳妥的办法是如果是自己的文件找找有没有之前的备份如果真忘了密码就把代码重写一遍。我在整理老代码时就干过这种事虽然费时间但重写的过程反而让你对代码理解更深。平时建议给自己所有的宏加上一段注释记录密码和修改日期写到标准模块的头部就好了 模块: DataTools 作者: 我 密码: 见公司内部密码库 2024-06这样十年之后翻出来不至于两眼一抹黑。宏密码本质上是防君子不防小人它只是工程查看密码不是对代码内容进行加密所以你越早接受密码保护不了代码这个现实越能合理安排你的安全策略。5. 自学路上的一些教训与工具箱5.1 100例这种学习方式的坑很多人搜vba编程入门自学100例我也这么搜过。说实话100个例子的书和视频我也看过一些它们的价值是让你知道VBA能做这些事但坏处是如果只是照着敲代码不解决自己的实际问题你会陷入一看就会一写就废的境地。我的建议是不要按目录学要按需求清单学。具体做法是把工作上所有重复性操作列一个清单比如每周下载报表后把前两行固定将多个sheet汇总成一个将表格按部门拆分成多个工作簿批量把Excel转成PDF发邮件前自动检查漏填项然后一个一个用VBA去攻克。每攻克一个你的能力就涨一块而不是背了100个语法点结果一个也用不上。我现在带新人也是这个思路不考知识点只考给你一个任务你能不能在半小时内写出能跑的脚本。5.2 我的学习路径与工具组合如果用一句话总结我的VBA学习顺序大概是录制宏→改宏→写事件→搞数组→用字典→做自定义函数→写类模块→集成应用。具体工具上我推荐三样VBA编辑器自带的帮助按F1键或者把光标放在函数名上按F1能查用法。虽然帮助文档有些晦涩但它最准确。宏录制器的代码遇到不会的API先录一遍宏看它生成什么代码再自己改成精简版本。本地Window的立即窗口在VBA编辑器里按CtrlG可以输入代码立刻看到运行结果。比如想知道某个单元格的值直接输入?Range(A1).Value回车方便极了。另外我强烈建议大家从一开始就把代码格式化归类。缩进按Tab键对齐变量名用有意义的词不要全是a1、b2。我见过很多老前辈的代码变量全是mm、nn基本等于天书。5.3 最后再分享一个我天天用的小脚本既然题目叫我与VBA的不解之缘我就把陪伴我最久的一个脚本放在这里当彩蛋吧。它解决的是我每天早上一到工位都要做的第一件事把某个固定文件夹里的临时数据清理掉然后重新生成一个带日期的日报Excel。Sub MorningReport() Dim strPath As String Dim strFile As String strPath C:\report\ 删除昨天的报表 strFile strPath 日报_ Format(DateAdd(d, -1, Date), yyyy-mm-dd) .xlsx If Dir(strFile) Then Kill strFile 生成今天的报表 Dim wb As Workbook Set wb Workbooks.Add wb.Sheets(1).Range(A1).Value 日报生成时间 Now wb.Sheets(1).Range(A2).Value 本日报数据区待填充 wb.SaveAs strPath 日报_ Format(Date, yyyy-mm-dd) .xlsx, 51 wb.Close False End Sub就这么个小东西每天早上帮我省了两分钟。很多人觉得VBA不够时髦但这个世界上的表格软件不会消失围绕表格的琐碎工作也不会消失。对我来说VBA就像一个老伙计不华丽不张扬但关键时刻靠得住。你把自己的工作流程好好琢磨一遍把这些琐碎的、重复的动作交给它你会发现自己多出来的不止是时间还有每天下班前那种活都干完了的轻松感。
我的VBA自学之路：从录制宏到高效办公自动化实战
1. 被重复劳动逼上梁山我的第一个VBA宏1.1 一个周五下午的崩溃2018年冬天我在一家公司做运营每周五下午都要处理一份从后台导出的数据报表。那会儿公司用的还是老掉牙的流程把系统里导出的CSV文件打开手动复制到Excel模板里然后删掉空白列调整格式把某些单元格标黄最后另存为xlsx发到群里。整个流程看起来不复杂但数据量一大就让人崩溃——有一回表格里有两千多行我光是调整列宽、刷格式就刷了四十分钟手腕都酸了。真正让我决定学VBA的是那次我连续弄错了三遍第一遍忘了标黄第二遍把公式列删错了第三遍另存的时候格式全乱了因为CSV打开的时候中文编码直接乱码。那天晚上我回家之后脑子里全是为什么我要做这么蠢的事的念头。第二天上班我打开Excel点开开发工具选项卡找到那个录制宏按钮然后开始记录我每一步操作。是的我的VBA之路就是从一个录制宏开始的。现在回想起来录制宏这件事其实特别有意思。它把人的手动操作翻译成VBA代码虽然代码啰嗦得像老太太的裹脚布但它让我第一次意识到原来我每天在表格里做的那些点来点去本质上都是一条条指令可以被记录下来再被批量执行。我当时看着那些Range(A1).Select、Selection.Interior.Color之类的代码完全看不懂但内心里已经隐隐约约感觉这玩意儿能救我的命。1.2 打开VBA编辑器后的第一课录制完我那个标黄调列宽的宏之后我按了一下AltF11第一次看到了VBA编辑器。左边是工程资源管理器右边是代码窗口中间还有一堆属性面板。说实话第一眼非常劝退满屏的英文术语什么Worksheet、Module、Procedure我全都不认识。但好在我录制的宏代码就摆在那里我能把代码和刚才的操作一一对照——Range(A1).Select就是选中A1ActiveCell.Font.Bold True就是把字体加粗。这种录制—回放—修改的学习方式特别适合零基础的人。我不需要先去背语法只需要把操作录下来然后对着代码猜意思。猜错了就运行一下看哪里报错再去看帮助文档。用这种方式我在两周之内搞懂了最常用的几个对象Workbook工作簿、Worksheet工作表、Range单元格区域、Cells单元格、ActiveSheet当前活动表。也明白了VBA本质上就是一套挂在Office/WPS宿主上的编程语言它用的语法和VB6同源但最大的价值不在于语言本身而在于它能直接操控你桌面上的表格软件。这里我想多说一句很多人一上来就买厚厚的VBA教材从数据类型、运算符、循环结构开始啃结果往往坚持不了三天。我的经验反过来了先有真实需求再带着需求去抄代码、改代码才是成年人自学的正确姿势。你不需要知道什么叫过程级变量声明的最佳实践你只需要知道我这段代码为什么红的搜索vba 变量未定义就能搞懂。1.3 第一次写出自己的代码录制宏用了大概一个月之后我开始不满足于单纯录制了因为录制出来的代码实在太啰嗦。有一次我要把C列的空行删除录制出来的代码有几十行里面全是Select和Selection运行得还很慢。后来我搜了一下发现原来可以直接写Range(C1:C1000).SpecialCells(xlCellTypeBlanks).EntireRow.Delete一行搞定。那一刻我才明白为什么别人说VBA要学代码而不是学录制。录制的代码是给电脑看的而手写的代码是给你自己看的。从那以后我开始尝试手写一些小东西先写一个遍历数据的For Each循环再写一个判断条件的If...Then...Else然后配合MsgBox弹个提示框。虽然写得丑但真是养活了我自己。我记得我写的第一个稍微有点复杂的代码是把某个工作表里所有大于100的单元格标红。那个代码大约长这样Sub MarkLargeValues() Dim rng As Range Dim cell As Range Set rng ThisWorkbook.Sheets(数据).Range(A1:Z1000) For Each cell In rng If IsNumeric(cell.Value) And cell.Value 100 Then cell.Interior.Color RGB(255, 0, 0) End If Next cell End Sub虽然现在看这段代码问题一大堆没加On Error、没限定使用区域、没处理空值但对于当时的我来说这已经是神器了。运行完看到所有大于100的格子唰唰变红那种成就感不亚于第一次独立做出一道硬菜。2. 那些让我效率翻倍的VBA实战场景2.1 批量把图片转换为嵌入格式后来我进入了一家做数据分析的公司VBA用得越来越频繁。有一个需求让我印象特别深业务部门每个月都会提交一堆带截图的Excel表但那些截图大多是浮动在表格上方的Shape对象而不是嵌入到单元格里的图片。一旦表格被转发、合并、或者从WPS切换到Excel浮动图片经常乱跑、丢失特别讨厌。我当时的任务是把一个文件夹里几十个工作簿里的浮动图片全部变成嵌入单元格内的图片。传统做法是手动一张张插入显然是干不完的。我研究了一下发现VBA里Shape对象有个Copy方法可以把图片复制到剪贴板然后通过Worksheet.Paste再粘贴回工作表。但关键是要把它嵌入单元格而不是作为浮动对象回来。具体做法是先定位图片所在的单元格区域复制图片然后选中目标单元格用Paste方式粘贴并且把图片的Placement属性设置为xlMoveAndSizeWithCells。我的核心代码大概长这样Sub EmbedImages() Dim ws As Worksheet Dim shp As Shape Dim targetCell As Range For Each ws In ThisWorkbook.Worksheets For Each shp In ws.Shapes 先把图片复制到剪贴板 shp.Copy 找到图片左上角对应的单元格 Set targetCell ws.Cells(shp.TopLeftCell.Row, shp.TopLeftCell.Column) 在目标单元格粘贴并调整为嵌入模式 ws.Paste targetCell With ws.Shapes(ws.Shapes.Count) .Placement xlMoveAndSizeWithCells .LockAspectRatio msoTrue End With 删除原来的浮动图片 shp.Delete Next shp Next ws End Sub这里面有一个坑就是Shape集合在循环过程中是动态变化的如果你一边遍历一边删除索引会乱套。我的做法是先从后往前遍历或者先收集所有Shape的名字到数组里再统一处理。后来我发现从后往前遍历最稳妥因为删除后面的元素不影响前面元素的位置。这个经验是实测出来的网上很多教程没仔细讲很多人写完一运行就下标越界。2.2 CSV转XLSX与AdvancedFilter去重再有一个特别高频的场景从系统导出的数据永远是CSV而CSV在中文环境下动不动就乱码而且不能保存公式、不能保存格式。于是我自己写了一个小工具一键把指定文件夹里的所有CSV文件转换成xlsx并且顺手做一次去重。CSV转xlsx的核心其实是用Workbooks.Open打开CSV时指定编码格式。我吃过大亏如果文件是UTF-8编码直接双击打开很容易乱码用VBA打开时需要把Local参数设为True或者指定Format。后来我干脆不用Open而是用QueryTables或者Workbooks.OpenText把编码指定为936GBK或65001UTF-8。下面这段我用了很久Sub CsvToXlsx() Dim fso As Object, fld As Object, f As Object Dim wb As Workbook, ws As Worksheet Dim pathStr As String pathStr D:\data\csv_folder\ 改成你的路径 Set fso CreateObject(Scripting.FileSystemObject) Set fld fso.GetFolder(pathStr) For Each f In fld.Files If LCase(f.Name) Like *.csv Then Set wb Workbooks.Open(FileName:f.Path, Local:True) Set ws wb.Sheets(1) 清理掉最后可能的空行 ws.UsedRange.AutoFilter ws.UsedRange.SpecialCells(xlCellTypeVisible).Copy wb.SaveAs Replace(f.Path, .csv, .xlsx), FileFormat:51 wb.Close False End If Next f End Sub至于AdvancedFilter这个是Excel自带的高级筛选功能VBA里可以一行代码完成去除重复项、只保留唯一值的操作。比如你要把A列的所有唯一值提取到D列可以用ws.Range(A1:A1000).AdvancedFilter Action:xlFilterCopy, CopyToRange:ws.Range(D1), Unique:True这个比用字典循环写代码快得多而且不用动原始数据。如果说字典是内存里的人力筛选那么AdvancedFilter就是Excel官方的自动筛选机。根据我的经验如果数据行数在几万行以内优先用AdvancedFilter又快又省内存如果数据量很大或者要顺便统计次数那才需要字典。2.3 用Shape绘制矩形与窗口状态控制有人可能会觉得VBA只适合处理数据平时在Excel里画图、画表头这种事也能用代码做还真能。有一回我需要给一张大宽表添加统一的表头色块和标签同时把Excel窗口最大化还要让用户看到自定义窗口状态。我查了很久发现Shape对象里AddShape方法可以画矩形、圆角矩形甚至可以画箭头和流程图。当时热词里不是有个excel vba绘制矩形吗我最初就是为了给报表封面画一个Logo占位写下了这段Sub DrawRectangle() Dim ws As Worksheet Set ws ThisWorkbook.Sheets(封面) Dim shp As Shape Set shp ws.Shapes.AddShape(msoShapeRoundedRectangle, 10, 10, 200, 50) With shp .Fill.ForeColor.RGB RGB(0, 112, 192) .Line.Visible msoFalse .TextFrame.Characters.Text 月度运营报表 .TextFrame.Characters.Font.Color RGB(255, 255, 255) .TextFrame.Characters.Font.Size 16 End With shp.TextFrame.HorizontalAlignment xlCenter shp.TextFrame.VerticalAlignment xlCenter End Sub在写这段之前我一直不知道msoShapeRoundedRectangle这种常量是什么。后来查了对象模型才知道msoShape*系列都是MsoAutoShapeType的枚举值。类似的知识点用的时候查一次下次就能记住。至于窗口状态热词里那个vba application.windowstate其实很简单Application.WindowState xlMaximized 最大化 Application.WindowState xlMinimized 最小化 Application.WindowState xlNormal 还原这个在自动化报表导出时特别有用尤其是需要给客户展示全屏效果的驾驶舱式表格。我做过一个报表自动化模板打开工作簿时自动隐藏Excel的菜单栏、最大化窗口然后展示一个全屏的Dashboard页等用户点某个按钮再恢复。这个其实用Application.DisplayFullScreen True也可以但有时会有兼容性问题相比之下WindowState更稳。2.4 日期比较与下拉框级联日期处理是Excel用户的阿喀琉斯之踵。因为Excel里的日期本质上是序列值1900年1月1日对应1往后每天加1。VBA里DateDiff、DateAdd、Format这些函数用起来很方便但真正坑的是日期比较大小。举个例子Dim d1 As Date, d2 As Date d1 2024-01-15 d2 2024-01-20 If d2 d1 Then MsgBox d2晚于d1看起来很简单但是如果你从单元格里取日期往往取出来的是Double或String直接比较就会出岔子。我在写报表自动核对时就曾经因为日期列里有文本型日期吃了大亏。后来我学乖了比较之前统一做一次转换Function NormalizeDate(v As Variant) As Date If IsDate(v) Then NormalizeDate CDate(v) Else NormalizeDate CDate(1900/1/1) End If End Function另一个高频需求是下拉框级联。比如省份下拉框选完城市下拉框只显示该省的城市大类选完子类跟着变。很多人一上来就写一堆If判断代码能写半屏。其实最优雅的做法是用一个辅助区域配合Worksheet_Change事件动态重置另一个下拉框。大致思路是这样的Private Sub Worksheet_Change(ByVal Target As Range) If Target.Address $B$2 Then B2是省份下拉框根据它的值刷新B3城市下拉框 With Range(B3).Validation .Delete .Add Type:xlValidateList, AlertStyle:xlValidAlertStop, _ Formula1:GenerateCityList(Target.Value) End With End If End Sub这里的GenerateCityList函数会扫描辅助区域返回一串逗号分隔的城市名。用Validation.Add动态设置数据验证列表是最稳妥的级联写法。注意在事件里改Validation时一定要先.Delete否则第二次运行会提示不能在数据验证上使用联合之类的错误。这些都是我踩过的坑写出来大家少走弯路。3. VBA字典与代码架构从随手写脚本到认真设计3.1 字典Dictionary到底解决了什么问题我大概在写VBA半年之后才真正理解字典这个热词为什么这么火。当时遇到一个场景有两张表一张是三万行的销售明细一张是一万行的客户归属表我需要根据客户ID把归属表里的地区批量填到销售明细里。用VLookup公式的话三万行跑起来慢得想砸电脑用循环单元格访问更是能跑到天荒地老。后来我搜到vba字典才发现原来可以把客户归属表先读进内存里的一个Dictionary对象中然后在一瞬间完成查找。字典的核心思想就是键值对。你往字典里放数据时把客户ID当key把地区当item之后再用客户ID去查地区速度是毫秒级的因为它内部用的是哈希表而不是像VLookup那样一个格子一个格子去比。一个我经常用的例子是统计重复次数Sub CountWithDictionary() Dim dict As Object Set dict CreateObject(Scripting.Dictionary) Dim cell As Range Dim key As String For Each cell In ThisWorkbook.Sheets(数据).Range(A1:A20000) key CStr(cell.Value) If dict.Exists(key) Then dict(key) dict(key) 1 Else dict.Add key, 1 End If Next cell 输出结果 Dim k As Variant For Each k In dict.Keys Debug.Print k, dict(k) Next k End Sub这段代码写得有点赘余其实用dict(key) dict(key) 1时如果key不存在在VBA里直接这样写会报错所以要先Exists判断。还有个小技巧如果只是想判断某个key是否存在用dict.Exists(key)判断速度很快。如果你用dict.CompareMode vbTextCompare还可以让key不区分大小写这在处理英文字段时特别方便。3.2 全局变量与模块规划当你的VBA代码超过几百行之后一片平铺的Sub/Function堆在一起就很难维护了。这时候就要考虑全局变量和模块化。所谓全局变量就是写在模块顶部的Public变量它在整个工程的所有过程之间共享。比如我在报表工具里就定义了一个全局的startTime记录宏运行开始的时间最后弹窗显示耗时多少秒——Public gStartTime As Double Sub StartTask() gStartTime Timer 调用其他过程 Call DoSomething MsgBox 总耗时 Format(Timer - gStartTime, 0.0) 秒 End Sub全局变量用起来方便但也要节制。如果到处都是Public temp代码会变得一团乱麻你不知道哪个过程改了它。我的建议是只有在多个过程之间共享状态时才用全局变量比如工作簿路径、当前工作簿对象、字典对象、日志集合等。反之单个过程内部用Dim定义局部变量外面不要随便暴露。模块规划上我习惯把代码按功能拆成不同的模块Module。比如数据清理.bas、报表生成.bas、界面交互.bas、工具函数.bas。这样改一个功能时心里有数去哪里找。很多初学者喜欢把所有代码都塞进Sheet的代码窗口里虽然能运行但一旦工作表被复制或删除代码也跟着混乱。正确的做法是与特定工作表强相关的事件代码放Sheet里通用业务代码放Module里这样结构清晰得多。3.3 异常处理与调试技巧聊到代码架构就不能不提错误处理。VBA默认的报错方式是弹出一个红色的对话框上面是英文的Run-time error 1004对新手极不友好而且程序直接中断什么都不保存。后来我学会了写On Error语句Sub SafeRun() On Error GoTo ErrorHandler 你的核心代码 Exit Sub ErrorHandler: MsgBox 出错了 Err.Description, vbCritical End SubOn Error GoTo是一种标准的错误跳转模式但要注意Exit Sub必须在ErrorHandler:之前否则错误处理代码会被穿透执行。这是我早期经常范的低级错误一运行总是弹出错误提示翻来覆去查不到原因。还有一种做法是On Error Resume Next意思是出错也继续下一行适合某些查找不到就算了的场景但一定要谨慎否则会把真正的错误也吞掉。调试方面VBA编辑器里最实用的三个工具是F8单步执行、本地窗口、立即窗口。F8可以一行一行看代码执行过程配合鼠标悬停在变量上就能看当前值本地窗口可以查看所有变量在这条语句时的值排查循环越界很有用立即窗口里可以输入?变量名回车直接打印结果。这套调试技巧比看一百篇文章都管用。4. WPS环境下的VBA生存指南4.1 WPS VBA插件与免费启用方法说到国产办公软件WPS现在用的人非常多但大家最头疼的问题就是WPS个人版默认不带VBA只有购买商业版或专业版才内置VBA模块。于是网上搜wps vba宏插件下载的人特别多。我在公司里有一段时间就遇到这个问题家里电脑装的是WPS单位是Excel两者代码兼容性还算好但WPS必须装一个VBA for WPS插件才能跑宏。关于这个插件我要说清楚几点首先WPS官方确实提供过VBA插件安装包最新版WPS特别是Office兼容模式中在开发工具—宏里如果提示未安装会引导你下载其次很多第三方网站的所谓绿色版破解版插件我不建议下载因为宏本身就能执行任意代码如果再扛着一个来路不明的插件等于把钥匙交给了陌生人。安软件和代码都只走官方渠道。启用宏的免费方法其实在WPS里有个功能叫宏安全性在开发工具选项卡最右侧。具体操作是打开WPS依次点开发工具—宏安全性—选择启用所有宏。但启用所有宏有风险日常办公里更推荐禁用所有宏并发出通知这样打开带宏的文件时会提示你你自己决定要不要启用。在Excel里也是类似文件—选项—信任中心—信任中心设置—宏设置。不过说实话如果只是自己用且宏的来源是自己写的可以直接启用所有宏如果是打开别人发的文件先看一眼代码再决定。4.2 宏安全性与密码管理因为宏能做的事情实在太多了它完全可以删除文件、读取通讯录、把数据发到某个网址。所以安全性怎么强调都不过分。我处理的一个原则是不是自己写的宏一律先看代码。在VBA编辑器里把每个模块打开翻一下有没有CreateObject(MSXML2.XMLHTTP)这种网络请求有没有Kill File这种删除文件命令。如果没有异常再运行。至于VBA密码忘记了怎么解锁——这是很多人在网上搜的热词。这类需求通常是这样的别人发来一个带宏的Excel里面有加密的项目工程你想看它的VBA代码但提示工程不可查看或者你自己N年前写的宏加了密码现在忘了。我的看法是不要尝试用破解工具去暴力解密。一是这些工具往往来自灰色渠道本身带着木马二是在公司环境下绕过别人宏密码去查看代码可能涉及合规问题。最稳妥的办法是如果是自己的文件找找有没有之前的备份如果真忘了密码就把代码重写一遍。我在整理老代码时就干过这种事虽然费时间但重写的过程反而让你对代码理解更深。平时建议给自己所有的宏加上一段注释记录密码和修改日期写到标准模块的头部就好了 模块: DataTools 作者: 我 密码: 见公司内部密码库 2024-06这样十年之后翻出来不至于两眼一抹黑。宏密码本质上是防君子不防小人它只是工程查看密码不是对代码内容进行加密所以你越早接受密码保护不了代码这个现实越能合理安排你的安全策略。5. 自学路上的一些教训与工具箱5.1 100例这种学习方式的坑很多人搜vba编程入门自学100例我也这么搜过。说实话100个例子的书和视频我也看过一些它们的价值是让你知道VBA能做这些事但坏处是如果只是照着敲代码不解决自己的实际问题你会陷入一看就会一写就废的境地。我的建议是不要按目录学要按需求清单学。具体做法是把工作上所有重复性操作列一个清单比如每周下载报表后把前两行固定将多个sheet汇总成一个将表格按部门拆分成多个工作簿批量把Excel转成PDF发邮件前自动检查漏填项然后一个一个用VBA去攻克。每攻克一个你的能力就涨一块而不是背了100个语法点结果一个也用不上。我现在带新人也是这个思路不考知识点只考给你一个任务你能不能在半小时内写出能跑的脚本。5.2 我的学习路径与工具组合如果用一句话总结我的VBA学习顺序大概是录制宏→改宏→写事件→搞数组→用字典→做自定义函数→写类模块→集成应用。具体工具上我推荐三样VBA编辑器自带的帮助按F1键或者把光标放在函数名上按F1能查用法。虽然帮助文档有些晦涩但它最准确。宏录制器的代码遇到不会的API先录一遍宏看它生成什么代码再自己改成精简版本。本地Window的立即窗口在VBA编辑器里按CtrlG可以输入代码立刻看到运行结果。比如想知道某个单元格的值直接输入?Range(A1).Value回车方便极了。另外我强烈建议大家从一开始就把代码格式化归类。缩进按Tab键对齐变量名用有意义的词不要全是a1、b2。我见过很多老前辈的代码变量全是mm、nn基本等于天书。5.3 最后再分享一个我天天用的小脚本既然题目叫我与VBA的不解之缘我就把陪伴我最久的一个脚本放在这里当彩蛋吧。它解决的是我每天早上一到工位都要做的第一件事把某个固定文件夹里的临时数据清理掉然后重新生成一个带日期的日报Excel。Sub MorningReport() Dim strPath As String Dim strFile As String strPath C:\report\ 删除昨天的报表 strFile strPath 日报_ Format(DateAdd(d, -1, Date), yyyy-mm-dd) .xlsx If Dir(strFile) Then Kill strFile 生成今天的报表 Dim wb As Workbook Set wb Workbooks.Add wb.Sheets(1).Range(A1).Value 日报生成时间 Now wb.Sheets(1).Range(A2).Value 本日报数据区待填充 wb.SaveAs strPath 日报_ Format(Date, yyyy-mm-dd) .xlsx, 51 wb.Close False End Sub就这么个小东西每天早上帮我省了两分钟。很多人觉得VBA不够时髦但这个世界上的表格软件不会消失围绕表格的琐碎工作也不会消失。对我来说VBA就像一个老伙计不华丽不张扬但关键时刻靠得住。你把自己的工作流程好好琢磨一遍把这些琐碎的、重复的动作交给它你会发现自己多出来的不止是时间还有每天下班前那种活都干完了的轻松感。
