Excel Parent Object VBA (Visual Basic for Applications) example

Parent

Is a readonly object, represents one level up object of current object. Parent is very useful object when user has no clue about current object that it is coming from. Consider an example we have a range object and want to know which sheet range belongs to? The parent object returns respective sheet object which you can cast into a Worksheet object and gain the access.

Code example

Public Sub GetRangeParentObject()
    'Declare worksheet object
    Dim oWkSheet As Worksheet
    
    'Create range object
    Dim oRange As Range
    'Bind range object to selection
    Set oRange = Selection
    
    'bind active sheet range object
    Set oWkSheet = oRange.Parent
    
    'Print sheet name
    Debug.Print oWkSheet.Name
    
    'Memory Cleanup
    Set oWkSheet = Nothing
End Sub

Output

Sheet2

Get Pivot Name from Pivot Cell

Public Sub GetPivotParent()
    'Declare application object
    Dim oApp As Application
    
    'Create Pivot object
    Dim oPivotTable As PivotTable
    
    'Bind pivot object to Pivot cell
    Set oPivotTable = oApp.Range("E7").PivotCell.Parent
    
    'Print Pivot name
    Debug.Print oPivotTable.Name
    
    'Memory Cleanup
    Set oPivotTable = Nothing
    Set oApp = Nothing
End Sub

Next >> Get List of Files from directory or sub directory

Share on Facebook

Tweet