UDF with infinite parameters

Question

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"

how about using `Public Function get_best(r1 As Range, r2 As Range, r3 As Range, Optional r4 as Range, Optional r5 as Range, etc... ) As Variant` ? — Shai Rado, Apr 04 '17 at 08:08
FWIW - The `Else` on those `If` statements is redundant - you only need to include an `Else` on single-line Ifs (and even in block Ifs) if there is code you need to execute when the condition is `False`. — YowE3K, Apr 04 '17 at 08:41

score 1 · Answer 1 · answered Apr 04 '17 at 08:15

1

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)

answered Apr 04 '17 at 08:15

Subodh Tiwari sktneer

9,906
2
18
22

Sorry but the answer is no. This take the max of the range but I want the value with the best precision. If the example was B3=5; B4=6 and B5=4 it should show 4 (because B5 is better than B4 that is better than B3). – Nicolaesse Apr 04 '17 at 08:25
1

Why is 4 more precise than 5 or 6 in that example? – Robin Mackenzie Apr 04 '17 at 08:29
because the "definitive" value is more precise than the "provisional" value. I've added few words to the first post (= the question) to explain it. Hope that now it should be clearer. – Nicolaesse Apr 04 '17 at 09:45

user3598756 · Answer 2 · 2017-04-04T09:08:16.553

1

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

edited Apr 04 '17 at 09:08

answered Apr 04 '17 at 08:23

user3598756

28,893
4
18
28

Yes, but first one gives me an error. Excel gives me an error because UDF doesn't acept argoument and then VBA Editor says that "argoument not optional". The secondo one gives me "#VALUE!" if I gives as argoument a range of cells or if I give few cells. – Nicolaesse Apr 04 '17 at 09:49
1) the first code is _intentionally_ designed (and tested) for accepting no parameters: just call that UDF from a cell above the range to be scanned 2) I don't get the issue for the 2nd code: in this case the UDF _is_ designed (and tested) to accept a _"(contiguous) range"_ as a parameter so what do you mean for _"range of cells"_ or _"few cells"_ throwing issues? – user3598756 Apr 04 '17 at 11:18
1

@Nicolaesse, I'd appreciate your feedback on my code clarifications I gave you with my last comment. thank you – user3598756 Apr 06 '17 at 06:01

score 1 · Answer 3 · answered Apr 04 '17 at 08:24

1

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

answered Apr 04 '17 at 08:24

Darren Bartrup-Cook

18,362
1
23
45

"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". – Nicolaesse Apr 04 '17 at 08:28
Ah, ok. In that case how does Excel know which is a predicted value and which is the effective and measured values? If it's just the last value in a single column then the answer given by @user3598756 would be the one to go for. Mine will give the last cell in a list of cells - would be easier just to select the last cell and ignore the rest (i.e. change your function to =B5). :) – Darren Bartrup-Cook Apr 04 '17 at 08:34

score 1 · Accepted Answer · answered Apr 04 '17 at 08:30

1

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

answered Apr 04 '17 at 08:30

Robin Mackenzie

18,801
7
38
56

The function works! The only point is that it works if I write the formula in B1 and the values in B3:B5. If I write the same formula in B7, for example, I get a cyrcular reference errore from Excel. – Nicolaesse Apr 04 '17 at 09:57
Yep - if your formula is in B7 you can only pass B3:B6 to the function and not B:B. If you want to pass the full column (which I think is what you want for an 'infinite' list of parameters) then you will have to put the cell with the function in another column. HTH. – Robin Mackenzie Apr 04 '17 at 09:59
When I asked the question I was thinking about giving the formula the single cells in a specific order but this solution is OK and works for my application. – Nicolaesse Apr 04 '17 at 10:15

UDF with infinite parameters

4 Answers4