Solved

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

Posted on 2010-08-18
8
1,689 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 80

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
How to run any project with ease

Manage projects of all sizes how you want. Great for personal to-do lists, project milestones, team priorities and launch plans.
- Combine task lists, docs, spreadsheets, and chat in one
- View and edit from mobile/offline
- Cut down on emails

 
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 80

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

Highfive Gives IT Their Time Back

Highfive is so simple that setting up every meeting room takes just minutes and every employee will be able to start or join a call from any room with ease. Never be called into a meeting just to get it started again. This is how video conferencing should work!

Join & Write a Comment

Convert between Excel file formats (.XLS, .XLSX, .XLSM) with/without macro option David Miller (dlmille) Intro Over this past Fall, I've had the opportunity to see several similar requests and have developed a couple related solutions associate…
Improved? Move/Copy Add-in Replacement - How to avoid the annoying, “A formula or sheet you want to move or copy contains the name XXX, which already exists on the destination worksheet.” David Miller (dlmille)  It was one of those days… I wa…
The viewer will learn how to simulate a series of sales calls dependent on a single skill level and learn how to simulate a series of sales calls dependent on two skill levels. Simulating Independent Sales Calls: Enter .75 into cell C2 – “skill leve…
This Micro Tutorial will demonstrate how to use a scrolling table in Microsoft Excel using the INDEX function.

707 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

15 Experts available now in Live!

Get 1:1 Help Now