Q:

Is it possible to return a worksheet type variable in a function?

In this project I'm developing, I created many Subs that all use the same three workbooks. But is there a better way to use these workbooks' sheets  without having to write them down everytime I create a new Sub? I tried returning it in a function but it does not work.
Function defineWorksheet() As Worksheet

Dim wk_Base_18 As Excel.Workbook
Dim ws_18 As Excel.Worksheet

Set wk_Base_18 = Excel.Workbooks("2019.01.03.xlsb")
Set ws_18 = wk_Base_18.Worksheets("Planilha1")

ws_18

End Function

error 91

A:

Yes, you can declare them as a global variable.
Public ws1 As Worksheet

Then instantiate the global variable during the application load event of the excel application
Private Sub Workbook_Open()
     Set ws1 = ThisWorkbook.Sheets("YourSheetName")
End Sub

And now, you can refer to it via the variable, eg.
Dim x as Integer: x = ws1.Range("B5")

