How to refer to with object

Question

How can i refer to the object i use inside With if i want the object itself, not its properties / methods?

With ThisWorkbook.Sheets("MySheet")
    Call MySub(ThisWorkbook.Sheets("MySheet")) ' works OK, but duplicated
    Call MySub(this) ' does not works
    .Range(...).Value2 = 1
    ...
End With

+ what is the correct terminology here? i don't even know how to compose a google query for this and get some usefull results (since with is a common word)...

UPDATE: to clarify, i was thinking in terms of a handle like with ... as handle from python syntax, not about object-oriented this keyword

Why would you want to do this? .Sheets("MySheet") is not a method and won't do anything. — Jesse, Jun 29 '12 at 17:34
i want to pass an Worksheet argument to my subroutine (i want to call the subroutine on different worksheets as needed) P.S.: if it was a method, why would i want to use it as a `with` argument? — Aprillion, Jun 29 '12 at 17:35

Siddharth Rout · Answer 1 · 2014-08-02T14:57:59.817

4

Try this

Sub Sample()
    Dim ws As Worksheet

    Set ws = ThisWorkbook.Sheets("MySheet")

    With ws
        MySub ws
        '~~> Rest of the code
    End With
End Sub

or

Sub Sample()
    Dim ws As Worksheet

    Set ws = ThisWorkbook.Sheets("MySheet")

    MySub ws

    With ws    
        '~~> Rest of the code
    End With
End Sub

Edit:

do you have any info about non-existence of "this"? – deathApril 19 mins ago

this is basically a keyword from C# which refers to the current instance of the class. The equivalent of this in VB is Me.

The Me keyword provides a way to refer to the specific instance of a class or structure in which the code is currently executing. For example in a Userform you can use

Me.textBox1.Text = "Blah Blah"

In VBA, Me can also be used for thisworkbook. For example, if you paste this code in the ThisWorkbook code Area then it will give you the name of the workbook

Sub Sample()
    Debug.Print Me.Name
End Sub

Similarly when you run the above code from the Sheet Code Area you will get the Sheet Name.

HTH

edited Aug 02 '14 at 14:57

answered Jun 29 '12 at 17:40

Siddharth Rout

147,039
17
206
250

+1 helpful, but not exactly what i asked - do you have any info about non-existence of "this"? – Aprillion Jun 29 '12 at 17:44
Updating.. gimme few minutes :) – Siddharth Rout Jun 29 '12 at 17:48
I have updated the post above... Edit: I see you have already got your answer :) – Siddharth Rout Jun 29 '12 at 18:08
uh.. both answers are very helpfull, i guess i like the "without with" option more... and i realised i should ask about `with ... as handle` instead about `this` ;)) – Aprillion Jun 29 '12 at 18:30
1

'With` is useless if you don't have any more code which refers to the sheet but is useful if you have more code which refers to the sheet for example `.Range(...).Value` – Siddharth Rout Jun 29 '12 at 18:47

score 4 · Accepted Answer · answered Jun 29 '12 at 17:41

4

How about by not using with in the first place? It makes your code much more readable, uses no more memory (as the with statement has to allocate a temporary variable anyway), and is less confusing.

Dim WS as WorkSheet
WS = ThisWorkBook.Sheets("MySheet")
Call vymaz_obrazky(WS)
WS.Range(...).Value2 = 1

In the code above, the total cost is one additional line of code (the DIM statement), and 9 less keystrokes overall. (The DIM statement is 19 keystrokes, changing to WS in the three lines is 6 keystrokes, but you've saved the with (4) and duplication (30), saving about 9 keystrokes.)

answered Jun 29 '12 at 17:41

Ken White

123,280
14
225
444

i guess i was just used to python's `with` (it automatically handles `finally` code to close files, ...), but if there is no reason for it in VBA. (BTW i don't really care about keystrokes or lines, just want to avoid duplicated code) – Aprillion Jun 29 '12 at 18:01
I know this was selected as the answer, and in the simplified example code included here, it makes the most sense, but in a more realistic scenario, not using With isn't an option. We use With in VBA because every dot costs cycles and using With reduces dots. – phrebh Oct 13 '16 at 13:35
@phrebh: Nonsense. *dots* do not cost cycles; the cycles required to reconcile the property or method are identical in either case with or without the dots, because they still have to be resolved to the object instance. *with* simply introduces difficulties and lessens keystrokes. – Ken White Oct 13 '16 at 15:19
@Ken White: First of all, every dot seen in the code does cost more, because the With statement creates a local instance of the object so the processor needs to work less to reference it. Benchmarking backs this up. It's not a great difference, but it does exist. Second of all, the With statement does **not** introduce difficulties, it helps produce easier to read, and therefore more maintainable, code. – phrebh Oct 20 '16 at 20:12
@phrebh: Official documentation link for your first point about performance, please. As to your second, that's your opinion, not fact. – Ken White Oct 20 '16 at 20:41
@Ken White: I countered your opinion with my opinion. Then you countered with a disingenuous debating tactic. As is your request for official documentation, which is lacking for most things VBA. [Chip Pearson](http://www.cpearson.com/excel/optimize.htm) talks about the difference. As does [ozgrid](http://www.ozgrid.com/VBA/SpeedingUpVBACode.htm). And [stackoverflow](http://stackoverflow.com/questions/283749/the-vb-net-with-statement-embrace-or-avoid) (although not for VBA specifically). – phrebh Oct 28 '16 at 16:09
@phrebh: *disingenuous debating tactic*? Is that what asking for proof of your claim is? Asking people to back up what they claim are facts is *disingenuous*? Right. Gotcha. You've got nothing. – Ken White Oct 28 '16 at 16:47

score 2 · Answer 3 · answered Nov 20 '15 at 22:18

Use .Cells.Parent. This only works for worksheets, but there are similar things for some other objects (for a workbook you can use .Sheets.Parent)

With ThisWorkbook.Sheets("MySheet")
    Call MySub(.Cells.Parent)
    .Range(...).Value2 = 1
    ...
End With

How to refer to with object

3 Answers3