Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
Unusual Operation of a User Defined Function
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 |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
Unusual Operation of a User Defined Function
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 |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
Unusual Operation of a User Defined Function
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! --- Mark Bigelow mjbigelow at hotmail dot com http://hm.imperialoiltx.com *** Sent via Developersdex http://www.developersdex.com *** Don't just participate in USENET...get rewarded for it! |
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
Unusual Operation of a User Defined Function
Thanks for the Ideas, but neither worked.
But, the next response did. Joe -----Original Message----- 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! --- Mark Bigelow mjbigelow at hotmail dot com http://hm.imperialoiltx.com *** Sent via Developersdex http://www.developersdex.com *** Don't just participate in USENET...get rewarded for it! . |
#5
Posted to microsoft.public.excel.programming
|
|||
|
|||
Unusual Operation of a User Defined Function
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 . |
#6
Posted to microsoft.public.excel.programming
|
|||
|
|||
Unusual Operation of a User Defined Function
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 . |
#7
Posted to microsoft.public.excel.programming
|
|||
|
|||
Unusual Operation of a User Defined Function
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. |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
User Defined Function Help | Excel Worksheet Functions | |||
user defined function | Excel Worksheet Functions | |||
user defined function | Excel Worksheet Functions | |||
User defined function | New Users to Excel | |||
User-defined function | Excel Worksheet Functions |