Is there a way to create a VBA function that can take in both arrays and ranges as input?

Question

I am trying to create a function that can take in both a range or an array to perform some further calculations. When an array passes, the function worked fine, but when the function is used on range in the worksheet, it gives me the VALUE! error.

My code looks like:

Function COMRET(data as variant, N as integer)

     Dim nrows as long

     If IsArray(data) Then
        N = UBound(data,1)
     Else
        N = data.rows.count
     End If

     '... some other calculations here

End Function

The problem seems to come from the identification of an array above... other parts of the code seems OK when I comment out the IF section above. Not sure what I am doing wrong here. Appreciate the help. Thanks!

GSerg · Accepted Answer · 2019-07-23T08:49:55.493

You are correct to declare data as Variant.
When you pass a range into that, it will be correctly passed as Variant/Object/Range, but then things will get a little bit more complicated.

When you call IsArray, which is a mere function, it will try to use the default property of the passed Range object, which is Value. IsArray(Range.Value) is True provided that the Range consist of more than one cell. But when you then call UBound, which is a very special function because it is highlighted as a keyword, it will not try to fetch data.Value and will operate on data directly, and data is a single Range object, so it cannot have an upper bound (while its .Value can).

You need to properly detect what you are being passed:

Public Function COMRET(data As Variant, N As Integer)
    Dim nrows As Long

    If TypeOf data Is Range Then
        N = data.Rows.Count
    ElseIf IsArray(data) Then
        N = UBound(data, 1)
    Else
        ' ???
    End If

End Function

Thanks.. I think I prefer this solution to my problem. – LYang Jul 24 '19 at 06:03 — LYang, Jul 24 '19 at 06:03

Tim Stack · Answer 2 · 2019-07-23T08:29:05.633

You're passing data as a Variant, which will cause IsArray(data) to always come out as true, regardless of whether you pass a range or array to it when calling the function.

Adding a third compulsory argument would work too, indicating the data type of the data variable:

Function COMRET(data As Variant, Is_Array As Boolean, N As Integer)

     Dim nrows As Long

     If Is_Array = True Then
        N = UBound(data, 1)
     Else
        N = data.Rows.Count
     End If

     '... some other calculations here

End Function

Alternatively, you could add a check that uses the error thrown by attempting to use UBound on a range. This clears the need of a third argument:

Function COMRET(data As Variant, N As Integer)

    Dim nrows As Long

    On Error Resume Next
        N = UBound(data, 1)
        If Err.Number <> 0 Then N = data.Rows.Count
        Err.Clear
    On Error GoTo 0

    '... some other calculations here

End Function

I see... thanks for the quick answer. Will adapt my code accordingly. — LYang, Jul 23 '19 at 08:40

score 0 · Answer 3 · answered Jul 23 '19 at 09:00

VarType() should be your friend:

' Const vbArray = 8192 (&H2000)
' Const vbString = 8
Debug.Print VarType(data)  ' = vbArray (8192) Or vbString (8) = 8200

' Is data an array of sorts?
Debug.Print CBool((VarType(data) And vbArray) = vbArray)

From MSDN help:

The VarType function never returns the value for vbArray by itself. It is always added to some other value to indicate an array of a particular type. The constant vbVariant is only returned in conjunction with vbArray to indicate that the argument to the VarType function is an array of type Variant. For example, the value returned for an array of integers is calculated as vbInteger + vbArray, or 8194. If an object has a default property, VarType (object) returns the type of the object's default property.

Is there a way to create a VBA function that can take in both arrays and ranges as input?

3 Answers3

Linked