使用VBA编写自定义函数(修改版)
合集下载
相关主题
- 1、下载文档前请自行甄别文档内容的完整性，平台不提供额外的编辑、内容补充、找答案等附加服务。
- 2、"仅部分预览"的文档,不可在线预览部分如存在完整性等问题,可反馈申请退款(可完整预览的文档不适用该条件!)。
- 3、如文档侵犯您的权益，请联系客服反馈,我们会尽快为您处理(人工客服工作时间：9:00-18:30)。
[、]、|、\、;、:、'、"、<、>、,、.、?、/等字符，也就是说，除了字母、数字、 下划线_ 可以使用外，其他键盘上的符号都不能使用，而且，一般情况下，名称最好 不要使用全角字符或汉字。 ③ 称的长度不得超过 255 个字符。 ④ 称不能与 Visual Basic 本身的函数、属性、方法的名称相同。例如，命名为 Left 的 变量与函数 Left() 的名称相同，这是不允许的。在 VBA 中也不要使用如 A1、B2 等 与单元格相同的命名。 ⑤ 能在范围的相同层次中使用重复的名称。例如，不能在同一过程中声明两个命名为 age 的变量。 注意 ：Visual Basic 不区分大小写，但它会在名称被声明的语句处保留大写，例如： Ax4d 与 ax4D 是相同的名称。 参考：可以使用单词、拼音或词组的简拼加前后缀的方式，以帮助记忆和理解，比如， 我编写的 YCH()自定义函数就是以我自己的名字命名的。
我们在使用 EXCEL 时，如果仅仅只限于简单的数据处理，那就完全没有发挥出 EXCEL 强 大的功能，我们完全可以使用 VBA 让我们的工作效率大大提高。
一般情况下，我们通常编写三类程序： 1、自定义函数：它与工作表内置函数使用方法一样，使用简单，适用于固定几个参数得 出结果这种类型； 2、表格处理，这种编程较为复杂，适用于对整表进行大批量量数据的处理，对表格进行 美化处理等。 3、可视化窗口，可以通过窗口设置对表格数据进行处理。 以上三种并不是独立存在的，可以相互结合使用，更能发挥更大的效率。
本次讲解的主要自定义函数，从较为简单的独立的自定义函数开始，逐渐学习进行大数 据处理的能力。
二、几个必须知道的基础知识
1、VBA 编辑、使用的相关说明
EXCEL2003 版
打开 VBE 编辑器
EXCEL2007 及其以上版本
创建模块，并在模块中编写代码
如果代码（宏）保存在某一个工作薄内（比如 A.xls），无论这个 A.xls 拷贝到任何电脑 上，都可以使用这个宏，而其他工作薄要使用这个宏，就必须同时也打这个 A.xls；
7、学习 VBA 的方法和途径
最好找一个懂 VB 的师父带，因为任何书本、资料、教学录像都是死的，它不能回答你具 体遇到的问题，只有人才是活的教学。
在网上寻找相应的 OFFICE 论坛学习，如：EXCELHOME VBA 帮助：在 VBE 窗口可以按 F1 调出帮助，也可以在把光标点在需要学习的代码名称上， 再按 F1 调出这个代码的相应帮助内容。 使用录制宏功能，参考 EXCEL 本身是编写的代码。 注意收集好的代码。
我们平常在编写过程中，要注意哪些数据的处理是我们经常会遇到的，那么，对这种数 据的处理，可以对应编写一个独立的处理过程，今后在处理大数据时，如果其中用得着这些 独立过程，就可以直接引用这个了，而不必重新编写。高塔是一砖一木搭建起来的，而每一 砖一木都是独立的，我们编写程序代码也一样，只要注意经常积累这些完善而独立的小过程， 将来编写大程序时，直接拿来引用即可，这样就能省去很多功夫，也能构建我们自己的程序 高塔。
6、过程
代码并不是零星散乱的随便写在模块里就可以的，它是以一段段以 Sub 宏名称() 语句 开始，End Sub 语句结束组成的过程，其语句结构如下：
Sub 宏名称() 过程代码
End Sub 但一个过程不可能无限长，即过程代码不能无限多，在 VB 或 VBA 中，过程代码大概只能 写 300 多行，超过了程序可能就不执行了。 如果代码过多，那么，我们可以分段写，然后，其他过程再引用执行这段代码就可以了。
Range("A1").Select ActiveCell.FormulaR1C1 = "123" 其中，.Select 就是单元格对象被选择到，其实这个代码可以改写为： Range("A1").FormulaR1C1 = "123" 或 Range("A1") = "123" 使用数组来处理数据组； 尽可能少的使用循环语句 其他，等等。
如果我们把包含程序（宏）的工作薄另存为加载宏文件（xla），并让 EXCELΒιβλιοθήκη Baidu加载它，那 么每次 EXCEL 打开时，都会自动加载这个 xla 文件，那么当前电脑上所有的工作薄都可以使 用这个宏。
宏安全（EXCEL2007 及其以上版本在信任中心中设置）
2、对象三要素：属性、事件、方法 对象代表应用程序中的元素（一个实体），比如，工作表、单元格、图表、窗体，或是一
变量的附值，书写格式为：变量 = 值 或 表达式，比如： A = 0.5 A=A+1 A = A & “VBA“
为什么 A 可以等于 A+1，因为它不是我们普通数学表达式，而是代码表达式，程序是按 顺序逐行处理的，前面附了值以后，下面就会自动代入先前的值，如果前面没有附过值，这 时，变量 A 会自动是 0 或 ““（空）。所以，第 1 行 A 的值为 0.5，第 2 行 A 的值为 0.5+1=1.5， 第 3 行 A 的值为 1.5VBA。
变量名称：当在 Visual Basic 的模块中为过程、变量命名时，应符合下列命名规则： ① 一个字符必须使用英文字母，例如，象 1xb、_w 这种名称都是错误的，而 xb1、w_这
样的名称才是正确的。 ② 不能在名称中使用空格、` 、~、!、@、、#、$、%、^、&、*、(、)、-、+、=、{、}、
一、VBA 简介
Visual Basic for Applications（VBA）基于 Visual Basic 的一种宏语言，是微软用来 扩展 Windows 的应用程序，特别是在 Microsoft Office 软件中执行通用的自动化(OLE)任务 的编程语言。它与 VB 不同的是，VB 的对象主要是可视化窗口，VBA 的对象则主要是 Microsoft Office 应用程序，如 EXCEL 的单元格、工作表、工作薄等。由于对象不同，那么，对象的属 性、方法、事件就会发生相应的变化，所以，学习 VBA，或从 VB 转到 VBA，就是要根据具体 对象特性而进行不同的处理。
5、单元格的表示方法 在 EXCEL VBA 里，最基本的元素就是单元格，通常单元格有以下几种表示方法： 单独的单元格： ① Range(“A1“) ② [A1] ③ Cells(1,1)————前 1 表示行，后 1 表列 ④ Y.Offset(1, 2)——此为相对引用，表示 Y（某个单元格）向下一行，向右 2 列的那 个单元格，比如，Y 为 A1 单元格，则本代码表示的单元格是 C2
使用 VBA 编写自定义函数
前言
本教程仅适用于不懂或初学 VBA 的朋友。 万事开头难，VB 的内容繁多庞杂，没接触过 VB 但想学习编程的朋友可能无所适从，然 而，这也远不是一个小教程能说得明白的，所以，本教程没有讲过多的语法、函数，也没有 摘录 VBA 帮助（可以算是主要的参考教程）里的内容，而是以口语的方式讲解编程的一个基 本思路，再辅以讲解平常可能遇到情况，让学习者能基本上手编写一段简单的代码，我的目 的就达到了，接下去，就是学习者自己去论坛找资料学习了。
8、如何让代码更高效，运行得更快
我在这方面水平不够，只能大概说说： 首先，必须要学会更高级的编程知识和编写技巧，这样才能编写出高效的代码； 其次，要有严密而有效的逻辑思维能力； 关闭屏幕更新，代码为：Application.ScreenUpdating = False，为 True 时打开屏幕更 新；运行宏时如果不关闭，会发现表格随着数据的输出在自动移动，关闭后就不会移动了。 处理工作表和单元格时，除非需要，不要去选择单元格（相当于用鼠标点先单元格），比 如我们要在 A1 单元格写入 123，录制宏的代码如下：
份报告、一个人物等。 属性：是指对象的特征，诸如大小、颜色或屏幕位置等；比如单元格的内容，填充颜色、
字体颜色、单元格行高及列宽等等，又比如，一个人的年龄、姓名、性别、体重、血型等等。 可以通过修改对象的属性值来改变对象的特性。
代码通常写为：对象.属性；好比是张三(.的)年龄 方法指的是对象能执行的动作；比如在单元格添加一个有效性序列（下拉菜单）。 代码通常写为：对象.方法；好比是领导（.下达）政令。 事件是一个对象可以辨认的动作，像单击鼠标或按下某键等，并且可以写某些代码针对 此述动作来做响应；比如领导写字。 提示：不同的对象有不同的属性和方法。在 VBA 模块窗口里，写下一个对象的名称，再 写一个.就会出现一个下拉列表，列表里就是该对象的属性和方法集合。
另外要注意，过程要按照顺序和需要递渐引用，不要形成循环引用，比如 Sub 过程 1() 过程 2 End Sub Sub 过程 2() 过程 1 End Sub
象这样的话，那程序就会无限循环执行下去了，当然，不只是过程，在过程代码的某些 循环语句中，如果设计考虑得不周，也可能会形成无限循环，如果发生这种状况，按 ESC 键 可让程序中止。
Sub 过程 1() 过程 2 过程 3
End Sub Sub 过程 2()
过程 2 代码 End Sub Sub 过程 3()
过程 4 过程 3 代码 End Sub Sub 过程 4() 过程 4 代码 过程 5 End Sub Sub 过程 5() 过程 5 代码 End Sub
当然，过程不是直接把代码分几段来连接，而是针对不同的情况，每个小过程单独处理 一种数据，或一种处理方式，然后把这些小过程集合起来，就形成了一个大的程序，来处理 全部的数据。
方法与事件的区别，方法好比是一个对象的能力，事件好比是一个对象的动作，不同的 人有不同的能力，好比大多数人都能写字，但只有领导才有能力下达政令。对象及其三要素 是编程的最基本知识，任何程序语言都如此。
3、数据类型 数据类型，简单来说，就是一个数据它是什么类型的内容，是数字还是文本，或是日期
等等，不同的数据类型，程序处理的方法是不同的，处理时数据时所消耗的系统内存也是不 一样的。
我们一般常用的几个数据类型有：Byte、Boolean（真假）、Integer 和 Long（长整数）、 Single 和 Double（小数）、String（文本字符串）、Date（日期）等。
4、变量 变量好比就是方程式中的未知数Ｘ。首先，它只是一个代号（先命名），其次，我们可以
给这个代号设置不同的内容（附值）。所以，这个代号的值是不固定的，是会变化的，我们可 以用这个代号来任意处理需要计算的内容。于是我们把这个代号称为变量。
变量是相对于常量而言的，常量是固定的内容，比如：π、密度、65，8，-2.3 等具体 的数值、指定的内容或名称，变量则是相对于不同的情况来设置一个代号。
比如我们要计算一个圆的面积，常量则是π，变量则是半径或直径，当设置给圆不同的 半径或直径时，圆的面积就会相应的改变。相对于整个计算而言，我们也可以说，变量就是 其中需要处理的数据，而常量则是不变的计算步骤；相对于目标而言，变量就是不同的方法， 而常量就是意志和决心。
三、实例设计讲解
1、编写目标
我们现在需要编写一个计算矩形面积（或长方形体积）的自定义函数。
2、查找相关资料
查找到矩形面积的计算公式为：长×宽 长方体的体积公式为：长×宽×高
单元格区域： ⑤ Range(“A1:B5“) ⑥ [A1:B5] ⑦ Range(Cells(1,1),Cells(5,2)) ⑧ Range(“MyRange“)——引用命名区域，比如本工作表上有一个区域命名为 MyRange ⑨ Range(“A1:B5,C2:E4,G1:I10“) 引用多个区域 本工作表上所有单元格（相当于选中行标和列标在左上角交汇的那个空白处） Cells.ClearContents 整行表示： Rows(1) ————第一行 Rows(“1:1“) ———第一行 Rows(“1:5“) ———第一行至第五行 Rows ——————工作表上所有的行 整列表示： Columns(1) ———第一列 Columns("A") ——第一列 Columns("A:B") —第一列至第二列 Columns ————工作表上所有的列
我们在使用 EXCEL 时，如果仅仅只限于简单的数据处理，那就完全没有发挥出 EXCEL 强 大的功能，我们完全可以使用 VBA 让我们的工作效率大大提高。
一般情况下，我们通常编写三类程序： 1、自定义函数：它与工作表内置函数使用方法一样，使用简单，适用于固定几个参数得 出结果这种类型； 2、表格处理，这种编程较为复杂，适用于对整表进行大批量量数据的处理，对表格进行 美化处理等。 3、可视化窗口，可以通过窗口设置对表格数据进行处理。 以上三种并不是独立存在的，可以相互结合使用，更能发挥更大的效率。
本次讲解的主要自定义函数，从较为简单的独立的自定义函数开始，逐渐学习进行大数 据处理的能力。
二、几个必须知道的基础知识
1、VBA 编辑、使用的相关说明
EXCEL2003 版
打开 VBE 编辑器
EXCEL2007 及其以上版本
创建模块，并在模块中编写代码
如果代码（宏）保存在某一个工作薄内（比如 A.xls），无论这个 A.xls 拷贝到任何电脑 上，都可以使用这个宏，而其他工作薄要使用这个宏，就必须同时也打这个 A.xls；
7、学习 VBA 的方法和途径
最好找一个懂 VB 的师父带，因为任何书本、资料、教学录像都是死的，它不能回答你具 体遇到的问题，只有人才是活的教学。
在网上寻找相应的 OFFICE 论坛学习，如：EXCELHOME VBA 帮助：在 VBE 窗口可以按 F1 调出帮助，也可以在把光标点在需要学习的代码名称上， 再按 F1 调出这个代码的相应帮助内容。 使用录制宏功能，参考 EXCEL 本身是编写的代码。 注意收集好的代码。
我们平常在编写过程中，要注意哪些数据的处理是我们经常会遇到的，那么，对这种数 据的处理，可以对应编写一个独立的处理过程，今后在处理大数据时，如果其中用得着这些 独立过程，就可以直接引用这个了，而不必重新编写。高塔是一砖一木搭建起来的，而每一 砖一木都是独立的，我们编写程序代码也一样，只要注意经常积累这些完善而独立的小过程， 将来编写大程序时，直接拿来引用即可，这样就能省去很多功夫，也能构建我们自己的程序 高塔。
6、过程
代码并不是零星散乱的随便写在模块里就可以的，它是以一段段以 Sub 宏名称() 语句 开始，End Sub 语句结束组成的过程，其语句结构如下：
Sub 宏名称() 过程代码
End Sub 但一个过程不可能无限长，即过程代码不能无限多，在 VB 或 VBA 中，过程代码大概只能 写 300 多行，超过了程序可能就不执行了。 如果代码过多，那么，我们可以分段写，然后，其他过程再引用执行这段代码就可以了。
Range("A1").Select ActiveCell.FormulaR1C1 = "123" 其中，.Select 就是单元格对象被选择到，其实这个代码可以改写为： Range("A1").FormulaR1C1 = "123" 或 Range("A1") = "123" 使用数组来处理数据组； 尽可能少的使用循环语句 其他，等等。
如果我们把包含程序（宏）的工作薄另存为加载宏文件（xla），并让 EXCELΒιβλιοθήκη Baidu加载它，那 么每次 EXCEL 打开时，都会自动加载这个 xla 文件，那么当前电脑上所有的工作薄都可以使 用这个宏。
宏安全（EXCEL2007 及其以上版本在信任中心中设置）
2、对象三要素：属性、事件、方法 对象代表应用程序中的元素（一个实体），比如，工作表、单元格、图表、窗体，或是一
变量的附值，书写格式为：变量 = 值 或 表达式，比如： A = 0.5 A=A+1 A = A & “VBA“
为什么 A 可以等于 A+1，因为它不是我们普通数学表达式，而是代码表达式，程序是按 顺序逐行处理的，前面附了值以后，下面就会自动代入先前的值，如果前面没有附过值，这 时，变量 A 会自动是 0 或 ““（空）。所以，第 1 行 A 的值为 0.5，第 2 行 A 的值为 0.5+1=1.5， 第 3 行 A 的值为 1.5VBA。
变量名称：当在 Visual Basic 的模块中为过程、变量命名时，应符合下列命名规则： ① 一个字符必须使用英文字母，例如，象 1xb、_w 这种名称都是错误的，而 xb1、w_这
样的名称才是正确的。 ② 不能在名称中使用空格、` 、~、!、@、、#、$、%、^、&、*、(、)、-、+、=、{、}、
一、VBA 简介
Visual Basic for Applications（VBA）基于 Visual Basic 的一种宏语言，是微软用来 扩展 Windows 的应用程序，特别是在 Microsoft Office 软件中执行通用的自动化(OLE)任务 的编程语言。它与 VB 不同的是，VB 的对象主要是可视化窗口，VBA 的对象则主要是 Microsoft Office 应用程序，如 EXCEL 的单元格、工作表、工作薄等。由于对象不同，那么，对象的属 性、方法、事件就会发生相应的变化，所以，学习 VBA，或从 VB 转到 VBA，就是要根据具体 对象特性而进行不同的处理。
5、单元格的表示方法 在 EXCEL VBA 里，最基本的元素就是单元格，通常单元格有以下几种表示方法： 单独的单元格： ① Range(“A1“) ② [A1] ③ Cells(1,1)————前 1 表示行，后 1 表列 ④ Y.Offset(1, 2)——此为相对引用，表示 Y（某个单元格）向下一行，向右 2 列的那 个单元格，比如，Y 为 A1 单元格，则本代码表示的单元格是 C2
使用 VBA 编写自定义函数
前言
本教程仅适用于不懂或初学 VBA 的朋友。 万事开头难，VB 的内容繁多庞杂，没接触过 VB 但想学习编程的朋友可能无所适从，然 而，这也远不是一个小教程能说得明白的，所以，本教程没有讲过多的语法、函数，也没有 摘录 VBA 帮助（可以算是主要的参考教程）里的内容，而是以口语的方式讲解编程的一个基 本思路，再辅以讲解平常可能遇到情况，让学习者能基本上手编写一段简单的代码，我的目 的就达到了，接下去，就是学习者自己去论坛找资料学习了。
8、如何让代码更高效，运行得更快
我在这方面水平不够，只能大概说说： 首先，必须要学会更高级的编程知识和编写技巧，这样才能编写出高效的代码； 其次，要有严密而有效的逻辑思维能力； 关闭屏幕更新，代码为：Application.ScreenUpdating = False，为 True 时打开屏幕更 新；运行宏时如果不关闭，会发现表格随着数据的输出在自动移动，关闭后就不会移动了。 处理工作表和单元格时，除非需要，不要去选择单元格（相当于用鼠标点先单元格），比 如我们要在 A1 单元格写入 123，录制宏的代码如下：
份报告、一个人物等。 属性：是指对象的特征，诸如大小、颜色或屏幕位置等；比如单元格的内容，填充颜色、
字体颜色、单元格行高及列宽等等，又比如，一个人的年龄、姓名、性别、体重、血型等等。 可以通过修改对象的属性值来改变对象的特性。
代码通常写为：对象.属性；好比是张三(.的)年龄 方法指的是对象能执行的动作；比如在单元格添加一个有效性序列（下拉菜单）。 代码通常写为：对象.方法；好比是领导（.下达）政令。 事件是一个对象可以辨认的动作，像单击鼠标或按下某键等，并且可以写某些代码针对 此述动作来做响应；比如领导写字。 提示：不同的对象有不同的属性和方法。在 VBA 模块窗口里，写下一个对象的名称，再 写一个.就会出现一个下拉列表，列表里就是该对象的属性和方法集合。
另外要注意，过程要按照顺序和需要递渐引用，不要形成循环引用，比如 Sub 过程 1() 过程 2 End Sub Sub 过程 2() 过程 1 End Sub
象这样的话，那程序就会无限循环执行下去了，当然，不只是过程，在过程代码的某些 循环语句中，如果设计考虑得不周，也可能会形成无限循环，如果发生这种状况，按 ESC 键 可让程序中止。
Sub 过程 1() 过程 2 过程 3
End Sub Sub 过程 2()
过程 2 代码 End Sub Sub 过程 3()
过程 4 过程 3 代码 End Sub Sub 过程 4() 过程 4 代码 过程 5 End Sub Sub 过程 5() 过程 5 代码 End Sub
当然，过程不是直接把代码分几段来连接，而是针对不同的情况，每个小过程单独处理 一种数据，或一种处理方式，然后把这些小过程集合起来，就形成了一个大的程序，来处理 全部的数据。
方法与事件的区别，方法好比是一个对象的能力，事件好比是一个对象的动作，不同的 人有不同的能力，好比大多数人都能写字，但只有领导才有能力下达政令。对象及其三要素 是编程的最基本知识，任何程序语言都如此。
3、数据类型 数据类型，简单来说，就是一个数据它是什么类型的内容，是数字还是文本，或是日期
等等，不同的数据类型，程序处理的方法是不同的，处理时数据时所消耗的系统内存也是不 一样的。
我们一般常用的几个数据类型有：Byte、Boolean（真假）、Integer 和 Long（长整数）、 Single 和 Double（小数）、String（文本字符串）、Date（日期）等。
4、变量 变量好比就是方程式中的未知数Ｘ。首先，它只是一个代号（先命名），其次，我们可以
给这个代号设置不同的内容（附值）。所以，这个代号的值是不固定的，是会变化的，我们可 以用这个代号来任意处理需要计算的内容。于是我们把这个代号称为变量。
变量是相对于常量而言的，常量是固定的内容，比如：π、密度、65，8，-2.3 等具体 的数值、指定的内容或名称，变量则是相对于不同的情况来设置一个代号。
比如我们要计算一个圆的面积，常量则是π，变量则是半径或直径，当设置给圆不同的 半径或直径时，圆的面积就会相应的改变。相对于整个计算而言，我们也可以说，变量就是 其中需要处理的数据，而常量则是不变的计算步骤；相对于目标而言，变量就是不同的方法， 而常量就是意志和决心。
三、实例设计讲解
1、编写目标
我们现在需要编写一个计算矩形面积（或长方形体积）的自定义函数。
2、查找相关资料
查找到矩形面积的计算公式为：长×宽 长方体的体积公式为：长×宽×高
单元格区域： ⑤ Range(“A1:B5“) ⑥ [A1:B5] ⑦ Range(Cells(1,1),Cells(5,2)) ⑧ Range(“MyRange“)——引用命名区域，比如本工作表上有一个区域命名为 MyRange ⑨ Range(“A1:B5,C2:E4,G1:I10“) 引用多个区域 本工作表上所有单元格（相当于选中行标和列标在左上角交汇的那个空白处） Cells.ClearContents 整行表示： Rows(1) ————第一行 Rows(“1:1“) ———第一行 Rows(“1:5“) ———第一行至第五行 Rows ——————工作表上所有的行 整列表示： Columns(1) ———第一列 Columns("A") ——第一列 Columns("A:B") —第一列至第二列 Columns ————工作表上所有的列