Return to Answer

Active reading [<https://en.wiktionary.org/wiki/generic#Adjective>].

Source Link

edited Sep 29, 2020 at 16:05

31.4k
22
110
134

Working with the .Parent.Parent feature. This, this example shows how setting only one myRng reference enables dynamic access to the entire environment with nowithout any .Select, .Activate, .Activecell, .ActiveWorkbook, .ActiveSheet and so on. (There's no genereicThere isn't any generic .Child.Child feature.)

Sub ShowParents()
    Dim myRng As Range
    Set myRng = ActiveCell
    Debug.Print myRng.Address                    ' anAn address of the selected cell
    Debug.Print myRng.Parent.name                ' theThe name of sheet, where MyRng is in
    Debug.Print myRng.Parent.Parent.name         ' theThe name of workbook, where MyRng is in
    Debug.Print myRng.Parent.Parent.Parent.name  ' theThe name of application, where MyRng is in

    ' You may use this feature to set reference to these objects
    Dim mySh    As Worksheet
    Dim myWbk   As Workbook
    Dim myApp   As Application
    
    Set mySh = myRng.Parent
    Set myWbk = myRng.Parent.Parent
    Set myApp = myRng.Parent.Parent.Parent
    Debug.Print mySh.name, mySh.Cells(10, 1).Value
    Debug.Print myWbk.name, myWbk.Sheets.Count
    Debug.Print myApp.name, myApp.Workbooks.Count
    
    ' You may use dynamically addressing
    With myRng
        .Copy
       
        ' pastesPastes in D1 on sheet 2 in the same workbook, where the copied cell is
        .Parent.Parent.Sheets(2).Range("D1").PasteSpecial xlValues 

        ' orOr myWbk.Sheets(2).Range("D1").PasteSpecial xlValues
       
        ' weWe may dynamically call active application too
        .Parent.Parent.Parent.CutCopyMode = False 

        ' orOr myApp.CutCopyMode = False
    End With
End Sub

Working with .Parent feature. This example shows how setting only one myRng reference enables dynamic access to the entire environment with no .Select, .Activate, .Activecell, .ActiveWorkbook, .ActiveSheet and so on. (There's no genereic .Child feature)

Sub ShowParents()
    Dim myRng As Range
    Set myRng = ActiveCell
    Debug.Print myRng.Address                    ' an address of the selected cell
    Debug.Print myRng.Parent.name                ' the name of sheet, where MyRng is in
    Debug.Print myRng.Parent.Parent.name         ' the name of workbook, where MyRng is in
    Debug.Print myRng.Parent.Parent.Parent.name  ' the name of application, where MyRng is in

    ' You may use this feature to set reference to these objects
    Dim mySh    As Worksheet
    Dim myWbk   As Workbook
    Dim myApp   As Application
    
    Set mySh = myRng.Parent
    Set myWbk = myRng.Parent.Parent
    Set myApp = myRng.Parent.Parent.Parent
    Debug.Print mySh.name, mySh.Cells(10, 1).Value
    Debug.Print myWbk.name, myWbk.Sheets.Count
    Debug.Print myApp.name, myApp.Workbooks.Count
    
    ' You may use dynamically addressing
    With myRng
        .Copy
       
       ' pastes in D1 on sheet 2 in the same workbook, where copied cell is
        .Parent.Parent.Sheets(2).Range("D1").PasteSpecial xlValues
    ' or myWbk.Sheets(2).Range("D1").PasteSpecial xlValues
       
       ' we may dynamically call active application too
        .Parent.Parent.Parent.CutCopyMode = False
    ' or myApp.CutCopyMode = False
    End With
End Sub

Working with the .Parent feature, this example shows how setting only one myRng reference enables dynamic access to the entire environment without any .Select, .Activate, .Activecell, .ActiveWorkbook, .ActiveSheet and so on. (There isn't any generic .Child feature.)

Sub ShowParents()
    Dim myRng As Range
    Set myRng = ActiveCell
    Debug.Print myRng.Address                    ' An address of the selected cell
    Debug.Print myRng.Parent.name                ' The name of sheet, where MyRng is in
    Debug.Print myRng.Parent.Parent.name         ' The name of workbook, where MyRng is in
    Debug.Print myRng.Parent.Parent.Parent.name  ' The name of application, where MyRng is in

    ' You may use this feature to set reference to these objects
    Dim mySh  As Worksheet
    Dim myWbk As Workbook
    Dim myApp As Application

    Set mySh = myRng.Parent
    Set myWbk = myRng.Parent.Parent
    Set myApp = myRng.Parent.Parent.Parent
    Debug.Print mySh.name, mySh.Cells(10, 1).Value
    Debug.Print myWbk.name, myWbk.Sheets.Count
    Debug.Print myApp.name, myApp.Workbooks.Count

    ' You may use dynamically addressing
    With myRng
        .Copy

        ' Pastes in D1 on sheet 2 in the same workbook, where the copied cell is
        .Parent.Parent.Sheets(2).Range("D1").PasteSpecial xlValues 

        ' Or myWbk.Sheets(2).Range("D1").PasteSpecial xlValues

        ' We may dynamically call active application too
        .Parent.Parent.Parent.CutCopyMode = False 

        ' Or myApp.CutCopyMode = False
    End With
End Sub

Source Link

answered Oct 30, 2019 at 9:45

barneyos

Sub ShowParents()
    Dim myRng As Range
    Set myRng = ActiveCell
    Debug.Print myRng.Address                    ' an address of the selected cell
    Debug.Print myRng.Parent.name                ' the name of sheet, where MyRng is in
    Debug.Print myRng.Parent.Parent.name         ' the name of workbook, where MyRng is in
    Debug.Print myRng.Parent.Parent.Parent.name  ' the name of application, where MyRng is in

    ' You may use this feature to set reference to these objects
    Dim mySh    As Worksheet
    Dim myWbk   As Workbook
    Dim myApp   As Application
    
    Set mySh = myRng.Parent
    Set myWbk = myRng.Parent.Parent
    Set myApp = myRng.Parent.Parent.Parent
    Debug.Print mySh.name, mySh.Cells(10, 1).Value
    Debug.Print myWbk.name, myWbk.Sheets.Count
    Debug.Print myApp.name, myApp.Workbooks.Count
    
    ' You may use dynamically addressing
    With myRng
        .Copy
       
       ' pastes in D1 on sheet 2 in the same workbook, where copied cell is
        .Parent.Parent.Sheets(2).Range("D1").PasteSpecial xlValues
    ' or myWbk.Sheets(2).Range("D1").PasteSpecial xlValues
       
       ' we may dynamically call active application too
        .Parent.Parent.Parent.CutCopyMode = False
    ' or myApp.CutCopyMode = False
    End With
End Sub

Collectives™ on Stack Overflow

Return to Answer