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 操作步骤
- 在电子表格中选择一个范围。
-
运行相应的代码(
shade给交替的行着色,shade1给交替的列着色)。 - 在弹出的颜色选择对话框中选择你想要的颜色。
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 操作步骤
- 选择包含公式和数字的单元格范围。
-
运行
col_cell代码。 - 在弹出的颜色选择对话框中选择你想要的颜色。
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 操作步骤
- 在电子表格中输入数据。
-
在公式栏中输入公式,例如:
=SUMCELLSBYREF(A1..A4,C1,"bold")。 - 按下回车键,即可得到符合条件的单元格的总和。
4. 全局更改单元格的值
当电子表格中有大量数据时,可能需要将一系列数字按设定的值或百分比进行更改。可以通过创建一个用户表单来实现这个功能。
4.1 创建用户表单
- 从VBE菜单中选择“插入” -> “用户表单”。
- 在表单中添加一个标签控件用于显示输入说明,一个文本框用于捕获用户输入,以及两个命令按钮分别为“确定”和“取消”。
- 点击表单本身,在属性窗口中更改标题的“caption”属性。
- 从工具箱中将控件拖到表单上。
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 操作步骤
- 在电子表格中输入一系列数据(包括一些公式)。
- 选择要更改的单元格范围。
-
运行
change_val代码。 -
在用户表单的文本框中输入更改因子,例如
*90%。 - 点击“确定”按钮,所有符合条件的单元格将按指定的因子进行更改。
通过以上这些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 给交替行或列着色
- 在电子表格中选择一个范围。
-
运行相应的代码(
shade给交替的行着色,shade1给交替的列着色)。 - 在弹出的颜色选择对话框中选择你想要的颜色。
8.2 给包含公式的单元格着色
- 选择包含公式和数字的单元格范围。
-
运行
col_cell代码。 - 在弹出的颜色选择对话框中选择你想要的颜色。
8.3 根据参考单元格对单元格求和
- 在电子表格中输入数据。
-
在公式栏中输入公式,例如:
=SUMCELLSBYREF(A1..A4,C1,"bold")。 - 按下回车键,即可得到符合条件的单元格的总和。
8.4 全局更改单元格的值
- 在电子表格中输入一系列数据(包括一些公式)。
- 选择要更改的单元格范围。
-
运行
change_val代码。 -
在用户表单的文本框中输入更改因子,例如
*90%。 - 点击“确定”按钮,所有符合条件的单元格将按指定的因子进行更改。
通过按照这些操作步骤,你可以轻松地使用这些Excel VBA代码技巧来处理电子表格中的各种任务。
995



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



