超级会员免费看
自定义 Excel 工作表函数与加载项创建指南
在 Excel 的使用中，自定义工作表函数和创建加载项能够极大地扩展其功能，满足特定的工作需求。下面将详细介绍相关内容。
自定义工作表函数
-
数组相关函数
-
LBound 和 UBound 函数
：用于确定数组的起始和结束元素。数组的起始元素通常为 0，除非另行声明或在模块开头使用
Option Base 1语句。 -
ParamArray
：只能应用于过程中的最后一个参数，它始终是
Variant数据类型，并且是可选参数（无需使用Optional关键字）。
-
LBound 和 UBound 函数
：用于确定数组的起始和结束元素。数组的起始元素通常为 0，除非另行声明或在模块开头使用
-
返回数组的函数
-
返回月份名称数组
：以下是
MonthNames函数的代码，它返回一个包含 12 个月份名称的数组。
-
返回月份名称数组
：以下是
Function MonthNames()
MonthNames = Array("January", "February", "March", _
"April", "May", "June", "July", "August", _
"September", "October", "November", "December")
End Function
使用方法：在工作表中，选择 12 个单元格的范围（如 A1:L1），输入 `=MonthNames()`，然后使用 `Ctrl + Shift + Enter` 输入数组公式。如果要将月份名称显示在列中，可选择一列中的 12 个单元格，使用 `=TRANSPOSE(MonthNames())` 并按 `Ctrl + Shift + Enter`。
- **返回排序后的列表**：`Sorted` 函数接受单列单元格范围作为参数，并返回排序后的数组。
Function Sorted(Rng As Range)
Dim SortedData() As Variant
Dim Cell As Range
Dim Temp As Variant, i As Long, j As Long
Dim NonEmpty As Long
' Transfer data to SortedData
For Each Cell In Rng
If Not IsEmpty(Cell) Then
NonEmpty = NonEmpty + 1
ReDim Preserve SortedData(1 To NonEmpty)
SortedData(NonEmpty) = Cell.Value
End If
Next Cell
' Sort the array
For i = 1 To NonEmpty
For j = i + 1 To NonEmpty
If SortedData(i) > SortedData(j) Then
Temp = SortedData(j)
SortedData(j) = SortedData(i)
SortedData(i) = Temp
End If
Next j
Next i
' Transpose the array and return it
Sorted = Application.Transpose(SortedData)
End Function
使用方法：假设 A2:A13 包含一些名称，在 C2:C13 中输入 `=Sorted(A2:A13)`，并按 `Ctrl + Shift + Enter`。如果未排序的数据在一行中，可使用 `=TRANSPOSE(Sorted(A16:L16))` 水平显示排序后的数据。
-
使用插入函数对话框
-
自定义函数显示
：自定义工作表函数会出现在“插入函数”对话框的“用户定义”类别中。使用
Private关键字定义的函数过程不会出现在该对话框中。 -
显示函数描述
：要在“插入函数”对话框中显示自定义函数的有意义描述，可按以下步骤操作：
- 激活包含自定义函数的工作簿中的工作表。
-
选择“开发工具”➪“代码”➪“宏”（或按
Alt + F8），弹出“宏”对话框。 - 在“宏名”字段中输入函数的名称。
- 点击“选项”按钮，弹出“宏选项”对话框。
- 在“说明”字段中输入函数的描述。
- 点击“确定”。
- 点击“取消”。
-
添加函数到不同类别
：可使用 VBA 将函数添加到不同类别。例如，将
TopAvg函数添加到“数学与三角函数”类别（类别 #3）的代码如下：
-
自定义函数显示
：自定义工作表函数会出现在“插入函数”对话框的“用户定义”类别中。使用
Application.MacroOptions Macro:="TopAvg", Category:=3
- **参数描述**：在 Excel 2010 中，可使用 `MacroOptions` 方法为自定义函数指定参数描述。以下是为 `TopAvg` 函数添加参数描述的示例：
Sub AddArgumentDescriptions()
Application.MacroOptions Macro:="TopAvg", _
ArgumentDescriptions:= _
Array("Range that contains the values", _
"Number of values to average")
End Sub
自定义函数操作步骤流程图
graph LR
A[开始] --> B[创建自定义函数]
B --> C{函数是否返回数组}
C -- 是 --> D[编写返回数组的函数代码]
C -- 否 --> E[编写普通函数代码]
D --> F[在工作表中使用数组公式输入函数]
E --> G[在工作表中输入函数]
F --> H[检查结果]
G --> H
H --> I{是否需要在插入函数对话框显示描述}
I -- 是 --> J[按步骤添加描述]
I -- 否 --> K[结束]
J --> K
自定义函数相关操作总结
| 操作 | 说明 |
|---|---|
| 返回数组函数 |
如
MonthNames
和
Sorted
函数，需使用数组公式输入
|
| 插入函数对话框显示描述 | 按特定步骤操作可添加函数描述 |
| 函数分类 | 使用 VBA 可将函数添加到不同类别 |
| 参数描述 |
Excel 2010 可使用
MacroOptions
方法添加
|
自定义 Excel 工作表函数与加载项创建指南
Excel 加载项概述
Excel 加载项是增强 Excel 功能的工具，有些加载项提供新的工作表函数，有些则提供新命令或实用工具。Excel 自带了一些加载项，如“分析工具库”和“规划求解”，也可以从第三方供应商或共享软件中获取。创建加载项需要具备 VBA 编程技能，它本质上是一种特殊形式的 XLSM 工作簿文件，具有以下特点：
-
Workbook
对象的
IsAddin
属性为
True
。
- 工作簿窗口隐藏，无法通过“视图”➪“窗口”➪“取消隐藏”命令取消隐藏。
- 工作簿不属于
Workbooks
集合，而是属于
AddIns
集合。
加载项通常使用 XLAM 文件扩展名，早期版本的 Excel 创建的加载项使用 XLA 扩展名。
创建加载项的原因
创建加载项有以下好处：
-
保护代码
：将应用程序作为加载项分发并保护其 VBA 项目，可使普通用户难以查看工作簿中的工作表，增加代码被复制的难度，但 Excel 的保护功能并非完美。
-
避免混淆
：加载项对用户不可见，不太可能让新手用户感到困惑或造成干扰，且其内容无法被显示。
-
简化函数访问
：存储在加载项中的自定义工作表函数无需使用工作簿名称限定符。例如，若将自定义函数
MOVAVG
存储在名为
NEWFUNC.XLSM
的工作簿中，在其他工作簿中使用该函数需输入
=NEWFUNC.XLSM!MOVAVG(A1:A50)
；而若该函数存储在加载项中，可直接使用
=MOVAVG(A1:A50)
。
-
方便用户使用
：加载项会显示在“加载项”对话框中，用户可轻松启用或禁用。
-
更好的加载控制
：加载项可在 Excel 启动时自动打开，不受存储目录的限制。
-
避免卸载提示
：关闭加载项时，不会出现询问是否保存文件更改的对话框。
加载和卸载加载项
可以通过以下方法打开“加载项”对话框：
- 选择“文件”➪“选项”➪“加载项”，在对话框底部的下拉列表中选择“Excel 加载项”，然后点击“转到”。
- 在 Excel 2010 中，选择“开发工具”➪“加载项”➪“加载项”。
- 按
Alt + TI
（Excel 2003 的旧键盘快捷键）。
“加载项”对话框列出了 Excel 已知的所有加载项，勾选表示当前已打开的加载项，可通过勾选或取消勾选来打开或关闭加载项。也可以通过“文件”➪“打开”命令打开大多数加载项文件，但以这种方式打开的加载项不会出现在“加载项”对话框中，且不能通过“文件”➪“关闭”关闭，只能通过退出并重新启动 Excel 或编写宏来关闭。
创建加载项的步骤
创建加载项可按以下步骤进行：
1.
开发应用程序
：确保应用程序的所有功能正常运行，若加载项仅包含函数，则无需包含执行方法；若包含宏，可定义快捷键或自定义用户界面。
2.
测试应用程序
：在不同工作簿处于活动状态时执行应用程序，模拟加载项的使用情况。
3.
保护 VBA 项目
：激活 VBE，在“项目”窗口中选择工作簿，选择“工具”➪“VBA 项目属性”，点击“保护”选项卡，选中“锁定项目以供查看”复选框并输入密码（两次），然后点击“确定”。
4.
输入文档属性
：在 Excel 2010 中，选择“开发工具”➪“文档面板”；在 Excel 2007 中，选择“Office”➪“准备”➪“属性”。在“文档属性”窗格的“标题”字段中输入简短的描述性标题，在“注释”字段中输入较长的描述。
5.
保存为加载项
：选择“文件”➪“另存为”，在“另存为”对话框的“保存类型”下拉列表中选择“Excel 加载项 (*.xlam)”，指定存储加载项的文件夹，然后点击“保存”。
加载项创建示例
以“更改大小写”文本转换实用工具为例，介绍创建加载项的过程。
设置工作簿
工作簿包含一个空白工作表、一个 VBA 模块和一个用户窗体。用户窗体在原有的大写、小写和正确大小写选项基础上，增加了“句首字母大写”和“切换大小写”两个选项。
用户窗体包含一个框架控件，其中有五个选项按钮，还有一个“取消”按钮（名为
CancelButton
）和一个“确定”按钮（名为
OKButton
）。
“取消”按钮的点击事件代码如下：
Private Sub CancelButton_Click()
Unload UserForm1
End Sub
“确定”按钮的点击事件代码如下：
Private Sub OKButton_Click()
Dim TextCells As Range
Dim cell As Range
Dim Text As String
Dim i As Long
' Create an object with just text constants
On Error Resume Next
Set TextCells = Selection.SpecialCells(xlConstants, _
xlTextValues)
' Turn off screen updating
Application.ScreenUpdating = False
' Loop through the cells
For Each cell In TextCells
Text = cell.Value
Select Case True
Case OptionLower 'lowercase
cell.Value = LCase(cell.Value)
Case OptionUpper 'UPPERCASE
cell.Value = UCase(cell.Value)
Case OptionProper 'Proper Case
cell.Value = _
WorksheetFunction.Proper(cell.Value)
Case OptionSentence 'Sentence case
Text = UCase(Left(cell.Value, 1))
Text = Text & LCase(Mid(cell.Value, 2,
Len(cell.Value)))
cell.Value = Text
Case OptionToggle 'tOGGLE CASE
For i = 1 To Len(Text)
If Mid(Text, i, 1) Like "[A-Z]" Then
Mid(Text, i, 1) = LCase(Mid(Text, i, 1))
Else
Mid(Text, i, 1) = UCase(Mid(Text, i, 1))
End If
Next i
cell.Value = Text
End Select
Next
' Unload the dialog box
Unload UserForm1
End Sub
测试工作簿
测试工作簿时，应在不同工作簿处于活动状态下进行，以模拟加载项的使用情况：
1. 打开一个新工作簿或现有工作簿，输入各种类型的信息，包括文本、值和公式。
2. 选择一个或多个单元格（或整行和整列）。
3. 通过右键单击单元格（或行或列）的快捷菜单选择“更改大小写”命令来执行宏。若该命令未显示，可能是打开工作簿时未启用宏，需关闭并重新打开工作簿，确保启用宏。
添加描述信息
建议为加载项输入描述信息，步骤如下：
1. 激活
change case.xlsm
工作簿。
2. 在 Excel 2010 中，选择“开发工具”➪“文档面板”；在 Excel 2007 中，选择“Office”➪“准备”➪“属性”。
3. 在“标题”字段中输入加载项的标题，如“更改大小写”。
4. 在“注释”字段中输入描述，如“更改所选单元格中文本的大小写。可通过右键快捷菜单访问此实用工具”。
保护 VBA 代码
若要添加密码以防止他人查看 VBA 代码，可按以下步骤操作：
1. 激活 VBE，在“项目”窗口中选择
change case.xlsm
工作簿。
2. 选择“工具”➪“VBA 项目属性”，点击“保护”选项卡。
3. 选中“锁定项目以供查看”复选框并输入密码（两次）。
4. 点击“确定”。
5. 通过 VBE 的“文件”➪“保存”菜单保存工作簿，或返回 Excel 窗口选择“文件”➪“保存”。
创建加载项
完成上述步骤后，可按以下步骤创建加载项：
1. 若需要，重新激活 Excel。
2. 激活
change case.xlsm
工作簿，选择“文件”➪“另存为”。
3. 在“保存类型”下拉菜单中选择“加载项 (*.xlam)”。
4. 指定位置并点击“保存”。
打开加载项
在打开加载项之前，关闭 XLSM 工作簿以避免混淆，打开加载项的步骤如下：
1. 按
Alt + TI
打开“加载项”对话框。
2. 点击“浏览”按钮。
3. 找到并选择刚刚创建的加载项。
4. 点击“确定”关闭“浏览”对话框。
5. 确保“加载项”对话框中为新加载项勾选了复选框。
6. 点击“确定”关闭对话框。
分发和修改加载项
若要分发加载项，可将 XLAM 文件副本提供给其他 Excel 用户。若需要修改受密码保护的加载项，可按以下步骤操作：
1. 打开 XLAM 文件。
2. 激活 VBE。
3. 在“项目”窗口中双击项目名称，输入密码并点击“确定”。
4. 修改代码。
5. 通过 VBE 的“文件”➪“保存”保存文件。
若加载项将信息存储在工作表中，需将工作簿的
IsAddIn
属性设置为
False
才能查看工作簿，修改完成后，保存文件前将该属性设置回
True
。
加载项操作步骤流程图
graph LR
A[开始] --> B[开发应用程序]
B --> C[测试应用程序]
C --> D{是否保护 VBA 项目}
D -- 是 --> E[设置保护密码]
D -- 否 --> F[跳过保护步骤]
E --> G[输入文档属性]
F --> G
G --> H[保存为加载项]
H --> I[关闭 XLSM 工作簿]
I --> J[打开加载项]
J --> K{是否需要修改加载项}
K -- 是 --> L[解锁并修改代码]
K -- 否 --> M[结束]
L --> M
加载项相关操作总结
| 操作 | 说明 |
|---|---|
| 创建加载项 | 按开发、测试、保护、输入属性、保存的步骤进行 |
| 打开加载项 | 通过“加载项”对话框浏览并选择加载项 |
| 分发加载项 | 提供 XLAM 文件副本 |
| 修改加载项 |
解锁 VBA 项目后修改代码，存储信息的加载项需调整
IsAddIn
属性
|
通过以上介绍，你可以了解如何创建自定义工作表函数和 Excel 加载项，以及它们的使用方法和优势。在实际应用中，可根据具体需求灵活运用这些技术，提高工作效率。
扫一扫
1061
被折叠的 条评论
为什么被折叠?
到【灌水乐园】发言
