编写Function过程开发函数

概述：function过程即自定义函数，在插件中应用极广。应用范围小，过程仅仅能返回一个只或者多个数的组合，而sub过程既可以返回值，还可以对引用的对象进行修改，例如，引入单元格A1的值，对A1设置心的格式。Function可以获取到工作名称，但无法修改工作表的名称，funcion过程可以不实用参数，类似于工作表函数Rand和Now等。但绝大部分是需要一个参数或者多个。最大达到255个参数。

一、Function语法解析

[public | private | friend ] [static] function name [(arglist)][as type]

         [statements]

         [Name=expression]

[Exit function]

[statements]

         [name=expression]

End function

Public 可选，表示所有模块的所有其他过程都可以访问这个function过程，如果是在包含option private的模块中使用，则这个过程在该工程外是不可使用的

Private 可选的，表示只有包含其声明的模块的其他过程可以访问该function

Friend  可选的，智能在类模块中使用，表示该function过程在整个过程中都是可见的，但对于对象实例的控制者是不可见的

Static        可选，表示在调用之间将保留function过程的局部变量值，static属性对在该function外声明的变量不会影响，即使过程中也使用了这些变量

Name      必须的。Function的名称

Arglist      参数

Expression        可选的，function的返回值，函数名=值，将返回这个值

        

         调用方式：在工作表中通过公式调用，想内部函数一样在工作表中使用，也可以嵌套

         被其他过程调用。VBA.函数名(参数)

         跟sub过程一样都可以实现递归

二、过程的参数

a)        Sub过程的参数及应用

                        i.             参数语法：

[Optional] [ByVal | ByRef] [ParamArray] varname[()] [astype][=defaultvalue]

Opntional 可选，表示参数不是必须的关键字，如果使用了该选择，则后续参数都必须是可选的，而且必须都使用optional关键字声明，如果使用了paramarray 则任何参数都不能使用optional

ByVal 可选的  表示该参数按值传递

ByRef        可选表示该参数按地址传递，ByRef是默认的

ParamArray     可选，只用于arglist的最后一个参数，致命最后一个参数一个variant元素optional数组，使用paramArray关键字可以提供任何数据的参数。ParamArray关键字不能写ByVal ByRef或optional一起使用

Defaultvalue 可选的，任何常数或常数表达式，只对optional参数合法，如何类型为Object，则显示的默认值只能是nothing

验证操作权限案例

Sub 姓名(name AsString)

Dim i AsByte, rng As Range

For i = 1To Sheets.Count

    If ThisWorkbook.Sheets(i).name = "许可人员列表"Then: GoTo OK

    Next i

    MsgBox "不存在许可人员列表", 64

    Exit Sub

OK:

    If Len(name) < 2 Or Len(name) > 10Then MsgBox "长度智能是2到4，请重新录入", 64: Exit Sub

    Set rng = ThisWorkbook.Sheets("许可人员列表").Range("a1:a10").Find(name)

    If rng Is Nothing Then MsgBox "你无权操作"Else MsgBox "你具有操作权限"

End Sub

Sub 确认权限一() '手工指定姓名

Call 姓名(Application.InputBox("请输入姓名","确认权限", "", , , , , 2))

End Sub

Sub 确认权限二() '以当前表A1的值进行判断

    Call 姓名(ActiveSheet.Range("A1"))

End Sub

Sub 确认权限三()

    MsgBox Application.UserName

    Call 姓名(Application.UserName)

End Sub

                      ii.             按值传递和按地址传递。

Byval 变量 as long

Byval 表示参数按值传递。不能改变主程序传过来的实参

ByRef 表示参数按地址传递   可以改变主程序传过来的实参  默认

b)        Function过程的参数

Function只能传过来来实参的引用，不能更改传的实参的属性和值，可以返回对应的值

c)        开发自定义函数

                        i.             开发不带参数的function过程

1.        获取本机IP地址

Function IP() '获取IP地址

    Dim item

    '通过WMI技术获取网卡当前设置的IP地址

    For Each item InGetObject("winmgmts:\\" & "." &"root\cimv2").ExecQuery("select * fromWin32_NetworkAdapterConfiguration")

    If TypeName(item.IPAddress) <>"null" Then IP = item.IPAddress(0)

    Next

End Function

2.        返回有公式的单元格地址

FunctionFunAdd()

Dim rngAs Range, cell As Range

For Eachrng In ActiveSheet.UsedRange    '遍历当前表的已用区域

Ifrng.HasFormula Then '如果有单元格公式

Ifrng.Address <> Application.ThisCell.Address Then '如果变量rng的地址不等于当前公式所在的单元格的地址

If cellIs Nothing Then '如果变量cell为初始化

    Set cell = rng

    Else

    Set cell = Appcation.Union(cell, rng) '将变量cell与rng所代表的两个单元格对象合并为一个对象，赋值给cell

    End If

End If

End If

Next rng

 

If cellIs Nothing Then FunAdd = "" Else FunAdd = cell.Address(0, 0)  '返回结果

 

End Function

                      ii.             开发带有一个参数的function过程

1.        将人民币金额转换成大写

Function 大写(cell As String)

    Dim rmbs As String

    If cell = "" Or NotIsNumeric(cell) Then 大写 = "": Exit Function '如果参数为空，或者不是数值就返回空退出过程

    If cell = 0 Then 大写 = "零元整":Exit Function '如果参数为0则返回字符串并退出

    '将数值转换成中文大些，并将点替换成元，将负号替换成负

    rmbs = Replace(Replace(Application.Text(Round(cell,2), "[DBnum2]"), ".", "元"),"-", "负")

    '加入角与分，同事将最后的"零"替换成元整

    rmbs = IIf(Left(Right(rmbs, 3), 1) = "元",Left(rmbs, Len(rmbs) - 1) & "角" &Right(rmbs, 1) & "分", IIf(Left(Right(rmbs, 2), 1) = "元", rmbs& "角", IIf(rmbs = "零","", rmbs & "元整")))

    '将零元和零角替换成空

    rmbs = Replace(Replace(rmbs, "零元",""), "零角", "")

    大写 = rmbs '返回

   

End Function

2.        建立工作表目录

Function 工作表(Optional 序号) As String '声明函数，有一个参数为可选参数

Application.Volatile'声明为易失性函数

'如果未输入参数，则赋予变量序号为当前表的地址

If IsMissing(序号) Then 序号 =ActiveSheet.Index

If 序号 >Sheets.Count Then '如果参数大于工作表数量

工作表 = ""

Else

工作表 = Sheets(序号).name

End If

End Function

公式：

=HYPERLINK("#" & 工作表(ROW(A2))& "!A1",工作表(ROW(A1)))

3.        关机函数

Function 关机(Optional Close_Time As Byte = 10)

关机 = Close_Time

Shell"shutdown -s -t" & Close_Time '在指定的时间内关闭计算器，调用dos命令

End Function

                            方法收获：

                                     IsNumeric  哟关于判断参数是否是数字

                                     Replace     是用于替换的函数，但它与工作表函数replace有极大不同，与substitute函数极其相近

                                     IsMissing 用于判断函数的可选参数是否已经传递给过程。

                                     Index              属性则是值工作表在所有工作表中的序号左---右

                                     Shell   操作Dos命令，传递dos命令即可

 

                     iii.             开发带有两个参数的function过程

1.        对系统导出的数据分列，有两个参数，第二个参数为可选参数

'第一个参数为单元格引用，第二参数表示取分列后的第几列

Function Breakdown(rng As Range, Optional style As Byte = 1) AsString

Application.Volatile

On ErrorResume Next '防错

Dim i AsInteger, str As String

'将单元格的值以逗号为分隔符转换成一位数组，再从数组中取字符串赋值给变量str 取值的位置取决于第二个参数

str = Split(rng.Text, ",")(WorksheetFunction.RoundUp(style/ 3, 0) - 1)

If styleMod 3 = 1 Then '如果第二个参数除以3余数是1

For i = 1To Len(str) '遍历str的每一个字符

    If IsNumeric(Mid(str, i, 1)) Then ExitFunction '如果遇到数字，结束过程

    Breakdown = Breakdown & Mid(str, i, 1)'将取出的所有字符串连接起来左右返回值

    Next i

ElseIfstyle Mod 3 = 2 Then '如果参数除以3余数是2

    For i = 1 To Len(str)

    '如果遇到数字或者小数点，就取出并串联起来作为返回值

    If VBA.IsNumeric(Mid(str, i, 1)) Or Mid(str,i, 1) = "." Then Breakdown = Breakdown & Mid(str, i, 1)

    Next i

ElseIfstyle Mod 3 = 0 Then

    For i = 1 To Len(str) Step -1 '遍历str的每一个字符， 从右向左

    If IsNumeric(Mid(str, i, 1)) Then ExitFunction '如果遇到数字就结束过程

    Breakdown = Mid(str, i, 1) & Breakdown

    Next i

End If

If Err<> 0 Then Breakdown = "" '如果有错误就返空

End Function

调用：=Breakdown($A3,COLUMN(A1))

2.        中国式排名

Function 排名(区域, 成绩) '声明函数，有两个参数

Application.Volatile

Dim dicAs Object, rng, i As Integer '声明变量，包括字典对象

Set dic =CreateObject("scripting.dictionary") '声明字典对象变量

For Eachrng In 区域 '遍历区域

'如果变量rng等于成绩则为变量i 赋值1，如果变量rng大于成绩，则将rng的值追加到字典中

If rng = 成绩 Then i = 1Else If rng > 成绩 Then dic(rng * 1) = 1

Next

'如果变量i大于0，则区域中有数据等于成绩，那么排名结果等于字典中的数量+1 (字典对象是忽略重复值)

If i >0 Then 排名 = dic.Count + 1 Else 排名 = "超出范围" '如果成绩与区域中任何数据都不想等则返回超出范围

End Function

CreateObject(“scripting.dictionary”)用于创建一个字典对象，它的特点是成员不重复，而中国式排名，是需要忽略重复值的，即四人中第一人100分算第一名，两个99分并列第二名。

函数的两个参数都支持手动录入参数，而非仅仅限于单元格引用

Split   是一个数组函数，它可以将一个字符串按某个字符为分隔符转换成一个数组。

                     iv.             开发两个带有可选参数的function过程

声明方式：function col(optional rng as range,optional style as string=”A”)  声明函数名称，有两个可选参数   后面=“A” 方便于判断用户是否输入值

收获：

函数中费对象变量被忽略时，可以用isMissing来判断，如果是单元格对象，只能用nothing来判断。If rng is nothing

Address属性的两个参数使用0时可以将地址转换成相对引用，这有利于获取列标

日期类型的变量。不能用nothing来判断。判断是否等于0

                      v.             开发带有不确定参数的function过程

声明方式  functionConnect(ParamArray  rng() as Variant)  有多个参数，包括1-255个，在处理的时候使用for循环遍历。这个数组的大小使用UBound函数获取

收获：

UBound  返回一个 Long 型数据，其值为指定的数组维可用的最大下标

For i=0 To UBound(rng) 因为是变体量，for循环时默认下标应该为0

声明不确定参数规则：

1.      所声明的参数必须位于最后位置

2.      声明的参数必须是Variant数据类型

3.      Application的Intersect的作用是让函数只计算数据区域与参数所有代码区域的重叠去，防止整列，整行或者整个工作表作为参数造成死机，但它同事也带来了一个缺点参数智能引用本工作表的区域，一用其他工作表或者工作簿区域时，将忽略

                     vi.             开发带具有三个参数切第三个为可选的function过程

声明方式：function 三餐(条件区 as range,颜色单元格 as range,optional 统计区)

收获：

计算单元格背景色必须使用Color，不能用ColorIndex。在2003中可以使用。但在2010中不能使用

Range.Resize(num1,num2)属性用于调整指定区域的大小。 参数1表示新区域的行数，新区域中的列数，如果忽略。则不变

ReDim Preserve array(1 To 2)没找到一个目标就需要重置数组的大小，且重置时需要保留原数组的值，所以循环中必须加入 ReDim Preserve 来声明数组

Transpose(数组)  将横向数组专置为纵向数组

Vlookup 函数  只能返回一个符合条件的目标值

三、编写函数的帮助

当你在模块或者其他地方编写了函数，需要让用户使用的时候，用户双击进去，帮助信息空白。那么给用户带来了很大的不便，所以需要编写函数的帮助信息

Application.MacroOptions方法编写帮助信息

Application.MacroOptions(macro,Description,HasMenu,MenuText,HasShortcutKey,ShortcutKey,Category,StatusBar,HelpCountextId,HelpFile)

Macro      宏的名称或用户定义的函数的名称

Description      宏的描述

hasMenu          忽略该参数

MenuText         忽略该参数

HasShortcutKey       如果为true，则为宏指定一个快捷键，必须指定shortcutKey。如果该参数为false，则不为宏指定快捷键。如果宏已经有快捷键，如果指定false就失效

ShortcutKey     如果上面参数为true，必须指定快捷键

Category           一个指定现有的宏函数类别的整数，如果提供了一个字符串，它将作为类别名称显示在插入函数的对话框中。如果此类别名称未使用过，则用该名称定义一个新的类别，如果使用的类别名称与某个内置名称相同，将把用户定义的函数映射到该内置类别

statusBar    宏的状态栏文本

HelpCountextId        一个指定分配给宏帮助主题上下文id的整数

HelFIie               包含HelpContextId定义的帮助主题的帮助文件名

ArgumentDescriptions             函数参数 对话框中显示的参数的描述

 

四、总结

Function过程即自定义函数，根据工作需要可以开发自己专用的函数

 

对于函数的运算速度，内部的工作表函数一定快于用户自定义的函数。

 

开发自定义函数的时候，应该尽量给用户一些可选项，使函数运算更简单，公式也更简短。参数可选的概念

 

对于某些运算结果，如果可以返回多个结果，应尽可能将所有格式全部罗列出来，让用户选择。例如本次的日期函数

 

对于有多个结果的函数，应将函数声明为数组，将所有结果显示给用户，但为了体现灵活性，同事需要指定一种默认显示的值。例如自定义函数VlookupCol。

 

为了让用户自定义的函数在任何工作簿都可以使用，应将工作簿为加载宏文件。并对齐加载或者至于自启动文件夹。