Search code examples
vbaexcelexcel-2007

How to refer to with object


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


Solution

  • 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.)