Excel VBA解读（136）： 在用户定义函数中的变体、引用、数组、计算表达式、标量

发布于

学习Excel技术，关注微信公众号：

excelperfect

在前面的几篇文章中，我们自定义的函数使用定义为Range的参数来从Excel工作表中获取数据，例如：

代码语言：javascript
复制
Function VINTERPOLATEB(Lookup_Value As Variant, _
   Table_Array As Range, _
   Col_Num As Long)

如果在公式中使用单元格区域作为参数来调用该函数，运转得非常好：

=VINTERPOLATEB($H1,$A$1:$C$10000,2)

但是，如果使用计算表达式或者一组常量作为参数，则返回的结果为#Value：

{=VINTERPOLATEB($H1,($A$1:$C$10000*1),2)}

这个公式是数组公式，因此输入完后按Ctrl+Shift+Enter组合键，Excel自动在公式两边添加花括号。

=VINTERPOLATEB(4.5,{1,3,3.5;4,4,4.5;5,4.5,5},2)

这个公式使用了3行3列的数组常量。

Excel在调用函数之前会检测到这些参数不是单元格区域。

通过将参数定义为Variant型而不是Range可以解决此问题：Variant型参数几乎可以包含任何内容！但用户自定义函数现在必须处理Variant可能包含的所有不同类型的数据。

一种简单的方法是将参数声明为Variant型变量：这会将所有内容强制转换为值：

代码语言：javascript
复制
Function TestFunc(theParameter AsVariant)
   Dim vArr As Variant
   vArr = theParameter
   TestFunc = vArr
End Function

在VBE中，在赋值给函数的返回值的语句行设置断点，如下图1所示

图1

输入数组公式：

=TestFunc($A$1:$A$5*1)

本地窗口显示如下图2所示。

图2

在本地窗口可以看到，vArr变量包含2维Variant型数组，子类型为Double。

输入公式：

=TestFunc({1,2,3;5,6,7})

在本地窗口中可以看到其结果也是2维数组：

图3

输入公式：

=TestFunc({1,2,3})

在本地窗口可以看到结果为1维数组：

图4

输入公式：

=TestFunc({1;2;3})

在本地窗口可以看到结果为2维数组：

图5

输入公式：

=TestFunc(15)

可以看到结果为一个标量，而非数组：

图6

如果提供单元格区域作为函数参数：

=TestFunc($A$1:$A$5)

则得到：

图7

注意，theParameter变量包含对象子类型Range，意味着必须将它视为Range变量，而vArr包含从该Range变量中提取的值。

因此，在通用目的的用户自定义函数中，希望使用Variant型参数，并且经常需要确定变体的类型以及上限和下限。

为了获得最大效率，不能只使用vArr=theVariant，因为：

  • 不能使用.Value2，因为它可能不是单元格区域。
  • 在许多情况下，希望在强制转换所有值之前操控Range对象或者操控Range对象而不是强制转换所有值。

因此，这里有一个函数用来确定传递的内容以及它的大小：

代码语言：javascript
复制
Function Variant_Type(theVariantAs Variant)
   Dim jRowL As Long
   Dim jRowU As Long
   Dim jColL As Long
   Dim jColU As Long
   Dim jType As Long
   Dim varr As Variant
    '
    ' theVariant可以包含标量, 数组, 或单元格区域
    ' 找到上限和下限以及类型
    'type=1:单元格区域, 2:2维variant数组,
    ' 3：1-维variant数组(列的单行), 4:标量
    '
   On Error GoTo FuncFail
   jType = 0
   jRowL = 0
   jColL = 0
   jRowU = -1
   jColU = -1
   If TypeName(theVariant) = "Range" Then
        jRowL = 1
        jColL = 1
        jRowU = theVariant.Rows.Count
        jColU = theVariant.Columns.Count
        jType = 1
   ElseIf IsArray(theVariant) Then
        jRowL = LBound(theVariant, 1)
        jRowU = UBound(theVariant, 1)
        On Error Resume Next
        jColL = LBound(theVariant, 2)
        jColU = UBound(theVariant, 2)
        On Error GoTo FuncFail
        If jColU < 0 Then
            jType = 3
            jColL = jRowL
            jColU = jRowU
            jRowL = 0
            jRowU = -1
        Else
            jType = 2
        End If
   Else
        jRowL = 1
        jRowU = 1
        jColL = 1
        jColU = 1
        jType = 4
   End If
   Variant_Type = jType
   Exit Function
FuncFail:
   Variant_Type = CVErr(xlErrValue)
   jType = 0
   jRowU = -1
   jColU = -1
End Function

注意，首先测试变量是否包含Range，这是为了避免无意中将Range强制转换为其值。在确定变体的子类型时，VBA还有几种方法：

  • If TypeOf theVariant Is Range Then
  • If TypeName(theVariant) = “Range”Then

尝试使用VarType(theVariant)时要特别小心，这会对Range的覆盖范围进行强制转换，然后抛出结果值！

代码的图片版：

小结：在通用目的的用户自定义函数中，必须使用Variant类型的参数而不是Range类型。可以通过在处理变量之前确定变体包含的内容来有效地处理出现的问题。

#编程算法#erp