-
Apr 13th, 2007, 04:40 AM
#1
Thread Starter
Lively Member
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 < ...
Code:
Option Compare Database
Option Explicit
Private Sub Form_Load()
Dim intAges(20) As Integer
Dim intInputAges As Integer
Dim i As Integer
For i = 1 To 20
intInputAges = InputBox("Geef een leeftijd in.", "Leeftijd", "Leeftijd")
intAges(i) = intInput
Next i
txtAges.SetFocus
End Sub
This is what I got so far. I wouldn't have a clue about how to sort them, and in the help file I only find SQL statements like ORDER BY.
Thanks for all feedback.
edit: I know that in visual basic .NET arrays start counting on zero, is it the same here or is it ok as I wrote it?
-
Apr 13th, 2007, 08:05 AM
#2
Addicted Member
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
-
Apr 13th, 2007, 03:04 PM
#3
Thread Starter
Lively Member
Re: Sorting numbers in an array (VBA)
It gives an error about the worksheetFunction not being declared, what do I have to do with that? Sounds like excel.
-
Apr 14th, 2007, 09:07 AM
#4
Frenzied Member
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
Last edited by salvelinus; Apr 14th, 2007 at 01:46 PM.
Reason: added SQL code
Tengo mas preguntas que contestas
-
Apr 14th, 2007, 09:45 AM
#5
Thread Starter
Lively Member
Re: Sorting numbers in an array (VBA)
So then how would I sort them? If I need to put them in a textbox, I can do that with my code with a for next and a string containing all numbers.
-
Apr 14th, 2007, 01:16 PM
#6
Frenzied Member
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.
Tengo mas preguntas que contestas
-
Apr 16th, 2007, 03:53 AM
#7
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...
-
Apr 16th, 2007, 08:00 AM
#8
Addicted Member
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
Posting Permissions
- You may not post new threads
- You may not post replies
- You may not post attachments
- You may not edit your posts
-
Forum Rules
|
Click Here to Expand Forum to Full Width
|