Excel UDF to loop existing function through range

Question

I'm trying to put together a user defined function that will allow excel functions to work over a range rather than just one cell. Example would be =isnumber(value) that function will not work across a range of cells so to use it to check a range I would need to repeat it for each cell in the range.

Has anybody tried putting together something along the lines of =RangeFunct(range, function,[boolean])? This would do something along the lines of loop through each cell in the range into the existing function and stop at the boolean that doesn't match the one selected. Any help on setting this code up would be appreciated.

Tim Williams · Accepted Answer · 2013-06-07 16:05:44Z

2

Eg: =RangeFunc(A1:A10,"ISNUMBER")

Function RangeFunc(rng As Range, funct As String) As Boolean
    Dim c As Range
    Dim rv As Boolean
    rv = True
    For Each c In rng.Cells
        If rng.Parent.Evaluate(funct & "(" & c.Address() & ")") = False Then
            rv = False
            Exit For
        End If
    Next c
    RangeFunc = rv
End Function

You could always just use an array formula instead though:

=AND(ISNUMBER(A1:A10))

...entered using Ctrl+Shift+Enter

answered Jun 7, 2013 at 16:05

Tim Williams

169k8 gold badges104 silver badges143 bronze badges

Sign up to request clarification or add additional context in comments.

Comments

rwisch45 · Accepted Answer · 2013-06-07 15:23:12Z

Something simple like this will let you define a range as a string (i.e. "A1:B10") and then loop through each cell in that range. For each cell you can test a condition, modify that cell (highlight it or take note of it), etc. Here's a very simple example:

Public Function RangeFunct(ByVal myRange As String) As Boolean
    Dim cell As Range
    Dim checkRange As Range
    Set checkRange = Range(myRange)

    Dim bTemp As Boolean
    bTemp = True

    For Each cell In checkRange
        If cell.Value = 1 Then
            bTemp = False
            Exit For
        End If

    Next cell

    RangeFunct = bTemp
End Function

If you can be more specific as to what you want to test for and how you want to use it, then I can modify the code to suit your needs.

Collectives™ on Stack Overflow

Excel UDF to loop existing function through range

2 Answers 2

Comments

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Related