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.
function described on this page does exactly that. The
code of is show below:
The calling syntax of is the following:
is either a Range object (the only type of
object you can pass directly from a cell), or one of the following string
"APP" or "APPLICATION"
"WB" or "TWB"
or "WORKBOOK" or "THISWORKBOOK"
or "WORKSHEET" or "THISWORKSHEET"
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
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.
Return the formula in A1:
Return the Workbook 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.
If you want to use the key name rather than the index of a collection,
either omit the quotes entirely, or double the quotes:
Many more examples are provided in the
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
' Otherwise, it better be one of the following strings. Else,
' blow up the user.
Select Case UCase(TopObj)
Case "APP", "APPLICATION"
Set Obj =
Case "WB", "TWB", "THISWORKBOOK",
Set Obj =
Case "WS", "TWS", "THISWORKSHEET",
Set Obj =
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
Pos2 = InStr(1, PropArr(Ndx), ")")
ItemSpec = Mid(PropArr(Ndx), Pos1 +
1, Pos2 - Pos1 - 1)
If IsNumeric(ItemSpec) Then
' numeric --
going by index number
' string --
going by key name, so get rid of any quotes.
Replace(ItemSpec, """", "")
' call the Item method of the
Set Obj = CallByName(Obj,
Mid(PropArr(Ndx), 1, Pos1 - 1), _
' we're not dealing with collections.
just get the object.
Set Obj = CallByName(Obj, PropArr(Ndx),
' get the final property (typically 'name' or 'value' of the object tree)
If IsObject(Obj) Then
GetInfo = CallByName(Obj, PropArr(UBound(PropArr)), VbGet)
You can download an example workbook here.
This workbook contains many examples of using GetInfo.