Can I take implicitly take a worksheet argument in a with statement?

Question

I have a sub that looks like this:

Public Sub CategoryChange(ChangeWS As Worksheet, NewCategory As String, StartDate As String, EndDate As String)
    ...
End Sub

And I'm wondering why this works:

Sub Test()

    Call CategoryChange(ActiveSheet, Range("A1"), Range("A2"), Range("A3"))

End Sub

and this doesn't work (compile error, argument not optional):

Sub Test2()

    With ActiveSheet

        Call CategoryChange(, Range("A1"), Range("A2"), Range("A3"))

    End With

End Sub

If my sub is expecting a worksheet variable and I'm writing within a With statement, why won't it implicitly take my worksheet?

You're misunderstanding how `With` works. Also note that without the `.` in front of each `Range` call, none of them are actually referring to `With ActiveSheet`. — BigBen, Mar 09 '21 at 21:42
@BigBen I understand the missing periods in from the `Range`s, I'm just curious about the first argument. — dwirony, Mar 09 '21 at 21:44

score 2 · Accepted Answer · answered Mar 09 '21 at 21:48

2

You can't do that because the syntax doesn't work that way. With only provides a shortcut to reference members of an object, not the object itself. It doesn't "magically" inject the object into a missing reference to the variable. It only looks for expressions that start with a period (.) with no reference in front and injects the object reference.

If your example actually compiles, then Range must be referring to some other reference, not ActiveSheet.Range. Otherwise, you would do this:

With ActiveSheet

    Call CategoryChange(ActiveSheet, .Range("A1"), .Range("A2"), .Range("A3"))

End With

answered Mar 09 '21 at 21:48

D Stanley

149,601
11
178
240

Seems like the italics were meant to be around "actually" and not "compiles", but otherwise, quite well said. – BigBen Mar 09 '21 at 21:51
1

Slightly confusingly for this example, `Range()` without an explicit object qualifier always refers to a range on `ActiveSheet`. – aucuparia Mar 09 '21 at 21:52
@acuparia [not technically true](https://stackoverflow.com/questions/28439376/what-is-the-default-scope-of-worksheets-and-cells-and-range) but yeah, here it just so happens that the implicit `ActiveSheet` (assuming OP's code is in a regular module) is the same as the explicit `With ActiveSheet`. – BigBen Mar 09 '21 at 21:55
Thanks - see, I think I thought it looked silly to have to write `ActiveSheet` inside of my with statement. – dwirony Mar 09 '21 at 22:20

score 0 · Answer 2 · answered Mar 09 '21 at 21:49

With just lets you access the properties and methods of an object without having to specify the object each time:

Worksheets(1).Range("A1").Select

Is the same as:

With Worksheets(1)
  .Range("A1").Select
End With

the period is needed; missing arguments are still treated as missing arguments. In your example, this should work:

With ActiveSheet

        Call CategoryChange(Worksheets(.Name), Range("A1"), Range("A2"), Range("A3"))

End With

as .Name will return the name of the object referenced by the With block. But it hardly seems worth the effort!

Can I take implicitly take a worksheet argument in a with statement?

2 Answers2