+ Reply to Thread
Results 1 to 2 of 2

With statement - how refer to root?

  1. #1
    Registered User
    Join Date
    10-30-2021
    Location
    Copenhagen area, Denmark
    MS-Off Ver
    365
    Posts
    13

    With statement - how refer to root?

    I often have a minor frustration about With statements.

    E.g., I have this (pseudo)code:

    With MyObject
    blah blah blah
    MyUDF .MyProperty
    With .MyProperty
    blah blah blah
    End With
    blah blah blah
    End With

    The call to MyUDF really belongs INSIDE the inner With block, since it is operating at the same property as the statements in that With block, but I don't know how to refer to the property inside the block (except by writing out in full MyObject.MyProperty, or in some cases .Parent.MyProperty, which would make the code segment less general). If the UDF call had to be in the middle of a long With block, I'd even have to break the With block in two.

    A similar situation:

    [something].Cells(1, 7).Value = 42
    With [something].Cells
    blah blah blah
    End With

    Here, for the sake of argument, "[something]." could be e.g. "ThisWorkbook.Worksheets("MyWorkSheet").", or something more complex than that.
    Again, I might prefer to move the first statement INSIDE the With block, and that would in fact be possible writing ".Item(1, 7).Value = 42". Another solution would, of course, be to write "With [something]" instead of "With [something].Cells", and prepend every reference to the root inside the block with ".Cells".

    What I miss is a more general, neat, compact and readable way to achive this sort of thing - or maybe just a better overview of the methods that can be used in various cases, like .Parent and .Item. (Perhaps a clearer idea inside my head of concepts like properties, methods and collections might help too.)

  2. #2
    Forum Expert rorya's Avatar
    Join Date
    08-13-2008
    Location
    East Sussex, UK
    MS-Off Ver
    365 Ent Monthly Channel / Insiders Beta
    Posts
    8,903

    Re: With statement - how refer to root?

    The methods depend entirely on the situation. Unless the object returned by MyProperty in your example has something like a Self or Object property, you can't do what you want.
    Rory

+ Reply to Thread

Thread Information

Users Browsing this Thread

There are currently 1 users browsing this thread. (0 members and 1 guests)

Similar Threads

  1. Replies: 8
    Last Post: 04-04-2015, 08:45 AM
  2. [SOLVED] If Statement - Select Case - refer to specific cells
    By ahilty in forum Excel Programming / VBA / Macros
    Replies: 20
    Last Post: 07-10-2013, 09:58 AM
  3. How do I refer to a cell in an If statement in VBA?
    By Motox in forum Excel Programming / VBA / Macros
    Replies: 3
    Last Post: 03-31-2013, 09:28 AM
  4. Loop Statement : Use of string to refer userform object
    By asha3010 in forum Excel Programming / VBA / Macros
    Replies: 7
    Last Post: 07-10-2010, 02:27 PM
  5. Nested IF statement to recognize root 'codes'
    By Ace of Clubs in forum Excel General
    Replies: 4
    Last Post: 07-06-2009, 09:37 PM
  6. 3rd root!
    By Flaxkrax in forum Excel General
    Replies: 2
    Last Post: 10-24-2006, 11:55 AM
  7. [SOLVED] How do I use if statement when I must refer to a cell that has te.
    By If Formula referring to cell with text in forum Excel Formulas & Functions
    Replies: 1
    Last Post: 03-02-2005, 04:06 PM

Tags for this Thread

Bookmarks

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts

Search Engine Friendly URLs by vBSEO 3.6.0 RC 1