CODEWITHSUNDEEP
vbaexceluser-defined-functionsexcel-udf
Nicolaesse
Nicolaesse

Reputation: 2714

UDF with infinite parameters

I am writing a User Defined Function (UDF) which take some cells as argument. These cells contain the same data but with a different level of precision; the function show the best precision available.

The argument of the funcion are written in the order of ascending precision.

This is an example:

+---+-----------------+---------------------+
|   |        A        |          B          |
+---+-----------------+---------------------+
| 1 | Best            | =get_best(B5;B4;B3) |
| 2 |                 |                     |
| 3 | provisional     | 1                   |
| 4 | definitive      | 2                   |
| 5 | etched in stone | 12                  |
+---+-----------------+---------------------+

The function show 12, because information in cell B5 has a better value than the B4 and B3. For this reason B5 is written before B4 and B3 in the formula argoument.

The code of my UDF is the following:

Public Function get_best(r1 As Range, r2 As Range, r3 As Range) As Variant

    get_best = ""

    If r3.Value <> "" Then get_best = r3.Value Else
    If r2.Value <> "" Then get_best = r2.Value Else
    If r1.Value <> "" Then get_best = r1.Value

End Function

It works! but I would like to edit it so it could takes infinite agoument like =get_best(B7;B6;B5;B4;B3). How could I do that?

Useful comment: "cell B5 has a better value than the B4 and B3" means, for example, that in B3 you have the predicted value you calculated 12 months ago. In cell B5 you have the effective and measured value. So when you have B5 you don't need B3 anymore because "B5 is better than B3"

Upvotes: 1

Views: 851

Answers (4)

user3598756
user3598756

Reputation: 29421

you could avoid passing any range parameter this way

Public Function get_best() As Variant
    get_best = Cells(Rows.Count, Application.Caller.Column).End(xlUp).Value
End Function

while if you do must specify a (contiguous) range, you can act as follows:

Public Function get_best(r As Range) As Variant
    With r
        If WorksheetFunction.CountA(.Cells) > 0 Then get_best = .Cells(.Rows.Count + 1).End(xlUp).Value
    End With
End Function

Upvotes: 1

Robin Mackenzie
Robin Mackenzie

Reputation: 19319

If the best value is always at the bottom of a Range but you are not sure of the number of the rows in the column you are searching you can use this:

Public Function get_best(rng As Range) As Variant

    Dim lngLastRow As Long

    lngLastRow = rng.Parent.Cells(rng.Parent.Rows.Count, rng.Column).End(xlUp).Row
    get_best = rng.Parent.Cells(lngLastRow, rng.Column).Value

End Function

Upvotes: 1

Darren Bartrup-Cook
Darren Bartrup-Cook

Reputation: 19737

I've no idea what you mean by "cell B5 has a better value than the B4 and B3". Your code looks to see which cell contains a value starting from the last one in the arguments.

You could use a paramarray to add as many ranges as you like: 

Public Function get_best(ParamArray Ranges()) As Variant

    Dim x As Long

    For x = UBound(Ranges) To LBound(Ranges) Step -1
        If Ranges(x) <> "" Then
            get_best = Ranges(x).Value
            Exit For
        End If
    Next x

End Function

Upvotes: 1

Subodh Tiwari sktneer
Subodh Tiwari sktneer

Reputation: 9966

Will this not work for you based on example you have shown?

Public Function get_best(ByVal Rng As Range) As Variant
    get_best = Application.Max(Rng)
End Function

Then you can try it like this...

=get_best(B3:B5)

Upvotes: 1

Related Questions