Workbook as Variable Generating Object Error

DJhuffman

New Member
Joined
Apr 16, 2019
Messages
25
I'm trying to use a workbook variable in my script to use one macro for multiple data groups. Basically, my goal is to use two workbook variables to designate a "data" excel file and a "Review" excel file, and to use those variables to drive scripts that I originally hard-coded the file names into (but had to make one copy of the script for each group). The issue seems to arise when the script attempts to activate the row "wbkrev.Worksheets("BS Reserves").Activate", at which point the system throws up "Run-time error '424': Object required".

For reference sake, CM and PM are dates on the file names. I would appreciate any ideas as to how to get past this issue. Or is it a case where the variable established in Test190Reformat does not carry over to Comp190TOH?

VBA Code:
Sub Test190Reformat()

Dim wbkdata As Workbook
Dim wbkrev As Workbook
Workbooks.Open (ThisWorkbook.Path & "\HMS Data Files\190 TOH " & CM & ".xlsx")
Set wbkdata = ActiveWorkbook
Workbooks.Open (ThisWorkbook.Path & "\190 TOH AR Review " & CM & ".xlsx")
Set wbkrev = ActiveWorkbook

'Following macro assumes data tab already sorted
Call Comp190TOH
wbkdata.Activate
ActiveWorkbook.Save
ActiveWorkbook.Close
wbkrev.Activate
ActiveWorkbook.Save
ActiveWorkbook.Close
End If

End Sub
VBA Code:
Sub Comp190TOH()

Application.ScreenUpdating = False
wbkvlook = "190 TOH AR Review " & PM
wbkrev.Worksheets("Reserves").Activate
'Following attempts to combine all activities for each tab into a tab specific macro
Call IPNONMC
Call MCIH
Call MCDIS

End Sub
 

Excel Facts

Lock one reference in a formula
Need 1 part of a formula to always point to the same range? use $ signs: $V$2:$Z$99 will always point to V2:Z99, even after copying

dmt32

Well-known Member
Joined
Jul 3, 2012
Messages
6,915
Office Version
  1. 2019
Platform
  1. Windows
Hi,
you need to make your workbook object variable available to the called procedure

Try these changes to your codes & see if resolves

VBA Code:
Sub Test190Reformat()
    
    Dim wbkdata     As Workbook
    Dim wbkrev      As Workbook
    
    Set wbkdata = Workbooks.Open(ThisWorkbook.Path & "\HMS Data Files\190 TOH " & CM & ".xlsx")
    
    Set wbkrev = Workbooks.Open(ThisWorkbook.Path & "\190 TOH AR Review " & CM & ".xlsx")
    
    'Following macro assumes data tab already sorted
    Call Comp190TOH(wbkrev)
    
    wbkdata.Close True
    wbkrev.Close True
End If

End Sub

Sub Comp190TOH(ByVal wb As Object)
    
    Application.ScreenUpdating = False
    wbkvlook = "190 TOH AR Review " & PM
    wb.Worksheets("Reserves").Activate
    'Following attempts to combine all activities for each tab into a tab specific macro
    Call IPNONMC
    Call MCIH
    Call MCDIS
    
End Sub

Dave
 
Solution

dmt32

Well-known Member
Joined
Jul 3, 2012
Messages
6,915
Office Version
  1. 2019
Platform
  1. Windows
Edit
I neglected to delete an Endif statement in the code

Rich (BB code):
    wbkdata.Close True
    wbkrev.Close True

End If

Delete line shown in BOLD.

Dave
 

DJhuffman

New Member
Joined
Apr 16, 2019
Messages
25
Dave,

Thank you for the response. Unfortunately, this did not seem to resolve the matter. I still have the same error issue when the called macro tries to reference the earlier established variable. I made some modification to the order of operations, which may have compounded the problem rather than fix them, but still no response.

Here's what I have so far, including your suggestions:
VBA Code:
Sub Test190Reformat()

xrow = WorksheetFunction.Match("190 TOH", Range("N:N"), 0)
If Range("O" & xrow) = 1 Then
Call Comp190TOH
End If

End Sub
This first part is just to reference a table to determine if a particular data group should be processed. That was where that hanging end if you noted earlier was from. Sorry about that.

VBA Code:
Sub Comp190TOH()

Application.ScreenUpdating = False
CM = Workbooks("Macro storage file - Modified").Worksheets("Main").Range("B5")
PM = Workbooks("Macro storage file - Modified").Worksheets("Main").Range("B4")
wbkvlook = "190 TOH AR Review " & PM

Dim wbkdata As Workbook
Dim wbkrev As Workbook
Workbooks.Open (ThisWorkbook.Path & "\HMS Data Files\190 TOH " & CM & ".xlsx")
Set wbkdata = ActiveWorkbook
Workbooks.Open (ThisWorkbook.Path & "\190 TOH AR Review " & CM & ".xlsx")
Set wbkrev = ActiveWorkbook

wbkrev.Worksheets("BS Reserves").Activate
'Following attempts to combine all activities for each tab into a tab specific macro
Call IPNONMC
    CM.Close True
    wbkdata.Close True
    wbkrev.Close True
    wbkvlook.Close True
Call MCIH
    CM.Close True
    wbkdata.Close True
    wbkrev.Close True
    wbkvlook.Close True
Call MCDIS
    CM.Close True
    wbkdata.Close True
    wbkrev.Close True
    wbkvlook.Close True

Workbooks("190 TOH " & CM).Save
Workbooks("190 TOH " & CM).Close
Workbooks("190 TOH AR Review " & CM).Save
Workbooks("190 TOH AR Review " & CM).Close

End Sub
This next portion of the code I used to establish the variables and to act as a sort of index as to which processes needed to be run on the data set. CM, wbkdata, wbkrev and wbkvlook are all variables that then occur in the called macros IPNONMC, MCIH, and MCDIS.

Given how this is set up now, the error has been shifted to when the macro tries to run IPNONMC and hits the first reference, which in this case is wbkrev.Worksheets("IP NON MC").Activate. At this point, I get the same error I did before.

Any thoughts?
 

DJhuffman

New Member
Joined
Apr 16, 2019
Messages
25

ADVERTISEMENT

Dave,

Sorry, I missed the inclusion of "ByVal wb As Object" in the Sub Comp190TOH() parentheses. I added this, but now I have a new error: "Compile Error: Argument not optional".
 

dmt32

Well-known Member
Joined
Jul 3, 2012
Messages
6,915
Office Version
  1. 2019
Platform
  1. Windows
Dave,

Sorry, I missed the inclusion of "ByVal wb As Object" in the Sub Comp190TOH() parentheses. I added this, but now I have a new error: "Compile Error: Argument not optional".

You get that because you have not passed the argument as shown in code I posted

Code:
Call Comp190TOH(wbkrev)

Also in your other codes if you need to keep referencing the object variable in other code then you need to only perform this action

Code:
wbkrev.Close True

In the very last instance as this will make it Nothing and you will get an error if called after this.

Dave
 

DJhuffman

New Member
Joined
Apr 16, 2019
Messages
25
Dave,

I believe I understand where my issue was. Thank you for leading me in the right direction. I was not familiar with the function ".close True", so I had not originally understood why you put it in the macro. Now that I realize it was designed to replace my save and close lines, I realized it wasn't tied to the wbkrev you put in the call parentheses. That also threw me until I realized that, for my purposes, I needed multiple items in the parentheses (and an analog of those items in the actual subroutine's parentheses as well).

I appreciate your patience with me and am grateful for your help. Thank you for your time.
 

Forum statistics

Threads
1,147,477
Messages
5,741,362
Members
423,657
Latest member
Medrok2021

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