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.
Note: If I double click to edit a working result, do
nothing, and hit a return; it becomes a non-working
result. (????)
Any Ideas???
Better ways?
I can work around this by changing/adding other VBA
code,
but I want to understand the error of my ways.
I have added a Application.Volatile statement, fully
defined the range (wB.wS.Range), added quotes to the
parameter string, taken them away, and many other tries.
Thanks for your help in advance,
Joe Adams
Private Function SortListElem(st_List As String, 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 =
Worksheets("Unique Lists").Range(st_List).Value
''' "rng_FormsList"
n = UBound(varSItems, 1)
''' Debug.Print n
ReDim Preserve varSItems(1 To n, 1 To 1)
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
.