Can an Excel VBA UDF called from a worksheet ever be passed an instance of any Excel VBA object model class other than "Range"?

Question

I am 99% sure that the answer is no, but I wonder if anyone who is 100% sure can say that.

Consider VBA UDF:

Public Function f(x) End Function 

When you call this from a worksheet, "x" will be a number, a string, a Boolean, an error, an array, or an object of type "Range". Could it be, for example, an instance of "Chart", "ListObject", or any other Excel-VBA object model class?

(The question arose because I was moving to Excel 2007 and playing with tables, and I was wondering if I could write UDFs that accept them as parameters instead of Range . The answer seems to be no, but then I realized that I I didn’t know for sure at all.)

Answer 1

Your suspicions are true - you can transfer only limited types of objects. For example, if I have a table on the active sheet and you want to know its number of columns, I could create a UDF called TableColumnCount and pass the table name to 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 a sheet with the name of my ability as a row, for example =TableColumnCount("Table1") .

Or as a range object, for example:

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

And then name it like this: =TableColumnCount(Table1)