User Defined Range
 

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.

User Defined Range
 

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.

--
Kind regards,

Niek Otten
Microsoft MVP - Excel


"caveman.savant" wrote in message
...
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.

User Defined Range
 

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

On Feb 13, 6:45*am, "Niek Otten" wrote:
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.

--
Kind regards,

Niek Otten
Microsoft MVP - Excel

"caveman.savant" wrote in message

...



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.

User Defined Range
 

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

"caveman.savant" wrote in message
...
The range can be large and dynamic so typing in the values would be
difficult

On Feb 13, 6:45 am, "Niek Otten" wrote:
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.

--
Kind regards,

Niek Otten
Microsoft MVP - Excel

"caveman.savant" wrote in message

...



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.