Calling a user defined Function in Sub VBA

Question

I am new to VBA. I am currently trying to apply a loop on a user defined function. The defined function is as follows.

  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

I am trying to use this function CountColor in a Sub. But it throws a runtime 424 error.

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

Could you please help me figure out what am doing wrong? Any help will be deeply appreciated. Thanks

Answer 1

There is one "(" more than needed in the way the function is called, thus it does not get the correct parameters. Try this:

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

To make sure you are passing on the expected parameter, press Ctrl + I, while on the line. The VBEditor would help:

In general, whenever working with Range() and Cells() objects, make sure to refer their Worksheets and Workbooks, to avoid 1004 errors. In the case of the example it should be like this:

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

Answer 2

Your CountColor function is expecting a Range object reference for the first parameter, but that's not what you're giving it:

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

The first argument is the result of this expression:

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

When you put an argument between parentheses, what you're passing is the evaluated result of the expression, passed ByVal (regardless of the function's signature specifying ByRef or not).

So why "object required" then? Doesn't the expression evaluate to a Range object?

The Range class has a hidden default member that you can reveal in the Object Browser (F2):

Notice the hidden/shaded _Default member.

If you're familiar with the Collection class, you might be aware of its Item member being that class' default member:

The default member can be implicitly invoked. That's how you can do myCollection("someKey") to retrieve an item, and that's how it's completely equivalent to myCollection.Item("someKey").

The Range.[_Default] member is a bit different, in that its implementation will "redirect" to different members, depending on context: when invoked without parameters, it returns Range.Value - for a single-cell range, that's the cell's value. For a multiple-cell range, that's a 2D variant array containing all the values.

So when you pass (someRange) as an argument, what you're implicitly passing is (someRange.[_Default]), because the class has a default, parameterless member (well, the two parameters are optional, so a parameterless invocation is legit).

In other words you're passing a 2D variant array to CountColor - not a Range object reference.

And that's why VBA throws run-time error 424 "object required" - because the call requires an object, but no object was provided.

As Vityata already answered, removing the extraneous parentheses will fix this, because without the extraneous parentheses, you're no longer forcing ByVal evaluation of the Range object/expression.