PDA

View Full Version : VBA code for sorting(descending or ascending users preference)



Wlee
08-23-2015, 09:27 PM
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
08-23-2015, 10:51 PM
hi.

http://www.vbaexpress.com/forum/faq.php?faq=psting_faq_item#faq_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
08-23-2015, 11:19 PM
Ah, True!
Yes it is course work.

Can I not get help within the forum then?

mancubus
08-24-2015, 01:17 AM
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.com/2009/03/23/a-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
08-24-2015, 03:59 AM
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
08-24-2015, 04:02 AM
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
08-24-2015, 04:31 AM
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.com/2009/03/23/a-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
08-24-2015, 03:44 PM
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
08-25-2015, 01:26 PM
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
08-26-2015, 06:47 AM
Thank you