Most Valuable Professional

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

Subscribe to our mailing list

* indicates required

Audit !!!

Check out our RefTreeAnalyser
the ultimate Excel formula auditing tool.


Excel VBA Masterclass (English)
Excel VBA for Financials (Dutch)

Third party tools

Speed up your file

The best tool to optimise your Excel model!

What has changed?

PerfectXL Compare, for Spreadsheet Comparison
This Excel compare tool is fast, reliable, and easy to use. Compare two Excel files now and see for yourself!
Home > English site > Articles > Custom Find
Deze pagina in het Nederlands

Finding cells matching a specific property using the CallByName function


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.


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.


CallByName(object, procname, calltype,[args()])

The CallByName function syntax has these named arguments:

Required; Variant (Object). The name of the object on which the function will be executed.
Required; Variant (String). A string expression containing the name of a property or method of the object.
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)
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)

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)
            If CallByName(oTemp, vProps(lProps), VbGet) = vValue Then
                If bDoneOne Then
                    Set oResultRange = Union(oResultRange, oCell)
                    Set oResultRange = oCell
                    bDoneOne = True
                End If
            End If
    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


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:

Please enter your name (required):

Your e-mail address (optional, will only be used to inform you when your comment is published or to respond to your question directly):

Your request or comment (max 2000 characters):

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

I give permission to process this data and display my name and my comment on this website accoring to our Privacy Policy.