![](https://img-blog.csdnimg.cn/20201014180756919.png?x-oss-process=image/resize,m_fixed,h_64,w_64)
VBA
taller_2000
不积跬步,无以至千里;不积小流,无以成江海
展开
-
使用VBA隐藏图表中的系列
Excel中很多图表相关的操作,并不能通过录制宏得到代码,这个场景中,如下希望开发代码实现自动化,就会无从下手,其实只要找到相关的属性和方法,代码可能并不复杂。Excel的线图如下所示,其中有三个系列(Test1,Test2和Test3)。通常认为系列(Series)对象,没有Visible属性,所有无法直接实现隐藏系列,建议通过设置线和数据点的颜色来实现隐藏,这个变通方案就比较复杂,需要考虑图表区域的背景色,如果背景色不是纯色填充,那么可能就无法实现隐藏了。原创 2024-06-26 10:53:13 · 221 阅读 · 0 评论 -
快速创建条形热力图
Excel中的条件格式可以有效的凸显数据特征,如下图中B列所示。现在需要使用图表展现热力条形图,如下图所示。由于颜色有多个过渡色,因此手工逐个设置数据条的颜色,基本上是不可能完成的任务,使用VBA代码可以快速创建这种图表。由于数据条是等高的,因此需要增加辅助列,如下图中C列所示。原创 2024-06-26 10:49:48 · 245 阅读 · 0 评论 -
快速清理Word中的嵌套表格
实例需求:Word文档中表格有的单元格中包含嵌套表格(注意其中表格中有合并单元格),如下图所示。现在需要删除单元格顶部的嵌套表格(如上图中的表格1和表格3),如下图所示,如果表格较多,手工操作需要大量时间,而且容易出错。原创 2024-06-25 00:44:05 · 312 阅读 · 0 评论 -
使用Word表格数据快速创建图表
实例需求:Word的表格如下所示,标题行有合并单元格。现在需要根据上述表格数据,在Word中创建如下柱图。如果数据在Excel之中,那么创建这个图并不复杂,但是Word中就没用那么简单了,虽然Word中可以插入图表,但是其数据源仍然是来自于Excel。原创 2024-05-22 06:30:57 · 777 阅读 · 0 评论 -
使用VBA实现快速梳理产品BOM
实例需求:源数据表如左图所示,包含两列:产品编号和类别,其中类别为多级列别列表,使用大于号分隔,类别级别不固定。现在需要按照列表进行拆分,产品编号依次重复,如右图所示。原创 2024-05-22 06:28:50 · 313 阅读 · 0 评论 -
快速创建指定日期带合计的日历表
实例需求:根据用户在B1单元格输入的年份,自动创建日历表,具体要求如下。- 日历表包含指定年份的全部星期四- 每个月份首行,在第一列写入月份的英文简称- 每个月份结束之后,添加月份合计行- 每个季度结束之后,添加季度合计行- 所有合计单元格设置格式如下图所示(粗体、填充色、边框)原创 2024-05-21 09:10:03 · 288 阅读 · 0 评论 -
快速将筛选后的数据加载到数组
实例需求:工作表中有一个格式化表格(ListObject),现需要将筛选后的表格区域(包含标题行)加载到数组中。原创 2024-05-21 06:21:57 · 254 阅读 · 0 评论 -
VBA之正则表达式(45)-- 提取SQL语句中的函数
实例需求:数据工程师或者DBA日常工作中大量使用SQL语句,有些语句(或者存储过程)行数非常多,现在需要提取其中的所有使用了函数的相关部分,对于如下语句,需要提取Mid([编号],2,4) AS [产品]和datediff("D",[购买日期],"2024-01-01") AS [日期]两个字符串。原创 2024-04-24 13:32:39 · 321 阅读 · 1 评论 -
Excel图表智能排序
实例需求:表格中的多个图表如下图左侧所示,对于表格进行排序时,希望第一列中的图表跟随相应数据。原创 2024-04-23 03:23:22 · 256 阅读 · 0 评论 -
VBA智能记录指定列更新时间戳
实例需求:某公司产品统计表如下所示,为了便于追溯变更,如果用户修改了“厂家指导价”或者“供货价”,那么“修改时间列”需要记录当前的时间。这个需要似乎也并不复杂,使用`Worksheet_Change`事件判断变化发生的列,然后再时间戳即可。但是每个公司都有一些奇葩的用户(领导),他们会用尽毕生精力来折腾这个表,为了满足这些奇葩用户需求,此功能需要支持如下场景:- 用户可以在任意位置插入\删除列- 用户可以在表格之上或者左侧插入任意多行原创 2024-04-14 13:29:47 · 497 阅读 · 0 评论 -
使用VBA巧妙获取图表数据源区域
代码很简单,即使大家不知道`SetSourceData`方法,也可以通过录制宏的方法获得代码。现在问题来了,如何读取图表的数据源区域呢?`Chart`对象并没有`SourceData`属性,也没有`GetSourceData`方法。这只能说明微软并未提供相应的接口,我们仍然可以使用变通的方法来获取图表的数据源区域。原创 2024-04-14 12:19:10 · 543 阅读 · 0 评论 -
如何锁定Excel表格(超级表)中的公式
实例需求:Excel的格式化表格(有时被称为超级表[非官方名称],通过`插入>表格`创建,下文简称为表格)是具备很多实用的功能,例如自动扩展表格并应用原格式和公式等等。如果表格中使用了公式列,那么有些小白用户可能会不小心修改某个单元格的公式,这种问题查找起来也很麻烦。有的同学可能说保护工作表(表格中的公式列)就可以了,其实不然,保护工作表虽然避免了用户意外修改公式,但是表格的自动扩展等功能也失效了。是否可以即不牺牲表格的易用性,又可以实现保护表格中的公式呢?效果如下图所示,B列和D列为公式列,无论如何如原创 2024-03-11 03:41:35 · 445 阅读 · 1 评论 -
VBA自适应多种排班计划日期填充
实例需求:某公司有两种不同排班计划- MWF: 周一周三周五-周一周三周五...- TTS: 周二周四周六-周二周四周六...但是数据表中有时会缺少部分日期,为了便于汇总多个部分的数据,现在需要将日期补全,对于补充的日期标记为黄色。原创 2024-03-06 03:01:07 · 528 阅读 · 0 评论 -
VBA如何记录单元格中字符内容和格式
实例需求:Excel单元格中的字符可以设置不同的字体格式(大小、颜色等),有时需要将这些信息保存起来,以便于后续代码的处理(例如替换后恢复原字体颜色,或者统计某种指定格式字符个数等等),此时使用自定义数据结构就可以高效的完成这个任务。原创 2024-03-06 02:59:29 · 352 阅读 · 0 评论 -
VBA实现快速逆透视
将工作表中的数据(多维度交叉),对日期进行逆透视,转换为下表的格式。原创 2024-02-25 04:14:00 · 293 阅读 · 0 评论 -
Excel工作表控件实现滚动按钮效果
工作表中有多个Button控件(工作表Form控件)和一个ScrollBar控件(工作表ActiveX控件,名称为ScrollBar2),需要实现如下图所示效果。点击ScrollBar控件实现按钮的滚动效果,实际工作表中Button控件个数不确定。原创 2024-02-25 04:06:45 · 702 阅读 · 0 评论 -
惊天秘闻-绕过安全设置悄悄导入VBA代码
`ThisWorkbook.Modules.Add` 可以在未启用【信任对VBA工程对象模型的访问】的情况下导入模块代码,并且新导入的模块属于`ThisWorkbook.VBProject.VBComponents`集合,是不是很神奇!原创 2024-01-27 14:30:12 · 381 阅读 · 0 评论 -
VBA快速智能拆分日期
现在需要将A列日期,按照如下规则筛选,并提取开始日期和结束日期,填写在B列和C列时间段(包含起止日期)大于等于5天如果没有符合条件的时间段,B列和C列留空如果有多个符合条件的时间段,提取最后一个,例如A2单元格中,5.15-19和5.29-6.2都符合条件,那么从5.29-6.2中提取起止日期原创 2024-01-27 12:36:44 · 491 阅读 · 0 评论 -
Excel表格的快速动态扩展与删除行
实例需求:工作表中的表格(ListObject)名称为`Table1`,表格列数不确定,需要实现如下功能:- 当用户完成最后一行最后一列输入之后(如果该单元格为空,则视为输入未完成),表格自动扩展一行- 扩展行中,第一列填充自动序号,其他列填充NA- 如果用户双击表格中最后一行的任意单元格,那么删除该行,并相应调整表格单元格范围原创 2024-01-19 14:45:10 · 546 阅读 · 0 评论 -
VBA窗体跟随活动单元格【简易版】(2/2)
上一篇博客(文章连接如下)中使用工作表事件`Worksheet_SelectionChange`实现了窗体跟随活动单元格的动态效果。> [VBA窗体跟随活动单元格【简易版】(1/2)](https://blog.csdn.net/taller_2000/article/details/133854760)为了在用户滚动工作表窗体之后仍能够实现跟随效果,需要使用`Application.Windows(1).VisibleRange`对于显示位置进行调整。俗话说,条条大路通罗马,本文将接受另外一个实现原创 2024-01-19 14:01:51 · 548 阅读 · 0 评论 -
限制选中指定个数CheckBox控件(2/2)
实例需求:工作表中有8个CheckBox控件(下文中简称为控件),现在需要实现限制用户最多只能勾选4个控件。在上一篇博客中已经实现了这个需求,其基本思路是用户选中第5个控件时,事件代码将取消勾选最后一个选中的控件。其实这里案例也可以使用防患于未然的思路来解决,即用户选中4个控件之后,就禁用其他控件,用户当然也就无法再勾选更多控件。原创 2024-01-09 13:07:01 · 401 阅读 · 0 评论 -
限制选中指定个数CheckBox控件(1/2)
实例需求:工作表中有8个CheckBox控件(下文中简称为控件),现在需要实现限制用户最多只能勾选4个控件。原创 2024-01-05 13:01:39 · 486 阅读 · 0 评论 -
定制样式堆积柱形图
实例需求:数据位于D2:G9,使用默认方式创建的堆积柱形图如右下图表所示。现在需要对图表格式进行样式定制。- 工作表中A列为相应的数据类别,其可能的取值与数据表中第2行标题内容相同,也就是只有4中不同的值。- 现在需要将数据类别与标题相匹配的数据点标记为蓝色,其他数据点标记为紫色。例如:对于D列Light系列,A3和A7为Light,即D3和D7为相应的匹配数据,对于的数据点标记为蓝色(参见第一个堆积矩形)。原创 2024-01-05 12:33:45 · 482 阅读 · 0 评论 -
VBA快速填充缺失数据
实例需求:数据表中F列中存在数据缺失,如下图所示。现需要根据A列中的内容(类别,图中C1、C2、B1为不同类别),补充F列数据,已知每个类别中F列存在不少于一个非空单元格,并且其内容相同。原创 2023-12-15 13:51:26 · 182 阅读 · 0 评论 -
Excel公式逆天了--使用公式修改其他单元格格式
如果用户修改单元格填充色,例如选中C列和D列清除填充色,那么当工作表中任意单元格发生变化时,公式将重新计算,C列和D列的填充色将被恢复,相当于实现了挥之不去的单元格格式,效果如下图所示。同理,可以使用公式设置多个单元格的填充色,如A3单元格所示。第2行代码设置UDF为易失函数,任何单元格发生变化时,将重算此函数,注意如果大量单元格使用易失函数,可能会引起Excel卡顿。第5行代码设置UDF返回值,如果设置返回值为空,则设置了公式的单元格中为空白,相当于被隐藏了。,此句是整个解决方案的核心,替换为。原创 2023-12-15 08:07:39 · 291 阅读 · 0 评论 -
使用VBA快速统计词组词频(多单词组合)(2/2)
实例需求:产品清单如A列所示,现在如下统计多单词组合词组词频。 在上一篇博客中[使用VBA快速统计词组词频(多单词组合)(1/2)](https://blog.csdn.net/taller_2000/article/details/134804555)讲解了如何实现双词的词频统计。本文将讲解如何实现3词的词频统计,掌握实现方法之后,可以很容易地将代码扩展到实现更多单词词频统计,实现的效果如下图所示。原创 2023-12-15 02:39:47 · 193 阅读 · 0 评论 -
快速多列查找匹配关键字
实例需求:根据第一列专业名称,在“专业分类指导目录”中,针对三个学历层次(研究生、本科生、专科生)分别查找对应专业类别,填写在对应位置,即截图中的黄色区域。原创 2023-12-15 02:02:28 · 121 阅读 · 0 评论 -
使用VBA快速统计词组词频(多单词组合)(1/2)
实例需求:产品清单如A列所示,现在如下统计词组词频。想必各位小伙伴都指定如何使用字典对象实现去重,进而实现单个单词的词频统计。但是统计词组词频就没有那么简单了,为了便于演示,此处的词组只限于两个单词的组合。提到词组,很多时候大家先想到的是如何将获取全部的组合,例如n个无重复单词,可以产生的无重复词组个数为C(n,2) ,但是在本示例中并不需要获取这些全部组合,实现思路自然也就不同了。原创 2023-12-05 13:08:42 · 218 阅读 · 0 评论 -
使用VBA快速创建日历表(时间维度表)
在数据统计分析中经常用到日历表,也有很多方法创建日历表,例如如下几篇博客分享如何使用Power Query创建日历表(相关链接参见本博文的最后部分)。本次将要分享如何使用VBA创建如下格式的日历表,需要注意的是`周次`列,其中第x周的计算需要一些技巧。原创 2023-12-05 11:52:27 · 614 阅读 · 0 评论 -
如何快速查找最后(最右侧)隐藏列
通常思路是从工作表最后列开始,倒序检查每个列,直到找到隐藏列或者检查完毕(无隐藏列)。其实可以借助VBA的一些特殊方法更快速实现这个需求。原创 2023-12-05 08:49:39 · 107 阅读 · 0 评论 -
使用VBA创建Excel条件格式
**实例需求**:数据总行数不确定,现需要将Category区域(即C列到J列)中第3行开始的区域设置条件格式,规则如下:- 只对部分指定单元格应用色阶条件格式(3色)- 指定单元格应满足条件:该行`B列+0.5`等于该列第2行的值(Category),例如对于第6行,`B6+0.5`值为3,对应Category为3的列为E列,因此E6单元格应用色阶条件格式,该行其他单元格无填充色原创 2023-12-05 08:00:54 · 1026 阅读 · 0 评论 -
VBA快速合并更新数据
实例需求:Sheet1中的数据每日更新,Sheet2的数据为数据总表,现需要每天将Sheet1的数据更新至Sheet2中,如果Name+Color组合在Sheet2中已经存在,那么更新该行的Sales列数据,如果不是全新的数据,那么将该数据行追加到Sheet2数据表之后。原创 2023-11-17 14:26:41 · 170 阅读 · 0 评论 -
VBA如何快速识别Excel单元格中的文本数字
Excel中一种非常特殊的数字,这些数字看似数字,其实是文本格式(下文简称为文本数字),在单元格的左上角会有一个绿色小三角作为标志,如B1:B3单元格。在编程时为什么需要区分普通数字和文本数字呢?先来看以下【立即窗口】种的测试代码。A1和B1单元格内容看似相同,但是测试代码的结果却完全不同。单元格A1为普通数字,加号之后的"0"被转换为数字零,然后进行加法运算。单元格B1为文本数字,加号作为字符串连接运算符,合并两个字符串。原创 2023-11-17 13:42:07 · 1029 阅读 · 0 评论 -
VBA动态按钮控制台
Excel工作表中可用使用两种命令按钮(ActiveX和窗体控件),但是这些按钮外观的定制并不方便,其实Shape对象完全可用作为按钮,使用方法类似窗体控件按钮,并且可用轻松定制其外观。现在需要实现如下效果:5个按钮作为一组用户单击某个按钮,此按钮变色并启用3D样式原创 2023-11-17 10:16:33 · 256 阅读 · 0 评论 -
VBA根据Excel内容快速创建PPT
示例需求:根据Excel中选中的单元格内容(3列)如下图所示,在已打卡的PowerPoint文件中创建页面。新增PPT Slide页面使用第二个模板页面,其中包含两个文本占位符,和一个图片占位符。将Excel选中区域中前两列写入文字占位符,第3列图片粘贴至图片占位符。原创 2023-11-05 03:05:57 · 1637 阅读 · 0 评论 -
VBA快速动态考勤统计
实例需求:某公司的上下班打卡记录如下所示,其中`Table_In`为上班打卡记录,`Table_Out`为下班打卡记录。现在需要根据日期整理为如下格式的考勤表。需要注意如下几点:- 每天的打卡次数不确定- 最后一列`Total/Day`统计该天的出勤总时长,忽略有缺卡的时间段- 对于缺卡记录标记为`Missing`,例如10/14,员工108500,7:59:34和14:59:34两次上班打卡记录之间并没有下班打卡记录,那么7:59:34对应的下班打卡记录为缺失原创 2023-11-04 02:07:26 · 604 阅读 · 0 评论 -
使用VBA打印PDF文件
使用VBA打印工作表和工作簿文件都很容易实现,但是有时需要使用VBA打印已经保存在本机的其他文件,例如PDF文件格式的账单,如果这个PDF并非由Excel生成的那么就无法使用前述方法实现。调用Windows的Shell命令可以实现打印PDF文件。原创 2023-11-04 01:32:19 · 2745 阅读 · 1 评论 -
VBA之正则表达式(44)-- 拆分商品和规格
实例需求:商品组清单保存在A列中,现需要将其拆分为商品名称,保存在从B列开始的后续单元格中,部分商品包含规格,并且多种规格属性使用了逗号分隔,因此无法直接使用Excel分列功能完成数据拆分。原创 2023-11-03 01:10:26 · 302 阅读 · 0 评论 -
限制LitstBox控件显示指定行数的最新数据(3/3)
实例需求:由于数据行数累加增加,控件加载的数据越来越多,每次用户都需要使用右侧滚动条拖动才能查看最新数据。因此希望ListBox只加载最后10行数据(不含标题行),这样用户可以非常方便地选择数据,如下图所示。原创 2023-11-03 01:08:21 · 405 阅读 · 0 评论 -
限制LitstBox控件显示指定行数的最新数据(2/3)
实例需求:由于数据行数累加增加,控件加载的数据越来越多,每次用户都需要使用右侧滚动条拖动才能查看最新数据。因此希望ListBox只加载最后10行数据(不含标题行),这样用户可以非常方便地选择数据,如下图所示。上一篇博客已经实现了上图效果,美中不足的是需要借助辅助单元格区域,某些`Excel洁癖`的小伙伴会觉得不爽,工作表中多了一些非必要的数据,接下来就一起来看看如果不借助辅助单元格区域实现类似的效果。原创 2023-10-16 13:10:50 · 194 阅读 · 0 评论