CODEWITHSUNDEEP
vbaexcel
StormsEdge
StormsEdge

Reputation: 885

Error Returning Workbook Object From Function

When I attempt to return an newly created workbook object from this function I receive an error. I've seen several posts on SO about this, but most seem to point to using a Variant as a return type and I have been told that's a big no no time and time again. (Frankly, I don't want to use a variant data type either)

Error text: Run-time error '91': Object variable or With block variable not set. I've seen other posts on SO as well that had errors, but none seem to have been '91'. 

Private Function NewWorkbook_Open(ByVal clientName As String, ByVal startDateFromSheet As Date) As Workbook

    'Creates/formats new workbook and saves it to the xdrive without any completed
    Dim newWorkBook As Workbook
    Dim activeWorkbookName As String
    Dim formattedDate

    Workbooks.Add

    formattedDate = Replace(Format(startDateFromSheet, "mm/dd/yy"), "/", ".")

    'Saves workbook with new file name with date attached in saveable format
    ActiveWorkbook.SaveAs Filename:=XLS_CONFIRM_FILE_PATH & "-" & GetOfficialClientName(clientName) & " " & formattedDate & ".xls", FileFormat:=xlNormal


    NewWorkbook_Open = Workbooks(ActiveWorkbook.Name)

The new workbook object is set in the main routine through the following line

Set newExcelConfirmBook = NewWorkbook_Open(.Cells(rowCounter,sellerFirmColumn).Value, startDateFromSheet)

Upvotes: 0

Views: 415

Answers (2)

Jochen
Jochen

Reputation: 1254

Dont use 'active...' if you dont need to. Use the variables you declard correctly.

Private Function NewWorkbook_Open(ByVal clientName As String, ByVal startDateFromSheet As Date) As Workbook

   'Creates/formats new workbook and saves it to the xdrive without any completed
   Dim newWorkBook As Workbook
   Dim activeWorkbookName As String
   Dim formattedDate

   Set newWorkBook = Workbooks.Add

   formattedDate = Replace(Format(startDateFromSheet, "mm/dd/yy"), "/", ".")

   'Saves workbook with new file name with date attached in saveable format
   newWorkBook.SaveAs Filename:=XLS_CONFIRM_FILE_PATH & "-" & GetOfficialClientName(clientName) & " " & formattedDate & ".xls", FileFormat:=xlNormal


   Set NewWorkbook_Open = newWorkBook

End Function

Upvotes: 1

Vityata
Vityata

Reputation: 43593

How about if you try like this at the end of your code:

set NewWorkbook_Open = Workbooks(ActiveWorkbook.Name)

Does it work?

Upvotes: 2

Related Questions