|
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.
|
|
|