VBA Run-Time error '424': Object required-CodePudding

I am trying to create a print function that selects from a range at the bottom of a long list. The Macro works but it gives me an error every time of 'Object Required' after the macro completes. I cannot figure out how to clean this up and prevent the error from occurring.

Option Explicit
Sub SchedulePrint()

    Dim lRow As Range
    
Application.ScreenUpdating = False
    With Sheets("Line 3")

        Set lRow = Range("G1024").End(xlUp).Offset(-3, -6).Resize(20, 14).PrintOut
'The above is the line where I get the debug error message
    
    End With
Application.ScreenUpdating = True
End Sub

CodePudding user response：

Object Required

This line is blowing up:

Set lRow = Range("G1024").End(xlUp).Offset(-3, -6).Resize(20, 14).PrintOut

Because the Range.PrintOut method returns nothing (it's a Sub, not a Function), so what's going on is that the right-hand side of the assignment gets evaluated. Since it's a side-effecting statement on the right-hand side, the side-effects happen as the expression is evaluated (i.e. you do get a printout). This should really be a compile-time error, but the VBA compiler is known to defer many checks to run-time, so...

VBA then takes the result of the RHS expression and attempts to honor the Set lrow instruction... but the RHS didn't yield any object reference, so you get an object required error, because when you have Set xyz on the LHS of an assignment, the RHS must evaluate to an object reference.

The solution is to remove the side-effect from the assignment:

Set lRow = Range("G1024").End(xlUp).Offset(-3, -6).Resize(20, 14)

Now the RHS evaluates to the result of Range.Resize, which is a Range object. So lrow holds a reference to that Range when this instruction completes.

Now another instruction can call the PrintOut method of that Range object:

lRow.PrintOut

But there's another problem.

Implicit ActiveSheet Reference

There's a stated intent here:

With Sheets("Line 3")

It looks like the intent is to work off a worksheet that's named Line 3, however the Range function isn't qualified, so it will behave differently depending on where in the project the code is written:

In a worksheet module, Range refers to a range of cells on that worksheet.
In any other module type, Range refers to whatever the active sheet is.

So if the macro is written in the code-behind of the Line 3 worksheet, then the With block is redundant, because the unqualified Range call is always going to be referring to the correct worksheet. The implicit containing workhseet reference can be made more explicit by qualifying with Me, i.e. use Me.Range(...) instead of just Range. If it's written in the code-behind of another worksheet, then the code is misleading because it is not working off the Line 3 worksheet (rather, it refers to whatever worksheet module the macro is written in).

If the macro is written in any other module, then there's an implicit requirement that Line 3 is also the ActiveSheet when the macro runs, otherwise it won't produce the expected result.

The wrong way to fix it is to activate that particular worksheet before you access a Range:

With Sheets("Line 3")
    .Activate
    Set lRow = Range(...)

That's wrong, because the With block is still useless. The correct way to fix it is to use the With block variable as a qualifier - then it doesn't matter which worksheet is active anymore:

With Sheets("Line 3")
    Set lRow = .Range(...)

Similarly, Sheets is implicitly referring to whatever the active workbook is, unless the code is written in the ThisWorkbook module - in which case Sheets is implicitly Me.Sheets, and if we know we're after a Worksheet type of sheet, we should be using the Worksheets collection instead. In any other module, Sheets (or Worksheets) used without a qualifier, refers to whatever workbook is currently active - which may or may not be the containing workbook. Use ThisWorkbook.Worksheets("...") to retrieve a Worksheet from the workbook that contains the macro.

See the following Rubberduck^* inspections for more details:

_{^*Rubberduck is a free and open-source VBIDE add-in project I maintain.}

CodePudding user response：

What @freeflow and @vbasic2008 both said is correct. Since you selected the "Line 3" worksheet first using the "With" statement the following Range needs to be proceeded with a period so that It's associated with the selected sheet.

It looks as if you then tried to set the range "LROW" at the same time as you were running the PrintOut function. Since the PrintOut method returns a variant and not a range Excel throws and error.

If you need to set lRow as a range it's best to set lRow first. Then use it after it's been set.

Set lRow = .Range("G1024").End(xlUp).Offset(-3, -6).Resize(20, 14)
lRow.PrintOut

It's also possible to avoid setting lRow at all and just call the printout function

.Range("G1024").End(xlUp).Offset(-3, -6).Resize(20, 14).PrintOut

CodePudding user response：

Building on @Mathiedu_Guindon great answer I could suggest reducing the code as follows

Option Explicit

Sub SchedulePrint()
    
    Application.ScreenUpdating = False
    Sheets("Line 3").Range("G1024").End(xlUp).Offset(-3, -6).Resize(20, 14).PrintOut
    Application.ScreenUpdating = True

End Sub