User Defined Range

[caveman.savant]

I have a UDF that calculates the percentage of a value (rVal) based on
a sum of similar values (rTot).
It returns a value multipled by another value (rTarg) and rounded.

Function FigurePert(rTot As Integer, rVal As Integer, rTarg) As
Variant
xx = (rVal / rTot)
FigurePert = Round(xx * rTarg, 0)
End Function

I would like rTot be the value of a given range that the user chooses
by a defined Name or selected.

 

[Niek Otten]

It's always better to supply input to the function via arguments, the way
you do now. That is the only way Excel can determine dependencies. Any other
way cells may not recalculate when needed or in the wrong order.
Sometimes adding Application.Volatile is suggested as a solution, but there
is no guarantee this will always work correctly and potentially it
calculates too often.

 

[caveman.savant]

The range can be large and dynamic so typing in the values would be
difficult

 

[Niek Otten]

You don't have to type the vales, just supply a reference to the range as an
argument to the function.

But what do you want the function to do with rTot? It is defined an Integer
now, I don't see what it should do with a range.
BTW, use Long instead of Integer to avoid overflow and improve calculation
speed.

--
Kind regards,

Niek Otten
Microsoft MVP - Excel

The range can be large and dynamic so typing in the values would be
difficult