RefEdit in UDF ?

[H.G. Lamy]

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

 

[Chip Pearson]

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]

 

[H.G. Lamy]

Chip,

thank you very much !
(And your website is a treasure, hopefully to be continued...)

Kind regards,

hgl

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]

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

 

[Greg Glynn]

This kind-of works and kind-of doesn't.

I have an 8 element test array and the code regularly generates 9 as
its random index number.

Greg

 

[Chip Pearson]

The line that generates the random index is bad. Change

N = Int(N) * Rnd + 1

To

N = ((Int(R.Cells.Count) - 1) * Rnd) + 1

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

 

[H.G. Lamy]

Thank you for the correction, Chip!

hgl

The line that generates the random index is bad. Change

N = Int(N) * Rnd + 1

To

N = ((Int(R.Cells.Count) - 1) * Rnd) + 1

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

This kind-of works and kind-of doesn't.

I have an 8 element test array and the code regularly generates 9 as
its random index number.

Greg