VBA Function That Works with Range and Array

Question

I am trying to write a UDF that takes either a range or an array from the user and iterates over it. If I declare it as a range like so:

Function Test(param As Range) As Variant
    Dim total As Integer
    Dim cell As Range
    total = 0
    For Each cell In param
        total = total + cell.Value2
    Next
    Test = total
End Function

it works fine when called like =TEST(C22:C24) but gives an error when called like =TEST({1,2,3,4}). On the other hand if I declare it as a variant like so:

Function Test(param As Variant) As Variant
    Dim i As Integer, total As Integer
    total = 0
    On Error GoTo endfunc
    For i = 1 To 100
        total = total + param(i)
    Next
endfunc:
    Test = total
End Function

it works fine when called like =TEST({1,2,3,4}) but when called like =TEST(C22:C24) it keeps on going way past the end of the range I give it (which is why I gave it the upper bound of 100). I can't use UBound on the variant to get the upper bound, it gives an error. Is there any way to get one function to work in both situations?

user6585761 · Accepted Answer · 2024-03-07 03:00:14Z

1

Here's another way...

Function Test(param As Variant) As Variant

    Dim total As Integer
    Dim item As Variant
    
    param = param
    
    If IsArray(param) Then
        total = 0
        For Each item In param
            total = total + item
        Next
    Else
        total = param
    End If
    
    Test = total
    
End Function

This part param = param does the following...

If param contains a Range object, it assigns the array of values from the range to the same variable, since the Value property is the default property of a Range object.
If param contains an array, it assigns that array to the same variable.
If param contains a single value, it assigns that value to the same variable.

answered Mar 7, 2024 at 3:00

user6585761

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

2 Comments

FunThomas Over a year ago

Be carefull - if param is a Range and the Range contains formulas, the statement param = param will convert all formulas into values.

Sam_W Over a year ago

Followup: in my application I actually need to index into param, not just iterate over its elements. This is causing issues because an array formed from a range is two-dimensional but an array passed in natively is one dimensional, and param(i) and param(i, 1) are mutually incompatible. Is there any way I can use the same code to index into either structure? Do I just have to copy everything over into another array that I decide the dimensionality of?

Sam_W · Accepted Answer · 2024-03-07 02:47:31Z

0

Aha! Although VarType(param) gives 8204 in both cases TypeName(param) gives Range in the first case and Variant() in the second case. Using this the solution I found is

Function Test(param As Variant) As Variant
    Dim total As Integer
    total = 0
    If TypeName(param) = "Range" Then
        Dim cell As Range
        For Each cell In param
            total = total + cell
        Next
    Else
        Dim i As Integer
        For i = 1 To UBound(param)
            total = total + param(i)
        Next
    End If
    Test = total
End Function

answered Mar 7, 2024 at 2:47

Sam_W

695 bronze badges

Collectives™ on Stack Overflow

VBA Function That Works with Range and Array

2 Answers 2

2 Comments

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

2 Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Related