Solved: Why is the sorted array Not being returned?

[RonMcK]

I want to sort the contents of individual cells: each cell contains between 0 and 16 codes in a comma-delimited string. The accompanying workbook contains my code for doing this. The only problem is that Sub BubbleSort(myArray as Variant) fails to return (or my Sub SortStdsString2() fails to receive) the sorted array.

On Sheet1, the source string in A1; the program copies the string to C1 before it begins slicing and dicing. Beneath that is my debug listing (captured in View Immediate window. There appears to be no problems loading the initial array (called myArry) and passing it into BubbleSort, where the array name is myArray. And, BubbleSort sorts the array as expected, ordering from lowest to highest.

The problem appears when I list the members of myArry immediately after returning to SortStdsString2 from BubbleSort. The array as displayed is no different from the array originally passed in to BubbleSort.

I'm puzzled. Am I doing something wrong or forgetting to do something?

Thanks in advance!

[Bob Phillips]

I get a compile error on this line

StdsStr = myArry

Type Mismatch.

[GTO]

Hello,

I believe the short answer is that you don't return 'MyArray' back from BubbleSort.

[VBA]BubbleSort (myArry)
Debug.Print "bubble sort done"[/VBA]

I couldn't see the need for the branching (GoTo), so I changed it up a bit (see 'basModuleThree'). I got a bit sloppy, but hopefully this helps :-)

[mikerickson]

Try this if you are on a Windows Machine, you might want to look at Split and Join.

[RonMcK]

GTO, thanks for pointing out that I needed to convert Sub BubbleSort into Public Function BubbleSort and assign myArray() to BubbleSort() before exiting back to Sort_Stds.

I cleaned up and simplified my original code for splitting the source string, I converted it to a Do While loop with a Case Select; those dreaded and feared GOTOs are gone.

Mikerickson, good suggestions but this code has to run on my Mac Desktop at work so I've not incorporated split/join.

Here is my code in its latest incarnation:[vba]Option Explicit

Sub Sort_String() ' (ByVal Target As Excel.Range)

Dim StdsStrIn As String
Dim StdsStrOut As String
Application.EnableEvents = False

StdsStrIn = Cells(1, 1)
Cells(1, 3) = StdsStrIn

ReDim myArry(20)
Dim my_i As Long
Dim my_j As Long
If Len(StdsStrIn) = 0 Then Exit Sub
my_i = -1
Do While Len(StdsStrIn) > 0
my_i = my_i + 1
my_j = InStr(1, StdsStrIn, ",")
Select Case my_j
Case 0
myArry(my_i) = StdsStrIn
StdsStrIn = ""
Case Else
myArry(my_i) = Trim(Left(StdsStrIn, my_j - 1))
StdsStrIn = Trim(Mid(StdsStrIn, my_j + 2, Len(StdsStrIn) - (my_j + 1)))
End Select
Loop
ReDim Preserve myArry(my_i)

myArry = BubbleSort(myArry)

For my_i = 0 To UBound(myArry)
Select Case my_i
Case 0
StdsStrOut = myArry(my_i)
Case Else
StdsStrOut = StdsStrOut & ", " & myArry(my_i)
End Select
Next my_i

Cells(1, 1) = StdsStrOut
Application.EnableEvents = True

End Sub

Public Function BubbleSort(MyArray As Variant)
'// courtesy of DRJ via MDMcKillop
Dim First As Integer
Dim Last As Integer
Dim i As Integer
Dim j As Integer
Dim Temp As String
Dim List As String

First = LBound(MyArray)
Last = UBound(MyArray)
For i = First To Last - 1
For j = i + 1 To Last
If MyArray(i) > MyArray(j) Then
Temp = MyArray(j)
MyArray(j) = MyArray(i)
MyArray(i) = Temp
End If
Next j
Next i
BubbleSort = MyArray
End Function

[/vba]

[GTO]

Glad to be of help Ron. Thanks for posting back too :-) I just couldn't get my head into the do loop this morning, as well as knew there had to be a way of not having to create/clean-up the leading/trailing slop in the return string that I had. Very nice/crisp! Mark

[RonMcK]

The following code can be substituted for the purple block of code in my program, above.
[vba] Dim LenStd As Long
Dim NumStds As Long

LenStd = InStr(1, StdsStrIn, ",") - 1
If LenStd = -1 Then LenStd = Len(StdsStrIn)
NumStds = Int(Len(StdsStrIn) / (LenStd + 2) + 0.5)

For my_i = 0 To NumStds - 1
myArry(my_i) = Mid(StdsStrIn, 1 + (LenStd + 2) * my_i, LenStd)
Next my_i

my_i = 0
ReDim Preserve myArry(NumStds - 1)
[/vba] Cheers,

[RonMcK]

Hi All,

Well, this solution didn't work on my Mac here at work. I apparently was blinded by the parentheses; this led us all of into forcing BubbleSort() to be a Function. The following change in Sort_String() reverts us to 'calling' BubbleSort() as a Sub. The Mac has no problem with this.[vba] ' myArry = BubbleSort(myArry) '// 'Can't Assign to Array' according to XL 2004 on Mac (works on PC w/ XL2002)
BubbleSort myArry '// Look! not parentheses around array being passed.[/vba][vba]Sub BubbleSort(myArry As Variant)
'// courtesy of DRJ via MDMcKillop
Dim First As Integer
Dim Last As Integer
Dim i As Integer
Dim j As Integer
Dim Temp As String
Dim List As String

First = LBound(myArry)
Last = UBound(myArry)
For i = First To Last - 1
For j = i + 1 To Last
If myArry(i) > myArry(j) Then
Temp = myArry(j)
myArry(j) = myArry(i)
myArry(i) = Temp
End If
Next j
Next i
End Sub[/vba]

[mikerickson]

Mac uses VB v.5 and Windows uses v.6, the syntax for a function returning an array is one of the differences between them.

Don't let your boss "upgrade" to Excel 2008, it doesn't support VB. If they want to upgrade Office, they should go the BootCamp/Windows/Office 2007 route.

[RonMcK]

Mikerickson,

Thanks for the explanation, that helps my tired brain a whole lot. The non-linearity was bugging me.

The company has about 500+ Macs in this building; I rather doubt we'll upgrade to 2008, more likely wait for the next version which rumor has it will have VBA restored as a feature. Most of our Macs are not wintels, so, bootcamp, etc is not an option.

Cheers,

Ron