Sorting arrays in VBA

These code samples sort values in the following order: NULLS (must be converted to 0-length strings), special characters (in numerical order from CHR() chart), numbers, upper case letters, lower case letters.

Here's the code to sort a one-dimensional array:

Function SortArray(ArrayToSort() As Variant) As Variant
     
    Dim First           As Integer
    Dim Last            As Integer
    Dim i               As Integer
    Dim j               As Integer
    Dim Temp            As String
     
    First = LBound(ArrayToSort)
    Last = UBound(ArrayToSort)
    For i = First To Last - 1
        For j = i + 1 To Last
            If ArrayToSort(i) > ArrayToSort(j) Then
                Temp = ArrayToSort(j)
                ArrayToSort(j) = ArrayToSort(i)
                ArrayToSort(i) = Temp
            End If
        Next j
    Next i
    
        For i = 1 To UBound(ArrayToSort)
            Debug.Print ArrayToSort(i) 'output sorted array elements
        Next i

End Function

If you want to sort an array with 2 dimensions, it's just like sorting rows of data on an excel sheet based on ONE column only (e.g. - sorting a table based on a column). For example, number of columns would be the 1st dimension and the number of rows would be the 2nd. Here's an example of sorting data on a sheet (a table of data) this way:

Function SortArray(ArrayToSort() As Variant, SortCol As Integer)

On Error Resume Next

    Dim First           As Integer
    Dim Last            As Integer
    Dim i               As Integer
    Dim j               As Integer
    Dim k               As Integer
    Dim Temp            As String
    Dim prev            As String
    Dim origarray()     As Variant
    Dim rowarray()      As Variant

origarray = ArrayToSort()
SortCol = SortCol - 1
k = 0

    First = LBound(ArrayToSort, 2)
    Last = UBound(ArrayToSort, 2)
    
    'SORT THE COLUMN VALUES HERE
    For i = First To Last - 1
        For j = i + 1 To Last
            If ArrayToSort(SortCol, i) > ArrayToSort(SortCol, j) Then
                Temp = ArrayToSort(SortCol, j)
                ArrayToSort(SortCol, j) = ArrayToSort(SortCol, i)
                ArrayToSort(SortCol, i) = Temp
            End If
        Next j
    Next i
    
    'USE SORTED COLUMN VALUES TO STORE RELATED ROW NUMBERS IN A NEW ARRAY
    For i = 1 To UBound(ArrayToSort, 2)
        If IIf(ArrayToSort(SortCol, i) = "", "NULL", ArrayToSort(SortCol, i)) <> prev Then
            For j = 0 To UBound(origarray, 2) - 1
                If IIf(origarray(SortCol, j) = "", "NULL", origarray(SortCol, j)) = _
                   IIf(ArrayToSort(SortCol, i) = "", "NULL", ArrayToSort(SortCol, i)) Then
                    ReDim Preserve rowarray(k)
                    rowarray(k) = j
                    k = k + 1
                End If
            Next j
        End If
                        prev = IIf(ArrayToSort(SortCol, i) = "", "NULL", ArrayToSort(SortCol, i))
    Next i

        For i = 0 To UBound(origarray, 2)
            For j = 0 To UBound(origarray, 1)
                'DO WHAT YOU NEED TO DO WITH THE SORTED VALUES HERE
                Debug.Print origarray(j, rowarray(i))
            Next j
        Next i

End Function

Someone has to ask: why Integer rather than Long? In the days of 4GB RAM floor for machines running Excel, is it REALLY vital to save 2 bytes per variable? I admit to decided bias on this point: I see no useful purpose served by using Integer or Single in VBA.

Looks like bubble sort or shell sort, I can never keep then straight. Useful for small arrays, but O(N^2). As an alternative, quicksort, which is O(N log(N)).

Sub qsort(ByRef v As Variant, lft As Long, rgt As Long)
  'ad hoc quicksort subroutine -- sorts 1D array in place
  'translated from Aho, Weinberger & Kernighan,
  '"The Awk Programming Language", page 161

  Dim j As Long, pvt As Long

  If (lft >= rgt) Then Exit Sub

  swap v, lft, lft + Int((rgt - lft + 1) * Rnd)

  pvt = lft

  For j = lft + 1 To rgt
    If v(j) < v(lft) Then
      pvt = pvt + 1
      swap v, pvt, j
    End If
  Next j

  swap v, lft, pvt

  qsort v, lft, pvt - 1
  qsort v, pvt + 1, rgt

End Sub


Private Sub swap(ByRef v As Variant, i As Long, j As Long)
  'modified version of the swap procedure
  'translated from Aho, Weinberger & Kernighan,
  '"The Awk Programming Language", page 161

  Dim t As Variant

  t = v(i)
  v(i) = v(j)
  v(j) = t

End Sub

Note: no error checking of v. If it isn't a 1D array, this throws runtime errors.

Unlikely to be much execution time difference for N <= 100.

As for sorting arrays of other dimensions, I've found it more useful to convert PxQ arrays into 1D arrays of P elements each of which is a 1D array of Q+1 elements with the sort key copied into the first element. Sort. Then convert the array of arrays back into a 2D array.

Your question was.... Why integer instead of long?

In my response is.... In this day why does it even matter? Considering the wide variance of knowledge level among all the people who ask questions here?you're posting this thread will do people a lot of good but for most of the people that I run into on a daily basis they won't understand the mathematics of it whatsoever. I understand all of that but everything you wrote is highly technical so I appreciate what you said. I won't add anything more to it.

The other thing to say is that I'm not a master coder nor have I ever been, but rather I manage technical people and use their talents to put things together.

Originally Posted by vba_php

. . . why does it even matter? . . .

Because arrays can have more than 32767 entries. Especially if the array in question were Application.Transpose(Range("A3:A100002")) or similar. Why write library routines for fractions of the problem domain?

. . . for most of the people that I run into on a daily basis they won't understand the mathematics of it whatsoever. . . .

Who reads this site's Tips and Tutorial forum over the course of the typical month? I figure most of them are likely to be sufficiently motivated to be able to understand quick sort. Heck, they probably already know it but just haven't seen a VBA implementation.

Originally Posted by hrlngrv

Because arrays can have more than 32767 entries. Especially if the array in question were Application.Transpose(Range("A3:A100002")) or similar.

never heard of any attempt close to these claims in 20 years. But I don't work in the space of languages that are 30+ years old. I think we've hijacked in here enough. All your points are fine. thanks.

?

Integer and Single types were needed 30+ years ago due to limited RAM and, all too often, software floating point processing.

The largest positive value for an Integer in VBA is 2^15-1, or 32,767, the figure in my previous reply. The largest positive value for a Long is 2^31-1, or 2,147,483,647. Whole Excel columns have 1,048,576 rows, so Integer variables are unreliable for indexing arrays formed from large portions of workbooks. That was even the case in Excel 97 to 2003, when it had 65,536 rows, but still too much for Integer variables to index reliably.

To repeat, if you're going to offer library routines, make them as general and robust as possible. That does, in fact, mean avoiding Integer type variables.

Originally Posted by hrlngrv

if you're going to offer library routines, .

nothing about the code in the original post here has anything to do with libraries. no extra additions are necessary.

I was saying that the discussion was not really relevant because we were essentially splitting hairs. I'll be back later.