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?

Answer 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...

1. 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.

2. If param contains an array, it assigns that array to the same variable.

3. If param contains a single value, it assigns that value to the same variable.

Answer 2

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