Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
RefEdit in UDF ?
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 |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
RefEdit in UDF ?
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 |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
RefEdit in UDF ?
Chip,
thank you very much ! (And your website is a treasure, hopefully to be continued...) Kind regards, hgl "Chip Pearson" wrote in message ... 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 |
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
RefEdit in UDF ?
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 |
#5
Posted to microsoft.public.excel.programming
|
|||
|
|||
RefEdit in UDF ?
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] On Tue, 2 Feb 2010 19:26:47 -0800 (PST), Greg Glynn wrote: 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 |
#6
Posted to microsoft.public.excel.programming
|
|||
|
|||
RefEdit in UDF ?
Thank you for the correction, Chip!
hgl "Chip Pearson" wrote in message ... 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] On Tue, 2 Feb 2010 19:26:47 -0800 (PST), Greg Glynn wrote: 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 |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
REFEDIT.DLL??? | Excel Programming | |||
To refedit, or not to refedit, that is the question. | Excel Programming | |||
RefEdit ??? | Excel Programming | |||
RefEdit in VB6 | Excel Programming | |||
RefEdit | Excel Programming |