Sorting Arrays of Strings in Access with WizHook

One of the more interesting methods of the undocumented WizHook object in Microsoft Access is WizHook.SortStringArray.

As the name suggests, the WizHook.SortStringArray routine sorts an array of strings in place.  By "in place," we mean that the original array variable gets overwritten.

Basic Usage

The SortStringArray routine is one of the few methods of the WizHook object that do not require setting the magic key before use.

In fact, you don't need to do anything special to use the function at all, though there are a few things to be aware of:

• You won't see IntelliSense for this method unless you right-click in the Object Browser and check the box to [√] Show Hidden Members
• If you leave [  ] Show Hidden members unchecked, the code will still work (just, you know, no IntelliSense)
• The array being sorted must be declared using the array syntax and explicitly set to the String type; e.g.,
     GOOD: Dim Vals() As String
     BAD: Dim Vals() As Variant
     BAD: Dim Vals As Variant
• While you can sort numbers stored as strings using this function, the stringified numbers get sorted in "alphabetical" order–not numerical order
• The WizHook object is a member of the Access library–not the standard VBA library–so this won't work in other Office applications¹

^{1. Unless you include a reference to Access and Access is present on the end user's machine}

Once you have a populated array, you simply pass it to the WizHook.SortStringArray method to sort it alphabetically:

WizHook.SortStringArray MyArray

Usage Samples

I'll be using the PrintArray function to output array values to the immediate window in my samples below:

Sub PrintArray(Values As Variant)
    Dim i As Long
    For i = LBound(Values) To UBound(Values)
        'The trailing comma will separate values with tabs
        '	without adding line breaks between Debug.Print calls
        Debug.Print Values(i),   
    Next i
    Debug.Print   'Add line break
End Sub

Basic Sample

The following sample illustrates how the function works using an explicitly populated, strongly-typed string array:

Sub Test1()
    Dim MyArray(0 To 3) As String
    MyArray(0) = "Happy"
    MyArray(1) = "30th"
    MyArray(2) = "Birthday"
    MyArray(3) = "Access"
    
    PrintArray MyArray
    WizHook.SortStringArray MyArray
    PrintArray MyArray
End Sub

Generating a String Array with Split()

This example uses the Split() function to quickly create a string array suitable for sorting with WizHook.  Note the following:

• The array is not explicitly dimensioned (i.e., the parentheses are empty in the declaration line)
• The array is explicitly typed (i.e., it is declared As String)
• The variable must be an array and not a Variant; a Variant would be compatible with the Split() function but is not compatible with WizHook.SortStringArray

Sub Test2()
    Dim MyArray() As String
    MyArray = Split("WizHook is the coolest")
    
    PrintArray MyArray
    WizHook.SortStringArray MyArray
    PrintArray MyArray
End Sub

Sorting Values Passed via ParamArray

This routine shows how to accept a variable number of parameters and still sort them using the SortStringArray method.  It also doubles as a handy way to run additional tests to better understand the sort ordering and capabilities of the SortStringArray method:

Sub TestWizHookSort(ParamArray Values() As Variant)
    'The array must be declared `As String` because
    '   WizHook.SortStringArray only works with arrays of *Strings*
    Dim Temp() As String
    
    'Redimension the temp array to fit the number of parameters passed
    ReDim Temp(LBound(Values) To UBound(Values))
    
    'Populate the temporary array
    Dim i As Long
    For i = LBound(Values) To UBound(Values)
        Temp(i) = Values(i)
    Next i
    
    'Print the unsorted array
    Debug.Print
    Debug.Print "Unsorted:",
    PrintArray Temp
    
    'Sort the array
    WizHook.SortStringArray Temp
    
    'Print the sorted array
    Debug.Print "Sorted:",
    PrintArray Temp
    Debug.Print
End Sub

Referenced articles

Image by Angela from Pixabay