Can a sub return an object and reference to object to a calling Sub????

[AlexB123]

Hi all,

A few weeks ago I asked about creating new workbooks with specific numbers of sheets. I was given a code that stores the default number of sheets to be created in a new workbook, then changes the Application.SheetsInNewWorkbook property to the desired of new sheets, creates a workbook, and then restores the defaults.

I want to turn this into a function I can call from any macro ... Since I always set workbook references like so:

Dim aWorkbook As Workbook
Dim bWorkbook As Workbook

Set aWorkbook = Workbooks.Add
Set bWorkbook = Worbooks.Open("C:\\myHome\some.xlsx")

I want to know if it is possible to have newWB returned to a calling sub:

Public Sub addWBsheets(shtsNum As Integer)
Dim shtsCnt As Integer
Dim newWB As Workbook
  With Application
                  shtsCnt = .SheetsInNewWorkbook
     .SheetsInNewWorkbook = shtsNum
                  Set newWB = Workbooks.Add
     .SheetsInNewWorkbook = shtsCnt
                End With
End Sub

or if I can set a workbook reference directly by calling:

Set cWorkbook = Call addWBsheets(8)

Sorry if my language is confusing. Happy to provide details..

 

[Fluff]

How about

Sub atest()
   Dim Awb As Workbook
   
   Set Awb = AddWb(5)
   Awb.Sheets(4).Select
End Sub
Function AddWb(Shts As Long)
   Dim shtsCnt As Integer
  
   With Application
      shtsCnt = .SheetsInNewWorkbook
      .SheetsInNewWorkbook = Shts
      Set AddWb = Workbooks.Add
      .SheetsInNewWorkbook = shtsCnt
   End With
End Function