Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
User Defined Function | Excel Worksheet Functions | |||
user defined function | Excel Worksheet Functions | |||
user defined function | Excel Worksheet Functions | |||
user defined function help | Excel Worksheet Functions | |||
Unusual Operation of a User Defined Function | Excel Programming |