使用VBA编写自定义函数修改版.docx

1、使用VBA编写自定义函数修改版使用VB编写自定义函数(修改版) 使用VB编写自定义函数 前言 本教程仅适用于不懂或初学VB的朋友。 万事开头难，VB的内容繁多庞杂，没接触过VB但想学习编程的朋友可能无所适从，然而，这也远不是一个小教程能说得明白的，所以，本教程没有讲过多的语法、函数，也没有摘录VB帮助（可以算是主要的参考教程）里的内容，而是以口语的方式讲解编程的一个基本思路，再辅以讲解平常可能遇到情况，让学习者能基本上手编写一段简单的代码，我的目的就达到了，接下去，就是学习者自己去论坛找资料学习了。 一、VB简介 Visul Bsic for pplictions（VB）基于Visul Bsi

2、c的一种宏语言，是微软用来扩展Windows的应用程序，特别是在Microsoft Office软件中执行通用的自动化(OLE)任务的编程语言。它与VB不同的是，VB的对象主要是可视化窗口，VB的对象则主要是Microsoft Office应用程序，如EXCEL的单元格、工作表、工作薄等。由于对象不同，那么，对象的属性、方法、事件就会发生相应的变化，所以，学习VB，或从VB转到VB，就是要根据具体对象特性而进行不同的处理。 我们在使用EXCEL时，如果仅仅只限于简单的数据处理，那就完全没有发挥出EXCEL强大的功能，我们完全可以使用VB让我们的工作效率大大提高。 一般情况下，我们通常编写三类程

3、序： 1、自定义函数：它与工作表内置函数使用方法一样，使用简单，适用于固定几个参数得出结果这种类型； 2、表格处理，这种编程较为复杂，适用于对整表进行大批量量数据的处理，对表格进行美化处理等。 3、可视化窗口，可以通过窗口设置对表格数据进行处理。 以上三种并不是独立存在的，可以相互结合使用，更能发挥更大的效率。 本次讲解的主要自定义函数，从较为简单的独立的自定义函数开始，逐渐学习进行大数据处理的能力。 二、几个必须知道的基础知识 1、VB编辑、使用的相关说明 打开VBE编辑器 EXCEL20XX版 EXCEL20XX及其以上版本 创建模块，并在模块中编写代码 如果代码（宏）保存在某一个工作薄内

4、（比如.xls），无论这个.xls拷贝到任何电脑上，都可以使用这个宏，而其他工作薄要使用这个宏，就必须同时也打这个.xls； 如果我们把包含程序（宏）的工作薄另存为加载宏文件（xl），并让EXCEL加载它，那么每次EXCEL打开时，都会自动加载这个xl文件，那么当前电脑上所有的工作薄都可以使用这个宏。 宏安全（EXCEL20XX及其以上版本在信任中心中设置） 2、对象三要素：属性、事件、方法 对象代表应用程序中的元素（一个实体），比如，工作表、单元格、图表、窗体，或是一份报告、一个人物等。 属性：是指对象的特征，诸如大小、颜色或屏幕位置等；比如单元格的内容，填充颜色、字体颜色、单元格行高及列宽

5、等等，又比如，一个人的年龄、姓名、性别、体重、血型等等。可以通过修改对象的属性值来改变对象的特性。 代码通常写为：对象.属性；好比是张三(.的)年龄 方法指的是对象能执行的动作；比如在单元格添加一个有效性序列（下拉菜单）。 代码通常写为：对象.方法；好比是领导（.下达）政令。 事件是一个对象可以辨认的动作，像单击鼠标或按下某键等，并且可以写某些代码针对此述动作来做响应；比如领导写字。 提示：不同的对象有不同的属性和方法。在VB模块窗口里，写下一个对象的名称，再写一个.就会出现一个下拉列表，列表里就是该对象的属性和方法集合。 方法与事件的区别，方法好比是一个对象的能力，事件好比是一个对象的动作，

6、不同的人有不同的能力，好比大多数人都能写字，但只有领导才有能力下达政令。对象及其三要素是编程的最基本知识，任何程序语言都如此。 3、数据类型 数据类型，简单来说，就是一个数据它是什么类型的内容，是数字还是文本，或是日期等等，不同的数据类型，程序处理的方法是不同的，处理时数据时所消耗的系统内存也是不一样的。 我们一般常用的几个数据类型有：Byte、Boolen（真假）、Integer 和 Long（长整数）、Single 和 Double（小数）、String（文本字符串）、Dte（日期）等。 4、变量 变量好比就是方程式中的未知数。首先，它只是一个（先命名），其次，我们可以给这个设置不同的内容

7、（附值）。所以，这个的值是不固定的，是会变化的，我们可以用这个来任意处理需要计算的内容。于是我们把这个称为变量。 变量是相对于常量而言的，常量是固定的内容，比如：、密度、65，8，-2.3等具体的数值、指定的内容或名称，变量则是相对于不同的情况来设置一个。 比如我们要计算一个圆的面积，常量则是，变量则是半径或直径，当设置给圆不同的半径或直径时，圆的面积就会相应的改变。相对于整个计算而言，我们也可以说，变量就是其中需要处理的数据，而常量则是不变的计算步骤；相对于目标而言，变量就是不同的方法，而常量就是意志和决心。 变量名称：当在 Visul Bsic 的模块中为过程、变量命名时，应符合下列命名规

8、则： 一个字符必须使用英文字母，例如，象1xb、_w这种名称都是错误的，而xb1、w_这 样的名称才是正确的。 不能在名称中使用空格、 、!、#、$、%、:、,、.、?、/等字符，也就是说，除了字母、数字、下划线_ 可以使用外，其他键盘上的符号都不能使用，而且，一般情况下，名称最好 不要使用全角字符或汉字。 称的长度不得超过 255 个字符。 称不能与 Visul Bsic 本身的函数、属性、方法的名称相同。例如，命名为 Left 的 变量与函数 Left() 的名称相同，这是不同意的。在VB中也不要使用如1、B2等 与单元格相同的命名。 能在范围的相同层次中使用重复的名称。例如，不能在同一过

9、程中声明两个命名为 ge 的变量。 注意 ：Visul Bsic 不区分大小写，但它会在名称被声明的语句处保留大写，例如：x4d 与 x4D 是相同的名称。 参考：可以使用单词、拼音或词组的简拼加前后缀的方式，以帮助记忆和理解，比如，我编写的YCH()自定义函数就是以我自己的名字命名的。 变量的附值，书写格式为：变量 = 值 或 表达式，比如： = 0.5 = + 1 = & “VB“ 为什么可以等于+1，因为它不是我们一般数学表达式，而是代码表达式，程序是按顺序逐行处理的，前面附了值以后，下面就会自动代入先前的值，如果前面没有附过值，这时，变量会自动是0 或 “（空）。所以，第1行的值为0.

10、5，第2行的值为0.5+1=1.5，第3行的值为1.5VB。 5、单元格的表示方法 在EXCEL VB里，最基本的元素就是单元格，通常单元格有以下几种表示方法： 单独的单元格： Rnge(“1“) 1 Cells(1,1)前1表示行，后1表列 Y.Offset(1, 2)此为相对引用，表示Y（某个单元格）向下一行，向右2列的那 个单元格，比如，Y为1单元格，则本代码表示的单元格是C2 单元格区域： Rnge(“1:B5“) 1:B5 Rnge(Cells(1,1),Cells(5,2) Rnge(“MyRnge“)引用命名区域，比如本工作表上有一个区域命名为MyRnge Rnge(“1:B5,

11、C2:E4,G1:I10“) 引用多个区域 本工作表上所有单元格（相当于选中行标和列标在左上角交汇的那个空白处） Cells.ClerContents 整行表示： Rows(1) 第一行 Rows(“1:1“) 第一行 Rows(“1:5“) 第一行至第五行 Rows 工作表上所有的行 整列表示： Columns(1) 第一列 Columns() 第一列 Columns(:B) 第一列至第二列 Columns 工作表上所有的列 6、过程 代码并不是零星散乱的随便写在模块里就可以的，它是以一段段以Sub 宏名称() 语句开始，End Sub 语句结束组成的过程，其语句结构如下： Sub 宏名称(

12、) 过程代码 End Sub 但一个过程不可能无限长，即过程代码不能无限多，在VB或VB中，过程代码大概只能写300多行，超过了程序可能就不执行了。 如果代码过多，那么，我们可以分段写，然后，其他过程再引用执行这段代码就可以了。 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 当然，过程不是直接把代码分几段来连接，而是针对不同的情况，每个小过程单独处理一种数据，或一种处理方式，然后把这

13、些小过程集合起来，就形成了一个大的程序，来处理全部的数据。 我们平常在编写过程中，要注意哪些数据的处理是我们经常会遇到的，那么，对这种数据的处理，可以对应编写一个独立的处理过程，今后在处理大数据时，如果其中用得着这些独立过程，就可以直接引用这个了，而不必重新编写。高塔是一砖一木搭建起来的，而每一砖一木都是独立的，我们编写程序代码也一样，只要注意经常积存这些完善而独立的小过程，将来编写大程序时，直接拿来引用即可，这样就能省去很多功夫，也能构建我们自己的程序高塔。 另外要注意，过程要按照顺序和需要递渐引用，不要形成循环引用，比如 Sub 过程1() 过程2 End Sub Sub 过程2() 过程

14、1 End Sub 象这样的话，那程序就会无限循环执行下去了，当然，不只是过程，在过程代码的某些循环语句中，如果设计考虑得不周，也可能会形成无限循环，如果发生这种状况，按ESC键可让程序中止。 7、学习VB的方法和途径 最好找一个懂VB的师父带，因为任何书本、资料、教学录像都是死的，它不能回答你具体遇到的问题，只有人才是活的教学。 在XX上寻找相应的OFFICE论坛学习，如：EXCELHOME VB帮助：在VBE窗口可以按F1调出帮助，也可以在把光标点在需要学习的代码名称上，再按F1调出这个代码的相应帮助内容。 使用录制宏功能，参考EXCEL本身是编写的代码。 注意收集好的代码。 8、如何让代

15、码更高效，运行得更快 我在这方面水平不够，只能大概说说： 首先，必须要学会更高级的编程知识和编写技巧，这样才能编写出高效的代码； 其次，要有严密而有效的逻辑思维能力； 关闭屏幕更新，代码为：ppliction.ScreenUpdting = Flse，为True时打开屏幕更新；运行宏时如果不关闭，会发现表格随着数据的输出在自动移动，关闭后就不会移动了。 处理工作表和单元格时，除非需要，不要去选择单元格（相当于用鼠标点先单元格），比如我们要在1单元格写入123，录制宏的代码如下： Rnge(1).Select ctiveCell.FormulR1C1 = 123 其中，.Select就是单元格对

16、象被选择到，其实这个代码可以改写为： Rnge(1).FormulR1C1 = 123 或Rnge(1) = 123 使用数组来处理数据组； 尽可能少的使用循环语句 其他，等等。 三、实例设计讲解 1、编写目标 我们现在需要编写一个计算矩形面积（或长方形体积）的自定义函数。 2、查找相关资料 查找到矩形面积的计算公式为：长宽 长方体的体积公式为：长宽高 3、分析 计算公式中，长、宽、高都是变量，可以分别设为变量1、变量2、变量3 其中：参数高可以有，也可以没有 计算结果根据需要可以是公式的结果（数值）也可以是公式本身（文本），可设为变量4 4、过程结构设计 可以制作一个步骤流程图，让代码编写的

17、思路更加清楚。 5、编写代码 声明自定义函数名称 声明：就是告诉程序，这个XX是专属名称，在本程序中只能用来表示XXXXX。 语法为： Function 函数名称（必要参数, Optionl 可选参数）定义函数的数据类型 过程代码 Exit Function 过程代码 End Function 说明： Function：起始句，使用Function声明以下过程为自定义函数。 函数和参数其实都是变量，所以，它们名称的命名规则都与变量名称的命名规则相同。 同变量一样，如果需要的话也可以对函数定义数据类型，也可以不用定义，那么，函数的数据类型就是“用户自定义”类型。 可以没有参数，那就如同工作表函数

18、PI()一样，它输出的结果就始终只有一个，即它就是一个常量。 可以只有必要参数，也可以只有可选参数。 如果必要参数和可选参数都有，那么，可选参数必须放在必要参数后面，可选参数必须使用Optionl声明。 可选参数可以指定一个常量，表示在没有写入该可选参数时，默认它为这个常量，比如Optionl可选参数 = 3.5；必要参数不能指定常量。 参数都可以声明数据类型，也可以不用声明，那么，未声明数据类型的参数就是“用户自定义”类型。 是常规的表示方法，表示其中的内容可以有，也可以没有。 Exit Function：在过程中任意位置，都可以使用该语句结束该函数的计算过程。 End Function：函

19、数过程的最终结束处。 过程：从Function起始 到 End Function结束，其中所有的内容称之为过程。 设置参数 Function YJX（C s Single, K s Single, Optionl G s Single, Optionl JG s Integer = 0） End Function 说明： 本次用YJX表示函数名称，C表示长，K表示宽，G表示高，JG表示结果样式 其中，G(高) 和JG（结果样式）参数不一定存在，所以声明为可选参数。 参数在声明数据类型时，语句必须是：变量名 s 类型名称 很显然，C(长)、K(宽)、G(高)都必须是数值，所以定义为Single（

20、小数） JG（结果样式）用整数来表示就足够了，我们可以设置为： 当JG=0时，输出公式的计算结果，当JG=1时，输出公式本身。 因为我们设置了Optionl JG s Integer = 0，即没有这个参数时，就默认为输出计算结果。 因为该函数的输出结果可能是一个数值，也可以是一个文本，所以，不对该函数进行数据类型的声明。 我们在工作表中实际使用这个函数时，公式写法就是： =YJX(长,宽,高,结果样式) 注意：当你计算的是矩形面积时，而且需要输出计算公式时，是没有高这个参数的，那就应该这样写： =YJX(3,4,1)表示输出长=3宽=4的矩形面积公式 即，后面的可选参数没有时可以不管，但前面

21、的可选参数没有时，必须加个逗号表示。 定义变量 起始行以下，紧接着就是设置本过程的变量，当然，如果计算过程中不需要其他的变量，也可以不设置，本次设置一个变量 GS,该变量是程序在内存处理数据中使用，使用者在工作表使用函数时，不会涉及到。 Function YJX（C s Single, K s Single, Optionl G s Single, Optionl JG s Integer = 0） Dim GS End Function 说明： 定义变量的语句为：Dim 变量名称 s 数据类型名称 也可以使用类型通配符来表示，比如，Dim GS%，表示GS是一个整型变量。当不确定GS 只附值

22、整数时，可以不用指定，让它成为一个用户自定义类型。 检查错误 检查输入的参数是否正确，以及数据关系是否正常，是保证程序在处理数据时，不会因为数据错误而导致程序中断的必要保障，是任何一个程序员编程时必需要做的功课。假如我们的函数是这样的一个写法：= YJX(3,将军)，即实际的计算公式为：3*将军，你认为它能计算出结果么？ 所以，要养成在计算之前先检查变量错误的良好习惯，这样编写出来的代码才不会有BUG。 实际上，在自定义函数中，已定义了数据类型的变量是可以不用检查的，如果你已经定义了参数是Single（小数），而你输入的是String（文本）时，那程序会马上中断进程，工作表中会显示#VLUE!

23、；如果你没有定义参数类型，即参数为用户自定义类型时，程序会使用该变量继续计算，直到发生错误后中断进程，但这样一来加重了系统的负担，让它多运算了一些内容，虽然表面上看起来，似乎也没有什么差别，但如果一个计算表中，有一万个函数在同时运算时，这种差别（计算延时）就很明显了。 本函数中，矩形或长方体的长宽高参数都应该是0的，但如果参数的是负值，我们则认为是错的，但负值也是数值，程序可不知道你是怎么想的，如果你不要求程序检查这个错误，程序就认为参数是正确的，依旧会计算出结果来。所以必须添加对参数是否为负值的检查代码：If IsMissing(C) = Flse nd C 1 Then YJX = 1 /

24、 0: Exit Function 如此，本节的代码如下： Function YJX(Optionl C s Single, Optionl K s Single, Optionl G s Single, Optionl JG s Integer = 0) Dim GS If IsMissing(C) = Flse nd C 0 nd JG 1 Then YJX = 1 / 0: Exit Function End Function 可选参数的推断及计算过程 计算时，我们必须要考虑可选参数对计算的影响，因为有可选参数与没有可选参数会影响到我们实际的计算需要。 因为我们已经设置了Optionl

25、JG s Integer = 0，即无论我们在工作表中写不写这个参数，它实际都存在，我们只是需要推断它是0还是1即可。 而高却可能是不存在的，所以我们现在只需要推断参数G是否存在就可以了： If IsMissing(G) Then GS = C & * & K Else GS = C & * & K & * & G End If 说明： 如果这个函数只需要输出公式的结果（数值），那我们可以不需要GS变量，代码可以直接写为： If IsMissing(G) Then YJX = C * K Else YJX = C * K * G End If 就因为我们还想着要输出计算公式，所以，要先用变量G

26、S来“装载”计算公式，无论最终的计算公式是什么，我们在最后直接处理这个GS的内容就可以得到结果了。 & 是连接符，表示把两个内容前后连接在一起。比如：3 & * & 4，表示“3*4”，如果是3 * 4就表示其计算结果12了；变量在和文本连接时，文本需要用双引号括起来。 如此，本节的代码如下： Function YJX(C s Single, Optionl K s Single, Optionl G s Single, Optionl JG s Integer = 0) Dim GS If IsMissing(G) Then GS = C & * & K Else GS = C & * &

27、K & * & G End If 过程代码 End Function 输出结果 现在，我们有了计算公式，下一步就是根据实际需要来处理这个公式内容了： If JG = 0 Then YJX = Evlute(= & GS) ElseIf JG = 1 Then YJX = GS End If 说明： 以上代码表示，当JG=0时，函数输出公式的计算结果（数值），当JG=1时，直接输出公式。 Evlute()是宏表函数，其作用是返回一个计算公式的结果，注意其参数为= & 公式。 如此，全部的函数代码就完成了： Function YJX(Optionl C s Single, Optionl K s

28、Single, Optionl G s Single, Optionl JG s Integer = 0) Dim GS If IsMissing(C) = Flse nd C 0 nd JG 1 Then YJX = 1 / 0: Exit Function If IsMissing(G) Then GS = C & * & K Else GS = C & * & K & * & G End If If JG = 0 Then YJX = Evlute(= & GS) ElseIf JG = 1 Then YJX = GS End If End Function 调试 下面，我们回到工作表中

29、，测试一下这个函数的效果。 为什么会发生这种情况呢？ 原来，在D2和E2单元格的公式中，我们引用了C2（高）这个参数，而C2的内容是空，VB认为空单元格的值为0，于是，这个公式的实际内容其实成了“=3*4*0”。 如果我们把D2单元格的公式改为“=YJX(2,B2)”倒是也能计算出结果来，但D3单元格的公式却必须为“=YJX(3,B3,C3)”，如此，我们就不能使用工作表的拖拽功能了，这样使用起来，就非常的麻烦，所以，我们即然是在编写VB的自定义函数，就应该考虑到应用的对象是工作表，参数可能是引用单元格的特别性。 所以，我们需要把代码修改为： If IsMissing(G) Then GS = C & * & K Else GS = C & * & K & * & IIf(G = 0, 1, G) End If 说明： IIf()为VB函数，与工作表函数IF()类似，它就相当于简单版的IfThenElse语句。 这里把推断参数G，当单元格为空时（即G=0），我们就让它等于1，因为任何数值乘以1都等于那个数值，如果单元格有数值，再乘以那个数值。 下面，我们再来看看效果。 这次，计算结果完全正确，但是 D2单元格的计算公式却为“3*4*1”，可实际C2单元格并没有内容，看来还得修改一下： If IsMissing(G) Then JX01: