Excel UDF - How to pass either a Range OR an Array to a UDF

Hi All,

I am wanting to write a UDF that will accept either a range or an array as inputs.

For the example, I'll say that I have the follow numbers types into cells:

B2 = 2
B3 = 3
C2 = 4
C3 = 6

This is a very simple example that does work (using just range inputs):

Public Function ADD2(Arg1 As Range, Arg2 As Range)
    ADD2 = WorksheetFunction.Sum(Arg1) + WorksheetFunction.Sum(Arg2)
End Function

If I put =ADD2(B2:B3,C2:C3) into a cell, it correctly (and trivially) returns 15.

Fine.

Now, I would like to be able to pass either an array or a range to the UDF thus:

=ADD2(VALUE(B2:B3),C2:C3)

If I evaluate those arguments in the user interface they evaluate identically to the first example, but of course VALUE(B2:B3) is not a range so the cell returns the #VALUE! error.

I tried changing the UDF to accept a Variant type as input like this but it doesn't work either (still gets the #VALUE! error in the cell):

Public Function ADD2(Arg1 As Variant, Arg2 As Variant)
    ADD2 = WorksheetFunction.Sum(Arg1) + WorksheetFunction.Sum(Arg2)
End Function


Ideally, I would like to be able for the user to pass either of the two arguments as either a range, or an array, but if easier I can make it so that only one of the two is optionally either.

If that is still too difficult, I guess I could get the user to wrap any range arguments in VALUE() to make them both arrays and have no ranges at all (but at least one must be an array result of a sub-formula and cannot be a straight range).


Hope that makes sense!

Thanks,

Alan.
LVL 22
AlanConsultantAsked:
Who is Participating?
 
byundtConnect With a Mentor Commented:
You need to array enter the function in the snippet if you are using a Boolean expression like:
=ADD2x(((B2:B3>1)*(B2:B3)),C2:C3)

Otherwise, you can use regular formulas.

Brad
Public Function ADD2x(Arg1 As Variant, Arg2 As Variant) As Double
Dim v As Variant
For Each v In Arg1
    ADD2x = ADD2x + Val(v)
Next
For Each v In Arg2
    ADD2x = ADD2x + Val(v)
Next
End Function

Open in new window

0
 
cyberkiwiCommented:
See the solution here:

http:Q_26196308.html

It has a sample function that takes array/range.
0
 
AlanConsultantAuthor Commented:
Hi CyberKiwi,

Thanks for the quick response.

I had a look at that question, and then tried this (to test my understanding):



Public Function ADD2(Arg1 As Variant, Arg2 As Variant)

    If IsArray(Arg1) Then
       
        ADD2 = "<Arg1 Array>"
       
    End If
   
    If IsArray(Arg2) Then
   
        ADD2 = ADD2 & "<Arg2 Array>"

    End If
   
   
    If ADD2 = "" Then
   
        ADD2 = "Neither Array"

    End If


    Debug.Print "Arg1(1) = " & Arg1(1)
    Debug.Print "Arg1(2) = " & Arg1(2)
    Debug.Print "Arg2(1) = " & Arg2(1)
    Debug.Print "Arg2(2) = " & Arg2(2)

End Function


With the same numerics in the same cells as per OP, the formula =add2(B2:B3,C2:C3) returns a cell value of "<Arg1 Array><Arg2 Array>" so it thinks the ranges are arrays (which is fine).

It returns the following in the immediate window in the VBE:

Arg1(1) = 1
Arg1(2) = 2
Arg2(1) = 3
Arg2(2) = 4

Which is also great - I can work with that.


However,.....

It appears that the arrays are unbounded.  If I now put B4 = 7 and C4 = 8, and then amend the code to this:

Public Function ADD2(Arg1 As Variant, Arg2 As Variant)

    If IsArray(Arg1) Then
       
        ADD2 = "<Arg1 Array>"
       
    End If
   
    If IsArray(Arg2) Then
   
        ADD2 = ADD2 & "<Arg2 Array>"

    End If
   
   
    If ADD2 = "" Then
   
        ADD2 = "Neither Array"

    End If


    Debug.Print "Arg1(1) = " & Arg1(1)
    Debug.Print "Arg1(2) = " & Arg1(2)
    Debug.Print "Arg1(3) = " & Arg1(3)
    Debug.Print "Arg2(1) = " & Arg2(1)
    Debug.Print "Arg2(2) = " & Arg2(2)
    Debug.Print "Arg2(3) = " & Arg2(3)

End Function


it still evaluates in the cell the same, but the immediate window now displays:

Arg1(1) = 1
Arg1(2) = 2
Arg1(3) = 7
Arg2(1) = 3
Arg2(2) = 4
Arg2(3) = 8


The problem I now have would be how to work out where to stop reading in the elements of the array since UBOUND won't work.

Thanks,

Alan.








0
Free Tool: Subnet Calculator

The subnet calculator helps you design networks by taking an IP address and network mask and returning information such as network, broadcast address, and host range.

One of a set of tools we're offering as a way of saying thank you for being a part of the community.

 
byundtCommented:
You can use VarType and TypeName functions to figure out what type of variant you are dealing with.
Function VariableType(v As Variant) As String
Dim cel As Range
VariableType = "Unknown"
Select Case TypeName(v)
Case "Variant()"
    If VarType(v) >= vbArray Then VariableType = "Array"
Case Else
    VariableType = TypeName(v)   '  "Range" is one of the possibilities
End Select
End Function

Open in new window

0
 
AlanConsultantAuthor Commented:
Hi Byundt,

Sorry, but I'm not sure how to apply that to my problem.

My function current stands as:

Public Function ADD2(Arg1 As Variant, Arg2 As Variant)
    ADD2 = WorksheetFunction.Sum(Arg1) + WorksheetFunction.Sum(Arg2)
End Function

but returns #VALUE! if the cell formula is:

=add2(((B2:B3>1)*(B2:B3)),C2:C3)

NB:  I changed from VALUE(B2:B3) to the above to be more explicit about why I cannot just pass a raw range to the UDF - same issue though either way.

Thanks,

Alan.
0
 
cyberkiwiConnect With a Mentor Commented:
If you must work with arrays, for the bottom of your function, a test

    If IsArray(Arg1) Then
        If TypeName(Arg1) = "Range" Then
            Arg1 = WorksheetFunction.Transpose(Arg1)
        End If
        Dim i As Integer
        For i = 1 To UBound(Arg1)
            ADD2 = ADD2 & Arg1(i) & ","
        Next
    End If

However, the FOR EACH x in Arg1 works just as well regardless of array or range, if you can work with it.
0
 
cyberkiwiCommented:
To test:

=ADD2(C1:C3,D1:D3)
=ADD2({1,2,3},D1:D3)
0
 
AlanConsultantAuthor Commented:
Thanks Guys - Got me to exactly what I needed.

I have split the points since you both walked me through it to the final solution.

Much appreicated.

Alan.
0
All Courses

From novice to tech pro — start learning today.