工作表
taller_2000
不积跬步,无以至千里;不积小流,无以成江海
展开
-
VBA智能记录指定列更新时间戳
实例需求:某公司产品统计表如下所示,为了便于追溯变更,如果用户修改了“厂家指导价”或者“供货价”,那么“修改时间列”需要记录当前的时间。这个需要似乎也并不复杂,使用`Worksheet_Change`事件判断变化发生的列,然后再时间戳即可。但是每个公司都有一些奇葩的用户(领导),他们会用尽毕生精力来折腾这个表,为了满足这些奇葩用户需求,此功能需要支持如下场景:- 用户可以在任意位置插入\删除列- 用户可以在表格之上或者左侧插入任意多行原创 2024-04-14 13:29:47 · 468 阅读 · 0 评论 -
Excel工作表控件实现滚动按钮效果
工作表中有多个Button控件(工作表Form控件)和一个ScrollBar控件(工作表ActiveX控件,名称为ScrollBar2),需要实现如下图所示效果。点击ScrollBar控件实现按钮的滚动效果,实际工作表中Button控件个数不确定。原创 2024-02-25 04:06:45 · 662 阅读 · 0 评论 -
VBA快速插入签名(位置不固定)
实例需求:Excel中的多页表格如下图所示,其中包含多个“受益人签字”,其位置不固定,现在需要在其后插入签名图片。原创 2023-09-02 02:27:18 · 426 阅读 · 0 评论 -
使用VBA快速选中指定的多个工作表
在Excel操作界面中选中工作表标签时保持或者按下,可以选中多个工作表,操作时录制宏,可以得到类似如下的代码。```Sheets(Array("Sheet1", "Sheet2", "Sheet3", "Sheet4")).Select```这样的代码使用起来有时并不方便,例如Excel中的工作表个数较多,名称时常会发生变化等,在VBA开发过程中有更简洁的方法可以实现选中指定的多个工作表。例如现在需要选中当前工作簿中名称中包含“月”字的全部工作表。原创 2023-08-01 14:09:00 · 2114 阅读 · 0 评论 -
如何使用递归函数实现Excel列号转换列标
本博客讲述了如何使用递归方法实现Excel列号转换列标的功能。递归过程从算法上看貌似很复杂,但是本文通过举例说明了如何使用递归过程,以及如何确保递归过程能够正确的结束执行。原创 2023-05-01 07:32:24 · 1253 阅读 · 0 评论 -
VBA快速复制全部工作表
实例需求:Excel工作簿中有多个工作表(个数不确定),现需要将所有工作表复制一份,插入到原工作表之前,如下图所示。原创 2023-04-01 01:03:19 · 2527 阅读 · 0 评论 -
快速批量在工作表插入图片
快速批量在工作表插入图片实例需求:在示例目录中有多个Excel文件,需要在每个文件的全部工作表中快速插入图片,图片文件位于同一目录中,文件名为Chart1.png,代码所在文件“Demo.xlsm”无需插入图片。原创 2022-10-09 05:54:18 · 857 阅读 · 0 评论 -
禁止Excel工作表更名(2/2)
此方案同样适用于单个工作表和不切换工作表的场景,但是凡事都有利有弊,此方案中的Commandbar对象的事件代码将会被多次激活,例如:选中单元格、切换工作表等等操作都会激活该事件代码。第5~6行代码为工作表的Open事件,在工作簿打开之后将被执行,第6行代码实现CommandBar对象的实例化。修改工作表名称后,按回车键之后,系统事件被触发,恢复工作表名称,进而实现了禁止更名。第2~4行代码为CommandBard的更新事件,用于监测工作表的名称。第1行代码声明带事件接口的对象。操作效果如下图所示。原创 2022-08-22 06:27:04 · 287 阅读 · 0 评论 -
禁止Excel工作表更名(1/2)
非常不幸的是,Excel中并没有提供工作表更名的相关事件接口,也就是说工作表更名操作没有类似于Worksheet_Change的事件,用户修改工作表名称时并没有激活某个Excel已经提供的事件。但是Excel已经提供了那么多个系统事件,肯定可以“通融”一下。Excel工作表更名非常简单,即使是办公小白也知道如何操作,有些应用场景中不希望用户修改工作表名称,使用VBA是否可以实现这个需求呢?用户切换工作表操作时,将触发此事件过程,如果第一个工作表的名称被修改了(不是默认名称Main),那么将会被自动恢复。原创 2022-08-22 06:09:45 · 353 阅读 · 0 评论 -
【增强版】快速判断工作簿中是否存在指定工作表
《快速判断工作簿中是否存在指定工作表》介绍了两种方法实现判断工作表是否存在与工作簿中,最近发现有更简单的方法实现,在这里和大家分享一下。示例代码如下。Function blnSheetExist(ByVal strSht As String) As Boolean blnSheetExist = (TypeName(Application.Evaluate(strSht & "!A1")) = "Range")End FunctionSub Demo() Dim aSht原创 2022-02-02 13:54:13 · 1221 阅读 · 2 评论 -
快速合并单元格(2/2)
数据表如下图中A列至D列所示,需要将C列和D列的数据进行合并。每个编号组中,C列合并后,保留第一个单元格(户主姓名),结果如F列至I列所示。原创 2021-08-01 10:31:14 · 211 阅读 · 0 评论 -
快速合并单元格(1/2)
数据表如下图中A列至D列所示,需要将C列和D列的数据进行合并。每个编号组中,C列合并后,保留第一个单元格(户主姓名),结果如F列至I列所示。原创 2021-08-01 10:22:55 · 304 阅读 · 0 评论 -
切片器实现表格统计指标切换显示
大家对于切片器并不陌生,在透视表中通过切片器实现数据切片(筛选),其实Excel中的表格(有时被称为超级表格)也是可以使用切片器的。实例需求:对于如下统计表,由于数据列比较多,希望能够使用切片器实现控制显示统计指标。原创 2021-07-05 03:09:27 · 1363 阅读 · 5 评论 -
工作表保护密码忘了怎么办?
【声明:本文只是技术探讨,不对此代码的效果负责,请勿用于非法用途。】很多人喜欢使用密码保护工作表,这个保护可以起到防止意外修改的作用,但是其保护强度很一般,当然这不是本文要讨论的话题。设置了密码,天长日久忘记了,怎么办?搬起石头砸自己脚,悔该当初 … 客观莫急,去掉这个密码保护很简单。按<Alt+F11>快捷键打开VBE窗口,在代码窗口中粘贴如下代码,运行,哒哒 … 回到了从前原创 2021-04-17 10:40:41 · 649 阅读 · 0 评论 -
快速隐藏/取消隐藏工作表
在日常工作中,工作簿文件中的工作表经常需要隐藏,如果需要隐藏多个工作表,手工操作就比较麻烦,下面看几个常用的场景。隐藏指定名称的工作表Sub HiddenShtByName() Dim objSheet, strShtName For Each strShtName In Array("Sheet1", "Sheet2") Sheets(strShtName).Visible = False Next Set objSheet = NothingEn原创 2021-03-25 14:04:24 · 832 阅读 · 0 评论 -
如何禁止修改Excel工作表名称
经常会有网友提问:如何实现禁止修改Excel工作表名称,这个暂时没有完美解决方案,但是如下变通解决方法基本上可以满足需求。示例文件中有两个工作表,名称为Summary和Sheet2,假设禁止修改第一个工作表名称。修改Excel工作表名称时,涉及跨表引用公式中的工作表名称会自动更新,无需大家操心,所以固定工作表名称一般是两个目的:便于在VBA中引用工作表对象表格文件更加规范化对于第一个目的,其实也更好的解决方法,用户无法在Excel界面中修改工作表的CodeName,所以在VBA代码中使用Cod原创 2021-02-28 15:38:42 · 1614 阅读 · 0 评论 -
Excel合并单元格之真假李逵
读过《水浒传》的肯定知道“真假李逵”这个段子,假李逵要抢真李逵,当然肯定没有得逞了…示例数据如下图所示,数据保存在A~D列,F列是辅助列用于显示D列的公式。每种货号的第一行其实是用Sum函数实现的汇总行,可能有的朋友会说:这个表格设计的不清真(合理),用数据透视表就可以实现汇总。没错我也不推荐这个数据表格的形式,不利于数据处理和分析。但是今天的主题是合并单元格,就用这个奇葩的数据开练吧!嫡传正宗的合并单元格操作:选中D2:D4单元格区域,单击【合并后居中】按钮,在弹出的提示框中单击【确定】按钮完成原创 2020-07-04 07:51:07 · 425 阅读 · 0 评论 -
快速判断工作簿中是否存在指定工作表
使用VBA代码操作工作表时,如果工作表并不存在,那么将产生运行时错误。为了避免这种异常的发生,使用代码操作任何表(不止局限于工作表,也适用于宏表和图表)之前应检测该工作表是否存在。在代码中这样用到此功能,那么将可以将此功能封装为一个自定义函数,在代码中可以很方便的调用函数。方法1:遍历表对象Function blnSheetExist1(ByVal strSheetName As Strin...原创 2020-01-29 23:57:14 · 4321 阅读 · 0 评论 -
如何防止Excel工作表名称被修改
在Excel工作表标签上双击可以修改名称,如果其他工作簿中公式使用了对于此工作表的外部引用,那么工作表名称变化后,这些外部引用经失效,在VBA代码中通过工作表名称引用对象也将出错。利用VBA对象事件代码可以一定程度上的实现防止Excel工作表名称被修改。假设实例文件中的工作表名称为“DEMO”,在该工作表模块中增加如下事件代码。Private Sub Worksheet_SelectionCh...原创 2020-01-29 14:51:53 · 3070 阅读 · 0 评论 -
Excel列标与列号转换
熟悉Excel操作的用户都只有工作表中列标题可以有列标(字母形式)和列号(数字形式)两种,同样在VBA代码中也会用到这两种不同的表示方式。例如应用C6单元格可以如下几种方式,对于Cells的第二个参数,即可以使用数字列号,也可以使用列标字母,然而对于Range来说参数为字符串,也就是使用列标形式。原创 2019-08-01 11:02:11 · 5252 阅读 · 0 评论 -
VBA之正则表达式(8)-- 重复字符分组
实例需求:数据保存在A列中,需要将其中重复字符分拆后,提取后续的列中,为简化示例代码,只考虑小写英文字符。这个需要也并不复杂,用VBA代码逐个判断字符和其前后的字符集可以区分每组,高手可以写出递归调用过程。我们还是来看一下正则如何处理这种问题。Sub RegExpDemo() Dim strTxt As String Dim objRegEx As Object, objMa...原创 2019-05-01 11:03:09 · 4464 阅读 · 1 评论 -
浅谈VBA中使用数组处理工作表数据
前言在VBA中最经常涉及的就是读写工作表中的单元格,如果读写操作次数较多,或者涉及到的单元格数量较多,往往会导致程序运行效率较低。这时经常用到的解决方法就是使用数组保存数据,在内存中进行数据处理和加工,最后一次行更新工作表,这样的操作方式往往会极大提升程序的运行效率。读取数据声明变量首先如何声明用于保存单元格内容的变量呢?虽然在VBA可以直接使用未声明的变量,但是声明变...原创 2018-02-28 12:55:53 · 10219 阅读 · 0 评论 -
VBA处理工作表合并单元格 - 格式篇
前言Excel合并单元格是有人爱有人恨的功能,一般来说数据分析师都非常憎恨这个功能,因为合并单元格常常闹幺蛾子,导致数据统计和格式化障碍重重。格式篇对于普通的规整数据,如果需要设置成间隔填充颜色,可以说是so easy。功能区中点几下鼠标(开始>>套用表格格式),选中自己喜欢的表格样式,瞬间完成这个任务。 如果数据表是有合并单元格的,套用表格格式,杯具了… …,合...原创 2018-03-01 01:49:30 · 9670 阅读 · 0 评论 -
深入了解Excel工作表中的控件
Excel工作表中可以使用两种控件:表单控件和ActiveX控件。插入控件使用VBA在工作表中插入控件非常简单。Sub AddCtls() '表单控件 ActiveSheet.Buttons.Add 87.75, 33, 86.25, 33 ActiveSheet.CheckBoxes.Add 228.75, 33, 70.5, 33 'Acti...原创 2018-03-02 03:44:38 · 7408 阅读 · 0 评论 -
快速批量删除工作表
前言删除工作表是VBA代码开发过程中经常用到的操作,一行代码就可以完成,这有啥可讲的呢!?代码一执行,是否就看到下面这个令人烦的窗口跳出来了。 此时,你就不得不点击【删除】按钮确认才可以删除工作表,如果要批量删除多个工作表,那么就需要不停的点击按钮,VBA代码实现自动化操作的目的就无法实现。解决方案Excel为了避免用户的误操作,每次删除工作表都会给出提示信息,用户确认后才会...原创 2018-04-05 10:58:17 · 10175 阅读 · 0 评论 -
如何判断工作簿中是否存在某个工作表
开发VBA代码时,经常需要操作工作表对象,但是这个是否存在呢?各种奇葩的用户都会有,所以在代码中一定要先判断然后再进行后续操作,非常可惜的是VBA中并没有提供直接的方法来完成这个判断,只有写一个自定义函数了。Function udfSheetExists(strShtName As String, Optional strWbName As String) As Boolean On ...原创 2018-05-09 11:26:17 · 13743 阅读 · 1 评论 -
修改Excel工作表名称(CodeName)并不简单
在Excel中修改工作表名称有什么可讲的,在工作表标签上点右键,选择重命名就可以了,恭喜你答对了一半。 另一半呢?除了这个名称外,工作表还有另外一个名称——CodeName,想修改这个名字就没那么简单了。一个工作表竟然有两个名字,这不是添乱吗!? 先看看CodeName在哪里,既然叫做CodeName,肯定和Coding相关了,打开VBE窗口。 工作表名称 工作表Cod...原创 2018-05-06 22:58:50 · 12735 阅读 · 0 评论 -
Excel筛选后数据行数
数据筛选是常用的功能,简单高效。原始数据如下。 筛选一下Garde=2的数据,Excel的状态条上就显示“在7条记录中找到3个”,可见数据是3行。 但是VBA中没有提供直接访问这个消息的途径,难道需要逐行去判断B列的数据进行统计!?这简直就是掰着手指头数数呀,肯定有其他解决方法。 听说使用Range(...).SpecialCells(xlCellTypeVisible)可以定位可见...原创 2018-07-29 12:21:03 · 10900 阅读 · 0 评论 -
使用VBA在工作表中快速插入行
在工作表中插入行,有需要用到代码吗?是不是杀鸡用牛刀的感觉。其实不是这样的,在很多复杂的应用场景中,插入行不再是简单的单击鼠标右键就可以即刻完成的。比如需要隔行插入空行,如果有一万行数据,是不是搞到手抽筋了。再比如插入空行的数量不是固定的,这样操作起来是不是要累瞎双眼了。 原始数据如下。 使用代码插入行也非常简单,插入单行代码如下,三行代码的效果是完全相同的。Sub InsertOne...原创 2018-07-27 05:17:00 · 34282 阅读 · 4 评论 -
工作表中控件的特殊引用方法
在我以前发表的博客文章“深入了解Excel工作表中的控件”中介绍了如何引用工作表中的控件,其使用方法和用户窗体中的控件有很多不同之处。 例如工作表中有如下四个ActiveX复选框。 如下代码可以很容易的读取控件的相关属性。Sub ActiveXCtl() For Each ctl In ActiveSheet.OLEObjects Debug.Print ctl...原创 2018-08-27 06:53:16 · 1422 阅读 · 0 评论 -
如何选中多个工作表
说起选中工作表,似乎是每个VBA小白用户在VBA之旅中最早接触的几个命令之一,似乎简单的不能再简单了:Sheet1.Select这么简单的语句有啥可讲的呢,那么我就来提个问题:如何选中多个工作表呢?有的网友的答案是:VBA不支持这个操作。真的吗?VBA这么弱吗?其实不是这样的,VBA不仅仅支持,而且还有不同的实现方式。代码也很简单,还是用工作表的SELECT方法来实现。只需要设置SELEC...原创 2019-01-26 03:00:31 · 2806 阅读 · 0 评论 -
快速批量另存工作表
不少Excel用户都遇到过这样的场景,一个工作簿中有N多个工作表,经常需要把全部(或者还多个)工作表单独另存为Excel文件,然后可能邮件发送个不同部门。操作步骤如下:选择工作表选中全部单元格拷贝新建工作簿文件粘贴(假设不需要格式调整,省略N步)保存工作簿这个操作并不复杂嘛!没错,如果老板告诉你,有100个工作表需要处理,每天都要做一遍,是否心中顿时飘过一万只某某马 …如果你...原创 2019-01-26 03:31:16 · 5422 阅读 · 0 评论 -
Excel工作表中定位最后单元格(END属性)
VBA实现自动化,经常需要定位工作表中的最后单元格,以便于获取已有数据,或者在该单元格之后写入新数据。使用RANGE对象的END属性可以定位最后单元格。假设工作表中的数据如下所示。单元格区域[A5:A7]看不出是否为空,因此在C列对其内容进行了说明。运行示例代码。Sub Demo1() Dim rngLast As Range Set rngLast = Cells(1, 1...原创 2019-02-05 02:59:31 · 5024 阅读 · 0 评论 -
Excel中列宽知多少
在无法预知单元格内容的情况下,一般都会使用自适应列宽,代码如下。ActiveSheet.Columns(1).EntireColumn.AutoFit这样操作会带来一个潜在问题,如果该列中某个单元格被意外填充了长字符串,将导致整个列宽都非常大,所以很多时候会在代码中直接指定列宽的值,设置第一列列宽代码如下。Columns(1).ColumnWidth = 10大家可能会想到,Range...原创 2019-01-29 12:19:28 · 2301 阅读 · 0 评论 -
Excel工作表中定位最后单元格(FIND方法)
前一篇博客讲解了如何使用END属性最后单元格。Excel工作表中定位最后单元格(END属性)在VBA中还有其他的方法可以用来定位最后单元格。工作表中示例数据如下图所示,注意其中A4为空,A6至A7单元格看似空白,其实是有内容的,A6为零长度公式,A7为空格,A8为前缀字符。示例代码如下。Sub Demo2() Dim rngLast As Range Set rngL...原创 2019-02-06 06:02:24 · 4095 阅读 · 0 评论 -
不打开Excel文件读取工作表名(ADOX)
经常会遇到网友提问,如何实现在不打开Excel工作簿文件的前提下,读取其中的工作表名称。说实话,我不太理解这个需要应用的场景,但是我相信存在的就是合理的,既然有人提问,可能就有其应用的场景。常规实现思路是障眼法,其实是个欺骗的方法,工作簿文件被打开了,但是用户看不到而已。代码如下。Sub getTablesNameFake() Dim objApp As Excel.Applicati...原创 2019-03-03 01:13:37 · 3795 阅读 · 0 评论 -
工作表单元格区域另存为图片
实例需求:工作表中的表格内容(A1:C8)另存为图片。这个很难吗?确实不难。一定要用VBA吗?不一定呦。先来看看如下操作,在工作表中选中相应的单元格区域,依次点击【开始】选项卡的【复制】下拉按钮->【复制为图片】命令;然后打开图片编辑器,粘贴图片;另存为图片文件。貌似这个操作不并不麻烦,如果老板需要你把100个工作表都这样处理一下,求此时的心理阴影面积!VBA就是来解救你的,翠花,...原创 2019-04-25 00:14:56 · 3825 阅读 · 0 评论 -
VBA之正则表达式(6)-- 设置音标格式
实例需求:数据保存在A列中,需要对其中音标部分进行下划线标注,如C列所示,匹配这样的非典型字符,正则表达式是最佳选择。示例代码如下。Sub RegExpDemo() Dim strTxt As String Dim objRegEx As Object, objMatch As Object Dim objMH As Object, c As Range Se...原创 2019-04-24 23:54:43 · 2124 阅读 · 0 评论 -
VBA添加工作表详解
看到这个题目,很多人可能会说:添加工作表需要解释吗?连VBA小白都会用呀!没错,大家可能经常用到,但是大家是否都知道下面的用法呢,可能还真不一定呦! VBA添加工作表的代码看起来确实很简单。Sheets.Add 语法格式也很简单,前两个参数用于指定工作表插入的位置,第3个参数指定插入的工作表的数量,第4个参数指定插入的工作表的类型,MSDN帮助中的解释也...原创 2018-02-28 00:43:32 · 24886 阅读 · 2 评论