从工作表调用的Excel VBA UDF是否可以传递"Range"以外的任何Excel VBA对象模型类的实例? [英] Can an Excel VBA UDF called from the worksheet ever be passed an instance of any Excel VBA object model class other than 'Range'?

问题描述

我非常肯定99％的答案是否"，但是我想知道是否100％肯定的人可以这么说.

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

考虑VBA UDF:

Public Function f(x)

End Function

当您从工作表中调用此函数时，"x"将是数字，字符串，布尔值，错误，数组或范围"类型的对象.可以说它是图表"，"ListObject"或任何其他Excel-VBA对象模型类的实例吗?

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?

(这个问题源于我迁移到Excel 2007并使用Tables的问题，想知道我是否可以编写将其作为参数而不是Range接受的UDF.答案似乎是否定的，但是后来我意识到我总体上不确定.)

(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.)

推荐答案

您的怀疑是正确的-您只能传入有限的对象类型.例如，如果我在活动工作表上有表，并且想知道它的列数，则可以创建一个名为TableColumnCount的UDF，并将表名传递给类似以下函数:

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

，然后在工作表上以我的名字作为字符串来调用它，例如=TableColumnCount("Table1").

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

或作为范围对象，如:

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

然后像这样调用它:=TableColumnCount(Table1)

这篇关于从工作表调用的Excel VBA UDF是否可以传递"Range"以外的任何Excel VBA对象模型类的实例?的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！