Results 1 to 8 of 8

Thread: Sorting numbers in an array (VBA)

  1. #1

    Thread Starter
    Lively Member
    Join Date
    Mar 2007
    Posts
    115

    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?

  2. #2
    Addicted Member
    Join Date
    Sep 2004
    Location
    Surrey, UK
    Posts
    163

    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

  3. #3

    Thread Starter
    Lively Member
    Join Date
    Mar 2007
    Posts
    115

    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.

  4. #4
    Frenzied Member
    Join Date
    Feb 2003
    Location
    Argentina
    Posts
    1,950

    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

  5. #5

    Thread Starter
    Lively Member
    Join Date
    Mar 2007
    Posts
    115

    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.

  6. #6
    Frenzied Member
    Join Date
    Feb 2003
    Location
    Argentina
    Posts
    1,950

    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

  7. #7
    Don't Panic! Ecniv's Avatar
    Join Date
    Nov 2000
    Location
    Amsterdam...
    Posts
    5,343

    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?

    BOFH Now, BOFH Past, Information on duplicates

    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...

  8. #8
    Addicted Member
    Join Date
    Sep 2004
    Location
    Surrey, UK
    Posts
    163

    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
  •  



Click Here to Expand Forum to Full Width