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

Excel VBA实用代码技巧分享

在Excel的使用过程中,我们常常会遇到一些繁琐的操作,比如工作表排序、字符替换、定时事件处理、矩阵自动求和以及公式引用方式转换等。下面将为大家详细介绍一些实用的VBA代码,帮助大家提高工作效率。

1. 工作表按字母顺序排序

随着工作簿的不断开发,工作表的数量会逐渐增加,其顺序往往变得杂乱无章。虽然可以通过拖动工作表标签来调整顺序,但当工作表数量较多时,这一过程会变得十分复杂。以下代码可以快速有效地按工作表标签名称对工作表进行排序:

Sub sortsheet()
    For i = 1 To Sheets.Count
        For j = 1 To Sheets.Count - 1
            If UCase$(Sheets(j).Name) > UCase$(Sheets(j + 1).Name) Then
                Sheets(j).Move after:=Sheets(j + 1)
            End If
        Next j
    Next i
End Sub

操作步骤

  1. 右键单击工作表标签,选择“重命名”,使用随机名称并确保工作表名称顺序混乱。
  2. 将上述代码插入到模块中。
  3. 运行代码,工作表及其内容将按正确顺序排列。

代码解释

  • 代码通过 Count 属性和 For..Next 循环遍历 Sheets 集合。
  • 嵌套循环遍历除最后一个工作表之外的所有工作表,因为最后一个工作表没有下一个工作表,避免代码出错。
  • 使用 Ucase 函数将工作表名称转换为大写文本,以避免大小写差异导致排序结果错误。
  • 如果当前工作表名称大于下一个工作表名称,则使用 Move 方法将其移到下一个工作表之后。

2. 字符串中字符替换

在处理从其他应用程序导入或粘贴的数据时,有时会包含一些多余的字符,需要将其全部删除或替换为其他字符。以下代码创建了一个工作表函数,用于将字符串中指定字符的所有实例替换为另一个指定字符:

Function REP(target As String, r As String, s As String)
    temp = ""
    For n = 1 To Len(target)
        If Mid(target, n, 1) = r Then
            temp = temp & s
        Else
            temp = temp & Mid(target, n, 1)
        End If
        REP = temp
    Next n
End Function

参数说明

参数名 类型 描述
target String 要搜索的字符串或其单元格引用
r String 要搜索的字符
s String 用于替换搜索字符的字符

操作步骤

  1. 将上述代码插入到模块中。
  2. 在工作表单元格中输入公式 =REP(A1,"*","!") ,其中 A1 为包含要搜索字符串的单元格, * 为要搜索的字符, ! 为要替换的字符。
  3. 点击公式栏上的“粘贴函数”图标,该公式将显示在“用户定义公式”部分,可像使用其他公式一样使用。

代码解释

  • 定义一个变量 temp 并初始化为空字符串,用于构建新的字符串。
  • 使用 For..Next 循环逐个字符遍历 target 字符串。
  • 使用 Mid 函数提取每个字符并与搜索字符进行比较,若匹配则将替换字符添加到 temp 中,否则将原字符添加到 temp 中。
  • 最后将 temp 的值赋给函数 REP ,并返回结果。

小技巧

若要从字符串中删除特定字符,可将搜索字符串设置为正常字符,将替换字符串设置为空字符串,例如 =REP(A1,"*","") 。

3. 定时事件处理

在编写应用程序时,有时需要按固定时间间隔执行某个过程。例如,模拟Excel的自动保存功能,每五分钟调用一次保存例程或重新计算工作簿。在VBA中,可以使用 OnTime 方法实现定时事件:

Private Sub time_set
    Dim w As Workbook
    Application.OnTime Now + TimeValue("00:05:00"), "time_set"
    For Each w In Application.Workbooks
        MsgBox w.Name
    Next w
End Sub

操作步骤

  1. 将上述代码插入到模块中。
  2. 该过程只需调用一次,可在工作簿首次加载时从 Workbook_Open 事件调用。

代码解释

  • 定义一个变量 w 作为工作簿对象。
  • OnTime 语句指定在五分钟后(当前时间加上五分钟的时间值)再次调用 time_set 例程。
  • 使用 For..Each 循环遍历并保存每个已加载的工作簿。

其他参数说明

  • EarliestTime :最早执行时间,已在代码中使用。
  • Procedure :要调用的子程序,已在代码中使用。
  • LatestTime :最迟执行时间,可指定该过程应运行的最晚时间。
  • Schedule :可设置为 True 或 False ,用于开启或关闭 OnTime 函数。

例如,要指定定时事件停止的最晚时间:

Application.OnTime Now + TimeValue("00:05:00"),  "time_set", TimeValue("22:00:00")

要停止自动化过程,将 Schedule 参数设置为 False :

Application.OnTime Now + TimeValue("00:05:00"), "time_set", , False

4. 矩阵自动求和

在处理大量数据时,需要对矩阵的每列底部和每行右侧进行求和,并在右下角得出总计。以下代码可根据用户选择的范围自动生成所有求和公式:

Sub matrix_total()
    If Application.Selection.Count = 1 Then MsgBox "Select a range": Exit Sub
    Dim rr As Range
    Dim coord(4) As String
    temp = Application.Selection.Address & "$"
    Set rr = ActiveWindow.RangeSelection
    coord(0) = rr.Column
    coord(2) = rr.Column + rr.Columns.Count - 1
    coord(1) = rr.Row
    coord(3) = rr.Row + rr.Rows.Count - 1
    For Each window In Windows
        For Each Worksheet In window.SelectedSheets
            con = 1
            For n = coord(0) To coord(2)
                formulastr = convert_asc(VAL(coord(0)) + con - 1) & coord(1) & ".." & convert_asc(VAL(coord(0)) + con - 1) & coord(3)
                Worksheet.Cells(VAL(coord(3)) + 1, n).Formula = "=sum(" & formulastr & ")"
                con = con + 1
            Next n
            con = 1
            For n = coord(1) To coord(3) + 1
                formulastr = convert_asc(VAL(coord(0))) & (coord(1) + con - 1) & ".." & convert_asc(VAL(coord(2))) & (coord(1) + con - 1)
                Worksheet.Cells(n, VAL(coord(2)) + 1).Formula = "=sum(" & formulastr & ")"
                con = con + 1
            Next n
        Next Worksheet
    Next window
End Sub

Private Function convert_asc(target As Integer) As String
    high = Int(target / 26)
    low = target Mod 26
    temp = ""
    If high > 0 Then temp = Chr(high + 64)
    temp = temp & Chr(low + 64)
    convert_asc = temp
End Function

操作步骤

  1. 选择要进行求和的矩阵范围。
  2. 将上述代码插入到模块中。
  3. 运行代码,矩阵的每列底部和每行右侧将自动添加求和公式,右下角将得出总计。

代码解释

  • 首先检查所选范围的单元格数量,如果只有一个单元格,则退出程序,因为单个单元格求和无意义。
  • 设置一个数组 coord 来保存所选范围的四个角的坐标。
  • 使用 convert_asc 函数将列号转换为字母表示。
  • 循环遍历所选范围的列和行,为每列和每行生成求和公式。

流程图

graph TD
    A[开始] --> B{选择范围单元格数量是否为1}
    B -- 是 --> C[退出程序]
    B -- 否 --> D[设置坐标数组]
    D --> E[遍历窗口和工作表]
    E --> F[遍历列生成列求和公式]
    F --> G[遍历行生成行求和公式]
    G --> H[结束]

5. 绝对和相对公式转换

公式可以使用绝对或相对地址进行单元格引用。将单个单元格公式转换为绝对引用很容易,但如果需要更改整个范围的公式,则手动更改每个单元格中的公式会很繁琐,并且容易出错。以下代码可以根据用户选择自动将公式转换为使用绝对引用,并为用户提供关于绝对和相对公式在工作表中显示方式的选项:

Global canc As Integer

Private Sub CommandButton1_Click()
    UserForm1.Hide
    canc = 0
End Sub

Private Sub CommandButton2_Click()
    UserForm1.Hide
    canc = 1
End Sub

Sub conv_formula()
    UserForm1.Show
    If canc = 1 Then Exit Sub
    If UserForm1.OptionButton1.Value = True Then act = xlRelative
    If UserForm1.OptionButton2.Value = True Then act = xlRelRowAbsColumn
    If UserForm1.OptionButton4.Value = True Then act = xlAbsolute
    If UserForm1.OptionButton3.Value = True Then act = xlAbsRowRelColumn
    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 = True Then Range(addr).Formula = Application.ConvertFormula(Formula:=Range(addr).Formula, fromreferencestyle:=xlA1, toreferencestyle:=xlA1, toabsolute:=act)
            Next cell
        Next worksheet
    Next window
End Sub

操作步骤

  1. 插入一个 UserForm ,并在模块的声明部分定义全局变量 canc :
Global canc As Integer
  1. 在 UserForm 中添加两个命令按钮(OK和Cancel)和四个单选按钮。
  2. 为命令按钮添加以下代码:
Private Sub CommandButton1_Click()
    UserForm1.Hide
    canc = 0
End Sub

Private Sub CommandButton2_Click()
    UserForm1.Hide
    canc = 1
End Sub
  1. 将主代码插入到模块中。
  2. 运行代码,点击VBE窗口中的“运行”符号(光标位于 conv_formula 代码上), UserForm 将显示。
  3. 从单选按钮中选择一个选项并点击“OK”,检查所选公式, $ 符号将根据需要在所选范围内添加或删除。

选项说明

选项 格式 描述
相对行,相对列 A1 行和列均为相对引用
相对行,绝对列 A$1 行相对,列绝对
绝对行,相对列 $A1 行绝对,列相对
绝对行,绝对列 $A$1 行和列均为绝对引用

代码解释

  • 显示 UserForm ,等待用户点击“OK”或“Cancel”按钮。
  • 根据用户点击的按钮设置 canc 变量的值。
  • 根据用户选择的单选按钮设置 act 变量的值。
  • 遍历所有窗口、所选工作表和所选单元格,检查单元格是否有公式,并使用 ConvertFormula 方法将公式转换为所需的引用方式。

通过以上这些实用的VBA代码,我们可以在Excel中更高效地完成各种复杂的操作,大大提高工作效率。希望这些代码能对大家有所帮助!

流程图

graph TD
    A[开始] --> B[显示UserForm]
    B --> C{用户点击Cancel?}
    C -- 是 --> D[退出程序]
    C -- 否 --> E{选择选项}
    E --> F[设置act变量]
    F --> G[遍历窗口和工作表]
    G --> H[遍历单元格]
    H --> I{单元格有公式?}
    I -- 是 --> J[转换公式引用方式]
    I -- 否 --> H
    J --> K[结束]

总结

本文为大家介绍了在Excel中使用VBA代码解决常见问题的方法,涵盖了工作表排序、字符替换、定时事件处理、矩阵自动求和以及公式引用方式转换等多个方面。以下是对各部分内容的总结:

功能 代码作用 关键代码 操作步骤
工作表排序 按字母顺序对工作表进行排序
Sub sortsheet()
    For i = 1 To Sheets.Count
        For j = 1 To Sheets.Count - 1
            If UCase$(Sheets(j).Name) > UCase$(Sheets(j + 1).Name) Then
                Sheets(j).Move after:=Sheets(j + 1)
            End If
        Next j
    Next i
End Sub

| 1. 重命名工作表并打乱顺序;2. 插入代码到模块;3. 运行代码 |
| 字符替换 | 将字符串中指定字符替换为另一个字符 |

Function REP(target As String, r As String, s As String)
    temp = ""
    For n = 1 To Len(target)
        If Mid(target, n, 1) = r Then
            temp = temp & s
        Else
            temp = temp & Mid(target, n, 1)
        End If
        REP = temp
    Next n
End Function

| 1. 插入代码到模块;2. 在单元格输入公式;3. 使用粘贴函数 |
| 定时事件处理 | 按固定时间间隔执行过程 |

Private Sub time_set
    Dim w As Workbook
    Application.OnTime Now + TimeValue("00:05:00"), "time_set"
    For Each w In Application.Workbooks
        MsgBox w.Name
    Next w
End Sub

| 1. 插入代码到模块;2. 从Workbook_Open事件调用 |
| 矩阵自动求和 | 对矩阵的列和行进行求和 |

Sub matrix_total()
    If Application.Selection.Count = 1 Then MsgBox "Select a range": Exit Sub
    Dim rr As Range
    Dim coord(4) As String
    temp = Application.Selection.Address & "$"
    Set rr = ActiveWindow.RangeSelection
    coord(0) = rr.Column
    coord(2) = rr.Column + rr.Columns.Count - 1
    coord(1) = rr.Row
    coord(3) = rr.Row + rr.Rows.Count - 1
    For Each window In Windows
        For Each Worksheet In window.SelectedSheets
            con = 1
            For n = coord(0) To coord(2)
                formulastr = convert_asc(VAL(coord(0)) + con - 1) & coord(1) & ".." & convert_asc(VAL(coord(0)) + con - 1) & coord(3)
                Worksheet.Cells(VAL(coord(3)) + 1, n).Formula = "=sum(" & formulastr & ")"
                con = con + 1
            Next n
            con = 1
            For n = coord(1) To coord(3) + 1
                formulastr = convert_asc(VAL(coord(0))) & (coord(1) + con - 1) & ".." & convert_asc(VAL(coord(2))) & (coord(1) + con - 1)
                Worksheet.Cells(n, VAL(coord(2)) + 1).Formula = "=sum(" & formulastr & ")"
                con = con + 1
            Next n
        Next Worksheet
    Next window
End Sub

Private Function convert_asc(target As Integer) As String
    high = Int(target / 26)
    low = target Mod 26
    temp = ""
    If high > 0 Then temp = Chr(high + 64)
    temp = temp & Chr(low + 64)
    convert_asc = temp
End Function

| 1. 选择矩阵范围;2. 插入代码到模块;3. 运行代码 |
| 公式引用方式转换 | 根据用户选择转换公式引用方式 |

Global canc As Integer

Private Sub CommandButton1_Click()
    UserForm1.Hide
    canc = 0
End Sub

Private Sub CommandButton2_Click()
    UserForm1.Hide
    canc = 1
End Sub

Sub conv_formula()
    UserForm1.Show
    If canc = 1 Then Exit Sub
    If UserForm1.OptionButton1.Value = True Then act = xlRelative
    If UserForm1.OptionButton2.Value = True Then act = xlRelRowAbsColumn
    If UserForm1.OptionButton4.Value = True Then act = xlAbsolute
    If UserForm1.OptionButton3.Value = True Then act = xlAbsRowRelColumn
    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 = True Then Range(addr).Formula = Application.ConvertFormula(Formula:=Range(addr).Formula, fromreferencestyle:=xlA1, toreferencestyle:=xlA1, toabsolute:=act)
            Next cell
        Next worksheet
    Next window
End Sub

| 1. 插入UserForm并定义变量;2. 添加按钮代码;3. 插入主代码到模块;4. 运行代码选择选项 |

建议

  • 代码复用 :将这些实用的VBA代码保存到一个常用的模块中,方便在不同的Excel工作簿中重复使用。
  • 错误处理 :在实际应用中,可以添加适当的错误处理代码,以应对可能出现的异常情况,使程序更加健壮。
  • 用户体验 :对于涉及用户交互的代码,如公式引用方式转换中的 UserForm ,可以进一步优化界面设计,提高用户体验。

通过掌握这些VBA代码技巧,你可以在Excel中更加高效地处理各种数据和任务,节省大量的时间和精力。希望大家在实际使用中不断探索和实践,让Excel成为你工作中的得力助手!

相关推荐

超实用Excel VBA工具箱0-零基础学代码其实很简单

从零基础学习Excel VBA,开发适合自己的工具,开始学习,永远不晚!

NanKelgz的博客 910

实用excel-vba代码集

本代码集包含常用excel vba代码 多表合并、批量导入图片、多工作簿合并、批量删除表内图片

cad-vba实用源代码

里面有本人多年来进行cad二次开发过程中所用到的绝大多数源代码,对想提高vba编程水平的朋友来说不可多得,几乎涉及到cad开发过程中可能遇到的方方面面的问题,既适合初学者,也能让有一定开发基础的朋友受益非浅。

EXCEL_VBA常用代码实战大全

VBA代码大全,可以当做查询手册使用,非常方便实用,希望亲们能够喜欢

iPhone用户必看:不装App也能搞定GitHub 2FA验证的3种方法(附恢复码管理技巧)

本文为iPhone用户提供了无需安装第三方App即可完成GitHub双重身份验证(2FA)的三种原生方案:利用相机扫码与iCloud钥匙串自动填充、使用iCloud钥匙串内置的TOTP验证码生成器,以及借助Safari浏览器扩展。文章详细解析了每种方法的操作步骤、适用场景,并重点强调了恢复码的安全管理与多层备份策略,帮助开发者在提升账户安全的同时,享受苹果生态的无缝便捷体验。

weixin_29324401的博客 662

芯片详解——AD7606

AD7606详解

weixin_45661757的博客 5024

Shopee动态参数x-sap-Ri与x-sap-Sec逆向解析实战

现代电商前端反爬普遍采用动态参数校验机制,其核心是将设备指纹、DOM状态与时间上下文耦合生成不可复用的请求凭证。x-sap-Ri本质是渲染上下文唯一标识,依赖真实浏览器环境中的DOM树结构、Canvas像素指纹及毫秒级时间扰动;x-sap-Sec则是基于该标识与服务端同步密钥生成的HMAC-SHA256签名,具备强时效性与密钥轮换特性。这类机制显著提升了自动化采集门槛,广泛应用于Shopee商品详情页等高价值数据接口。本文聚焦于从Chrome DevTools调试出发,系统还原参数生成逻辑,覆盖DOM一致性

weixin_30603633的博客 901

MATLAB/Simulink实战:三电平逆变器SVPWM调制从入门到精通(附完整仿真模型)

本文提供了一份详尽的MATLAB/Simulink实战指南,手把手教你从零搭建三电平逆变器的SVPWM调制系统。文章深入剖析了27个空间矢量的分区与分解算法,并完整实现了包含中点电位平衡控制的Simulink仿真模型,旨在帮助电力电子与电机控制领域的学习者和工程师跨越从理论到实践的鸿沟。

sugar的博客 1216

机器学习-02 模型的评估与选择

2.1 误差与过拟合 我们将学习器对样本的实际预测结果与样本的真实值之间的差异成为:误差(error)。定义: 在训练集上的误差称为训练误差(training error)或经验误差(empirical error)。 在测试集上的误差称为测试误差(test error)。 学习器在所有新样本上的误差称为泛化误差(generalization error)。 显然,我们希望得到的是在新样本上表现得很好的学习器,即泛化误差小的学习器。因此,我们应该让学习器尽可能地从训练集中学出普适性的“一般特征”,这

一个小白 1707

【WRF运行报错】总结WRF运行时报错及解决方案(持续更新)

总结WRF运行时报错及解决方案(持续更新)

WW、forever的博客 1634

地平线旭日X5开发板--搭建ROS1环境开发

由于RDK X5是基于Ubuntu22.04 进行版本释放,而ROS1只兼容到了20.04,为了更很好的做以前ROS1相关代码方案的快速迁移,提供了使用docker 的ros环境,进而使用ros1的方案。

bingdund的博客 1022

R语言基于机器学习三分类结果、计算三分类混淆矩阵、计算每一个类别对应的特异度(specificity)、灵敏度(sensitivity)、PPV、NPV

R语言基于机器学习三分类结果、计算三分类混淆矩阵、计算每一个类别对应的特异度(specificity)、灵敏度(sensitivity)、PPV、NPV

data+scenario+science+insight 657

TMS_WebGMaps_1.9.8.0_Full_Source

TMS_WebGMaps_1.9.8.0_Full_Source 全源码 delphi cbuilder

win10 跳过U8环境检测的IE WebControls.rar

win10 安装u8的时候 提示IE WebControls 不通过,这个工具可以欺骗u8的检查。 跳过U8环境检测的IE WebControls。提示导入失败后,重新检测通过。

数学建模模型总结

这是本人在两年内准备参加数学建模竞赛搜集到的模型资料,已经经过了本人的筛选和整理,希望能够对准备参加数学建模竞赛的学弟学妹们提供一些帮助!

计算机控制技术及应用

《计算机控制技术及应用》通过总结作者的科研成果和吸收国内外的先进理论、方法和技术,以计算机控制系统的网络化、开放化、智能化和集成化发展趋势为主线索,主要介绍计算机控制系统的基本概念和计算机控制系统的典型形式,计算机控制系统的数学描述与基本分析方法,以及数字PID控制器、最少拍控制系统、达林算法、状态变量设计法等设计方法及其各种改进算法。重点突出计算机控制系统输入输出通道接口的设计方法和典型控制系统的设计案例与实现方法等。

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

请填写红包祝福语或标题

个

红包个数最小为10个

元

红包金额最低5元

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

抵扣说明:

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

余额充值