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
操作步骤
- 右键单击工作表标签,选择“重命名”,使用随机名称并确保工作表名称顺序混乱。
- 将上述代码插入到模块中。
- 运行代码,工作表及其内容将按正确顺序排列。
代码解释
-
代码通过
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 | 用于替换搜索字符的字符 |
操作步骤
- 将上述代码插入到模块中。
-
在工作表单元格中输入公式
=REP(A1,"*","!"),其中A1为包含要搜索字符串的单元格,*为要搜索的字符,!为要替换的字符。 - 点击公式栏上的“粘贴函数”图标,该公式将显示在“用户定义公式”部分,可像使用其他公式一样使用。
代码解释
-
定义一个变量
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
操作步骤
- 将上述代码插入到模块中。
-
该过程只需调用一次,可在工作簿首次加载时从
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
操作步骤
- 选择要进行求和的矩阵范围。
- 将上述代码插入到模块中。
- 运行代码,矩阵的每列底部和每行右侧将自动添加求和公式,右下角将得出总计。
代码解释
- 首先检查所选范围的单元格数量,如果只有一个单元格,则退出程序,因为单个单元格求和无意义。
-
设置一个数组
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
操作步骤
-
插入一个
UserForm,并在模块的声明部分定义全局变量canc:
Global canc As Integer
-
在
UserForm中添加两个命令按钮(OK和Cancel)和四个单选按钮。 - 为命令按钮添加以下代码:
Private Sub CommandButton1_Click()
UserForm1.Hide
canc = 0
End Sub
Private Sub CommandButton2_Click()
UserForm1.Hide
canc = 1
End Sub
- 将主代码插入到模块中。
-
运行代码,点击VBE窗口中的“运行”符号(光标位于
conv_formula代码上),UserForm将显示。 -
从单选按钮中选择一个选项并点击“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成为你工作中的得力助手!
910



被折叠的 条评论
为什么被折叠?



