Excel 2010 VBA 入门 110 获取最小值的自定义函数

目录

例    

码

使用ParamArray关键字声明数组参数

IsMissing函数

IsArray函数

函数的嵌套与递归

---

​​​​​​​

例    

在VBA编程过程中，经常会使用工作表函数Min来确立某组数中的最小值。但是工作表函数Min无法适用于二维以上的数组，也无法适用于某一维的大小超过65536的数组。

码

    编写含有两个参数的自定义函数，第1个参数为变体类型，第2个参数为ParamArray关键字所指定的不确定数量的参数数组。假设第1个参数所代表的数值为最小值，然后遍历第2
个参数所表示的数组中所有的元素，包括作为元素的数组中的元素，当找到比最小值小的数字，则选取该数为最小值。

Option Explicit
Sub GetMin()
    Dim arr()
    arr = Range("A2:A1000000").Value
    '下面的语句会出错
    'debug.print worksheetfunction.min(range("A2:A100000").value)
    
    Debug.Print My_Min(arr(1, 1), arr)
End Sub


Function My_Min(number1 As Variant, ParamArray numbers()) As Variant
    Dim MinNum As Variant
    Dim num1 As Variant
    Dim num2 As Variant
    
    '假设number1最小
    MinNum = number1
    
    '若省略第2个参数
    If IsMissing(numbers) Then
        My_Min = MinNum
        Exit Function
    End If
    
    '遍历行
    For Each num1 In numbers
        '若行>1
        If IsArray(num1) Then
            '遍历列
            For Each num2 In num1
                If num2 < MinNum Then My_Min = num2
            Next num2
        Else
            If num1 < MinNum Then My_Min = num1
        End If
    Next num1
    
    My_Min = MinNum
    
End Function

使用ParamArray关键字声明数组参数

    当使用ParamArray关键字标注某个数组参数时，调用该函数时，可以使用逗号隔开各个参数，从该参数位置起，之后的参数输入将以一个以0为下限的一维数组传递给该程序。而且以ParamArray关键字标记的参数始终是以数组形式传递，即使该参数传递的元素个数为1。因而以ParamArray标记的参数可以作为一个一维数组处理。
    当调用含有ParamArray关键字所指定的参数时，该参数可以省略，也可以是数组。作为函数时，必须能够处理这些数据。

IsMissing函数

    IsMissing函数用来判断自定义函数或过程中的参数在调用时是否被省略。当该参数省略时，函数返回True，反之返回False。IsMissing函数的语法如下：

IsMissing(arcgname)

参数argname为某个参数的名称。
    当需要使用IsMissing函数判断某个参数是否省略时，则在声明自定义函数或过程时，必须满足以下两个条件。

• 该参数以Optional关键字或ParamArray关键字标记。
• 该参数的数据类型是Variant类型。

    本例中，由于参数Numbers是可省略的参数，因而需要使用IsMissing函数判断其是否省略，以避免发生错误。

IsArray函数

    IsArray函数可以用来判断某个变量是否为数组。当该变量为数组时，函数返回True，反之返回False。其语法为：

IsArray(varname)

参数varname为变量名。
    本例中，numbers参数为一个一维数组，其所含的元素可以为值，也可以为另一个数组。因而，当循环遍历numbers数组中的各个元素时，首先应判断其是否为数组。当元素是数组时，则必须对该元素再次进行遍历。本例中由于无法得知数组的维数，因而一般采用For Each---Next循环进行遍历。
知识扩展

函数的嵌套与递归

    本例中，为了遍历所有参数中的数字，需要对数组中的元素进行判断，当其为数组时，需要遍历数组中的元素，而当内层数组中的元素再次为数组时，则又需要对该数组进行遍历循环。
当数组数量不确定时，则无法确定需要多少次循环的嵌套。此类情形可以使用函数的嵌套，下面将本例更改为以下代码：

Option Explicit
Sub GetMin()
    Dim arr()
    arr = Range("A1:A1000000").Value
    
    Debug.Print MyMin2(arr)
End Sub

'递归方法获取最小值的自定义函数
Function MyMin2(ParamArray Numbers()) As Variant
    '变量声明
    Dim MinNum      As Variant   '最小值
    Dim Number1     As Variant   'Numbers数组的第一个元素
    Dim Num1        As Variant   '数组中的元素
    Dim Num2        As Variant   '数组中的元素
    
    '获取Numbers数组中的元素
    Number1 = Numbers(0)
    
    '若该元素是数组
    If IsArray(Number1) Then
        '找到数组中的第一个元素
        For Each Num1 In Number1
            '使用MyMin2函数获取该元素的最小值作为当前所有元素的最小值
            MinNum = MyMin2(Num1)
            Exit For
        Next
    Else
        '假设第一个元素为最小值
        MinNum = Number1
    End If
    
    '遍历数组中的所有元素，获取最小值
    For Each Num1 In Numbers
        '若元素是数组，则遍历数组中的元素
        '使用MyMin2获取MinNum与数组的最小值
        If IsArray(Num1) Then
            For Each Num2 In Num1
                MinNum = MyMin2(MinNum, Num2)
            Next
        Else
            '若不是数组，则直接比较，获取较小值
            If Num1 < MinNum Then MinNum = Num1
        End If
    Next
    '结果返回
    MyMin2 = MinNum
End Function

    以上代码使用了函数的嵌套，在该函数内再次调用其函数本身，一旦遇到某个元素为数组的情况，则利用该函数再次计算其数组中的最小值。当数组中的元素为数组时，则会使该函数
循环调用，这种在函数中又调用函数的情况称为递归。递归同样也适用于自定义过程。递归过程与事件的循环触发非常类似。
    当需要在Excel VBA中使用递归过程时，必须注意以下几点。

• 有明确的结束条件。若没有结束条件，则递归会无止境地运行下去。
• 结束条件最终必须会被执行。如定义了某个递归的结束条件变量i为1时结束，而在实际过程中，i起始值为2，并且i每次不断增加，此时由于i永远不会等于1，则程序将会不断地执行下去。
• 递归层数不宜过多。每次递归都会使用堆栈存储现在正在运行的程序，一旦递归层数过多，则会导致堆栈溢出错误。