Thanks for the feedback, I will visit your web site again.
This code seems to work.
Private Function SortListElem(st_List As Range, _
m As Long)
If m <= 0 Then Exit Function ' Error Trap
Dim varSItems() As Variant
Dim varSItems2() As Variant
Dim k As Long
Dim n As Long
varSItems = st_List.Value
If Not IsArray(varSItems) Then
SortListElem = varSItems ' Single Item
Else
n = UBound(varSItems, 1)
If m n Then Exit Function ' Error Trap
ReDim varSItems2(1 To n)
For k = 1 To n
varSItems2(k) = varSItems(k, 1)
Next k
Call dhQuickSort(varArray:=varSItems2)
SortListElem = varSItems2(m)
End If
End Function
Thanks again,
Joe
-----Original Message-----
Hi Joe,
Well there were a number of funnies in the code and I am
not sure exactly
which one caused what symptom:
- you should assign a range to a variant not to an array
of variants: they
are not the same thing, even though you can subsequently
reference the
subscripts in the same way
- I dont know what redim preserve does on a variant
containing an array, but
its not needed when you assign an array or a range to a
variant.
- If you pass the name of a range name as a string Excel
will not know when
it has changed so will not know when/how to recalculate
your function.
- the function would crash if the range name did not
refer to the hard-coded
worksheet
- better to put the dimension check inside the function
- should really add an on error handler
- better to have the first input parameter as a range
because then the user
can either use a defined name or a range.
- the second parameter should really be a variant.
see http://www.DecisionModels.com/calcsecretsj.htm for
more fun stuff on
UDFs
Charles
______________________
Decision Models
The Excel Calculation Site
www.DecisionModels.com
"Joe Adams" wrote in message
...
Thanks for the help. It worked in every instance.
Additional question: Why did assigning a range name
work
while the range method did not. I am trying to learn
as I
burn.
Joe
-----Original Message-----
Hi Joe,
Try this: assigns the defined name to a range variable,
no Redim Preserve
etc.
=SortListElem(rng_FormsList ,$G14)
Public Function SortListElem(st_List As Range, m As
Long)
If m = 0 Then Exit Function
Dim varSItems As Variant
Dim varSItems2() As Variant
Dim k As Long
Dim n As Long
''' Debug.Print st_List, m
varSItems = st_List.Value
''' "rng_FormsList"
n = UBound(varSItems, 1)
If m n Then Exit Function
''' Debug.Print n
ReDim varSItems2(1 To n)
For k = 1 To n
varSItems2(k) = varSItems(k, 1)
Next k
Call dhQuickSort(varArray:=varSItems2)
SortListElem = varSItems2(m)
End Function
hth
Charles
______________________
Decision Models
The Excel Calculation Site
www.DecisionModels.com
"Joe Adams" wrote in message
...
I have a variable range, rng_FormsList, which I would
like
to sort actively within the worksheet. I have
written a
function which somewhat works. I say somewhat
because
sometimes it works and other times it does not.
So, I know I have missed something fundimental.
I call the function (listed below) with a range
string
and
a positional element number to return that is located
in a
reference cell:
=SortListElem("rng_FormsList",$G14)
where,
rng_FormsList = OFFSET('Unique Lists'!$V$11,0,0,
COUNTIF('Unique Lists'!$V:$V,""""),1)
and say $G14 = 4
Note: To assure that I do not ask for an element
larger
than the length of the range I have an if statement
check
for validity.
Therefore, the formula in cell W14 is:
=IF($G14$N$9,"",
SortListElem("rng_FormsList",$G14))
Now, I copy this formula to cells W15:W25.
RESULT: Some cells have the correct value and some
cells
result in #VALUE!.
During debugging, I get the same string being passed,
st_List, and the proper desired element number, m.
(AOK so far)
But, after assigning the range to an array and
finding
the
Ubound of the array, n, I find that the cells which
have a
correct result have a value for n but the cells which
contain the #VALUE! result did not have a value for
n.