CODEWITHSUNDEEP
vbaexcel
user155754
user155754

Reputation: 93

Excel vba - passing worksheet to a public function

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.

Upvotes: 2

Views: 831

Answers (1)

Vityata
Vityata

Reputation: 43585

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

Upvotes: 6

Related Questions