在VBA中调用用户定义的函数

Question

我是VBA的新手，目前正在尝试在用户定义的函数上应用循环。 定义的函数如下所示。

  Function CountColor(InRange As range, ColorIndex As Long, _
    Optional OfText As Boolean = False) As Long
'''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''
' CountColor
' This function counts the cells in InRange whose ColorIndex
' is equal to the ColorIndex parameter. The ColorIndex of the
' Font is tested if OfText is True, or the Interior property
' if OfText is omitted or False. If ColorIndex is not a valid
' ColorIndex (1 -> 56, xlColorIndexNone, xlColorIndexAutomatic)
' 0 is returned. If ColorIndex is 0, then xlColorIndexNone is
' used if OfText is Fasle or xlColorIndexAutomatic if OfText
' is True. This allows the caller to use a value of 0 to indicate
' no color for either the Interior or the Font.
'''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''

Dim R As range
Dim N As Long
Dim CI As Long

If ColorIndex = 0 Then
    If OfText = False Then
        CI = xlColorIndexNone
    Else
        CI = xlColorIndexAutomatic
    End If
Else
    CI = ColorIndex
End If


Application.Volatile True
Select Case ColorIndex
    Case 0, xlColorIndexNone, xlColorIndexAutomatic
        ' OK
    Case Else
        If IsValidColorIndex(ColorIndex) = False Then
            CountColor = 0
            Exit Function
        End If
End Select

For Each R In InRange.Cells
    If OfText = True Then
        If R.Font.ColorIndex = CI Then
            N = N + 1
        End If
    Else
        If R.Interior.ColorIndex = CI Then
            N = N + 1
        End If
    End If
Next R

CountColor = N


End Function

我试图在子程序中使用 CountColor 函数，但它抛出了运行时 424 错误。

Sub Summary()    
    LastColumn = Cells(1, Columns.Count).End(xlToLeft).Column
    LastRow = Cells(Rows.Count, 1).End(xlUp).Row

        For i = 2 To LastRow
        TOTALFAILS = CountColor((range(Cells(i, 4), Cells(i, LastColumn))), 38)
        Cells(i, LastColumn + 8) = TOTALFAILS

        Next i
End Sub

你能帮我找出我做错了什么吗？非常感谢你的帮助。 谢谢。

- Rohin Raj

---

在您的循环中，您将2个范围变量传递到函数中。您的函数需要一个范围、长整型和一个可选布尔值。您是想传递值还是单元格作为ColorIndex？或者是单元格的颜色？ - Dude_Scott

@Dude_Scott - 实际上 Range(Cells(i,4),Cells(i,LastColumn)) 是一个范围参数，而不是两个。 - Vityata

你是对的。我看错了。 - Dude_Scott

Answer 1

函数的调用方式多了一个"("，因此它没有得到正确的参数。请尝试以下方法：

CountColor(Range(Cells(i, 4), Cells(i, LastColumn)), 38)

为了确保您传递了预期的参数，请在该行上按Ctrl + I。VBEditor会提供帮助：

通常，在使用Range()和Cells()对象时，请确保引用它们所在的工作表和工作簿，以避免1004错误。在本例中应该这样做：

With ThisWorkbook.Worksheets(1)
    For i = 2 To LastRow
        TOTALFAILS = CountColor(.Range(.Cells(i, 4), .Cells(i, LastColumn)), 38)
        .Cells(i, LastColumn + 8) = TOTALFAILS
    Next i
End With

- Vityata

---

因正确而点赞。@RohinRaj，请查看我的答案，了解为什么这个答案是正确的。 - Mathieu Guindon

@RohinRaj 如果您点击答案下方的空心绿色复选标记，将其标记为“已接受”，您将获得+2声望奖励。请注意，这也会将您的问题从“未回答”的问题列表中移除。 - Mathieu Guindon

Answer 2

你的 CountColor 函数期望第一个参数是 Range 对象引用，但你传递的不是这个：

TOTALFAILS = CountColor((range(Cells(i, 4), Cells(i, LastColumn))), 38)

第一个参数是此表达式的结果：

(range(Cells(i, 4), Cells(i, LastColumn)))

当你把一个参数放在括号中时，传递的是表达式求值后的结果，无论函数签名是否指定为ByRef，都会按值（ByVal）传递。

那么为什么还会出现“需要对象”这个错误呢？这个表达式难道不是求值为Range对象吗？

Range类有一个隐藏的默认成员，可以在“对象浏览器”（F2）中显示出来。

注意隐藏/阴影的_Default成员。

如果您熟悉Collection类，则可能已经了解其Item成员是该类的默认成员：

默认成员可以隐式调用。这就是你可以使用myCollection("someKey")来检索项的方式，这也是它与myCollection.Item("someKey")完全相同的方式。 Range.[_Default]成员有点不同，因为它的实现将根据上下文“重定向”到不同的成员：当没有参数调用时，它返回Range.Value——对于单元格范围，这是单元格的值。对于多个单元格范围，这是包含所有值的二维变体数组。

因此，当您将(someRange)作为参数传递时，您隐式传递的是(someRange.[_Default])，因为该类具有默认的无参数成员（好吧，两个参数是可选的，因此无参数调用是合法的）。

换句话说，您将一个二维变体数组传递给了CountColor——而不是一个Range对象引用。

这就是为什么VBA会抛出运行时错误424“对象必需”——因为该调用需要一个对象，但没有提供对象。

正如Vityata已经回答的那样, 去掉多余的括号就可以解决这个问题，因为没有多余的括号，你就不再强制对Range对象/表达式进行ByVal评估。

- Mathieu Guindon

---

注意：对象浏览器默认不显示隐藏成员。您需要右键单击某个位置并选中“显示隐藏成员”选项才能使它们显示出来。 - Mathieu Guindon