Sorting a variant array

[George]

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]

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.

 

[Peter T]

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