Unusual Operation of a User Defined Function

[Joe Adams]

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

 

[Charles Williams]

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

 

[Mark Bigelow]

One thing clicked when you said you double-click in the cell and then do
nothing, hit return and it doesn't work. I've had a similar thing
happen. I'll use a formula to split a number from a different cell
(e.g., =LEFT(A1, 10)). Then, I paste special values the cell containing
the formula. That value will be left-aligned and treated as text, until
I double-click the cell and hit return. So, the reason your formula
doesn't work, I imagine, is because it's trying to compare a cell with a
number with a cell with text. Try putting VALUE() functions in or
Cint() functions.

Please let me know if this works, as I am interested in finding the
cause of this problem!

 

[Joe Adams]

Thanks for the Ideas, but neither worked.
But, the next response did.

Joe

 

[Joe Adams]

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

 

[Charles Williams]

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]

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