Solved

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

Posted on 2010-08-18
8
1,799 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
PeopleSoft Has Never Been Easier

PeopleSoft Adoption Made Smooth & Simple!

On-The-Job Training Is made Intuitive & Easy With WalkMe's On-Screen Guidance Tool.  Claim Your Free WalkMe Account Now

 
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

Independent Software Vendors: We Want Your Opinion

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

Question has a verified solution.

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

Freeze panes is an option within all variants of Excel to enable parts of a sheet to remain stationary when the cursor is in another part of the sheet. This is a very useful feature which is overlooked or under used.
Do you use a spreadsheet like Microsoft's Excel?  Have you ever wanted to link out to a non excel file on your computer or network drive?  This is the way I found to do it!
The viewer will learn how to create two correlated normally distributed random variables in Excel, use a normal distribution to simulate the return on different levels of investment in each of the two funds over a period of ten years, and, create a …
This Micro Tutorial will demonstrate on a Mac how to change the sort order for chart legend values and decrpyt the intimidating chart menu.

730 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