Sorting array problem 1

[bozic12]

Hi there,
I am trying to sort an array of strings. When i run the code, I get a compile error saying that the array argument must be passed ByRef. I have my sorting function and calling line below. If anyone can figure out why I am getting this message, also if you see anything wrong in my sort function, I would really appreciate it, because I can't figure it out for the life of me.

(options() is the array that I am trying to sort)

Private Sub ModelCombo_Change()
...
...
Sort(options) --THIS IS WHERE THE ERROR REFERENCES
...
end sub

Function Sort(ByRef Data() As String) As String
Dim Temp As String, i As Integer, j As Integer

For i = 1 To UBound(Data)
If StrComp(Data(i - 1), Data(i), 1) = 1 Then
j = i
While j > 0
If StrComp(Data(j - 1), Data(j), 1) = 1 Then
Temp = Data(j)
Data(j) = Data(j - 1)
Data(j - 1) = Temp
Else
j = 0
End If
j = j - 1
Wend
End If
Next i
Sort = Data

End Function

Thanks,
Jeff

 

[Zathras]

You were very close. The thing to remember that arrays are passed as type Variant (which is the default). Here is some code you can play around with:

Option Explicit

Private Sub ModelCombo_Change()
Dim options() As String
Dim i As Integer
  ReDim options(10)
  Randomize
  For i = 0 To 10
    options(i) = "Foo " & Int(Rnd * 100)
  Next i
  display options
  Sort options
  display options

End Sub

Sub Sort(MyData)
Dim Temp As String, i As Integer, j As Integer

For i = 1 To UBound(MyData)
    If StrComp(MyData(i - 1), MyData(i), 1) = 1 Then
        j = i
        While j > 0
            If StrComp(MyData(j - 1), MyData(j), 1) = 1 Then
                Temp = MyData(j)
                MyData(j) = MyData(j - 1)
                MyData(j - 1) = Temp
            Else
                j = 0
            End If
            j = j - 1
        Wend
    End If
Next i
End Sub

Sub display(MyData)
Dim sText As String
Dim i As Integer
  For i = LBound(MyData) To UBound(MyData)
    sText = sText & MyData(i) & vbNewLine
  Next i
  MsgBox sText
End Sub

 

[JohnYingling]

Change
Function Sort(ByRef Data() As String) As String
to
Function Sort(ByRef Data() As String) As String()

The As String()
indicates that an array of strings will be returned.
However you have already sorted the passed array so returning it separately is superfluous.


Zatras.
"that arrays are passed as type Variant (which is the default)."
Where did you ever read that? Arrays can be any type and there is no conversion performed when they are passed. The problem was in the definition of the return value.

The array above is defined as being passed As String so it better well be an array of strings.

http://www.VBResizer.com

Forms/Controls Resizing/Tabbing Control

http://www.VBCompare.com

Compare Code (Text)

http://www.VBSortGen.com

Generate Sort Class in VB or VBScript

 

[Zathras]

John, thank you. It's always a pleasure to learn something new. I have never seen the "String()" syntax anywhere in the Excel VBA help file. Could you point out where that might be located? The only examples I could find seemed to suggest quite strongly that Variants were necessary. I looked at the links you suggested, but couldn't find anything to do with "String()" or arrays there, either.

It just goes to show you can't rely on Microsoft's help to get the answers you really need.

By the way, the code can also be made to work by simply removing the line "Sort = Data" -- since the result of the function is not being used, there is no reason to assign anything to it.

 

[xlbo]

"Where did you ever read that?" ....excel help file.
I have to admit, I thought that arrays had to be declared as variants also (not the data type held but the array container itself)
Excel 97 help certainly seems to suggest this - especially if you are just declaring the container so it can be populated later

The example given is
dim myArray as variant

myArray = array("A","B","C",etc etc etc) Rgds
Geoff
"Some cause happiness wherever they go; others whenever they go."
-Oscar Wilde

 

[bryanbayfield]

You could always be a smart-ass and use the WordBasic functionality to do it for you. Don't forget to add a reference to the Word DLL (from VBE, select Tools > References, then tick the appropriate check box). For some reason if you declare the array as a Variant, this method falls over. Since WordBasic is a legacy programming language in Word VBA, you migth like to do some digging around yourself before relying on teh method too much.

Private Sub CommandButton1_Click()
Dim appWD As Word.Application, my_arr() As String, i As Integer
Set appWD = CreateObject("Word.Application")
ReDim my_arr(2)
For i = 0 To 2
    my_arr(i) = "Basher" & (2 - i)
    Next
WordBasic.SortArray my_arr
For i = 0 To 2
    Debug.Print my_arr(i)
    Next
End Sub

Now check your immediate window > it should say Basher0; Basher1; Basher2.

:

 

[bozic12]

Thanks everyone for their input. It seems that the problem was in my Function declaration. It should be Function Sort(Data) instead of Function Sort(data()), which I got from Zathras. Thanks again to all who tried to help.

-Jeff

 

[bozic12]

Actually, I have a pretty big problem now. The array is passed fine, but the function doesn't do anything. It outputs the same order it recieves the array in. Hopefully someone will be able to help me out because I have no ideas. Below is my code and an example of what the array would contain. I've tried declaring Options() as a string and variant, but as I suspected, it doesn't make a difference.

(options() is the array that I am trying to sort)

Option Base 1
Private Sub ModelCombo_Change()
...
...
Sort(options)
...
end sub

Function Sort(Data)
Dim Temp As String, i As Integer, j As Integer

For i = 2 To UBound(Data)
If StrComp(Data(i - 1), Data(i), 1) = 1 Then
j = i
While j > 1
If StrComp(Data(j - 1), Data(j), 1) = 1 Then
Temp = Data(j)
Data(j) = Data(j - 1)
Data(j - 1) = Temp
Else
j = 1
End If
j = j - 1
Wend
End If
Next i

End Function

Options(1) = 7.5K, 20-hp, 40 Taper Spindle (Geared)
Options(2) = 10K, 20-hp, 40 Taper Spindle (Belt)
Options(3) = 10K, 20-hp, 40 Taper Spindle (Geared)
Options(4) = 15K, 20-hp, 40 Taper Spindle (Belt)
Options(5) = 30K, 18-hp, BT 30, Spindle (Belt)
Options(6) = BT 40 Spindle
Options(7) = 3.25" Z-axis Riser Plate
Options(8) = 10K, 30-hp, 40 Taper Spindle (Geared)
Options(9) = 24 Station, 40 Taper ATC (Side Mount)
Options(10) = 40 Station, 40 Taper ATC (Side Mount)
Options(11) = 32 Station, 40 Taper ATC (Carousel)
Options(12) = Through Spindle Coolant (300 psi)

I really don't care in what order it is sorted, just as long as it is sorted so like entries will be next to each other. (i.e. 1,2,3,4,5,8 together, 9,10,11 together, etc.)

Thanks again in advance for any help you can offer.

Jeff

 

[bryanbayfield]

Why don't you try my method, it will save you farting about with your own functions to sort arrays, the functionality is already in the word.dll.

You just have to remember not to ask it to sort variants and also to add appWD.quit to the end of the routine I posted above.

:

 

[Zathras]

You are comparing the entire string in the sort. Since you want to compare beginning at someplace other than the first character, you have to deal with that.

One thing I have used in the past is to pre-process each element of the array by "folding" it at the point where I want to sort. Then after sorting I "unfold" each element. To "fold", I pick a unique character that will NEVER be found in the data and do something like this:

 "7.5K, 20-hp, 40 Taper Spindle (Geared)" becomes
 "Taper Spindle (Geared)|7.5K, 20-hp, 40 "

 "10K, 20-hp, 40 Taper Spindle (Belt)" becomes
 "Taper Spindle (Belt)|10K, 20-hp, 40 "

and so on. (The split vertical bar is usually available for the folding marker.)
Then after sorting, process each element by finding the marker with the InStr function and put it back the way it was.