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

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

《使用VBA编写自定义函数修改版.docx》由会员分享，可在线阅读，更多相关《使用VBA编写自定义函数修改版.docx（17页珍藏版）》请在冰点文库上搜索。

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

使用VB编写自定义函数（修改版）

使用VB编写自定义函数

前言

本教程仅适用于不懂或初学VB的朋友。

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

一、VB简介

VisulBsicforpplictions（VB）基于VisulBsic的一种宏语言，是微软用来扩展Windows的应用程序，特别是在MicrosoftOffice软件中执行通用的自动化（OLE）任务的编程语言。

它与VB不同的是，VB的对象主要是可视化窗口，VB的对象则主要是MicrosoftOffice应用程序，如EXCEL的单元格、工作表、工作薄等。

由于对象不同，那么，对象的属性、方法、事件就会发生相应的变化，所以，学习VB，或从VB转到VB，就是要根据具体对象特性而进行不同的处理。

我们在使用EXCEL时，如果仅仅只限于简单的数据处理，那就完全没有发挥出EXCEL强大的功能，我们完全可以使用VB让我们的工作效率大大提高。

一般情况下，我们通常编写三类程序：

1、自定义函数：

它与工作表内置函数使用方法一样，使用简单，适用于固定几个参数得出结果这种类型；

2、表格处理，这种编程较为复杂，适用于对整表进行大批量量数据的处理，对表格进行美化处理等。

3、可视化窗口，可以通过窗口设置对表格数据进行处理。

以上三种并不是独立存在的，可以相互结合使用，更能发挥更大的效率。

本次讲解的主要自定义函数，从较为简单的独立的自定义函数开始，逐渐学习进行大数据处理的能力。

二、几个必须知道的基础知识

1、VB编辑、使用的相关说明

打开VBE编辑器

EXCEL20XX版

EXCEL20XX及其以上版本

创建模块，并在模块中编写代码

如果代码（宏）保存在某一个工作薄内（比如.xls），无论这个.xls拷贝到任何电脑上，都可以使用这个宏，而其他工作薄要使用这个宏，就必须同时也打这个.xls；

如果我们把包含程序（宏）的工作薄另存为加载宏文件（xl），并让EXCEL加载它，那么每次EXCEL打开时，都会自动加载这个xl文件，那么当前电脑上所有的工作薄都可以使用这个宏。

宏安全（EXCEL20XX及其以上版本在信任中心中设置）

2、对象三要素：

属性、事件、方法

对象代表应用程序中的元素（一个实体），比如，工作表、单元格、图表、窗体，或是一份报告、一个人物等。

属性：

是指对象的特征，诸如大小、颜色或屏幕位置等；比如单元格的内容，填充颜色、字体颜色、单元格行高及列宽等等，又比如，一个人的年龄、姓名、性别、体重、血型等等。

可以通过修改对象的属性值来改变对象的特性。

代码通常写为：

对象.属性；好比是张三（.的）年龄

方法指的是对象能执行的动作；比如在单元格添加一个有效性序列（下拉菜单）。

代码通常写为：

对象.方法；好比是领导（.下达）政令。

事件是一个对象可以辨认的动作，像单击鼠标或按下某键等，并且可以写某些代码针对此述动作来做响应；比如领导写字。

提示：

不同的对象有不同的属性和方法。

在VB模块窗口里，写下一个对象的名称，再写一个.就会出现一个下拉列表，列表里就是该对象的属性和方法集合。

方法与事件的区别，方法好比是一个对象的能力，事件好比是一个对象的动作，不同的人有不同的能力，好比大多数人都能写字，但只有领导才有能力下达政令。

对象及其三要素是编程的最基本知识，任何程序语言都如此。

3、数据类型

数据类型，简单来说，就是一个数据它是什么类型的内容，是数字还是文本，或是日期等等，不同的数据类型，程序处理的方法是不同的，处理时数据时所消耗的系统内存也是不一样的。

我们一般常用的几个数据类型有：

Byte、Boolen（真假）、Integer和Long（长整数）、Single和Double（小数）、String（文本字符串）、Dte（日期）等。

4、变量

变量好比就是方程式中的未知数Ｘ。

首先，它只是一个（先命名），其次，我们可以给这个设置不同的内容（附值）。

所以，这个的值是不固定的，是会变化的，我们可以用这个来任意处理需要计算的内容。

于是我们把这个称为变量。

变量是相对于常量而言的，常量是固定的内容，比如：

π、密度、65，8，-2.3等具体的数值、指定的内容或名称，变量则是相对于不同的情况来设置一个。

比如我们要计算一个圆的面积，常量则是π，变量则是半径或直径，当设置给圆不同的半径或直径时，圆的面积就会相应的改变。

相对于整个计算而言，我们也可以说，变量就是其中需要处理的数据，而常量则是不变的计算步骤；相对于目标而言，变量就是不同的方法，而常量就是意志和决心。

变量名称：

当在VisulBsic的模块中为过程、变量命名时，应符合下列命名规则：

①一个字符必须使用英文字母，例如，象1xb、_w这种名称都是错误的，而xb1、w_这

样的名称才是正确的。

②不能在名称中使用空格、`、~、!

、@、、#、$、%、^、、:

、'、"、、,、.、?

、/等字符，也就是说，除了字母、数字、下划线_可以使用外，其他键盘上的符号都不能使用，而且，一般情况下，名称最好

不要使用全角字符或汉字。

③称的长度不得超过255个字符。

④称不能与VisulBsic本身的函数、属性、方法的名称相同。

例如，命名为Left的

变量与函数Left（）的名称相同，这是不同意的。

在VB中也不要使用如1、B2等

与单元格相同的命名。

⑤能在范围的相同层次中使用重复的名称。

例如，不能在同一过程中声明两个命名为

ge的变量。

注意：

VisulBsic不区分大小写，但它会在名称被声明的语句处保留大写，例如：

x4d与x4D是相同的名称。

参考：

可以使用单词、拼音或词组的简拼加前后缀的方式，以帮助记忆和理解，比如，我编写的YCH（）自定义函数就是以我自己的名字命名的。

变量的附值，书写格式为：

变量=值或表达式，比如：

=0.5

=+1

=&“VB“

为什么可以等于+1，因为它不是我们一般数学表达式，而是代码表达式，程序是按顺序逐行处理的，前面附了值以后，下面就会自动代入先前的值，如果前面没有附过值，这时，变量会自动是0或““（空）。

所以，第1行的值为0.5，第2行的值为0.5+1=1.5，第3行的值为1.5VB。

5、单元格的表示方法

在EXCELVB里，最基本的元素就是单元格，通常单元格有以下几种表示方法：

单独的单元格：

①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,C2:

E4,G1:

I10“）引用多个区域

本工作表上所有单元格（相当于选中行标和列标在左上角交汇的那个空白处）

Cells.ClerContents

整行表示：

Rows

（1）————第一行

Rows（“1:

1“）———第一行

Rows（“1:

5“）———第一行至第五行

Rows——————工作表上所有的行

整列表示：

Columns

（1）———第一列

Columns（""）——第一列

Columns（":

B"）—第一列至第二列

Columns————工作表上所有的列

6、过程

代码并不是零星散乱的随便写在模块里就可以的，它是以一段段以Sub宏名称（）语句开始，EndSub语句结束组成的过程，其语句结构如下：

Sub宏名称（）

过程代码

EndSub

但一个过程不可能无限长，即过程代码不能无限多，在VB或VB中，过程代码大概只能写300多行，超过了程序可能就不执行了。

如果代码过多，那么，我们可以分段写，然后，其他过程再引用执行这段代码就可以了。

Sub过程1（）

过程2

过程3

EndSub

Sub过程2（）

过程2代码

EndSub

Sub过程3（）

过程4

过程3代码

EndSub

Sub过程4（）

过程4代码

过程5

EndSub

Sub过程5（）

过程5代码

EndSub

当然，过程不是直接把代码分几段来连接，而是针对不同的情况，每个小过程单独处理一种数据，或一种处理方式，然后把这些小过程集合起来，就形成了一个大的程序，来处理全部的数据。

我们平常在编写过程中，要注意哪些数据的处理是我们经常会遇到的，那么，对这种数据的处理，可以对应编写一个独立的处理过程，今后在处理大数据时，如果其中用得着这些独立过程，就可以直接引用这个了，而不必重新编写。

高塔是一砖一木搭建起来的，而每一砖一木都是独立的，我们编写程序代码也一样，只要注意经常积存这些完善而独立的小过程，将来编写大程序时，直接拿来引用即可，这样就能省去很多功夫，也能构建我们自己的程序高塔。

另外要注意，过程要按照顺序和需要递渐引用，不要形成循环引用，比如Sub过程1（）

过程2

EndSub

Sub过程2（）

过程1

EndSub

象这样的话，那程序就会无限循环执行下去了，当然，不只是过程，在过程代码的某些循环语句中，如果设计考虑得不周，也可能会形成无限循环，如果发生这种状况，按ESC键可让程序中止。

7、学习VB的方法和途径

最好找一个懂VB的师父带，因为任何书本、资料、教学录像都是死的，它不能回答你具体遇到的问题，只有人才是活的教学。

在XX上寻找相应的OFFICE论坛学习，如：

EXCELHOME

VB帮助：

在VBE窗口可以按F1调出帮助，也可以在把光标点在需要学习的代码名称上，再按F1调出这个代码的相应帮助内容。

使用录制宏功能，参考EXCEL本身是编写的代码。

注意收集好的代码。

8、如何让代码更高效，运行得更快

我在这方面水平不够，只能大概说说：

首先，必须要学会更高级的编程知识和编写技巧，这样才能编写出高效的代码；

其次，要有严密而有效的逻辑思维能力；

关闭屏幕更新，代码为：

ppliction.ScreenUpdting=Flse，为True时打开屏幕更新；运行宏时如果不关闭，会发现表格随着数据的输出在自动移动，关闭后就不会移动了。

处理工作表和单元格时，除非需要，不要去选择单元格（相当于用鼠标点先单元格），比如我们要在1单元格写入123，录制宏的代码如下：

Rnge（"1"）.Select

ctiveCell.FormulR1C1="123"

其中，.Select就是单元格对象被选择到，其实这个代码可以改写为：

Rnge（"1"）.FormulR1C1="123"或Rnge（"1"）="123"

使用数组来处理数据组；

尽可能少的使用循环语句

其他，等等。

三、实例设计讲解

1、编写目标

我们现在需要编写一个计算矩形面积（或长方形体积）的自定义函数。

2、查找相关资料

查找到矩形面积的计算公式为：

长×宽

长方体的体积公式为：

长×宽×高

3、分析

计算公式中，长、宽、高都是变量，可以分别设为变量1、变量2、变量3

其中：

参数高可以有，也可以没有

计算结果根据需要可以是公式的结果（数值）也可以是公式本身（文本），可设为变量4

4、过程结构设计

可以制作一个步骤流程图，让代码编写的思路更加清楚。

5、编写代码

①声明自定义函数名称

声明：

就是告诉程序，这个XX是专属名称，在本程序中只能用来表示XXXXX。

语法为：

Function函数名称（必要参数,Optionl可选参数）定义函数的数据类型过程代码

[ExitFunction]

过程代码

EndFunction

说明：

Function：

起始句，使用Function声明以下过程为自定义函数。

函数和参数其实都是变量，所以，它们名称的命名规则都与变量名称的命名规则相同。

同变量一样，如果需要的话也可以对函数定义数据类型，也可以不用定义，那么，函数的数据类型就是“用户自定义”类型。

可以没有参数，那就如同工作表函数PI（）一样，它输出的结果就始终只有一个，即它就是一个常量。

可以只有必要参数，也可以只有可选参数。

如果必要参数和可选参数都有，那么，可选参数必须放在必要参数后面，可选参数必须使用Optionl声明。

可选参数可以指定一个常量，表示在没有写入该可选参数时，默认它为这个常量，比如Optionl可选参数=3.5；必要参数不能指定常量。

参数都可以声明数据类型，也可以不用声明，那么，未声明数据类型的参数就是“用户自定义”类型。

[]是常规的表示方法，表示其中的内容可以有，也可以没有。

ExitFunction：

在过程中任意位置，都可以使用该语句结束该函数的计算过程。

EndFunction：

函数过程的最终结束处。

过程：

从Function起始到EndFunction结束，其中所有的内容称之为过程。

②设置参数

FunctionYJX（CsSingle,KsSingle,OptionlGsSingle,OptionlJGsInteger=0）

…

EndFunction

说明：

本次用YJX表示函数名称，C表示长，K表示宽，G表示高，JG表示结果样式其中，G（高）和JG（结果样式）参数不一定存在，所以声明为可选参数。

参数在声明数据类型时，语句必须是：

变量名s类型名称

很显然，C（长）、K（宽）、G（高）都必须是数值，所以定义为Single（小数）

JG（结果样式）用整数来表示就足够了，我们可以设置为：

当JG=0时，输出公式的计算结果，当JG=1时，输出公式本身。

因为我们设置了OptionlJGsInteger=0，即没有这个参数时，就默认为输出计算结果。

因为该函数的输出结果可能是一个数值，也可以是一个文本，所以，不对该函数进行数据类型的声明。

我们在工作表中实际使用这个函数时，公式写法就是：

=YJX（长,宽[,高][,结果样式]）

注意：

当你计算的是矩形面积时，而且需要输出计算公式时，是没有高这个参数的，那就应该这样写：

=YJX（3,4,,1）——表示输出长=3宽=4的矩形面积公式

即，后面的可选参数没有时可以不管，但前面的可选参数没有时，必须加个逗号表示。

③定义变量

起始行以下，紧接着就是设置本过程的变量，当然，如果计算过程中不需要其他的变量，也可以不设置，本次设置一个变量GS,该变量是程序在内存处理数据中使用，使用者在工作表使用函数时，不会涉及到。

FunctionYJX（CsSingle,KsSingle,OptionlGsSingle,OptionlJGsInteger=0）

DimGS

…

EndFunction

说明：

定义变量的语句为：

Dim变量名称s数据类型名称

也可以使用类型通配符来表示，比如，DimGS%，表示GS是一个整型变量。

当不确定GS只附值整数时，可以不用指定，让它成为一个用户自定义类型。

④检查错误

检查输入的参数是否正确，以及数据关系是否正常，是保证程序在处理数据时，不会因为数据错误而导致程序中断的必要保障，是任何一个程序员编程时必需要做的功课。

假如我们的函数是这样的一个写法：

=YJX（3,将军），即实际的计算公式为：

3*将军，你认为它能计算出结果么？

所以，要养成在计算之前先检查变量错误的良好习惯，这样编写出来的代码才不会有BUG。

实际上，在自定义函数中，已定义了数据类型的变量是可以不用检查的，如果你已经定义了参数是Single（小数），而你输入的是String（文本）时，那程序会马上中断进程，工作表中会显示#VLUE!

；如果你没有定义参数类型，即参数为用户自定义类型时，程序会使用该变量继续计算，直到发生错误后中断进程，但这样一来加重了系统的负担，让它多运算了一些内容，虽然表面上看起来，似乎也没有什么差别，但如果一个计算表中，有一万个函数在同时运算时，这种差别（计算延时）就很明显了。

本函数中，矩形或长方体的长宽高参数都应该是＞0的，但如果参数的是负值，我们则认为是错的，但负值也是数值，程序可不知道你是怎么想的，如果你不要求程序检查这个错误，程序就认为参数是正确的，依旧会计算出结果来。

所以必须添加对参数是否为负值的检查代码：

IfIsMissing（C）=FlsendC1ThenYJX=1/0:

ExitFunction

如此，本节的代码如下：

FunctionYJX（OptionlCsSingle,OptionlKsSingle,OptionlGsSingle,OptionlJGsInteger=0）

DimGS

IfIsMissing（C）=FlsendC0ndJG1ThenYJX=1/0:

ExitFunction

…

EndFunction

⑤可选参数的推断及计算过程

计算时，我们必须要考虑可选参数对计算的影响，因为有可选参数与没有可选参数会影响到我们实际的计算需要。

因为我们已经设置了OptionlJGsInteger=0，即无论我们在工作表中写不写这个参数，它实际都存在，我们只是需要推断它是0还是1即可。

而高却可能是不存在的，所以我们现在只需要推断参数G是否存在就可以了：

IfIsMissing（G）Then

GS=C&"*"&K

Else

GS=C&"*"&K&"*"&G

EndIf

说明：

如果这个函数只需要输出公式的结果（数值），那我们可以不需要GS变量，代码可以直接写为：

IfIsMissing（G）Then

YJX=C*K

Else

YJX=C*K*G

EndIf

就因为我们还想着要输出计算公式，所以，要先用变量GS来“装载”计算公式，无论最终的计算公式是什么，我们在最后直接处理这个GS的内容就可以得到结果了。

&是连接符，表示把两个内容前后连接在一起。

比如：

3&"*"&4，表示“3*4”，如果是3*4就表示其计算结果12了；变量在和文本连接时，文本需要用双引号""括起来。

如此，本节的代码如下：

FunctionYJX（CsSingle,OptionlKsSingle,OptionlGsSingle,OptionlJGsInteger=0）

DimGS

IfIsMissing（G）Then

GS=C&"*"&K

Else

GS=C&"*"&K&"*"&G

EndIf

过程代码

EndFunction

⑥输出结果

现在，我们有了计算公式，下一步就是根据实际需要来处理这个公式内容了：

IfJG=0Then

YJX=Evlute（"="&GS）

ElseIfJG=1Then

YJX=GS

EndIf

说明：

以上代码表示，当JG=0时，函数输出公式的计算结果（数值），当JG=1时，直接输出公式。

Evlute（）是宏表函数，其作用是返回一个计算公式的结果，注意其参数为"="&公式。

如此，全部的函数代码就完成了：

FunctionYJX（OptionlCsSingle,OptionlKsSingle,OptionlGsSingle,OptionlJGsInteger=0）

DimGS

IfIsMissing（C）=FlsendC0ndJG1ThenYJX=1/0:

ExitFunction

IfIsMissing（G）Then

GS=C&"*"&K

Else

GS=C&"*"&K&"*"&G

EndIf

IfJG=0Then

YJX=Evlute（"="&GS）

ElseIfJG=1Then

YJX=GS

EndIf

EndFunction

⑦调试

下面，我们回到工作表中，测试一下这个函数的效果。

为什么会发生这种情况呢？

原来，在D2和E2单元格的公式中，我们引用了C2（高）这个参数，而C2的内容是空，VB认为空单元格的值为0，于是，这个公式的实际内容其实成了“=3*4*0”。

如果我们把D2单元格的公式改为“=YJX（2,B2）”倒是也能计算出结果来，但D3单元格的公式却必须为“=YJX（3,B3,C3）”，如此，我们就不能使用工作表的拖拽功能了，这样使用起来，就非常的麻烦，所以，我们即然是在编写VB的自定义函数，就应该考虑到应用的对象是工作表，参数可能是引用单元格的特别性。

所以，我们需要把代码修改为：

IfIsMissing（G）Then

GS=C&"*"&K

Else

GS=C&"*"&K&"*"&IIf（G=0,1,G）

EndIf

说明：

IIf（）为VB函数，与工作表函数IF（）类似，它就相当于简单版的If…Then…Else语句。

这里把推断参数G，当单元格为空时（即G=0），我们就让它等于1，因为任何数值乘以1都等于那个数值，如果单元格有数值，再乘以那个数值。

下面，我们再来看看效果。

这次，计算结果完全正确，但是…

D2单元格的计算公式却为“3*4*1”，可实际C2单元格并没有内容，看来还得修改一下：

IfIsMissing（G）Then

JX01: