Solved

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

Posted on 2010-08-18
8
1,772 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
Free Tool: Path Explorer

An intuitive utility to help find the CSS path to UI elements on a webpage. These paths are used frequently in a variety of front-end development and QA automation tasks.

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

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

Free Tool: IP Lookup

Get more info about an IP address or domain name, such as organization, abuse contacts and geolocation.

One of a set of tools we are providing to everyone as a way of saying thank you for being a part of the community.

Question has a verified solution.

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

Suggested Solutions

A little background as to how I came to I design this code: Around 5 years ago I designed an add-in that formatted Excel files to a corporate standard, applying different cell colours and font type depending on whether the cells contained inputs,…
When you see single cell contains number and text, and you have to get any date out of it seems like cracking our heads.
Graphs within dashboards are meant to be dynamic, representing data from a period of time that will change each time the dashboard is updated with new data. Rather than update each graph to point to a different set within a static set of data, t…
This Micro Tutorial will demonstrate in Microsoft Excel how to add style and sexy appeal to horizontal bar charts.

839 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