Pass "Wb" to Sub-CodePudding

Is there a way how to pass String or maybe some other type of data to Sub() and based on that String, define and open new WorkBook?

For Example

Sub main()
   Call workbook_generator("Wb1")
End Sub

Sub workbook_generator(name As String)
   Dim name as Workbook
   Set name = Workbooks.Add
End Sub

CodePudding user response：

You have to pass the workbook object if you want to reuse it. I added the option to pass the fullfilename as well ...

Option Explicit

Sub main()

Dim fullfilename As String
fullfilename = "D:\file1.xlsx"

Dim wb As Workbook
workbook_generator wb, fullfilename

wb.Worksheets(1).Name = "testsheet"
End Sub

Public Sub workbook_generator(ByRef wb As Workbook, Optional fullfilename As String)

Set wb = Application.Workbooks.Add
If fullfilename <> vbNullString Then
    'you should put a check here if folder exists
    'or at least an error handler
    wb.SaveAs fullfilename
End If

End Sub