CODEWITHSUNDEEP
excelvba
Daniel
Daniel

Reputation: 31

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

Upvotes: 0

Views: 137

Answers (2)

Samuel Hulla
Samuel Hulla

Reputation: 7089

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 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")

Upvotes: 1

Greg Viers
Greg Viers

Reputation: 3523

The general idea is sound, you just need to create one function per object:

Function wk_Base_18() as workbook
    Set wk_Base_18 = Excel.Workbooks("2019.01.03.xlsb")
End Function

Function ws_18() as worksheet
    Set ws_18 = wk_Base_18.Worksheets("Planilha1")
End Function

Then whenever you go to use the variable ws_18 or wk_Base_18, you will be calling these same functions.

Upvotes: 0

Related Questions