Pearson Software Consulting Services

    GetInfo Function For VBA 

The standard Excel worksheet functions allow you to retrieve only a very limited set of information about worksheet cells or the worksheet or workbook that contains the cell.  For example, there is no simple function that allows you to retrieve the name of the worksheet.  Of course, you can write a formula to do this, or you could write a User Defined Function (UDF) in VBA to return the sheet name.  You could write a similar UDF to return the name of the workbook.

The trouble with this approach is that a different UDF is required for each type of information you want to retrieve. It would be much easier to have a general information function that would allow you to retrieve any property of any object in the Excel object model.

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.
 

 

        

 

 

 Created By Chip Pearson and Pearson Software Consulting, LLC 
This Page:                Updated: November 06, 2013     
MAIN PAGE    About This Site    Consulting    Downloads  
Page Index     Search    Topic Index    What's New   
Links   Legalese And Disclaimers
chip@cpearson.com

Copyright 1997-2007  Charles H. Pearson