25、Excel VBA实用代码技巧分享

Excel VBA实用代码技巧分享

在处理Excel电子表格时,我们常常会遇到一些需要自动化操作的场景,比如给表格的行或列交替着色、给包含公式的单元格着色、根据特定条件对单元格求和以及全局更改单元格的值等。下面将详细介绍如何使用VBA代码实现这些功能。

1. 给表格的行或列交替着色

当电子表格中有很多行数据时,阅读起来可能会比较困难,尤其是打印出来或者数据行很长的时候。一种解决方案是给交替的行着色。

1.1 代码实现

首先,需要在用户表单模块的声明部分插入API调用声明和变量类型声明:

Private Type COLORSTRUC
    lStructSize As Long
    hwnd As Long
    hInstance As Long
    rgbResult As Long
    lpCustColors As String
    Flags As Long
    lCustData As Long
    lpfnHook As Long
    lpTemplateName As String
End Type
Private Const CC_SOLIDCOLOR = &H80
Private Declare Function ChooseColor _
    Lib "comdlg32.dll" Alias "ChooseColorA" _
    (pChoosecolor As COLORSTRUC) As Long

然后,插入以下代码给交替的行着色:

Sub shade()
    Dim x As Long, CSel As COLORSTRUC, CustColor(16) As Long
    CSel.lStructSize = Len(CSel)
    CSel.Flags = CC_SOLIDCOLOR
    CSel.lpCustColors = String$(16 * 4, 0)
    x = ChooseColor(CSel)
    For Each window In Windows
        For Each Worksheet In window.SelectedSheets
            addr = Worksheet.Name & "!" & Selection.Address
            For counter = 1 To Application.Selection.Rows.Count
                If counter Mod 2 = 1 Then
                    Range(addr).Rows(counter).Interior.Color = CSel.rgbResult
                End If
            Next counter
        Next worksheet
    Next window
End Sub

要给交替的列着色,只需对上述代码稍作修改:

Sub shade1()
    Dim x As Long, CSel As COLORSTRUC, CustColor(16) As Long
    CSel.lStructSize = Len(CSel)
    CSel.Flags = CC_SOLIDCOLOR
    CSel.lpCustColors = String$(16 * 4, 0)
    x = ChooseColor(CSel)
    For Each window In Windows
        For Each Worksheet In window.SelectedSheets
            addr = Worksheet.Name & "!" & Selection.Address
            For counter = 1 To Application.Selection.Columns.Count
                If counter Mod 2 = 1 Then
                    Range(addr).Columns(counter).Interior.Color = CSel.rgbResult
                End If
            Next counter
        Next worksheet
    Next window
End Sub
1.2 操作步骤
  1. 在电子表格中选择一个范围。
  2. 运行相应的代码( shade 给交替的行着色, shade1 给交替的列着色)。
  3. 在弹出的颜色选择对话框中选择你想要的颜色。
2. 给包含公式的单元格着色

在复杂的电子表格中,有时很难区分哪些单元格包含实际数字,哪些单元格包含公式。可以通过给包含公式的单元格着色来解决这个问题。

2.1 代码实现

同样,先插入API调用声明和变量类型声明(与给行或列着色的声明相同)。然后,插入以下代码:

Sub col_cell()
    Dim x As Long, CSel As COLORSTRUC, CustColor(16) As Long
    CSel.lStructSize = Len(CSel)
    CSel.Flags = CC_SOLIDCOLOR
    CSel.lpCustColors = String$(16 * 4, 0)
    x = ChooseColor(CSel)
    For Each window In Windows
        For Each Worksheet In window.SelectedSheets
            For Each cell In Application.Selection
                addr = Worksheet.Name & "!" & cell.Address
                If Range(addr).HasFormula Then
                    Range(addr).Interior.Color = CSel.rgbResult
                End If
            Next cell
        Next worksheet
    Next window
End Sub
2.2 操作步骤
  1. 选择包含公式和数字的单元格范围。
  2. 运行 col_cell 代码。
  3. 在弹出的颜色选择对话框中选择你想要的颜色。
3. 根据参考单元格对单元格求和

在对一行或一列单元格求和时,有时我们只想包含特定属性的单元格的值,比如字体属性或背景颜色。可以使用自定义的VBA函数来实现这个功能。

3.1 函数语法
SUMCELLSBYREF (range, reference, attribute)

参数说明如下:
| 参数 | 类型 | 描述 |
| ---- | ---- | ---- |
| range | Object | 要求和的单元格范围,定义方式与标准SUM函数相同。 |
| reference | Object | 参考单元格的地址,该单元格已设置为要求和的属性,如粗体或斜体。 |
| attribute | String | 属性类型,包含以下值之一:Color、Italic、Bold、Size、Underline、Subscript、Superscript。 |

3.2 代码实现
Public Function SUMCELLSBYREF(cells_to_sum As Object, r As Object, p As String)
    Application.Volatile
    total = 0
    For Each cell In cells_to_sum
        If p = "bold" And cell.Font.Bold = r.Font.Bold Then
            total = total + cell.Value
        End If
        If p = "color" And cell.Font.Color = r.Font.Color Then
            total = total + cell.Value
        End If
        If p = "italic" And cell.Font.Italic = r.Font.Italic Then
            total = total + cell.Value
        End If
        If p = "name" And cell.Font.Name = r.Font.Name Then
            total = total + cell.Value
        End If
        If p = "size" And cell.Font.Size = r.Font.Size Then
            total = total + cell.Value
        End If
        If p = "underline" And cell.Font.Underline = r.Font.Underline Then
            total = total + cell.Value
        End If
        If p = "subscript" And cell.Font.Subscript = r.Font.Subscript Then
            total = total + cell.Value
        End If
        If p = "superscript" And cell.Font.Superscript = r.Font.Superscript Then
            total = total + cell.Value
        End If
    Next cell
    SUMCELLSBYREF = total
End Function
3.3 操作步骤
  1. 在电子表格中输入数据。
  2. 在公式栏中输入公式,例如: =SUMCELLSBYREF(A1..A4,C1,"bold") 。
  3. 按下回车键,即可得到符合条件的单元格的总和。
4. 全局更改单元格的值

当电子表格中有大量数据时,可能需要将一系列数字按设定的值或百分比进行更改。可以通过创建一个用户表单来实现这个功能。

4.1 创建用户表单
  1. 从VBE菜单中选择“插入” -> “用户表单”。
  2. 在表单中添加一个标签控件用于显示输入说明,一个文本框用于捕获用户输入,以及两个命令按钮分别为“确定”和“取消”。
  3. 点击表单本身,在属性窗口中更改标题的“caption”属性。
  4. 从工具箱中将控件拖到表单上。
4.2 代码实现

首先,在模块的声明部分设置全局变量:

Global canc As Integer

然后,为“确定”和“取消”按钮添加点击事件代码:

Private Sub CommandButton1_Click()
    'This button is for OK
    UserForm2.Hide
    canc = 0
End Sub
Private Sub CommandButton2_Click()
    'This button is for Cancel
    UserForm2.Hide
    canc = 1
End Sub

最后,插入以下代码到模块中:

Sub change_val()
    UserForm2.Show
    If canc = 1 Then Exit Sub
    op = UserForm2.TextBox1.Text
    Dim addr As String
    For Each window In Windows
        For Each Worksheet In window.SelectedSheets
            For Each cell In Application.Selection
                addr = Worksheet.Name & "!" & cell.Address
                On Error Resume Next
                If Range(addr).Value <> "" And IsNumeric(Range(addr).Value) And Range(addr).HasFormula = False Then
                    Range(addr).Value = "=" & Val(Range(addr).Value) & op
                    Range(addr).Copy
                    Range(addr).PasteSpecial xlPasteValues
                End If
            Next cell
        Next worksheet
    Next window
End Sub
4.3 操作步骤
  1. 在电子表格中输入一系列数据(包括一些公式)。
  2. 选择要更改的单元格范围。
  3. 运行 change_val 代码。
  4. 在用户表单的文本框中输入更改因子,例如 *90% 。
  5. 点击“确定”按钮,所有符合条件的单元格将按指定的因子进行更改。

通过以上这些VBA代码和操作步骤,我们可以更高效地处理Excel电子表格中的各种任务,提高工作效率。希望这些技巧对你有所帮助!

Excel VBA实用代码技巧分享(续)

5. 代码逻辑与流程分析

为了更好地理解上述代码的执行过程,下面通过mermaid格式的流程图来展示部分关键代码的逻辑。

5.1 给交替行或列着色代码流程
graph TD;
    A[开始] --> B[选择范围];
    B --> C[运行代码];
    C --> D[显示颜色选择对话框];
    D --> E{选择颜色};
    E --> F[遍历窗口];
    F --> G[遍历工作表];
    G --> H[获取当前工作表和选择地址];
    H --> I{是否为交替行/列};
    I -- 是 --> J[设置行/列颜色];
    I -- 否 --> K[继续下一行/列];
    J --> K;
    K --> L{是否还有行/列};
    L -- 是 --> I;
    L -- 否 --> M{是否还有工作表};
    M -- 是 --> G;
    M -- 否 --> N{是否还有窗口};
    N -- 是 --> F;
    N -- 否 --> O[结束];
5.2 给包含公式的单元格着色代码流程
graph TD;
    A[开始] --> B[选择包含公式和数字的范围];
    B --> C[运行代码];
    C --> D[显示颜色选择对话框];
    D --> E{选择颜色};
    E --> F[遍历窗口];
    F --> G[遍历工作表];
    G --> H[遍历单元格];
    H --> I{单元格是否有公式};
    I -- 是 --> J[设置单元格颜色];
    I -- 否 --> K[继续下一个单元格];
    J --> K;
    K --> L{是否还有单元格};
    L -- 是 --> H;
    L -- 否 --> M{是否还有工作表};
    M -- 是 --> G;
    M -- 否 --> N{是否还有窗口};
    N -- 是 --> F;
    N -- 否 --> O[结束];
6. 常见问题与解决方案

在使用这些代码时,可能会遇到一些常见问题,下面为你提供相应的解决方案。

问题 解决方案
颜色选择对话框不显示 检查API调用声明是否正确插入到模块的声明部分,确保代码中没有语法错误。
公式中参数缺失导致错误 仔细检查公式中每个参数是否正确输入,确保 range 、 reference 和 attribute 都符合要求。
用户表单无法正常工作 检查用户表单的控件是否正确添加,以及按钮的点击事件代码是否正确设置。
单元格值未按预期更改 确认输入的更改因子是否正确,如 *90% 等,同时检查单元格是否为数值且不包含公式。
7. 总结与拓展

通过前面介绍的这些Excel VBA代码技巧,我们可以实现给表格的行或列交替着色、给包含公式的单元格着色、根据参考单元格对单元格求和以及全局更改单元格的值等功能。这些技巧不仅提高了数据处理的效率,还能让我们更灵活地操作Excel电子表格。

在实际应用中,我们还可以对这些代码进行拓展。例如,在给交替行或列着色的代码中,可以增加更多的颜色选择逻辑,让用户可以选择不同的颜色组合。在根据参考单元格对单元格求和的函数中,可以添加更多的属性类型,以满足更复杂的求和需求。

希望大家能够熟练掌握这些技巧,并根据自己的实际需求进行拓展和优化,让Excel VBA更好地为我们的工作服务。

8. 操作步骤总结

为了方便大家回顾和操作,下面将前面介绍的各项功能的操作步骤进行总结。

8.1 给交替行或列着色
  1. 在电子表格中选择一个范围。
  2. 运行相应的代码( shade 给交替的行着色, shade1 给交替的列着色)。
  3. 在弹出的颜色选择对话框中选择你想要的颜色。
8.2 给包含公式的单元格着色
  1. 选择包含公式和数字的单元格范围。
  2. 运行 col_cell 代码。
  3. 在弹出的颜色选择对话框中选择你想要的颜色。
8.3 根据参考单元格对单元格求和
  1. 在电子表格中输入数据。
  2. 在公式栏中输入公式,例如: =SUMCELLSBYREF(A1..A4,C1,"bold") 。
  3. 按下回车键,即可得到符合条件的单元格的总和。
8.4 全局更改单元格的值
  1. 在电子表格中输入一系列数据(包括一些公式)。
  2. 选择要更改的单元格范围。
  3. 运行 change_val 代码。
  4. 在用户表单的文本框中输入更改因子,例如 *90% 。
  5. 点击“确定”按钮,所有符合条件的单元格将按指定的因子进行更改。

通过按照这些操作步骤,你可以轻松地使用这些Excel VBA代码技巧来处理电子表格中的各种任务。

相关推荐

【Excel VBA 编程】分段表格数据汇总,Excel公式和透视表很难实现的功能,VBA一键搞定

到这里本期的分享也就要结束了,大家都学会了吗?一定要跟上进度,每天坚持输出和练习哦我的公众号也在不间断地分享的编程案例,如果想学习更多的编程知识,无论是用来提升自动化办公效率还是想着提升自我,都可以关注我的公众号,解锁更多的VBA技能最后,再提醒一下大家,如果你有需求但因为各种问题搁置,可以把你的问题反馈给我,一起帮你出谋划策哦!搜索公众号“努力鸭是黑色的”,关注我的公众号能够更加及时沟通反馈哦!

wei1019的专栏 995

Twincat Scopeview录波中的Ringbuffer设置技巧与常见问题解决

本文深入解析了倍福Twincat Scopeview录波功能中Ringbuffer(环形缓冲区)的核心设置技巧与常见问题解决方案。针对数据丢失、触发时机不准等痛点,详细阐述了缓冲区大小、预触发样本、采样率等关键参数的优化配置,并提供了多通道同步、自动诊断等实战应用指南,帮助工程师高效捕捉和分析PLC数据。

rainy的博客 849

TortoiseSVN的安装与使用

TortoiseSVN的安装与应用1、安装1.1、下载TortoiseSVN    点击下载TortoiseSvn1.2、下载汉化包。上面的页面中,找到Chinese, simplified汉化包,点击32位或者64位的Setup,下载汉化包。1.3、安装TortoiseSVN。在选择安装路径页面时要记得勾选“command line client tools”,默认不是勾选的。这里不勾选的话在I...

qq_28566071的博客 4万+

Plant Simulation新手入门:5步搞定生产线物流仿真(附模型库使用技巧)

本文为Plant Simulation新手提供了从零开始构建生产线物流仿真的五步入门指南。通过理念重塑、流程规划、对象连接、参数设置到数据分析的完整流程,帮助用户快速掌握这一强大仿真工具的核心逻辑。文章特别强调了模型库的高效使用技巧,助力工程师在虚拟环境中精准模拟和优化生产系统,提升规划效率。

efc123456的博客 336

YOLOv11改进策略【模型轻量化】| 使用 MoblieOne 模块,引入结构重参数化,提高模型检测效率

本文记录的是基于MobileOne的YOLOv11骨干网络改进方法研究。MobileOne的基础模块通过重参数化,。在将其替换YOLOv11的骨干网络后,实现模型轻量化。

Limiiiing的博客 1428

无人机导航中的坐标系统详解与转换实现

本文系统介绍了无人机导航中常用的坐标系及其转换方法。主要内容包括:1) 四种关键坐标系(大地坐标系ECEF、经纬度LLA、北东天坐标系NED、机体坐标系)的定义和特点;2) 坐标系间的转换原理与实现,重点讲解LLA与ECEF的互转公式及ECEF与NED的转换方法;3) 提供可直接使用的C++实现代码,包括基于WGS84椭球模型的精确转换算法。这些内容为无人机导航系统开发提供了实用的坐标系处理方案,能够有效解决多源数据在不同坐标系下的统一表示问题。

电脑管家 380

人形机器人开源资源大盘点(四):硬件开源平台

开源人形机器人平台推动技术民主化 本期聚焦四大开源人形机器人平台:斯坦福的ToddlerBot以6000美元低成本实现30自由度运动,支持仿真到现实的零样本迁移;法国Reachy2被HuggingFace收购,融合开源AI与硬件,适配医疗、工业等场景;国内OriginMan基于ROS2全开源,集成大模型能力,支持深度改造;OAN框架则为经典NAO机器人注入开源活力,扩展交互功能。这些平台通过不同路径降低研发门槛,推动人形机器人技术走向开放共享的未来。

Studying…… 370

【STM32 CubeMX】I2C中断方式与DMA方式

在STM32 CubeMX环境中,I2C(Inter-Integrated Circuit)通信协议的实现可以采用中断方式或DMA(Direct Memory Access)方式。中断方式通过触发中断来处理数据传输,而DMA方式则利用直接内存访问技术来实现数据传输,减轻了CPU的负担。在选择I2C通信方式时,开发者需要根据具体的应用需求和性能要求来选择合适的方式。STM32 CubeMX提供了图形化配置工具,使得配置I2C通信方式变得更加便捷。

m0_62599305的博客 7750

YOLO11涨点优化:损失函数优化 | 引入MPDIoU,利用边界框左上角和右下角距离,彻底解决重叠框匹配失效问题

【摘要】针对目标检测中重叠框匹配失效问题,本文提出基于MPDIoU损失函数的解决方案。MPDIoU通过同时约束边界框的左上角和右下角两点距离,有效解决了传统IoU损失在重叠场景下的优化歧义问题。文章首先分析YOLO系列在密集目标检测中的性能瓶颈,指出GIoU/CIoU等现有方法无法区分相同IoU值但偏移方向不同的预测框。随后详细解析MPDIoU的数学原理及其相对于主流损失函数的优势,证明两点距离约束能提供唯一优化方向。最后给出在YOLO11中实现MPDIoU的具体方案,包括核心代码修改点和架构适配建议。实验

2601_95807009的博客 173

C# WinForm 基于DirectShow.dll编写的程序,可边预览边保存视频,音频和图片

基于微软的DirectShow.dll的二次开发1.自动识别连接的摄像头跟麦克风且可以对其进行选择(默认选中第一个)2.可录制wmv和avi两种格式的视频并且都带有音频,avi可以录制的分辨率和帧数进行选择,wmv默认进行了淸晰度优化如不喜欢可去除代码中有相关提示3.可在预览点开之后进行拍照,也可边录制视频边拍照4.添加了视频和音频的压缩功能(酌情使用)这次二次开发发现C#对DirectShow的相关信息太少,这次做完也不敢藏私共享出来,还望大家指正

lwip-2.0.3

lwip是瑞典计算机科学院(SICS)的Adam Dunkels 开发的一个小型开源的TCP/IP协议栈。实现的重点是在保持TCP协议主要功能的基础上减少对RAM 的占用。

OV9734 DATASHEET,完整规格书

OV9734完整规格书,包括电气参数,尺寸规格,寄存器配置表等

上一篇: 24、Excel VBA实用代码技巧分享
下一篇: 26、Excel VBA:查看隐藏工作表与多表多工作簿搜索
电竞养老选手
博客等级 码龄4年 61粉丝 1196原创
评论
成就一亿技术人!
拼手气红包6.0元
还能输入1000个字符  | 博主筛选后可见
 
 条评论被折叠 查看
添加红包

请填写红包祝福语或标题

个

红包个数最小为10个

元

红包金额最低5元

当前余额3.43元 前往充值 >
需支付:10.00元
成就一亿技术人!
领取后你会自动成为博主和红包主的粉丝 规则
hope_wisdom
发出的红包
实付元
使用余额支付
点击重新获取
扫码支付
钱包余额 0

抵扣说明:

1.余额是钱包充值的虚拟货币,按照1:1的比例进行支付金额的抵扣。
2.余额无法直接购买下载,可以购买VIP、付费专栏及课程。

余额充值