User-Defined Function Unusual Operation, Again
 

Follow-up of 10-Jul discussion. Please Help me again
(especially Charles of Decision Models).
I know I have missed something fundimental, again.

The UDF worked on the 11th but not today?? - so I have
added some additional error handleing code, to no avail.

Symptom - the UDF gives me a #Value! error if I double
click to edit a cell with the UDF, do nothing, and hit a
return. All other cells which have the UDF return #Name? .

I have a variable range, rng_FormsList, which I would like
to sort actively within the worksheet.
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: See above symtoms.

During debugging, I do not get any values passed to the
intermediate window through the Debug.Print statements.

Any Ideas???

Thanks for your help in advance,

Joe Adams


Private Function SortListElem(st_List As Range,
m As Variant) As Variant
On Error GoTo FuncFailed
''' Dim Variables
Dim varSItems As Variant
Dim varSItems2() As Variant
Dim k As Long
Dim n As Long
If Not IsEmpty(st_List) Then
Debug.Print IsEmpty(st_List)
End If
If IsEmpty(m) Then
Debug.Print m
End If
Debug.Print m
If m <= 0 Then GoTo ErrTrap
varSItems = st_List.Value
If Not IsArray(varSItems) Then
If m 1 Then GoTo ErrTrap
SortListElem = varSItems ' Single Item
Else
n = UBound(varSItems, 1)
If m n Then GoTo ErrTrap
' Put 2 dimensional range array into a
' single dim array
' 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
Exit Function
ErrTrap:
SortListElem = vbNullString
Exit Function
FuncFailed:
SortListElem = CVErr(xlErrValue)
Debug.Print CVErr(xlErrValue)
End Function

User-Defined Function Unusual Operation, Again
 

Hi Joe,

You should be using
=SortListElem(rng_FormsList,$G14)
not
=SortListElem("rng_FormsList",$G14)


hth
Charles
______________________
Decision Models
The Excel Calculation Site
www.DecisionModels.com

"Joe Adams" wrote in message
...
Follow-up of 10-Jul discussion. Please Help me again
(especially Charles of Decision Models).
I know I have missed something fundimental, again.

The UDF worked on the 11th but not today?? - so I have
added some additional error handleing code, to no avail.

Symptom - the UDF gives me a #Value! error if I double
click to edit a cell with the UDF, do nothing, and hit a
return. All other cells which have the UDF return #Name? .

I have a variable range, rng_FormsList, which I would like
to sort actively within the worksheet.
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: See above symtoms.

During debugging, I do not get any values passed to the
intermediate window through the Debug.Print statements.

Any Ideas???

Thanks for your help in advance,

Joe Adams


Private Function SortListElem(st_List As Range,
m As Variant) As Variant
On Error GoTo FuncFailed
''' Dim Variables
Dim varSItems As Variant
Dim varSItems2() As Variant
Dim k As Long
Dim n As Long
If Not IsEmpty(st_List) Then
Debug.Print IsEmpty(st_List)
End If
If IsEmpty(m) Then
Debug.Print m
End If
Debug.Print m
If m <= 0 Then GoTo ErrTrap
varSItems = st_List.Value
If Not IsArray(varSItems) Then
If m 1 Then GoTo ErrTrap
SortListElem = varSItems ' Single Item
Else
n = UBound(varSItems, 1)
If m n Then GoTo ErrTrap
' Put 2 dimensional range array into a
' single dim array
' 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
Exit Function
ErrTrap:
SortListElem = vbNullString
Exit Function
FuncFailed:
SortListElem = CVErr(xlErrValue)
Debug.Print CVErr(xlErrValue)
End Function