Solved

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

Posted on 2010-08-18
8
1,724 Views
Last Modified: 2012-08-14
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.
0
Comment
Question by:Alan3285
  • 3
  • 3
  • 2
8 Comments
 
LVL 58

Expert Comment

by:cyberkiwi
ID: 33470178
See the solution here:

http:Q_26196308.html

It has a sample function that takes array/range.
0
 
LVL 12

Author Comment

by:Alan3285
ID: 33470427
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
 
LVL 81

Expert Comment

by:byundt
ID: 33470615
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
 
LVL 12

Author Comment

by:Alan3285
ID: 33470653
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
Is Your Active Directory as Secure as You Think?

More than 75% of all records are compromised because of the loss or theft of a privileged credential. Experts have been exploring Active Directory infrastructure to identify key threats and establish best practices for keeping data safe. Attend this month’s webinar to learn more.

 
LVL 58

Assisted Solution

by:cyberkiwi
cyberkiwi earned 200 total points
ID: 33470673
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
 
LVL 58

Expert Comment

by:cyberkiwi
ID: 33470678
To test:

=ADD2(C1:C3,D1:D3)
=ADD2({1,2,3},D1:D3)
0
 
LVL 81

Accepted Solution

by:
byundt earned 300 total points
ID: 33470767
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
 
LVL 12

Author Closing Comment

by:Alan3285
ID: 33470989
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

Featured Post

Is Your Active Directory as Secure as You Think?

More than 75% of all records are compromised because of the loss or theft of a privileged credential. Experts have been exploring Active Directory infrastructure to identify key threats and establish best practices for keeping data safe. Attend this month’s webinar to learn more.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Suggested Solutions

This tutorial explains how to create a series of drop-down lists that are dependent upon prior selections to guide (“force”) the user to make the correct selection and reduce data errors within Microsoft Excel. Excel 2010 was used for this tutorial;…
This article descibes how to create a connection between Excel and SAP and how to move data from Excel to SAP or the other way around.
This Micro Tutorial will demonstrate the scrolling table in Microsoft Excel using the INDEX function.
This Micro Tutorial demonstrates in Microsoft Excel how to consolidate your marketing data by creating an interactive charts using form controls. This creates cool drop-downs for viewers of your chart to choose from.

910 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

20 Experts available now in Live!

Get 1:1 Help Now