Most Valuable Professional


View Jan Karel Pieterse's profile on LinkedIn subscribe to rss feed
Subscribe in a reader

Home > English site > Articles > Custom Find
Deze pagina in het Nederlands

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 it's 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

Have a question, comment or suggestion? Then please use this form.

If your question is not directly related to this web page, but rather a more general "How do I do this" Excel question, then I advise you to ask your question here: www.eileenslounge.com.

Please enter your name (required):

Your e-mail address (optional but if you want me to respond it helps!; will not be shown, nor be used to send you unsolicited information):

Your request or comment:

To post VBA code in your comment, use [VB] tags, like this: [VB]Code goes here[/VB].