Hi

I see that Marcelo has already explained it, but since I had finished writing this maybe it will complement the help.

Yes, or, more exactly, excel does.

When you use a formula, for ex. C1 = SUM(A1, B1), you expect that when you change A1 or B1 the formula in C1 is reevaluated and the value in C1 is updated.

That's the point in excel, you write formulas and when any of its precedents changes value the formula value is updated.

When you define a udf with a parameter, like

You'll use in a cell something like =MyFun(A1) and excel knows that if A1 changes value this formula should be reevaluatedCode:Public Function MyFun(lMaxValue as Long) as Long

Let's say now that you use

and you use the value of A1 in the code of the udf.Code:Public Function MyFun() as Long

You'll use in a cell something like =MyFun() and if you change the value of A1 excel will not know that the formula should be reevaluated.

This means that the only way you have to be sure that a formula that uses a udf like this one is displaying the correct result is by refreshing it, meaning recalculating the worksheet or the workbook, or confirming the formula again.

This is why I said that it is usually considered bad practice to use an external value in a udf that is not included in the parameters. You cannot trust the result unless after a recaclulation.

In this specific case, you are using the named range MaxValueName reading it inside your code, not passing it as a parameter.

This means that if you change it's value all the formulas that use the udf may display an incorrect value until they are recalculated.

Hope it helps.

## Like this thread? Share it with others