VBA algorithm for sorting

[Chyma]

Hello, I need help with my task in the school, I should write my own bubble for sorting in alphabetic order and not use build-in functions. I know how to do it for numbers but I have no idea how to do it for letters. I am talking about this code specifically:

Range("A1:F20").Sort Key1:=Range("C2"), Order1:=xlAscending, Header:= _
xlGuess, OrderCustom:=1, MatchCase:=False, Orientation:=xlTopToBottom

 Thanks a lot for any help or hints. Have a good day 

[#2]

@Chyma 

The bubble sort algorithm for text values is exactly the same as that for numeric values. The only difference is the data type of the variable that you use to store a value temporarily while swapping two values. That variable should be of type String instead of Long (or Double, or whatever you were using).

[#3]

@Chyma 

I would say that a text sort presents some additional problems, besides just changing the data type of variables.  (Which is actually optional, if you choose to declare variables as type Variant, explicitly or by default.  But I do not recommend that.)

First, how to treat upper and lower cases.  (Hint: See the Option Compare statement.)

Second, how to treat spaces (and other whitespace characters like tab and nonbreaking spaces?),  if you are sorting phrases.  (Hint: See the Replace function to remove whitespace characters, if desired.) 

Similarly, how to treat special characters, notably commas, semicolons, colons, dashes, etc.  For example, sort "A-c-b" and "Abc", noting that "-" < "b" is True.

Bottom line:  Provide an example with some representative data and the sorted result that you want.  Or....

Re: ``I am talking about this code specifically: Range("A1:F20").Sort Key1:=Range("C2"), Order1:=xlAscending, Header:=xlGuess, OrderCustom:=1, MatchCase:=False, [....]``

Do you mean that you want the result of your sort to be the same as the result of the range.Sort method with those options?

(I'm surprised that you want Header:=xlGuess instead of xlNo.)

Finally, if you are looking for anything more specific, show us your implementation of a numeric "bubble sort" -- or at least the algorithm.

The bubble sort per se is inefficient.  Given the choice, I would use an exchange sort.  But it depends on the specifics of the assignment.

[#4]

@Joe User  

This was the task:

***Grading journal***

(Name, last name, grade1, grade2 grade3)

I want You to write me a project that is the grading journal. The program will read the given file "student.txt" and put it in the table/array. There will be 3 buttons: 1. sorting button (any known for labratory)
2. adding new student and his grades
3. writing the table to new file "sorted Student.txt

- reading list from the .txt file
- putting them in table
- sort the table by last name
- button to add new name and grade
- when finnished working with excel write it down to .txt again

this is my solution of the step sort the table by last name:

Sub Button4_Click() Range("A1:F20").Sort Key1:=Range("C2"), Order1:=xlAscending, Header:= _ xlGuess, OrderCustom:=1, MatchCase:=False, Orientation:=xlTopToBottom End Sub

but teacher wants from me to use not build-in function and instead of that to do my own bubble sort by changing latters to ASCII but  I dont really know how to match particular letter to the given number from ASCII table, and mainly if i will sort it by the last names it will mix all table,( wrong last name will match wrong first name) i have to lock somehow whole row so it will be sorted with first name and grades and so on. So here i struggle , the rest i know how to do more or less, so if you would have any idea i would really appreciate that

[#5]

Hi@Hans Vogelaar , how can we check the Performance of different sorting algo using Excel?

Thanks in Advance,

Br,

Anupam

[#6]

@anupambit1797 

Use code like this:

Sub TestBubbleSort()
    Dim t As Single ' to keep track of time
    Dim MyArray ' Array to be sorted
    ' Set up MyArray
    ' ...
    t = Timer
    Call BubbleSort(MyArray)
    t = Timer - t
    Debug.Print "Bubble Sort took " & t & " seconds"
End Sub

Create similar macros for other sorting algorithms that you want to test: insert sort, heap sort, comb sort, quick sort, ...