003.VBA过程和函数

 

大家好，我是永不止步的老牛。

上一篇我们介绍了VBA的编辑器，本篇我们介绍VBA过程和函数以及MsgBox函数、InputBox函数、InputBox方法(用代码给单元格设置公式)。

前面文章提到过，过程和函数就是执行某些动作的代码组合，在程序运行时完成具体的任务。

过程以Sub开头，以End Sub结束，过程执行一些代码但不返回值。

函数以Function开头，以End Function结束，函数执行一些代码并返回值，函数可以从过程中执行，也可以在Excel工作表中使用，就像Excel内置函数一样在公式中直接使用，Excel中内置了很多函数，但是总有内置函数无法实现的需求，我们可以自己编写代码去实现，建立一些Excel没有的函数。

我们以一个例子开始我们今天的学习，身体质量指数BMI是常用的衡量人体肥胖程度和是否健康的重要标准，BMI正常范围为18.5-23.9，低于18.5为体重过轻，高于23.9为体重过重，BMI=体重（公斤）除以身高的平方（米的平方）。

1.编写一个过程

• 新建一个文件“过程和函数.xlsx”并打开

• 打开VBA编辑器并选择VBAProject(过程和函数.xlsx)

• 在属性窗口，将VBAProject改成SubAndFunction

• 在工程资源管理器窗口中右键，选择“插入”-“模块”

• 在属性窗口将“模块1”改成“BMI计算”

• 在工程资源管理器中，选中“MI计算”，点击顶部菜单“插入”-“过程”

在名称处输入“CalculateBMI”，点击确定，关闭添加过程窗体，VBA代码窗口增加了一个过程：

Public Sub CalculateBMI()

End Sub

第一句声明过程名称，默认是关键字Public，表示这个过程可以在所有模块的所有过程里访问。关键字Public是可选的。关键字Sub后面是过程名称CalculateBMI和一对空括号。在括号里你可以添加需要传递的参数。过程都要以End Sub语句结束。

如果把Public替换成Private，那么过程只能被“BMI计算”模块里的其它过程调用，而不能被其它模块里的过程调用。

我们在过程体内，就是Public Sub CalculateBMI()和End Sub之间，输入计算BMI的代码，首先定义3个变量BMI、Height和Weight，并给Height和Weight赋值，然后计算BMI数值并将BMI值输出至立即窗口，最后用MsgBox函数提示计算完成及BMI值。

Public Sub CalculateBMI()

Dim BMI As Single 'BMI值

Dim Height As Single '身高值

Dim Weight As Single '体重值

Height = 1.81

Weight = 66

BMI = Weight / (Height) ^ 2

Debug.Print BMI

MsgBox "身体质量指数BMI计算完成，BMI为" & BMI, vbOKOnly + vbInformation, "提示"

End Sub

将光标定位在过程代码的任何地方，按F5运行过程，立即窗口会输出BMI值，并弹出对话框显示BMI值。

在这个过程中用Dim声明变量并明确数据类型是Single，用=将右边的值赋给左边的变量，

“/”是除运算符，“^”表示计算平方，Debug.Print BMI是将变量BMI的值输出到立即窗口，MsgBox是对话框函数，提供给用户一些信息，并支持人机交互。

2.使用MsgBox函数

MsgBox函数语法为：MsgBox(prompt[, buttons] [, title] [, helpfile, context])

[]内的参数可以省略，具体参数的含义、参数数值、返回值后面用表格列举，MsgBox函数可以有返回值，有2种调用方式：

A. MsgBox "消息", vbOKOnly, "提示"

B. iReturn =MsgBox("确认删除该条数据吗？", vbYesNo + vbQuestion, "提示")

A相当于只是在界面上显示了一个对话框，提示用户一些信息，用户点击按钮后不做任何处理。

B相当于在界面上显示了一个对话框，将用户点击不同的按钮的值返回给变量iReturn，然后程序可以根据iReturn的值决定后续代码如何执行。

MsgBox 函数参数说明如下图：

 buttons参数值设置如下图：

 buttons参数的

• 第一组值 (0-5) 表示对话框中显示的按钮的数量和类型；

• 第二组值（16、32、48、64）表示图标样式；

• 第三组值（0、256、512）用于确定默认按钮；

• 第四组值（0、4096）用于确定消息框的形式。

用每组数字的一个值加起来就是最终buttons的值。

返回值如下图：

 MsgBox函数我们先介绍到这里，我们继续我们的编程，前面编写的代码中我们直接给定了身高和体重数值，我们现在需要让用户输入身高和体重，我们将代码修改成这样：

Public Sub CalculateBMI()

Dim BMI As Single 'BMI值

Dim Height As Single '身高值

Dim Weight As Single '体重值

Height = InputBox("请输入自己的身高")

Weight = InputBox("请输入自己的体重")

BMI = Weight / (Height) ^ 2

Debug.Print BMI

MsgBox "身体质量指数BMI计算完成，BMI为" & BMI, vbOKOnly + vbInformation + vbMsgBoxRight, "提示"

End Sub

将光标定位在过程代码的任何地方，按F5运行过程，会先弹出对话框要求输入身高，输入并确定后，再弹出对话框要求输入体重，输入并确定后，立即窗口会输出BMI值，并弹出对话框显示BMI值。这里我们使用了InputBox函数让用户输入信息。

3.使用InputBox函数

InputBox函数语法：InputBox(prompt, [ title ], [ default ], [ xpos ], [ ypos ], [ helpfile, context ])

[]内的参数可以省略。

Prompt：显示在对话框上的信息。

Title：对话框的标题，默认的标题是Mictosoft Excel。

Default：文本框里显示一个默认值，如果忽略，显示空白文本框。

xpos和ypos：对话框在屏幕上出现的位置，如果忽略，显示在当前窗口的中央，xpos决定对话框在屏幕上从左起的水平位置，忽略它时，对话框显示在水平中央，ypos决定对话框在屏幕从上而下的竖直位置，忽略它时，对话框就在竖直大约三分之一的位置。

helpfile和context：和本章前面介绍的MsgBox函数相应的参数使用方法一样。

具体参数说明见下表：

 明白InputBox函数的用法后，我们将上述代码再修改一下，不使用InputBox函数的默认标题“Mictosoft Excel”。

Public Sub CalculateBMI()

Dim BMI As Single 'BMI值

Dim Height As Single '身高值

Dim Weight As Single '体重值

Height = InputBox("请输入一个数值", "输入自己的身高")

Weight = InputBox("请输入一个数值", "输入自己的体重")

BMI = Weight / (Height) ^ 2

Debug.Print BMI

MsgBox "身体质量指数BMI计算完成，BMI为" & BMI, vbOKOnly + vbInformation + vbMsgBoxRight, "提示"

End Sub

4.编译一个函数，并在过程及公式中使用

过程我们先介绍到这里，我们看一下函数，函数也是一种过程，只是函数能返回值，函数只能由过程调用或在Excel工作表的的公式中使用，而不能像过程一样用F5或菜单“运行”执行。

函数的建立可以像前面介绍的建立过程一样，通过菜单“插入”-“模块”，选择“函数”来建立。

也可以自己手工编写代码来建立，我们在模块“BMI计算”的代码窗口，直接输入如下代码：

Public Function GetBMI(w, h As Single) As Single

GetBMI = w / (h) ^ 2

End Function

关键字Function后面是函数名称GetBMI和一对空括号。括号里的w和h是传递给函数的参数，函数以Function开头，以End Function语句结束。

Public表示这个函数可以在所有模块的所有过程里访问，在Excel公式中也可以使用，如果将Public换成Private，那么函数只能被同一模块里的其它过程调用，而不能被其它模块里的过程调用，也不能被Excel公式中使用。

最后面的As Single表示函数返回值的数据类型，如果省略，默认成Variant。

函数的返回值就是把要返回的内容赋值给函数名称。

如果给参数前加关键字Optional，那么表示这个是可选参数，就是说调用函数时，可以传递这个参数值，也可以不传递，注意的是，如果某个参数设置成可选参数，那么这个参数之后的参数必须都是可选参数。

函数我们先简单介绍这么多，还有按地址和按值传递参数等内容，后续我们用到时再细说。

写好函数后，我们可以修改前面的过程CalculateBMI，在过程中调用函数GetBMI，代码如下：

Public Sub CalculateBMI()

Dim BMI As Single 'BMI值

Dim Height As Single '身高值

Dim Weight As Single '体重值

Height = InputBox("请输入一个数值", "输入自己的身高")

Weight = InputBox("请输入一个数值", "输入自己的体重")

BMI = GetBMI(Weight, Height)

Debug.Print BMI

MsgBox "身体质量指数BMI计算完成，BMI为" & BMI, vbOKOnly + vbInformation + vbMsgBoxRight, "提示"

End Sub

F5运行，结果和刚才的一样。

我们看一下Excel公式中如何引用函数GetBMI。

大家现在已经了解了过程和函数的写法，以及MsgBox函数InputBox函数的用法。

5.使用InputBox方法

前面说的InputBox函数属于VBA库，在Excel库中有一个InputBox方法，大家可以在对象浏览器中搜索InputBox，搜索结果可以看到2个InputBox，分别属于VBA和Excel。

 

InputBox方法的语法：expression.InputBox (Prompt, Title, Default, Left, Top, HelpFile, HelpContextID, Type)

expression：表示 Application 对象的变量。

参数说明如下图： 

 除了最后一个参数Type，其余参数基本和InputBox函数对应，我们重点说一下Type参数，Type参数的值为下表中的数据：

 Type设置可以允许用户输入的数据类型，可以是一个值，也可以将多个值相加。 假如允许输入文本和数字，Type就设置为 1 + 2。

如果 Type 为 0，InputBox 返回文本格式的公式，如果 Type 为 4，InputBox 返回True或False，如果 Type 为 8，InputBox 返回 Range 对象，如果是8， 必须使用 Set 语句，将结果分配给 Range 对象，

如果不使用 Set 语句，此变量就会被设置为区域中的值，而不是 Range 对象本身。

如果使用 InputBox 方法提示用户输入公式，必须使用 FormulaLocal 属性，将公式分配给 Range 对象。

InputBox 方法与 InputBox 函数的区别在于，前者可以对用户输入进行选择性验证，并能与 Excel 对象、错误值和公式结合使用。 Application.InputBox 调用的是 InputBox 方法；不带对象限定符的 InputBox 调用的是 InputBox 函数。

如果用户输入的内容和Type设置的不相符，会出提示，确认后，继续等待用户输入。

用下面的示例展示一下Type 为 8时，使用和不使用Set的区别，代码如下，区别效果见动图：

Public Sub TestInputBox()

Dim Value

Dim Value2

Set Value = Application.InputBox(Prompt:="请选择单元格", Type:=8)

Set Value2 = Application.InputBox(Prompt:="请选择单元格", Type:=8)

MsgBox Value

End Sub

我们在演示一下Type为0时，如何给一个单元格设置我们动态输入的公式，代码如下，效果见动图：

Public Sub TestInputBox2()

Dim Value

Value = Application.InputBox(Prompt:="请输入BMI公式", Type:=0)

Sheet5.Range("F7").FormulaLocal = Value

End Sub

VBA过程和函数以及MsgBox函数、InputBox函数、InputBox方法介绍到这里，今天的内容有点多，大家最好能自己实际练习一下，下一篇文章我们介绍Excel宏。