Try something like

Function RandName(ElementList As String) As String
Dim N As Long
Dim R As Range
Set R = Range(ElementList)
N = R.Cells.Count
N = Int(N) * Rnd + 1
RandName = R(N)
End Function

Then, define a name of "TheName" (no quotes) to refer to A1:A10. Then,
in cell C1, enter "TheName" (without the quotes). Finally, call the
function with

=RandName(C1)

The function will return a random value from the list that is
referenced either by name or address in C1. So, if C1 = "TheName" (no
quotes), the function will look in the range TheName and retrieve a
value from that range. Similarly, C1 could contain "A1:A100" and the
function would return a value from A1:A100.

Cordially,
Chip Pearson
Microsoft MVP 1998 - 2010
Pearson Software Consulting, LLC
www.cpearson.com
[email on web site]





On Tue, 2 Feb 2010 23:22:48 +0100, "H.G. Lamy"
wrote:

Hello,

I often use this simple UDF:

Public Function RANDNAME(ElementList)
Application.Volatile (True)
RANDNAME = WorksheetFunction.Index(ElementList, (Rnd() *
WorksheetFunction.CountA(ElementList)) + 1)
End Function

which provides a random text element from a list (named range). It works
fine.

However, I always have to manually type the name of the element list (named
range) into the function's argument panel.

I would much prefer to point to a cell address which already contains that
name.

How could I possibly adapt the above UDF, so that a cell with the element
list's name would suffice ?

Thank you in advance.

Kind regards,

H.G. Lamy