Page 1 of 2 12 LastLast
Results 1 to 15 of 19
  1. #1
    WS Lounge VIP
    Join Date
    Mar 2002
    Location
    Newcazzle, UK
    Posts
    2,823
    Thanks
    135
    Thanked 481 Times in 458 Posts

    VBA - select Cell containing named range

    In response to Marin's CLOSED thread, and RG's response, here is a shorter method:

    zSheet = Range("RangeName").Parent.Name

    But, more importantly, my secret plan to prepare quietly for RG's 1000th 'Thanks received' has now been exposed.
    Drats!

    ..the race is now on, who is going to be the one to give RG his phenomenally well-deserved biggest Thank You?????
    We can't thank him enough in my book.
    Hang on.. that should be
    '..can't thank him enough in my workbook"

    zeddy

  2. The Following User Says Thank You to zeddy For This Useful Post:

    MartinM (2015-04-12)

  3. #2
    5 Star Lounger
    Join Date
    Jan 2004
    Location
    Praha
    Posts
    988
    Thanks
    56
    Thanked 105 Times in 90 Posts
    Only 760 to go, Z !

  4. #3
    5 Star Lounger
    Join Date
    Jan 2004
    Location
    Praha
    Posts
    988
    Thanks
    56
    Thanked 105 Times in 90 Posts
    It's good to save 3 characters but luckily they're free

  5. The Following User Says Thank You to MartinM For This Useful Post:

    zeddy (2015-04-12)

  6. #4
    WS Lounge VIP
    Join Date
    Mar 2002
    Location
    Newcazzle, UK
    Posts
    2,823
    Thanks
    135
    Thanked 481 Times in 458 Posts
    Hi Martin

    I was just showing respect for Parent.

    zeddy

  7. #5
    Gold Lounger Maudibe's Avatar
    Join Date
    Aug 2010
    Location
    Pa, USA
    Posts
    2,633
    Thanks
    115
    Thanked 647 Times in 590 Posts
    Yet, even a couple more letters:

    zSheet = [RangeName].Parent.Name

  8. The Following 2 Users Say Thank You to Maudibe For This Useful Post:

    MartinM (2015-04-12),zeddy (2015-04-12)

  9. #6
    WS Lounge VIP
    Join Date
    Mar 2002
    Location
    Newcazzle, UK
    Posts
    2,823
    Thanks
    135
    Thanked 481 Times in 458 Posts
    Hi Maud

    less is more

    zeddy

  10. #7
    5 Star Lounger
    Join Date
    Jan 2004
    Location
    Praha
    Posts
    988
    Thanks
    56
    Thanked 105 Times in 90 Posts
    You are both right, more or less.

    I like terse !

  11. #8
    Super Moderator RetiredGeek's Avatar
    Join Date
    Mar 2004
    Location
    Manning, South Carolina
    Posts
    9,434
    Thanks
    372
    Thanked 1,457 Times in 1,326 Posts
    Hey Y'all,

    Continuing this discussion in the vein of the OP in the referenced thread where OP was talking about selecting a cell on another sheet before doing operations. This is totally unnecessary as well as inefficient as pointed out often by my learned colleagues in this forum. So I thought I'd create a small example to demonstrate that point.

    Code:
    Option Explicit
    
    Sub TestNoSelect()
    
       Dim shtTarget As Worksheet
       Dim lRowCntr  As Long
       
       
    '*** NOTE: All named ranges are on Sheet2 w/Workbook Scope!         ***
    
       Sheets("Sheet1").Activate  '*** Establish as Active Worksheet    ***
       
       '*** Setup an Object Referenct to sheet containing the RangeName ***
       Set shtTarget = Worksheets(Range("TargetB3").Worksheet.Name)
    
       '*** Change Value of Named Range on a NON-Active sheet ***
       '*** where the Named Range has Workbook Scope!         ***
       [TargetB3].Value = 10
       Range("TargetF4").Formula = "=" & [TargetB3] & "+7.5"
       
       For lRowCntr = 0 To 4  '*** Fill 5 cells with data ***
          [TargetD2].Offset(lRowCntr, 0).Value = lRowCntr * 5
       Next lRowCntr
       
    End Sub  'TestNoSelect
    Results:
    NoSelect.JPG

    You'll notice I used a variety of referencing styles to show how they all work. The discussion got into saving keystrokes and this is something that I don't personally like (YMMV) as I prefer clarity over keystrokes thus I did not use the .parent reference as I feel that is not intuitively obvious, at least not to me (my apologies Zeddy nothing personal ).

    Test File: NOSelect.xlsm

    HTH
    May the Forces of good computing be with you!

    RG

    PowerShell & VBA Rule!

    My Systems: Desktop Specs
    Laptop Specs

  12. #9
    Super Moderator RetiredGeek's Avatar
    Join Date
    Mar 2004
    Location
    Manning, South Carolina
    Posts
    9,434
    Thanks
    372
    Thanked 1,457 Times in 1,326 Posts
    Hey Y'all,

    Here's a follow on to my last post with some more info that I forgot to include showing how the references are affected if you change the active sheet:
    Code:
    Option Explicit
    
    Sub TestNoSelect()
    
       Dim shtTarget As Worksheet
       Dim lRowCntr  As Long
       
       
    '*** NOTE: All named ranges are on Sheet2 w/Workbook Scope!         ***
    
       Sheets("Sheet1").Activate  '*** Establish as Active Worksheet    ***
       
       '*** Setup an Object Referenct to sheet containing the RangeName ***
       Set shtTarget = Worksheets(Range("TargetB3").Worksheet.Name)
    
       '*** Change Value of Named Range on a NON-Active sheet ***
       '*** where the Named Range has Workbook Scope!         ***
       [TargetB3].Value = 10
       Range("TargetF4").Formula = "=" & [TargetB3] & "+7.5"
       
       For lRowCntr = 0 To 4  '*** Fill 5 cells with data ***
          [TargetD2].Offset(lRowCntr, 0).Value = lRowCntr * 5
       Next lRowCntr
       
       '*** Same name different scopes ***
       Sheets("Sheet3").Activate         '*** Select Sheet 3!     ***
       [TargetA5].Value = 100            '*** Writes to Sheet1!A5 ***
       shtTarget.[TargetA5].Value = 999  '*** Writes to Sheet2!A5 ***
       
       '*** Now we'll Activate Sheet2 and try the unqualified reference    ***
       '*** Note: Excel now references the name on the active sheet first! ***
       shtTarget.Activate
       [TargetA5].Value = 777    '*** Writes to Sheet2!A5 ***
       
    End Sub  'TestNoSelect
    Test File: NOSelect.xlsm

    HTH
    May the Forces of good computing be with you!

    RG

    PowerShell & VBA Rule!

    My Systems: Desktop Specs
    Laptop Specs

  13. #10
    WS Lounge VIP
    Join Date
    Mar 2002
    Location
    Newcazzle, UK
    Posts
    2,823
    Thanks
    135
    Thanked 481 Times in 458 Posts

    Parent Property

    Hi RG

    Re:
    I did not use the .parent reference as I feel that is not intuitively obvious
    No offence taken. With coding, I think brevity is easier to follow. Reducing code clutter helps beginners focus on the purpose of what the routine is meant to do. I love short formulas. I love short code lines. I love the challenge of reducing as much baggage as possible whilst still achieving the purpose. For clarity, I prefer adding comments. I recommend commenting every line of code.

    Now, as I'm sure you will agree, in VBA it is important to know what objects are, and how they are related. Excel's structure is hierarchal ie contains objects, which contain other objects, which contain other objects, and so on. So we have, for example,
    Application Object (Excel) > Workbook Object > Worksheet Object > Range object > Comment object

    In the language of OOP, a cell comment's parent is the Range object. A Range object's parent is the Worksheet object that contains it. The parent of the worksheet is the workbook. The parent of the workbook is the Excel Application.
    So for a range zRef we have
    zSheetName = zRef.Parent.Name
    zWorkbookName = zRef.Parent.Parent.Name
    zAppName = zRef.Parent.Parent.Parent.Name

    We can also use the Parent property to find the name of the Chart that contains an axis etc etc.
    So parents are good for you.

    zeddy

  14. #11
    WS Lounge VIP rory's Avatar
    Join Date
    Dec 2000
    Location
    Burwash, East Sussex, United Kingdom
    Posts
    6,280
    Thanks
    3
    Thanked 191 Times in 177 Posts
    A propos the original issue, in the interest of brevity I would use:

    Code:
    application.Goto range("rangename")
    instead of locating the sheet name etc.

    IMO, brevity should never be an end in and of itself. I've seen some truly awful code created by people who think like that, and I think it rarely helps beginners. YMMV.
    Regards,
    Rory

    Microsoft MVP - Excel

  15. #12
    WS Lounge VIP
    Join Date
    Mar 2002
    Location
    Newcazzle, UK
    Posts
    2,823
    Thanks
    135
    Thanked 481 Times in 458 Posts
    Hi Rory

    You are right. Brevity. I was just quoting Polonius in Hamlet Act 2 Scene 2, but kept it shorter.

    zeddy

  16. #13
    5 Star Lounger
    Join Date
    Jan 2004
    Location
    Praha
    Posts
    988
    Thanks
    56
    Thanked 105 Times in 90 Posts
    Interesting - I've learned as much from this thread as from the original one !

    For "readable code" as well as ease of use I find application.Goto range("rangename") to be near perfect.

    I don't really understand why Range("rangename").Select isn't included in VBA.

  17. #14
    Gold Lounger Maudibe's Avatar
    Join Date
    Aug 2010
    Location
    Pa, USA
    Posts
    2,633
    Thanks
    115
    Thanked 647 Times in 590 Posts
    Honestly, they are all readable and one being better than the other is just a matter of opinion. While we have an "obligation" to show different ways of achieving the same thing, what is most important is the consistency with how you do it throughout the code.

    Maud

  18. #15
    5 Star Lounger
    Join Date
    Jan 2004
    Location
    Praha
    Posts
    988
    Thanks
    56
    Thanked 105 Times in 90 Posts
    I find consistency easy to achieve, and rewarding as you say.

    The problem in this case was that it was, to my surprise, the first time I had tried, in VBA, to go to a particular cell which could be on any Worksheet.

    It has been great to have the options, and their various merits, laid out so clearly by the helpers here.

    In passing, I have a particularly complex Workbook with lots of internal checks for consistency so that I do not make mistakes without noticing them quickly - which is the key to sorting them out without too much pain.

    Each Worksheet has at least one "check" cell whose value is either "OK" or the quantum of the inconsistency found and each "check" cell has a Range Name using a consistent naming scheme. The "check" cells' contents are tabulated on a "Checks" Worksheet and if any of the values is not "OK" the Worksheet tab colour changes to yellow . As this tab is visible in the areas I do most work it is a simple warning as soon as something is wrong. In a little refinement, as more errors are found the tab colour successively darkens through the oranges and finally to red !

    Despite all that, I sometimes start up the Workbook without noticing errors - some of which may occur during the initialisation processes, so I included some extra lines in the Auto_Open macro to take me straight to the first "check" cell whose value is not "OK". Hence the need to go to a cell which could be anywhere in the Workbook.

    I prefer the tab colour system to a Message Box as the former is "permanent" until the problem is cleared.

    This overall system of checking and alerting has saved me from numerous little disasters: having established a system it is easy to put in a "check" cell whenever I add a new function - it's when I am creating the function that I understand best what it is meant to do and how it does it . . . so that is the best time to work out how to check that it is working properly. This is the Worksheet equivalent philosophy of putting error checking routines in VBA modules I suppose.

    Anyway, thanks for all the help.
    Last edited by MartinM; 2015-04-16 at 06:11.

  19. The Following User Says Thank You to MartinM For This Useful Post:

    Maudibe (2015-04-16)

Page 1 of 2 12 LastLast

Posting Permissions

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