VBA code for sorting(descending or ascending users preference)

[Wlee]

Hi, I was wondering if any one had code for the following situation:
develop a sorting algorithm on an array in VBA for EXCEL: The sub procedure should:

• obtain values from the spreadsheet based on the users requirements ( the user could want to sort numbers in A1:A12 or B4:B15. Assume that the numbers to sort are in a single column.)
• ask the user whether the values should be ascending or descending with own written function to perform the sorting algorithm, which is called the sub procedure.
• replace the values in the cells originally selected with the sorted values.

use both Explicit and Option Base 1 statements.

any help with this would be greatly appreciated.

Kind regards,
will

[mancubus]

hi.

http://www.vbaexpress.com/forum/faq....q_hom_faq_item

Can I ask about my homework here?

Please don't ask us questions directly out of your coursework materials. We are happy to provide direction and guidance for those studying VBA and other software. Be open about the fact that it is coursework, and you'll likely find yourself with more resources than you could possible need.

[Wlee]

Ah, True!
Yes it is course work.

Can I not get help within the forum then?

[mancubus]

sure you can. remember giygf!

1st:
a) obtain values from the spreadsheet based
search: "excel vba populate array from range"
b) based on the users requirements
search: "excel vba Application.Inputbox"

2nd:
a) ask the user whether the values should be ascending or descending
search : 1b, ie, same as 1st b
b) with own written function to perform the sorting algorithm
search: "excel vba sort array elements"
i understand it is required a separate sorting sub which will take inputs such as range, sort order.
here is one: https://newtonexcelbach.wordpress.co...sort-function/ click the links provided in this blog.

3rd
replace the values in the cells originally selected with the sorted values.
search: "excel vba write array to range"

also you need: "excel vba call another sub"


you can ask for guidance any time.
good luck!

[Wlee]

Sub Will()


Dim Arr() As Variant
Dim RangeName As String
Dim R As Long
Dim C As Long
Dim RR As Range


RangeName = "TheRange"
Set RR = Range(RangeName)
If RR.Cells.Count = 1 Then
ReDim Arr(1 To 1, 1 To 1)
Arr(1, 1) = RR.Value
Else
Arr = Range(RangeName)
End If
End Sub


Worksheets("Sheet1").Activate
Set myCell = Application.InputBox( _
prompt:="Select a cell", Type:=8)
'prompts the user to select a cell on Sheet1. The example uses the Type argument to ensure that the return value is a valid cell reference (a Range object)

End Sub

[Wlee]

This is what I have so far.
I am however lost when you advise me to search : 1 b
I have figured out it is a match function..
would you mind giving me a few more clues on this one?

regards,
will

[mancubus]

1b is for "1st question part b". i modified my message.

what i recommended is not an easy task. first you should read the related documentation and understand the basic concepts.

after reading (and learning), first start with writing a function or sub which will sort an array. (see the second link in https://newtonexcelbach.wordpress.co...sort-function/ which sorts a range not an array. but can easily be adopted.)

i sometimes click 100-200 Google results in order to understand a subject.

[Wlee]

Hi mancubus,

Please find attached my current working model.
unfortunately when it is sorting either ascending or descending,
it is not selecting the first value in the column.

would you kindly suggest and recommend a solution.

thanks in advance.
Regatds,
will

[mancubus]

you posted a blank workbook with some buttons and recorded / modified range sorting macros.

your assignment is sorting (via separate function) an array which will be populated from and written back to a range.

i will make an exception and provide the code. but it is your responsibility to fully understand the procedures (without my help, for sure) and explain the details to the tutor where necessary.

i used popular bubble sort method you can easily find. my contribution is adding the sort method only.

Option Explicit
Option Base 1

Sub vbax_53555_SortAndRewriteRangeBasedArray()

    Dim rng As Range
    'Dim MyArr()
    Dim MyArr() As Long 'Declare array's data type as integer, long, double, etc if you want to sort numbers
    Dim NumArrElements As Long, i As Long, SortOrder As Long
    
    On Error Resume Next
    Set rng = Application.InputBox("Select the range to sort", Type:=8)
    If rng Is Nothing Then
        MsgBox "You pressed Cancel. Please select a range to continue.", vbOKOnly, "Quitting macro..."
        Exit Sub
    End If
    
    SortOrder = Application.InputBox("Input sort method: 1 for ascending and 2 = Descending", Type:=1)
    If SortOrder = 0 Then
        MsgBox "You pressed Cancel. Please input 1 or 2.", vbOKOnly, "Quitting macro..."
        Exit Sub
    End If
    On Error GoTo 0
    
    NumArrElements = rng.Count
    
    ReDim MyArr(1 To NumArrElements)
    For i = 1 To NumArrElements
        MyArr(i) = rng.Cells(i).Value
    Next i
    
    BubbleSort MyArr, SortOrder
    'Call BubbleSort(MyArr, SortOrder) 'onother way to call the function
    
    rng.Value = Application.Transpose(MyArr) '1D array is like a row. convert it to 'column'.

End Sub


Function BubbleSort(ArrToSort, Order)
'Order: 1= Ascending, 2= Descending

    Dim strTemp As String
    Dim i As Long
    Dim j As Long
    Dim lngMin As Long
    Dim lngMax As Long
    
    lngMin = LBound(ArrToSort)
    lngMax = UBound(ArrToSort)
    
    For i = lngMin To lngMax - 1
        For j = i + 1 To lngMax
            If Order = 1 Then
                If ArrToSort(i) > ArrToSort(j) Then
                    strTemp = ArrToSort(i)
                    ArrToSort(i) = ArrToSort(j)
                    ArrToSort(j) = strTemp
                End If
            ElseIf Order = 2 Then
                If ArrToSort(i) < ArrToSort(j) Then
                    strTemp = ArrToSort(i)
                    ArrToSort(i) = ArrToSort(j)
                    ArrToSort(j) = strTemp
                End If
            Else
                MsgBox "Input 1 for Ascending, 2 for Descending sort!" & vbLf & "Array not sorted!", vbOKOnly, "Error"
                Exit Function
            End If
        Next j
    Next i

End Function

[Wlee]

Thank you