vba - Excel vba - 将工作表传递给公共(public)函数

Question

我有一个公共(public)职能:

Public Function Test(wrs As Worksheet, arr As Variant) As Variant

我有主子:

Sub Main()
Dim ws as Worksheet
Dim out, in

ws = ThisWorkbook.Sheets("Sheet1") 

out = Test (ws, in)

调用测试时我收到“ByRef 参数类型不匹配”错误。

Answer 1

使用 Set 关键字，如下所示:

set ws = ThisWorkbook.Sheets("Sheet1") 

编辑:

然后确保您也设置了函数的返回值。像这样:

Public Function Test() As Variant
    Set Test = ActiveSheet
End Function

Public Sub TestMe()
    Debug.Print Test.name
End Sub