Excel vba - passing worksheet to a public function

2
votes

I have a public function:

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

and I have main sub:

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

ws = ThisWorkbook.Sheets("Sheet1") 

out = Test (ws, in)

Calling the Test I get "ByRef argument type mismatch" error.

1
vbaexcel

1 Answers

6
votes

Use the Set keyword like this:

set ws = ThisWorkbook.Sheets("Sheet1")

Edit:

Then make sure that you set the return of the function as well. Like this:

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

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