VBA: Returning A Worksheets Object Reference From A Function

Question

How do I return a Worksheets Object Reference? I've been perusing through various Google searches with nada results.

For example, I have a functioning code like so. wSheet already dim'ed:

Public wSheet As Worksheet
...
Set wSheet = ActiveWorkbook.Worksheets("ExampleSheet")
wSheet.Range("A1").Value = "Hello"

However, I want wSheet to now call a module that supplies it to the correct reference. Something like this:

Public wSheet As Worksheet
...
Set wSheet = myModule.get_ExampleSheet
wSheet.Range("A1").Value = "Hello"

And then have a function in module myModule

Function get_ExampleSheet() As Worksheets
    get_ExampleSheet = ActiveWorkbook.Worksheets("ExampleSheet")
End Function

Everything I try gives me various runtime errors. Is there anyway to get this to work?

Thanks and advance!

Answer 1

You are returning the wrong type of object in your function.

Function get_ExampleSheet() As Worksheets
    get_ExampleSheet = ActiveWorkbook.Worksheets("ExampleSheet")
End Function

This currently has several errors.

Function get_ExampleSheet() As Worksheet
   Set get_ExampleSheet = ActiveWorkbook.Sheets("Sheet1")
End Function

Note I changed:

1. Return type to Worksheet (you are trying to set a variable, wSheet, which is of type Worksheet to a Worksheets type variable)
2. Added set keyword
3. Changed to .Worksheets to .Sheets to return the specific sheet you are interested in

Answer 2

Sub Lookup()
Dim state As Variant
Dim PodName As Variant
Set state = ThisWorkbook.Worksheets("Sheet1").Range("E:E")
Sheets("Sheet1").Activate
PodName = WorksheetFunction.VLookup(state, Range("A1:C55"), 2, False)
ThisWorkbook.Worksheets("Sheet1").Range("F:F") = PodName
End Sub

Macro should stop once the target cell is blank