Error Returning Workbook Object From Function

Question

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)

Not sure if this helps but startDateFromSheet parameter in the main routine is the same as in your function. — Brian
– Brian, Commented Apr 26, 2016 at 14:34
I believe you are missing Set before NewWorkbook_Open = ... — yk11
– yk11, Commented Apr 26, 2016 at 14:36

Vityata · Accepted Answer · 2016-04-26 14:39:37Z

2

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

set NewWorkbook_Open = Workbooks(ActiveWorkbook.Name)

Does it work?

answered Apr 26, 2016 at 14:39

Vityata

43.8k10 gold badges64 silver badges110 bronze badges

Sign up to request clarification or add additional context in comments.

Comments

Scott · Accepted Answer · 2019-01-24 18:08:14Z

1

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

edited Jan 24, 2019 at 18:08

Scott

1741 silver badge13 bronze badges

answered Apr 26, 2016 at 15:01

Jochen

1,2541 gold badge7 silver badges9 bronze badges

Collectives™ on Stack Overflow

Error Returning Workbook Object From Function

2 Answers 2

Comments

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Related