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)...
with ... as handle
from python syntax, not about object-oriented this
keyword
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.)
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
If you love us? You can donate to us via Paypal or buy me a coffee so we can maintain and grow! Thank you!
Donate Us With