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

What do {} around a formula in the formula bar mean?
{Formula} means the formula was entered using Ctrl+Shift+Enter signifying an old-style array formula.

Jonmo1

MrExcel MVP
Joined
Oct 12, 2006
Messages
44,061
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?
 

ChrisM

Well-known Member
Joined
Jun 11, 2002
Messages
2,129
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"
 

James Snyder

Well-known Member
Joined
Jan 11, 2013
Messages
618
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.
 

James Snyder

Well-known Member
Joined
Jan 11, 2013
Messages
618

ADVERTISEMENT

Sorry Chris! Answered before seeing your post...
 

RoryA

MrExcel MVP, Moderator
Joined
May 2, 2008
Messages
37,145
Office Version
  1. 365
  2. 2019
  3. 2016
  4. 2010
Platform
  1. Windows
  2. MacOS
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.
 

ChrisM

Well-known Member
Joined
Jun 11, 2002
Messages
2,129

ADVERTISEMENT

You're still not showing where you assign the values to

workOWkBook and workOWkSheet
 

James Snyder

Well-known Member
Joined
Jan 11, 2013
Messages
618
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:

James Snyder

Well-known Member
Joined
Jan 11, 2013
Messages
618
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!
 

Forum statistics

Threads
1,136,780
Messages
5,677,687
Members
419,712
Latest member
LearningCR

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
Top