Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
Sorting a variant array
Having a problem sorting my variant array which I'm hoping someone will be
able to assist me with. I'm positive it's something I'm doing as the sorting algorithim I'm using works in all the examples supplied with the routine - it's the one from MS :-) The complete code I've got at the moment (including my pathetic attempts at Debug.Print to find out why it's not working is below): Dim tempList() As Variant Dim testerList() As String Function sortTesters(rangeName As String) Application.Volatile ' check if the input name exists If nameExists(rangeName) Then ' retrieve the named range 'Set namedRange = ThisWorkbook.Names.Item(rangeName) ' read the named range into an array tempList = Range(ThisWorkbook.Names.Item(rangeName).RefersTo) Debug.Print UBound(tempList) For Each tester In tempList Debug.Print tester ' ***1*** Next Dim i As Integer For i = LBound(tempList) To UBound(tempList) testerList(i) = tempList(i) Debug.Print testerList(i) ' ***2*** Next i 'Call BubbleSort(testerList) End If End Function I've commented out the BubbleSort call at the moment because I can't even move the Variant array into a String array which is what I thought might be causing the problem. The ***1*** debug line prints out all the staff one by one in the same order that's in the Range. This works fine, however when I try and move them (one by one) to a string array I get no debug output and (i presume) the routine falls over and exits. Even if I don't try the movement to a String Array the sorting algorithm doesn't do anything with the variant array. If I put a debug line in the sorting algorithm to output the number of items in the passed array it gives me the correct reading but as soon as it tries to "do something" with any value in the array it must bomb out - there's no further debug output and no error message. I'm at a bit of a loss as to what is happening here - the sorting code is fine (as I say trying it with the examples supplied gives me correct output) so it must be something stupid that I've done. Any pointers gratefully taken :-) George |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
Sorting a variant array
Maybe...
Option Explicit Dim tempList As Variant Dim testerList() As String Function sortTesters(rangeName As String) Dim Tester As Variant Dim i As Long Application.Volatile ' check if the input name exists 'If nameExists(rangeName) Then ' retrieve the named range 'Set namedRange = ThisWorkbook.Names.Item(rangeName) ' read the named range into an array tempList = Range(ThisWorkbook.Names.Item(rangeName).RefersTo) .Value Debug.Print UBound(tempList) ReDim testerList(LBound(tempList, 1) To UBound(tempList, 1)) For i = LBound(tempList, 1) To UBound(tempList, 1) testerList(i) = tempList(i, 1) Debug.Print testerList(i) ' ***2*** Next i 'Call BubbleSort(testerList) 'End If End Function When you pick up the values from a Range in a worksheet, you end up with a x-rows by y-columns array/matrix. In your case, I was guessing that you were picking up the values from a single column range--but even that ends up as a x-rows by 1 column array. ===== If you have a followup, you may want to include all the procedures--including the bubblesort and nameexists routine. George wrote: Having a problem sorting my variant array which I'm hoping someone will be able to assist me with. I'm positive it's something I'm doing as the sorting algorithim I'm using works in all the examples supplied with the routine - it's the one from MS :-) The complete code I've got at the moment (including my pathetic attempts at Debug.Print to find out why it's not working is below): Dim tempList() As Variant Dim testerList() As String Function sortTesters(rangeName As String) Application.Volatile ' check if the input name exists If nameExists(rangeName) Then ' retrieve the named range 'Set namedRange = ThisWorkbook.Names.Item(rangeName) ' read the named range into an array tempList = Range(ThisWorkbook.Names.Item(rangeName).RefersTo) Debug.Print UBound(tempList) For Each tester In tempList Debug.Print tester ' ***1*** Next Dim i As Integer For i = LBound(tempList) To UBound(tempList) testerList(i) = tempList(i) Debug.Print testerList(i) ' ***2*** Next i 'Call BubbleSort(testerList) End If End Function I've commented out the BubbleSort call at the moment because I can't even move the Variant array into a String array which is what I thought might be causing the problem. The ***1*** debug line prints out all the staff one by one in the same order that's in the Range. This works fine, however when I try and move them (one by one) to a string array I get no debug output and (i presume) the routine falls over and exits. Even if I don't try the movement to a String Array the sorting algorithm doesn't do anything with the variant array. If I put a debug line in the sorting algorithm to output the number of items in the passed array it gives me the correct reading but as soon as it tries to "do something" with any value in the array it must bomb out - there's no further debug output and no error message. I'm at a bit of a loss as to what is happening here - the sorting code is fine (as I say trying it with the examples supplied gives me correct output) so it must be something stupid that I've done. Any pointers gratefully taken :-) George -- Dave Peterson |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
Sorting a variant array
Hi George,
tempList = Range(ThisWorkbook.Names.Item(rangeName).RefersTo) (which btw you could write as tempList = ThisWorkbook.Range(rangeName).Value assuming you really do mean ThisWorkbook and not ActiveWorkbook (if not same) and assuming rangeName refers to a range, etc Anyway, the point is tempList will become a 2D array, even if its one column or one row. That means when you want to refer to it elements you will need to do tempList(i, 1) ' cells down column 1 tempList (1, i) ' cells accross row 1 I don't follow why you need to convert to strings. Regards, Peter T "George" wrote in message ... Having a problem sorting my variant array which I'm hoping someone will be able to assist me with. I'm positive it's something I'm doing as the sorting algorithim I'm using works in all the examples supplied with the routine - it's the one from MS :-) The complete code I've got at the moment (including my pathetic attempts at Debug.Print to find out why it's not working is below): Dim tempList() As Variant Dim testerList() As String Function sortTesters(rangeName As String) Application.Volatile ' check if the input name exists If nameExists(rangeName) Then ' retrieve the named range 'Set namedRange = ThisWorkbook.Names.Item(rangeName) ' read the named range into an array tempList = Range(ThisWorkbook.Names.Item(rangeName).RefersTo) Debug.Print UBound(tempList) For Each tester In tempList Debug.Print tester ' ***1*** Next Dim i As Integer For i = LBound(tempList) To UBound(tempList) testerList(i) = tempList(i) Debug.Print testerList(i) ' ***2*** Next i 'Call BubbleSort(testerList) End If End Function I've commented out the BubbleSort call at the moment because I can't even move the Variant array into a String array which is what I thought might be causing the problem. The ***1*** debug line prints out all the staff one by one in the same order that's in the Range. This works fine, however when I try and move them (one by one) to a string array I get no debug output and (i presume) the routine falls over and exits. Even if I don't try the movement to a String Array the sorting algorithm doesn't do anything with the variant array. If I put a debug line in the sorting algorithm to output the number of items in the passed array it gives me the correct reading but as soon as it tries to "do something" with any value in the array it must bomb out - there's no further debug output and no error message. I'm at a bit of a loss as to what is happening here - the sorting code is fine (as I say trying it with the examples supplied gives me correct output) so it must be something stupid that I've done. Any pointers gratefully taken :-) George |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Array problem: Key words-Variant Array, single-element, type mismatch error | Excel Programming | |||
Array problem: Key words-Variant Array, single-element, type mismatch error | Excel Programming | |||
ReDim Object array as parameter of Variant array | Excel Programming | |||
Variant Array | Excel Programming | |||
variant array containing cel adresses convert to actual ranges-array | Excel Programming |