Sorting a string

[Lysander]

I need to sort a string, actually several thousand strings, and my head aches and I can't remember if there is a built in function to do this. (Access 2003)

I have a string, say, "BDCA" and I want to return "ABCD"

Is there a built in function for this, or do I have to remember my school lessons of ages ago and write some bubble sort function to do this?

[JKing]

There is a built-in sort function which through a little searching I just found myself. It's actually a hidden class. The class is Called Wizhook. You can find it in the object browser by turning on show hidden members. There's quite a few neat members to the class. The one you are looking for is SortStringArray.

Here's a look at it:

Dim str As String
Dim charArray(4) As String
Dim intx As Integer
charArray(0) = "b"
charArray(1) = "D"
charArray(2) = "E"
charArray(3) = "c"
charArray(4) = "a"
 
WizHook.SortStringArray charArray
 
str = Join(charArray, "")
 
MsgBox str
 

I looked for a method for splitting strings into character arrays and I havent come across one yet. Split unfrotunately doesn't work if there isnt a delimiter. Specifying "" just returns the string itself and not omitting a delimiter defaults it to " ". Unless there is some wildcard that can be use that would delimit by each character I really dont know.

[Lysander]

There is a built-in sort function which through a little searching I just found myself. It's actually a hidden class. The class is Called Wizhook. You can find it in the object browser by turning on show hidden members. There's quite a few neat members to the class. The one you are looking for is SortStringArray.

Here's a look at it:

Dim str As String
Dim charArray(4) As String
Dim intx As Integer
charArray(0) = "b"
charArray(1) = "D"
charArray(2) = "E"
charArray(3) = "c"
charArray(4) = "a"
 
WizHook.SortStringArray charArray
 
str = Join(charArray, "")
 
MsgBox str
 

I looked for a method for splitting strings into character arrays and I havent come across one yet. Split unfrotunately doesn't work if there isnt a delimiter. Specifying "" just returns the string itself and not omitting a delimiter defaults it to " ". Unless there is some wildcard that can be use that would delimit by each character I really dont know.

Thanks, just what I need. I can split the string by using len(str) and mid(str,x,y).

[JKing]

Great! I was just hoping there was a string function that will send all of a string's characters to an array instead of having to loop through it yourself. There was a handy function in C# to do this. But of course no two languages are the same.

[Lysander]

Great! I was just hoping there was a string function that will send all of a string's characters to an array instead of having to loop through it yourself. There was a handy function in C# to do this. But of course no two languages are the same.

Unfortunatly, when I used wizhook.sortstringarray it sat there for a bit, and then crashed access, repeatadly no matter how simple I made the function.

I did find a simple QuickSort routine on another site. Unfortunatly the copyright code in the routine says I can use it freely, but can't post it onto any other web site, so I can't list it here.

Thanks for you help, I'll investigate wizhook in detail when I get some free time.

[JKing]

Hey again, it seems WizHook requires an activation key. In my research I came across the Key property being used just before the sort function and thought the Key property was irreleveant and just removed it. Especially after I removed the line and it kept working I thought it safe to assume the line did nothing relevant. Though I had executed the code once with the key property which would have activated the class.

So if you're still interested and or curious about WizHook add in this line above the sort one.

WizHook.Key = 51488399
WizHook.SortStringArray charArray
 

Also here is a link with a PDF that documents the members of WizHook.

WizHook Documentation

[JKing]

Actually it seems that the Key is not required for this particular method. Though I've just been reading through this help file and there is a note for the method declaring this:

This will crash access if you pass it an array with uninitialized elements.

[Snoday]

I ran across this old post when looking for Wizhook use. I noticed you asked for an easy way to sort a non delimited string, and developed the below function.

Function fNonDelimitedStrSorter(strInput As String) As Variant
Dim iloop As Integer
Dim istrLength As Integer
Dim charArray() As String
istrLength = Len(strInput)
If istrLength > 0 Then ' String Passed Continue
ReDim charArray(istrLength - 1) '-1 for shift to zero based list
    For iloop = LBound(charArray()) To UBound(charArray())
            charArray(iloop) = Mid(strInput, iloop + 1, 1)
    Next
    With WizHook
        .Key = 51488399
        .SortStringArray charArray
        '.WizMsgBox "Sorted Array" & vbCrLf & Join(charArray, vbCrLf), "The Wizhook Caption", vbOK, -1, ""
    End With
    fNonDelimitedStrSorter = Join(charArray, ", ")
Else
    fNonDelimitedStrSorter = "No Data Passed to function - "
End If
    Erase charArray()
End Function
 

[Killer42]

Hold on, there is a VB function to split a string into an array, isn't there? Though I think that'd be a byte array.

[NeoPa]

There's a function (Split()) that allows you to split a string into a Variant array of string type Variants based on a separator character if that's any help.

[Killer42]

There's a function (Split()) that allows you to split a string into a Variant array of string type Variants based on a separator character if that's any help.

Hm, probably not a lot of help in this case, since it requires a delimiter.

After a bit of research, I've come to the conclusion that I was imagining the string-to-char-array function. It doesn't exist in these older versions of VB (though it does in the .Net framework).

Looks as though you'd need to build your own. Which is perfectly simple, of course, using a loop and the Mid() function. And would be a handy function to keep in your toolkit.

[NeoPa]

I wasn't sure what you were after TBF. Split() can be used without the separator parameter, but it still uses a default of comma (,) in that case. probably no use, but it's there in case it might be ;-)

[Killer42]

Ok, I got a bit carried away here. Couldn't find any old sort routines, so I wrote a new one. Also a couple of functions to split a string into arrays of Byte and String format (the latter of which is used by the sort function).

Here's the code. Note that it's just something I banged out, and not extensively tested for either correct results or performance. Feel free to improve it. From a quick test it does appear to work, but use at your own risk. :-)

Public Function SortedString(ByVal Src As String, Optional Descending As Boolean = False, Optional TrimSpaces As Boolean = False) As String
  ' Take a string and sort the characters within it. Eg. "BCDA" --> "ABCD"
 
  Dim c() As String, TempChar As String
  Dim LowestSwap As Long, HighestSwap As Long
  Dim DoSwap As Boolean, AnySwapped As Boolean
  Dim I As Long
  Dim S As Long, E As Long
 
  If TrimSpaces Then Src = Trim$(Src)
  If Src = "" Then
    Exit Function
  ElseIf Len(Src) = 1 Then
    SortedString = Src
    Exit Function
  End If
  c = String2CharArray(Src)
  S = 1: E = UBound(c) - 1
  Do
    AnySwapped = False
    HighestSwap = E
    For I = S To E
      If (Descending = True And c(I) < c(I + 1)) Or _
         (Descending = False And c(I) > c(I + 1)) Then
        ' Swap this pair of characters.
        TempChar = c(I)
        c(I) = c(I + 1)
        c(I + 1) = TempChar
        HighestSwap = I
        AnySwapped = True
      End If
    Next
    If Not AnySwapped Then Exit Do
    E = HighestSwap
    AnySwapped = False
    For I = E To S Step -1
      If (Descending = True And c(I) < c(I + 1)) Or _
         (Descending = False And c(I) > c(I + 1)) Then
        ' Swap this pair of characters.
        TempChar = c(I)
        c(I) = c(I + 1)
        c(I + 1) = TempChar
        LowestSwap = I
        AnySwapped = True
      End If
    Next
    S = LowestSwap
  Loop While AnySwapped
  SortedString = Join(c, "")
End Function
 
 
 
Public Function String2ByteArray(ByVal Src As String, Optional LtrimFirst As Boolean = False, Optional RtrimFirst As Boolean = False) As Byte()
  ' Split a string into a Byte array.
  Dim c() As Byte
  Static L As Long, I As Long
  If LtrimFirst Then Src = LTrim$(Src)
  If RtrimFirst Then Src = RTrim$(Src)
  If Src = "" Then Exit Function
  L = Len(Src)
  ReDim c(1 To L)
  For I = 1 To L
    c(I) = Asc(Mid$(Src, I, 1))
  Next
  String2ByteArray = c
End Function
 
 
Public Function String2CharArray(ByVal Src As String, Optional LtrimFirst As Boolean = False, Optional RtrimFirst As Boolean = False) As String()
  ' Split characters in a string into a string array.
  Dim c() As String
  Static L As Long, I As Long
  If LtrimFirst Then Src = LTrim$(Src)
  If RtrimFirst Then Src = RTrim$(Src)
  If Src = "" Then Exit Function
  L = Len(Src)
  ReDim c(1 To L)
  For I = 1 To L
    c(I) = Mid$(Src, I, 1)
  Next
  String2CharArray = c
End Function
 

A couple of notes.

• This code is entirely self-contained (apart from the fact that the SortedString function is dependent on the String2CharArray function). So to try it out you can just start a new project, paste into a code module, then go to the immediate window and try for example...
  ? sortedstring("KHD FKLD F ")
• I've included a few parameters to allow control over how the functions operate, such as trimming off extra spaces and reversing the sort sequence.

[Killer42]

Sorry, forgot this was Access/VBA. I was writing for VB6. Hopefully it'll still work the same (fingers crossed), but one or two references such as "create a new project" may need to be taken with a grain of salt.

[NeoPa]

Seems like good VBA to me Killer. I checked, but not carefully, and all seems VBA consistent.

[Killer42]

Thanks. I do tend to get tripped up by little differences when working with VBA, but that's usually more to do with references to form fields and the like.