Solved: How do I pass an Array back to Sub from Function?

[RonMcK]

Hi, All,

I am having a problem getting the following code to work on my Mac here at work. On Friday, I began a conversation in 'VBA Code and Other Help' in my [thread=http://vbaexpress.com/forum/showthread.php?t=22494]original thread[/thread] where I thought we solved this question. Workbook is attached in that thread.

The challenge is getting my calling program (sort_string()) to recognize and use the sorted array returned by the function BubbleSort(). This works on my PC at home but I get the error noted in the remark in the code. How does one (on a Mac) assign the sorted array in BubbleSort to the array in Sort_String?

[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 + 1, Len(StdsStrIn) - (my_j + 1)))
End Select
Loop
ReDim Preserve myArry(my_i)

myArry = BubbleSort(myArry) '// 'Can't Assign to Array' according to XL 2004 on Mac (works on PC w/ XL2002)

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]

Many thanks in advance.

[RonMcK]

Well, I have sorted this out. Here is the code change needed in Sort_String() and beneath it I reverted Function BubbleSort() back to Sub BubbleSort(). Things work as expected.
[vba]' myArry = BubbleSort(myArry) '// 'Can't Assign to Array' according to XL 2004 on Mac (works on PC w/ XL2002)
BubbleSort myArry [/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]