The GetInfo function described on this page does exactly that.  The GetInfo code of is show below:

The calling syntax of is the following:

=GetInfo (TopObj,PropertySpec)

Where

TopObj is either a Range object (the only type of object you can pass directly from a cell), or one of the following string values:
    "APP" or "APPLICATION"
    "WB" or "TWB" or  "WORKBOOK" or "THISWORKBOOK"
    "WS"  "TWS" or "WORKSHEET" or "THISWORKSHEET"

PropertySpec is a string containing the object tree to get to the property you want to receive. The syntax of PropertySpec is the same as the standard VBA object tree syntax (dot separated). The entire object tree is parsed down from the object specified in TopObj .

The function allows you to traverse the entire Excel object model, starting with a Range object, the Application object, the ThisWorkbook object, or the ActiveSheet object.  Because you can get to the Application object from any one of these objects, the entire Excel object model is accessible.

NOTE: GetInfo works only in Excel 2000 or later. It will not work in Excel97 or earlier versions.

Examples:
 ---------------
Return the formula in A1: =GetInfo(A1,"Formula")
Return the Workbook path: =GetInfo("TWB","Path")
Return the App version: =GetInfo("APP","Version")


Notes on returning items from collections:

You can access a collection with or without the Item property. E.g.,
the following are equivalent.

=GetInfo("TWB","Worksheets(3).Name")
=GetInfo("TWB","Worksheets.Item(3).Name")

If you want to use the key name rather than the index of a collection,
either omit the quotes entirely, or double the quotes:

=GetInfo("APP","workbooks(""personal"").fullname")
=GetInfo("APP","workbooks(personal).fullname")
 

Many more examples are provided in the example workbook.

The Code:

Function GetInfo(TopObj As Variant, PropertySpec As Variant) As Variant

Dim PropArr As Variant ' array returned by Split of object tree
Dim ItemSpec As Variant ' item in collection
Dim Obj As Object ' generic Object to hold
                  'the top-level object (ws,wb,range, or app)
Dim Ndx As Long ' loop counter
Dim Pos1 As Integer ' used to find the Item specified in collection objects
Dim Pos2 As Integer ' used to find the Item specified in collection objects
Dim TempObj As Object

'
' split the object/property spec
'
PropArr = Split(PropertySpec, ".")
'
' If Rng is an object, then it must be a Range. That's the only
' type of object you pass from a cell.
'
If IsObject(TopObj) Then
    Set Obj = TopObj
Else
    '
    ' Otherwise, it better be one of the following strings. Else,
    ' blow up the user.
    '
    Select Case UCase(TopObj)
        Case "APP", "APPLICATION"
            Set Obj = Application
        Case "WB", "TWB", "THISWORKBOOK", "WORKBOOK"
            Set Obj = ThisWorkbook
        Case "WS", "TWS", "THISWORKSHEET", "WORKSHEET"
            Set Obj = Application.Caller.Parent
        Case Else
            GetInfo = CVErr(xlErrValue)
    End Select
End If

For Ndx = LBound(PropArr) To UBound(PropArr) - 1
    '
    ' this block of code is for handling items of a collection
    '
    Pos1 = InStr(1, PropArr(Ndx), "(")
    If Pos1 > 0 Then
        '
        ' if we've found the open paren, we're dealing with an
        ' item of a collection. now, find the closing paren.
        '
        Pos2 = InStr(1, PropArr(Ndx), ")")
        ItemSpec = Mid(PropArr(Ndx), Pos1 + 1, Pos2 - Pos1 - 1)
        If IsNumeric(ItemSpec) Then
            ' numeric -- going by index number
            ItemSpec = CLng(ItemSpec)
        Else
            ' string -- going by key name, so get rid of any quotes.
            ItemSpec = Replace(ItemSpec, """", "")
        End If
        '
        ' call the Item method of the collection object.
        '
        Set Obj = CallByName(Obj, Mid(PropArr(Ndx), 1, Pos1 - 1), _
            VbGet, ItemSpec)
    Else
        '
        ' we're not dealing with collections. just get the object.
        '
        Set Obj = CallByName(Obj, PropArr(Ndx), VbGet)
    End If
Next Ndx
'
' get the final property (typically 'name' or 'value' of the object tree)
'
If IsObject(Obj) Then
    GetInfo = CallByName(Obj, PropArr(UBound(PropArr)), VbGet)
End If

End Function

You can download an example workbook here. This workbook contains many examples of using GetInfo.