How to access an object

Question

How can I refer to the object that I use inside With , if I want the object itself, and 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 useful results (since With is a common word) ...

<h / "> UPDATE : for clarification, I was thinking of a handle like with ... as handle from python syntax, not an object-oriented key this

Answer 1

How about not using with in the first place? This makes your code more readable, does not use more memory (since the with statement should allocate a temporary variable anyway), and less confusing.

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

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

Answer 2

try it

 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 information about the existence of "this"? - deathApril 19 minutes ago

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

The Me keyword provides a way to access a specific instance of the class or structure in which the code is currently executing . For example, in 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 into the code area of thisworkbook , it will give you the name of the book

 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.

NTN

Answer 3

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

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