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
    3,442
    Thanks
    166
    Thanked 651 Times in 619 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
    1,121
    Thanks
    79
    Thanked 129 Times in 113 Posts
    Only 760 to go, Z !

  4. #3
    5 Star Lounger
    Join Date
    Jan 2004
    Location
    Praha
    Posts
    1,121
    Thanks
    79
    Thanked 129 Times in 113 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
    3,442
    Thanks
    166
    Thanked 651 Times in 619 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
    3,038
    Thanks
    166
    Thanked 800 Times in 729 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
    3,442
    Thanks
    166
    Thanked 651 Times in 619 Posts
    Hi Maud

    less is more

    zeddy

  10. #7
    5 Star Lounger
    Join Date
    Jan 2004
    Location
    Praha
    Posts
    1,121
    Thanks
    79
    Thanked 129 Times in 113 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
    10,000
    Thanks
    423
    Thanked 1,608 Times in 1,452 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
    10,000
    Thanks
    423
    Thanked 1,608 Times in 1,452 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
    3,442
    Thanks
    166
    Thanked 651 Times in 619 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,353
    Thanks
    4
    Thanked 229 Times in 210 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
    3,442
    Thanks
    166
    Thanked 651 Times in 619 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
    1,121
    Thanks
    79
    Thanked 129 Times in 113 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
    3,038
    Thanks
    166
    Thanked 800 Times in 729 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
    1,121
    Thanks
    79
    Thanked 129 Times in 113 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 05: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
  •