Can an Excel VBA UDF called from the worksheet eve

Question

I'm 99% sure that the answer is "no", but I'm wondering if someone who is 100% sure can say so.

Consider a VBA UDF:

Public Function f(x)

End Function

When you call this from the worksheet, 'x' will be a number, string, boolean, error, array, or object of type 'Range'. Can it ever be, say, an instance of 'Chart', 'ListObject', or any other Excel-VBA object model class?

(The question arose from me moving to Excel 2007 and playing with Tables, and wondering if I could write UDFs that accept them as parameters instead of Range. The answer to that seems to be no, but then I realized I didn't know for sure in general.)

Answer 1

Your suspicions are correct - you can only pass in limited object types. For example, if I have table on the active worksheet and wanted to know it's column count, I could create a UDF called TableColumnCount and pass in the table name into a function like:

Function TableColumnCount(tn As String) As Integer
    Dim myTableName As ListObject
    Dim ActiveS As Worksheet
    Set ActiveS = ActiveWorkbook.ActiveSheet
    Set myTableName = ActiveS.ListObjects(tn)
    TableColumnCount = myTableName.Range.Columns.Count
End Function

and then call it on sheet with the name of my able as a string, like =TableColumnCount("Table1").

Or as a range object like:

Function TableColumnCount(tn As Range) As Integer
    TableColumnCount = tn.Columns.Count
End Function

And then call it like: =TableColumnCount(Table1)