excel - 在Sub VBA中调用用户定义的函数

我是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

我正在尝试在Sub中使用此功能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

您能帮我找出问题所在吗？任何帮助将不胜感激。
谢谢

最佳答案

调用函数的方式多于所需的“（”，因此无法获取正确的参数。请尝试以下操作：

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

为了确保传递期望的参数，请在在线时按Ctrl +I。 VBEditor将帮助：



通常，每当使用Range()和Cells()对象时，请确保引用其工作表和工作簿to avoid 1004 errors。在这个例子中，它应该是这样的：

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

关于excel - 在Sub VBA中调用用户定义的函数，我们在Stack Overflow上找到一个类似的问题：https://stackoverflow.com/questions/55051506/