Returning a Class in UDF throws error in cell RRS feed

  • Question

  • Can a UDF parameter be a class when typing the UDF into the cell?  For example, when I type "=GetDoorCount(GetCars(),1)" in cell A1, I'm getting a #VALUE! error, why?

    When I set breakpoints on the functions, I see GetCars() is called, but GetDoorCount() is never called.  This makes me believe you can't use classes as parameters in UDF's in a cell.

    This code goes into a Module:

    Function GetDoorCount(c As Collection, i As Long) As Long
        If TypeName(c(i)) = "Car" Then
            Dim ca As Car
            Set ca = c.Item(i)
            GetDoorCount = ca.Doors
        End If
    End Function
    Function GetCars() As Collection
        Dim col As New Collection
        Dim car1 As New Car
        car1.Doors = 2
        col.Add Item:=car1
        Dim car2 As New Car
        car2.Doors = 4
        col.Add Item:=car2
        Set GetCars = col
        Set col = Nothing
        Set car1 = Nothing
        Set car2 = Nothing
    End Function

    This code goes into a Car Class

    Private pDoors As Long
    Property Get Doors() As Long
        Doors = pDoors
    End Property
    Property Let Doors(value As Long)
        pDoors = value
    End Property



    • Edited by Ryan0827 Tuesday, September 17, 2019 8:22 PM
    Tuesday, September 17, 2019 8:20 PM

All replies

  • As you have found: no. Nor can you use a Collection as argument in a UDF.

    The argument to a UDF should be something that you can specify on a worksheet.

    Regards, Hans Vogelaar (

    Tuesday, September 17, 2019 8:39 PM