超级会员免费看
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
方法将其移至下一个工作表之后。
操作步骤如下：
1. 右键单击底部的工作表标签，选择“重命名”，使用随机名称并确保工作表名称顺序混乱。
2. 将上述代码插入到模块中。
3. 运行代码，工作表及其内容将按正确顺序排列。
2. 字符串中字符替换
该代码创建了一个工作表函数，用于将字符串中所有指定字符替换为另一个指定字符。例如，若电子表格中的单元格包含文本“
Replacement String
”，可使用此函数将
*
字符替换为
!
字符，使字符串变为“!!!Replacement String!!!”。这对于从其他应用程序导入或粘贴的数据非常有用，因为导入的数据有时会包含需要删除或替换的多余字符。
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,"*","!")
。
3. 若点击公式工具栏上的“公式粘贴”图标，该公式将位于“用户定义公式”部分，可像使用其他公式一样使用它。若未输入正确参数，将出现标准的Excel错误。
若要从字符串中删除特定字符，可将搜索字符串设置为正常字符，将替换字符串设置为空字符串，如
=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
循环遍历并保存每个已加载的工作簿。
OnTime
方法还有其他参数：
-
EarliestTime
：最早执行时间。
-
Procedure
：要调用的子例程。
-
LatestTime
：最晚执行时间，当前时间超过该时间后，
OnTime
过程将不再被调用。
-
Schedule
：可设置为
True
或
False
，用于开启或关闭
OnTime
函数。
例如，若要在晚上10点前每五分钟调用一次
time_set
例程，可使用以下代码：
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
代码首先检查用户选择的范围是否只有一个单元格，若是则提示用户选择一个范围并退出过程。若选择范围包含多个单元格，代码会计算所选范围的左上角和右下角坐标，并通过
convert_asc
函数将列号转换为字母。
操作步骤如下：
1. 将上述代码插入到模块中。
2. 选择需要求和的矩阵范围。
3. 运行
matrix_total
代码，即可在所选范围的底部和右侧生成总和，右下角生成总计。
5. 绝对和相对公式转换
公式可使用绝对或相对地址引用单元格。例如，公式中的地址
A1
是相对地址，复制公式时，单元格地址会相对移动；而绝对地址
$A$1
无论复制到何处，都始终引用单元格
A1
。若要将整个范围的公式转换为绝对引用，手动更改每个单元格的公式容易出错。以下代码可根据用户选择自动将公式转换为使用绝对引用，并为用户提供绝对和相对公式的不同显示选项。
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
用于检查用户是否点击了表单上的“取消”按钮。表单上有一组单选按钮和两个命令按钮（“确定”和“取消”），点击“确定”或“取消”按钮时，会隐藏表单并设置
canc
变量的值。
操作步骤如下：
1. 将上述代码插入到模块中。
2. 运行
conv_formula
代码，会显示
UserForm
。
3. 用户选择一个单选按钮选项并点击“确定”，表单将隐藏，代码会根据用户选择将所选范围内的公式转换为相应的绝对或相对引用。若点击“取消”，则不执行任何操作。
绝对和相对公式的四种选项如下：
| 状态 | 格式 |
| ---- | ---- |
| 相对行，相对列 | A1 |
| 相对行，绝对列 | A$1 |
| 绝对行，相对列 | $A1 |
| 绝对行，绝对列 | $A$1 |
通过以上这些Excel VBA技巧，可提高工作效率，减少手动操作带来的错误。无论是工作表排序、字符替换、定时事件、矩阵求和还是公式转换，都能在电子表格处理中发挥重要作用。
Excel VBA实用技巧：工作表排序、字符替换与定时事件等
6. 操作流程总结
为了更清晰地展示上述各项功能的操作流程，下面以mermaid格式流程图呈现：
graph LR
classDef startend fill:#F5EBFF,stroke:#BE8FED,stroke-width:2px
classDef process fill:#E5F6FF,stroke:#73A6FF,stroke-width:2px
classDef decision fill:#FFF6CC,stroke:#FFBC52,stroke-width:2px
A([开始]):::startend --> B{选择操作}:::decision
B -->|工作表排序| C(重命名工作表并打乱顺序):::process
C --> D(插入排序代码到模块):::process
D --> E(运行排序代码):::process
B -->|字符替换| F(插入字符替换代码到模块):::process
F --> G(在单元格输入替换公式):::process
B -->|定时事件| H(插入定时事件代码到模块):::process
H --> I(在工作簿打开事件调用代码):::process
B -->|矩阵求和| J(插入矩阵求和代码到模块):::process
J --> K(选择矩阵范围):::process
K --> L(运行矩阵求和代码):::process
B -->|公式转换| M(插入公式转换代码到模块):::process
M --> N(运行conv_formula代码):::process
N --> O{选择选项}:::decision
O -->|确定| P(转换公式):::process
O -->|取消| Q(结束操作):::process
E --> R([结束]):::startend
G --> R
I --> R
L --> R
P --> R
Q --> R
此流程图涵盖了从开始选择操作到最终结束的整个过程，清晰地展示了各项功能的操作步骤和决策点。
7. 代码注意事项
在使用上述代码时，有一些注意事项需要牢记：
-
工作表排序代码
：
- 该代码仅对当前具有焦点的工作簿生效，确保在需要排序的工作簿处于活动状态时运行。
- 代码通过比较工作表名称的大写形式来排序，避免了大小写差异对排序结果的影响。
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
-
字符替换代码
：
- 该函数的匹配是区分大小写的，确保输入的搜索字符和目标字符串的大小写一致。
- 调用该函数时，需按照正确的参数顺序输入，否则会出现标准的Excel错误。
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
-
定时事件代码
：
- 定时事件代码只需调用一次，可在工作簿打开时触发，避免重复调用导致混乱。
-
合理设置
LatestTime和Schedule参数，以控制定时事件的执行时间和开关。
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
-
矩阵求和代码
：
- 确保选择的范围包含多个单元格，若只选择一个单元格，代码会提示并退出。
-
代码依赖
convert_asc函数将列号转换为字母，确保该函数正常工作。
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
-
公式转换代码
：
-
插入
UserForm并正确设置控件属性，确保用户能够正常选择选项。 -
定义全局变量
canc，用于判断用户是否点击取消按钮，避免误操作。
-
插入
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
8. 实际应用场景
这些Excel VBA技巧在实际工作中有广泛的应用场景：
-
财务报表处理
：在处理大量财务数据时，可使用工作表排序功能将不同部门或项目的报表按字母顺序排列，方便查看和分析。字符替换功能可用于清理导入数据中的特殊字符，使数据更加规范。
-
项目进度跟踪
：通过定时事件功能，可设置每小时或每天自动保存项目进度表，避免数据丢失。矩阵求和功能可用于计算项目各项任务的总和，如工时、成本等。
-
数据分析
：在进行数据分析时，公式转换功能可根据需要将相对公式转换为绝对公式，确保数据计算的准确性。字符替换功能可用于统一数据格式，便于后续分析。
9. 总结
通过掌握上述Excel VBA技巧，我们可以在电子表格处理中实现自动化操作，提高工作效率，减少手动操作带来的错误。无论是工作表排序、字符替换、定时事件、矩阵求和还是公式转换，都能根据实际需求灵活运用。在实际应用中，要注意代码的使用细节和操作步骤，确保各项功能正常运行。同时，不断探索和尝试这些技巧，将能更好地应对各种复杂的数据处理任务。
扫一扫
725
被折叠的 条评论
为什么被折叠?
到【灌水乐园】发言
