Finding cells matching a specific property using the CallByName function

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:

Part

Description

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

Comments

Loading comments...