'Run time error 424': Object required' When calling function where object has been defined in sub

[TrainTrist]

Hello,

I have a function that when I try and call from it's parent subroutine it gives me Run time error 424: Object required. My understanding is that if I have an object defined in a subroutine and then call a function defined within that subroutine, the scope of the object should extend to that function? If I copy and paste the code from the function into the subroutine the code runs fine. If I run the code in the function block it throws the error. Looking at the contents of the variables, it seems as though the OverdueWO_sheet variable loses its value when used in the function. Can someone explain why variable loses it's value when used in the function which is defined within the scope? I have placed the problem code in red. Any feedback would be greatly appreciated.

Sub Macro1()
' Declare variables
Dim ws As Worksheet, today As String, AllWO_date As String, AllWO_sheet As String
Dim OverdueWO_date As String, OverdueWO_sheet As String
Dim EOR037WB As Workbook, thisWB As Workbook, EOR037 As String

today = Date
Set thisWB = ActiveWorkbook

' Rename Worksheets to reflect current date

For Each ws In Sheets                               ' This for loop looks through each sheet in the workbook and finds the one
    If ws.Name Like "All WO*" Then                  ' that has the words "ALL WO" with some text after those words i.e. the date
        ws.Select                                   ' The worksheet is then selected
        AllWO_date = "All WO " & today              ' A string is created with the words "All WO" concatenated with the today's date in xx/xx/xxx format
        AllWO_sheet = Replace(AllWO_date, "/", "-") ' The forward slash is an illegal character in a worksheet name so we replace the "/" with a "-"
        ActiveSheet.Name = AllWO_sheet              ' Renames the active worksheet to "ALL WO dd-mm-ccyy"
    End If
Next

For Each ws In Sheets                                   ' This for loop looks through each sheet in the workbook and finds the one
    If ws.Name Like "Overdue WO*" Then                  ' that has the words "Overdue WO" with some text after those words i.e. the date
        ws.Select                                       ' The worksheet is then selected
        OverdueWO_date = "Overdue WO " & today          ' A string is created with the words "Overdue WO" concatenated with the today's date in xx/xx/xxx format
        OverdueWO_sheet = Replace(OverdueWO_date, "/", "-") ' The forward slash is an illegal character in a worksheet name so we replace the "/" with a "-"
        ActiveSheet.Name = OverdueWO_sheet              ' Renames the active worksheet to "Overdue WO dd-mm-ccyy"
    End If
Next

' Clear data in AllWO_sheet and copy data from EOR 037 into this worksheet
Sheets(OverdueWO_sheet).Cells.ClearContents
fileEOR037 = filePath()
Set EOR037WB = Workbooks.Open(fileEOR037)
EOR037WB.Sheets("Details").UsedRange.Copy thisWB.Sheets(AllWO_sheet).Range("A1")
EOR037WB.Close

thisWB.Sheets(AllWO_sheet).UsedRange.Copy thisWB.Sheets(OverdueWO_sheet).Range("A1")

' Delete irrelevant columns from OVerdue WO sheet

[COLOR="#FF0000"]deleteColumns[/COLOR]

' Update Pivot table for work orders by unit

' Function to select file (EOR037 for specific week)
End Sub

Private Function filePath() As String

Dim fd As Office.FileDialog                             ' Declare variable type to allocate storage
Set fd = Application.FileDialog(msoFileDialogOpen)      ' set assigns object reference
fd.AllowMultiSelect = False                             ' Only allow user to select a single file
fd.Title = "Please Select EOR037 Report"                ' Dialogue box title  set as "Please Select EOR037 Report"
checkShow = fd.Show                                     ' Show the dialogue box so the user can select the file If user presses 'Cancel' checkShow is set to 0; If user selects file and presses 'Open' checkShow is set to -1.
'Range("A1").Value = checkShow                          ' Debug tool
If checkShow = -1 Then filePath = fd.SelectedItems(1)   ' If no selection do nothing else assign fil path to variable filePath
End Function

[COLOR="#FF0000"]' Works fine if pasted outside of function but not within function? Check scope of variables and objects in function
Private Function deleteColumns()

Dim currentCol As Integer                               'Declare variable type to allocate storage
Dim colHeading As Integer

For currentCol = thisWB.Sheets(OverdueWO_sheet).UsedRange.Columns.Count To 1 Step -1
    colHeading = thisWB.Sheets(OverdueWO_sheet).UsedRange.Cells(1, currentCoumn).Value
    
    Select Case colHeading
        Case "WO Number", "Date WO Raised", "WO Required by Date", "Due Date", "Days Over Due", "Unit status Work Order Desc"
            'Do nothing
        Case Else
            thisWB.Sheets(OverdueWO_sheet).Columns(currentColumn).Delete
    End Select
Next
End Function[/COLOR]

Regards,
Trist

 

[RoryA]

My understanding is that if I have an object defined in a subroutine and then call a function defined within that subroutine, the scope of the object should extend to that function?

No. If you have a variable declared within a routine, then it is only visible to that routine, not to other routines you may call. You can either pass the object as an argument to the routine you call, or you can declare the variable so that it is visible to both routines.

 

[TrainTrist]

Hi Rory,

Thanks for clearing that up. So if I am using a workbook in a function and it has already been declared in the calling sub routine, I must then declare it again in the function?

Regards,
Tristan

 

[RoryA]

No - you can pass it to the function as an argument (my preference), or move the original declaration so that it is visible to both routines. For example (changes highlighted in blue):

Sub Macro1()
' Declare variables
Dim ws As Worksheet, today As String, AllWO_date As String, AllWO_sheet As String
Dim OverdueWO_date As String, OverdueWO_sheet As String
Dim EOR037WB As Workbook, thisWB As Workbook, EOR037 As String

today = Date
Set thisWB = ActiveWorkbook

' Rename Worksheets to reflect current date

For Each ws In Sheets                               ' This for loop looks through each sheet in the workbook and finds the one
    If ws.Name Like "All WO*" Then                  ' that has the words "ALL WO" with some text after those words i.e. the date
        ws.Select                                   ' The worksheet is then selected
        AllWO_date = "All WO " & today              ' A string is created with the words "All WO" concatenated with the today's date in xx/xx/xxx format
        AllWO_sheet = Replace(AllWO_date, "/", "-") ' The forward slash is an illegal character in a worksheet name so we replace the "/" with a "-"
        ActiveSheet.Name = AllWO_sheet              ' Renames the active worksheet to "ALL WO dd-mm-ccyy"
    End If
Next

For Each ws In Sheets                                   ' This for loop looks through each sheet in the workbook and finds the one
    If ws.Name Like "Overdue WO*" Then                  ' that has the words "Overdue WO" with some text after those words i.e. the date
        ws.Select                                       ' The worksheet is then selected
        OverdueWO_date = "Overdue WO " & today          ' A string is created with the words "Overdue WO" concatenated with the today's date in xx/xx/xxx format
        OverdueWO_sheet = Replace(OverdueWO_date, "/", "-") ' The forward slash is an illegal character in a worksheet name so we replace the "/" with a "-"
        ActiveSheet.Name = OverdueWO_sheet              ' Renames the active worksheet to "Overdue WO dd-mm-ccyy"
    End If
Next

' Clear data in AllWO_sheet and copy data from EOR 037 into this worksheet
Sheets(OverdueWO_sheet).Cells.ClearContents
fileEOR037 = filePath()
Set EOR037WB = Workbooks.Open(fileEOR037)
EOR037WB.Sheets("Details").UsedRange.Copy thisWB.Sheets(AllWO_sheet).Range("A1")
EOR037WB.Close

thisWB.Sheets(AllWO_sheet).UsedRange.Copy thisWB.Sheets(OverdueWO_sheet).Range("A1")

' Delete irrelevant columns from OVerdue WO sheet

deleteColumns thisWB

' Update Pivot table for work orders by unit

' Function to select file (EOR037 for specific week)
End Sub

Private Function filePath() As String

Dim fd As Office.FileDialog                             ' Declare variable type to allocate storage
Set fd = Application.FileDialog(msoFileDialogOpen)      ' set assigns object reference
fd.AllowMultiSelect = False                             ' Only allow user to select a single file
fd.Title = "Please Select EOR037 Report"                ' Dialogue box title  set as "Please Select EOR037 Report"
checkShow = fd.Show                                     ' Show the dialogue box so the user can select the file If user presses 'Cancel' checkShow is set to 0; If user selects file and presses 'Open' checkShow is set to -1.
'Range("A1").Value = checkShow                          ' Debug tool
If checkShow = -1 Then filePath = fd.SelectedItems(1)   ' If no selection do nothing else assign fil path to variable filePath
End Function

' Works fine if pasted outside of function but not within function? Check scope of variables and objects in function
Private Function deleteColumns(wb As Workbook)

Dim currentCol As Integer                               'Declare variable type to allocate storage
Dim colHeading As Integer

For currentCol = wb.Sheets(OverdueWO_sheet).UsedRange.Columns.Count To 1 Step -1
    colHeading = wb.Sheets(OverdueWO_sheet).UsedRange.Cells(1, currentCoumn).Value
    
    Select Case colHeading
        Case "WO Number", "Date WO Raised", "WO Required by Date", "Due Date", "Days Over Due", "Unit status Work Order Desc"
            'Do nothing
        Case Else
            wb.Sheets(OverdueWO_sheet).Columns(currentColumn).Delete
    End Select
Next
End Function

 

[TrainTrist]

Cheers Rory. Thanks for clearing those scope issues up.

Regards,
Trist