Passing a workbook as an object in VBA ByRef - having a problem

James Snyder

Well-known Member
Joined
Jan 11, 2013
Messages
618
I am working with multiple workbooks from a master workbook and need to pass a workbook object and worksheet object as ByRef parameters. I am successful in other functions, but am not figuring out how to do it into a function that is generic.

I am using Excel 2010, workbooks are made in 2003 and saved to 2010 before being referenced, and the passed references get used in multiple functions.

The declarations are in Main():
Code:
    Dim obstWkBook As Workbook
    Dim obstWkSheet As Worksheet
    Dim [COLOR=#008000]workOWkBook[/COLOR] As Workbook
    Dim [COLOR=#008000]workOWkSheet[/COLOR] As Worksheet
    Dim ftpWkBook As Workbook
    Dim ftpWkSheet As Worksheet
    Dim arrDupes() As String
    Static sendDate As String
    Dim funcReturn As String
    Dim failReturn As String
    Dim errString As String


The function call that isn't working:
Code:
    funcReturn = PSIDDupeCheck([COLOR=#008000]workOWkBook[/COLOR], [COLOR=#008000]workOWkSheet[/COLOR], arrDupes, sendDate)
    If funcReturn <> "Success" Then
        errString = "DupeCheck fail:   Error while checking for PSID duplicates"
        failReturn = ProblemReport(errString, sendDate)
        Err.Clear
        GoTo ExitPoint
    End If


The function:
Code:
Private Function PSIDDupeCheck([COLOR=#008000]ByRef wkBook As Workbook[/COLOR], _
    [COLOR=#008000]ByRef wkSheet As Worksheet[/COLOR], _
    ByRef dupeArray() As String, _
    ByRef sendDate As String) As String
    
    Dim rowMax As Long
    Dim dupeRow As Long
    Dim i As Long
    Dim failReturn As String
    Dim errString As String
    
    [COLOR=#008000]wkBook[/COLOR].Open      [COLOR=#ff0000]<=== Fails here with Run-time error '424' Object Required[/COLOR]
    Workbooks.Open [COLOR=#008000]wkBook[/COLOR] [COLOR=#ff0000]<=== or fails here with Run-time error '424' Object Required[/COLOR]
    If Not [COLOR=#008000]wkBook[/COLOR] Is Nothing Then
'<<snip>>
    Else
        errString = "WorkOrders:       Failed to open workbook get dupes"
        failReturn = ProblemReport(errString, sendDate)
        Err.Clear
        PSIDDupeCheck = errString
    End If
    wkSheet = Nothing
    wkBook = Nothing
End Function
</snip>
 

Excel Facts

Bring active cell back into view
Start at A1 and select to A9999 while writing a formula, you can't see A1 anymore. Press Ctrl+Backspace to bring active cell into view.
We need to see the code "leading up to" this line
funcReturn = PSIDDupeCheck(workOWkBook, workOWkSheet, arrDupes, sendDate)

How is the variable workOWkBook and workOWkSheet assigned an Object?
 
Upvote 0
How are you setting the value of workOWkBook?

I'm not quite sure why you are trying to run "WkBook.Open" in your function when you already had to "open" it to set it equal to workOWkBook.

Also, WkBook.Open is not a valid function.

Workbooks.Open xxx is okay, but it requires a file path as an attribute, not a workbook object.

I think you'd be all set changing your function to accept two file paths instead of two workbook objects, then use the Workbooks.Open command with those. Maybe like:

Set wkBook = Workbooks.Open "My file path"
 
Upvote 0
Here is the code immediately before the call (including the call):
Code:
    ' Get the WorkOrder file path
    fileName = "WorkOrders.xlsx"
    Set cellRef = Range("$I$3")
    
    workOInFile = RetrieveFilePath(cellRef, fileName, sendDate)
    If workOInFile = "" Then
        errString = "WorkOrder.xlsx:   Unable to find file path."
        failReturn = ProblemReport(errString, sendDate)
        Err.Clear
        GoTo ExitPoint
    Else
        ' Open the Obstructed .xlsx and correct headers
        funcReturn = CorrectWorkOHeaders([COLOR=#008000]workOInFile[/COLOR], [COLOR=#008000]workOWkBook[/COLOR], [COLOR=#008000]workOWkSheet[/COLOR], sendDate)
        If funcReturn <> "Success" Then
            GoTo ExitPoint
        End If
    End If
    
    ' Get the FTP file path
    fileName = "FTP.xlsx"
    Set cellRef = Range("$I$4")
    
    ftpInFile = RetrieveFilePath(cellRef, fileName, sendDate)
    If ftpInFile = "" Then
        errString = "FTP.xlsx:         Unable to find file path."
        failReturn = ProblemReport(errString, sendDate)
        Err.Clear
        GoTo ExitPoint
    Else
        ' Correct the leading space in " Assigned"
        funcReturn = CorrectFTPHeaders(ftpInFile, ftpWkBook, ftpWkSheet, sendDate)
        If funcReturn <> "Success" Then
            GoTo ExitPoint
        End If
    End If
    
    ' Check for PSID Duplicates
    funcReturn = PSIDDupeCheck([COLOR=#008000]workOWkBook[/COLOR], [COLOR=#008000]workOWkSheet[/COLOR], arrDupes, sendDate)
    If funcReturn <> "Success" Then
        errString = "DupeCheck fail:   Error while checking for PSID duplicates"
        failReturn = ProblemReport(errString, sendDate)
        Err.Clear
        GoTo ExitPoint
    End If

I have tried setting it where I am assigning it with Set wkBook = Workbooks(wkBook), and tried a few other things, but no good combo yet. In the CorrectWorkOHeaders() function, I handle it like this:
Code:
Private Function CorrectWorkOHeaders(ByRef workOInFile As String, _
    ByRef workOWkBook As Workbook, _
    ByRef workOWkSheet As Worksheet, _
    ByRef sendDate As String) As String
    
    Dim errString As String
    Dim failReturn As String
    
    Workbooks.Open fileName:=workOInFile
    Set workOWkBook = ActiveWorkbook
    If Not workOWkBook Is Nothing Then
        Set workOWkSheet = workOWkBook.Sheets(1)
        If Not workOWkSheet Is Nothing Then
            workOWkSheet.Range("B1") = "PSID"
            workOWkSheet.Range("C1") = "Item No"
            Application.EnableEvents = False        ' Suppress BeforeSave event
            workOWkBook.Close savechanges:=True
            Application.EnableEvents = True
            CorrectWorkOHeaders = "Success"
        Else
            errString = "WorkOrders:       Failed to open worksheet to fix headers"
            failReturn = ProblemReport(errString, sendDate)
            Err.Clear
            CorrectWorkOHeaders = errString
        End If
    Else
        errString = "WorkOrders:       Failed to open workbook to fix headers"
        failReturn = ProblemReport(errString, sendDate)
        Err.Clear
        CorrectWorkOHeaders = errString
    End If
End Function

It is going to a generic function that I am having problems. I could add the parameter for the file path again.
 
Upvote 0
You either have to pass an open workbook, or you need to pass a path so that the function can open the correct workbook. I'm not really clear why you would need to do the latter and if you do the former you don't, as ChrisM already stated, need to try and open the workbook again.
 
Upvote 0
You're still not showing where you assign the values to

workOWkBook and workOWkSheet
 
Upvote 0
Thanks Rory! In a previous function, I make changes and close it:
Code:
Private Function CorrectWorkOHeaders(ByRef workOInFile As String, _
    ByRef workOWkBook As Workbook, _
    ByRef workOWkSheet As Worksheet, _
    ByRef sendDate As String) As String
    
    Dim errString As String
    Dim failReturn As String
    
    Workbooks.Open fileName:=workOInFile
    Set workOWkBook = ActiveWorkbook
    If Not workOWkBook Is Nothing Then
        Set workOWkSheet = workOWkBook.Sheets(1)
        If Not workOWkSheet Is Nothing Then
            workOWkSheet.Range("B1") = "PSID"
            workOWkSheet.Range("C1") = "Item No"
            Application.EnableEvents = False        ' Suppress BeforeSave event
  [B]          workOWkBook.Close savechanges:=True[/B]
            Application.EnableEvents = True
            CorrectWorkOHeaders = "Success"
...

@ChrisM
I worked on it a half day on Friday and sent it in the state I left it. In the function I use before this call, it gets assigned, opened, then saved and closed. I need to play with what Rory gave me. He is correct in that it is already assigned, and I need only to reopen it. I will play with it a bit more and return here for an update as to what either finally works or what I can't get through my thick head!
 
Last edited:
Upvote 0
SOLVED: Instead of closing and using the .SaveAs, I leave it open and use the .Save:

Before:
Code:
workOWkBook.Close savechanges:=True

After:
Code:
workOWkBook.Save

It does mean I will have to explicitly close them before using ADO against them. Thanks Rory and Chris!
 
Upvote 0

Forum statistics

Threads
1,224,455
Messages
6,178,768
Members
452,875
Latest member
Disastrouscoder

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top