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

All comments about this page:

Comment by: Sancarn (21-4-2021 19:43:00) deeplink to this comment

This is pretty cool. You might want to look into stdVBA's stdLambda as an alternative solution :) Full disclosure - I am the maintainer of this project but it's free and open source :)

Documentation here: https://github.com/sancarn/stdVBA/blob/master/docs/stdLambda.md

Essentially it allows you to do:

set getProp = stdLambda.create("$1.prop1.prop2.prop3")
debug.print getProp(ofThisObject)

Currently there is no way to set properties, but we have the ability to call methods and call property getters :) Hope it helps you (or readers) out ^_^

Comment by: Jan Karel Pieterse (22-4-2021 13:34:00) deeplink to this comment

Hi Sancarn,

Quite interesting indeed, thanks!

Comment by: J. Woolley (6-1-2022 22:40:00) deeplink to this comment

I've read several articles about use of vbGet, vbLet, and vbMethod in CallByName, but I don't understand vbSet. Can you provide an example of use of vbSet in CallByName?

Comment by: Jan Karel Pieterse (7-1-2022 11:28:00) deeplink to this comment

If you have a class that has properties, you can use vbSet to set an object by its object property name in a string variable:

Option Explicit

Public mRng As Range

Public Property Get myRng() As Range
    Set myRng = mRng
End Property

Public Property Set myRng(newRng As Range)
    Set mRng = newRng
End Property

Sub demo()
    CallByName Me, "myRng", VbSet, ActiveCell
    MsgBox myRng.Value
End Sub

Comment by: J. Woolley (7-1-2022 16:59:00) deeplink to this comment

JKP:
Thanks for the vbSet example. I guess it is only for use with Class modules that have a Property Set procedure. I was trying to do something like the following, but I suppose it fails because the ActiveCell property is read-only:

Sub ATest()
    Dim obj As Object
    Set obj = Application
    CallByName obj, "ActiveCell", VbSet, (Range("C3"))
    Debug.Print obj.Name, obj.ActiveCell.Address
End Sub

Comment by: Jan Karel Pieterse (7-1-2022 17:51:00) deeplink to this comment

I guess you are correct :-)

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.