Introduction
I thought it would be nice to have a generic VBA function to which we
could pass a range object, a string indicating a property of the object
and the property's value, which would then return all cells matching
that criteria.
CallByName
I decided it was time to explore the CallByName function, introduced
with Office 2000. According to Excel XP VBA Help:
CallByName Function
Executes a method of an object, or sets or returns a property of an
object.
Syntax
CallByName(object, procname, calltype,[args()])
The CallByName function syntax has these named arguments:
object
Required; Variant (Object). The name of the object on
which the function will be executed.
procname
Required; Variant (String). A string expression
containing the name of a property or method of the
object.
calltype
Required; Constant. A constant of type vbCallType
representing the type of procedure being called. Can be
vbGet (to return a property), vbLet (to change a
property), vbMethod (to execute a method) or vbSet (to
set an Object)
args()
Optional: Variant (Array).
Suppose we'd want to find out the colorindex of a cell's interior:
Sub test()
MsgBox CallByName(ActiveCell.Interior, "Colorindex", VbGet)
End Sub
Since I would like to make this method a bit more general, I would
like to just pass the cell object and the entire "procname" in a string:
Sub test()
MsgBox CallByName(ActiveCell, "Interior.Colorindex", VbGet)
End Sub
Unfortunately, this does not work, "procname" only accepts a single
entity (Property, Method or Object). So it is necessary to split up the
"procname" string into its individual elements.
Something like this (note: Excel 97 doesn't have the Split function,
nor the CallByName function):
Dim lProps As
Long
Dim vProps As
Variant
vProps = Split(sProperties, ".")
lProps = UBound(vProps)
So to get at the colorindex property of the Interior object of the
Cell object, we need to loop through the variant vProps:
For lCount = 0 To lProps - 1
Set oTemp = CallByName(oTemp, vProps(lCount), VbGet)
Next
We stop the loop at the one-but-last element of vProps, because all
of the elements except the last one will be objects and the last one
will be the property we're interested in. Then we get the property of
the last object the loop has given us:
CallByName(oTemp, vProps(lProps), VbGet)
The complete function is shown below:
Function FindCells(ByRef
oRange As Range, ByVal
sProperties As String,
_
ByVal
vValue As Variant)
As Range
Dim oResultRange
As Range
Dim oArea As
Range
Dim oCell As
Range
Dim bDoneOne
As Boolean
Dim oTemp As
Object
Dim lCount
As Long
Dim lProps
As Long
Dim vProps
As Variant
vProps = Split(sProperties, ".")
lProps = UBound(vProps)
For Each
oArea In oRange.Areas
For
Each oCell In
oArea.Cells
Set
oTemp = oCell
For
lCount = 0 To lProps - 1
Set
oTemp = CallByName(oTemp, vProps(lCount), VbGet)
Next
If
CallByName(oTemp, vProps(lProps), VbGet) = vValue
Then
If
bDoneOne Then
Set
oResultRange = Union(oResultRange, oCell)
Else
Set
oResultRange = oCell
bDoneOne = True
End
If
End
If
Next
Next
If Not
oResultRange Is Nothing
Then
Set FindCells = oResultRange
End If
End Function
A small example of its use, selecting all cells with a white fill:
Sub UseFindCellsExample()
FindCells(ActiveSheet.UsedRange, "Interior.ColorIndex",
0).Select
End Sub