If this is your first visit, be sure to
check out the FAQ by clicking the
link above. You may have to register
before you can post: click the register link above to proceed. To start viewing messages,
select the forum that you want to visit from the selection below.

Sorting numbers in an array (VBA)

Hi,

I'm making a program in VBA where the user enters 20 ages / numbers in inputboxes, and then all these ages / numbers should be printed in a textbox and also be sorted ascending. I think it's ascending, it's like
x < y< z < ...

Re: Sorting numbers in an array (VBA)

Yes the first entry is zero in the array.

Try this....

The setting the array values to 32767 is cheating a bit but it works perfectly.

Code:

Sub SetAges()
Dim intAges() As Integer, intOutput() As Integer
Dim intTotal As Integer
Dim i As Integer, j As Integer
' Set number of Names
intTotal = 5
ReDim intAges(intTotal - 1)
ReDim intOutput(intTotal - 1)
' Input Entries
For i = 0 To intTotal - 1
intAges(i) = InputBox("Enter an Age. This is No: " & i + 1, "Age")
Next i
For i = 0 To intTotal - 1
' Finds the Minimum value and stores in in the new array and the txtAges variable
intOutput(i) = WorksheetFunction.Min(intAges)
txtAges = txtAges & intOutput(i) & ", "
' All entries in the intAges array that match the minimum are set to a max number so they
' don't appear twice on list
For j = 0 To intTotal - 1
If intAges(j) = intOutput(i) Then
intAges(j) = 32767
End If
Next j
Next i
MsgBox "These are your ages in order!: " & txtAges
End Sub

Re: Sorting numbers in an array (VBA)

Code:

'Note that this is DAO code, not ADO
'Most people here use ADO, which is usually better,
'but DAO works fine within Access VBA, at least through 2K
'Álso note that this will work ok for a fairly small number of entries.
'If there are a lot of entries, running an INSERT statement on each one is
'inefficient, and you'd want want to look at other methods
Option Compare Database
Option Explicit
Private Sub Form_Load()
Dim intInputAges As Integer
Dim i As Integer
Dim s As String, strSQL As String
Dim db as DAO.Database
Dim rs as DAO.Recordset
s = s & InputBox("Geef een leeftijd in.", "Leeftijd", "Leeftijd") & " " 'ínitialize
'here tblFoo only has one field, integer data type, where your numbers are going
For i = 1 To 20
s = InputBox("Geef een leeftijd in.", "Leeftijd", "Leeftijd")
strSQL = "INSERT INTO tblFoo VALUES (" & CInt(s) & ")"
DoCmd.RunSql strSql
Next i
strSQL = "SELECT tblFoo.fldNumber FROM tblFoo ORDER BY tblFoo.fldNumber"
Set db = CurrentDB
Set rs = db.OpenRecordset(strSQL)
'now build a string from the table, sorted nicely
If Not (rs.EOF and rs.BOF) Then
s = rs.Fields("fldNumber")
rs.MoveNext
Do While Not rs.EOF
s = " "& s & s.Fields("fldNumber")
rs.MoveNext
Loop
End If
Me.txtAges.SetFocus
Me.TextAges.Text = s
'always clean up! if you'll need db elsewhere in your code, declare it in a code
'module instead of above, and don't set it to Nothing till Form_Unload(), or
'whenever you're done with it
Set rs = Nothing
Set db = Nothing
End Sub

Re: Sorting numbers in an array (VBA)

It would be much easier to put them in a table and use ORDER BY to fill your textbox.
You could work with an array and use a sort routine to build a string for your textbox, but it's a lot more work for no good reason. I'll modify my code above to show that.
Also, if you put this code in the Form_Load() event, the user will get these 20 input prompts before the form is ever visible. May be what you want, may not.

Re: Sorting numbers in an array (VBA)

Read up on bubble sort. Its the easiest to implement.
Requires an array, a variable or another array. One field in the array to sort on.

Depending on how many entries you have ...

Pseudocode (sp?) is:

Begin loop from first to last minus one of the array as outerloop
Begin loop from outerloop+1 to last as inner loop
if array at innerloop is greater than the outerloop, swap them via variable/array)
End Loop
End Loop

You should be able to code yourself. Post up if you still have probs with your code.

If you are in vb.net, you might have a sort method. Or you use a collection and I think there is a sort option?

Feeling like a fly on the inside of a closed window (Thunk!)
If I post a lot, it is because I am bored at work! ;D Or stuck...
* Anything I post can be only my opinion. Advice etc is up to you to persue...

Re: Sorting numbers in an array (VBA)

Not sure if you want to use the other guys methods but here is the fix for mine so it runs in Access. Thought you were using Excel !!

I have just put it in the Form_Load sub and then you can use the intOuput() array to populate your textbox or similar...

Code:

Private Sub Form_Load()
Dim intAges() As Integer, intOutput() As Integer
Dim intTotal As Integer
Dim i As Integer, j As Integer
' Set number of Names
intTotal = 5
ReDim intAges(intTotal - 1)
ReDim intOutput(intTotal - 1)
' Input Entries
For i = 0 To intTotal - 1
intAges(i) = InputBox("Enter an Age. This is No: " & i + 1, "Age")
Next i
For i = 0 To intTotal - 1
' Finds the Minimum value and stores in in the new array and the txtAges variable
intOutput(i) = intAges(1)
For j = 0 To intTotal - 1
If intAges(j) < intOutput(i) Then
intOutput(i) = intAges(j)
End If
Next j
txtAges = txtAges & intOutput(i) & ", "
' All entries in the intAges array that match the minimum are set to a max number so they
' don't appear twice on list
For j = 0 To intTotal - 1
If intAges(j) = intOutput(i) Then
intAges(j) = 32767
End If
Next j
Next i
MsgBox "These are your ages in order!: " & txtAges
End Sub