none
Array Find and Sort question RRS feed

  • Question

  • Hi

    These question relate to a one dimensional array - say 100 unique items.

    Is ther a simple way to sort the items in an array?

    Is there a simple to find a particular occurrence in an array?

    I know both of the these can be done by looping, but I wondered if there was a more elegant way?

    Many thanks

    Peter

    Friday, July 29, 2016 8:59 AM

Answers

  • I know both of the these can be done by looping, but I wondered if there was a more elegant way?

    Hi Peter,

    if you (resp. your VBA code) doesn't loop through the items and perform a sort, you have to push all items onto the stack and call an external source that does the loop and performs the sort.

    It is not possible to sort without a loop!

    So IMHO the real question is what is easier and/or faster. And what kind of sort you want to use:
    https://en.wikipedia.org/wiki/Sorting_algorithm

    Long talk, short end: Use the code you already have. 100 items is so small... .-)

    Andreas.


    • Edited by Andreas Killer Friday, July 29, 2016 10:14 AM
    • Marked as answer by py1 Friday, July 29, 2016 10:28 AM
    Friday, July 29, 2016 10:13 AM
  • Alternatively, you could store the array in a range of cells, then use the Sort method to sort it, or the Find method to search for a specific value. You'd still have to loop to read the array back in from the range. For an array declared as arr(1 To 100):

        Dim i As Long
        With Range("A1:A100")
            ' Store array in range
            .Value = Application.Transpose(arr)
            ' Sort range
            .Sort Key1:=Range("A1"), Header:=xlNo
        End With
        ' Read array back in
        For i = 1 To 100
            arr(i) = Range("A" & i).Value
        Next i


    Regards, Hans Vogelaar (http://www.eileenslounge.com)

    • Marked as answer by py1 Friday, July 29, 2016 10:28 AM
    Friday, July 29, 2016 10:25 AM

All replies

  • I know both of the these can be done by looping, but I wondered if there was a more elegant way?

    Hi Peter,

    if you (resp. your VBA code) doesn't loop through the items and perform a sort, you have to push all items onto the stack and call an external source that does the loop and performs the sort.

    It is not possible to sort without a loop!

    So IMHO the real question is what is easier and/or faster. And what kind of sort you want to use:
    https://en.wikipedia.org/wiki/Sorting_algorithm

    Long talk, short end: Use the code you already have. 100 items is so small... .-)

    Andreas.


    • Edited by Andreas Killer Friday, July 29, 2016 10:14 AM
    • Marked as answer by py1 Friday, July 29, 2016 10:28 AM
    Friday, July 29, 2016 10:13 AM
  • Alternatively, you could store the array in a range of cells, then use the Sort method to sort it, or the Find method to search for a specific value. You'd still have to loop to read the array back in from the range. For an array declared as arr(1 To 100):

        Dim i As Long
        With Range("A1:A100")
            ' Store array in range
            .Value = Application.Transpose(arr)
            ' Sort range
            .Sort Key1:=Range("A1"), Header:=xlNo
        End With
        ' Read array back in
        For i = 1 To 100
            arr(i) = Range("A" & i).Value
        Next i


    Regards, Hans Vogelaar (http://www.eileenslounge.com)

    • Marked as answer by py1 Friday, July 29, 2016 10:28 AM
    Friday, July 29, 2016 10:25 AM
  • thanks Andreas

    kind regards

    Peter

    Friday, July 29, 2016 10:28 AM
  • Thank you Hans - that is helpful.
    Friday, July 29, 2016 10:28 AM