How to sort an array

Brian Element

Somteimes you need to be able to sort an array.  This function will take a single array and sort it in alphabetical order.

Snippet

Private Function sortArray(MyArray() As String)
 Dim lLoop, lLoop2 As Integer
 Dim str1, str2 As String
 For lLoop = 0 To UBound(MyArray)

  For lLoop2 = lLoop To UBound(MyArray)

   If UCase(MyArray(lLoop2)) < UCase(MyArray(lLoop)) Then

    str1 = MyArray(lLoop)
    str2 = MyArray(lLoop2)
    MyArray(lLoop) = str2
    MyArray(lLoop2) = str1

   End If

  Next lLoop2

 Next lLoop

End Function

tryvltkcb Wed, 04/03/2019 - 00:52

Hi Brian,
I have apply this function to sort selected fields with selected field type. It works well except there is no field matching with my selected field type.
For example, I select Date type in a list of Radio Button to get & load all Date type fields to a Drop Down Combo Box. However, it returns error "Subcript out of range". I tried and failed to fix this error. Is there any solution for it?
I'm eager to receive your feedback.

Brian Element Wed, 04/03/2019 - 07:30

Can you validate that you actually have date field types in your database, you can check that out by double clicking on a cell in your database to open the Field Manipulation and then in the type column make sure there is at least one date field.  This type of error generally indicates that there are no fields of this type as I don't think I bothered to add error checking code on this.

tryvltkcb Wed, 04/03/2019 - 21:42

The script works well when there is at lease one date field in db. However, users need to cancel and then reopen the script if there is no Date field in the db.
As I let users choose data type in a list of Radio Button first, I think the script would be more user-friendly if it returns a error checking when there is actually no Date field in the table with many fields (so they dont need to check field types before using the script:) ). 
Maybe I will try with On error resume next/goto error handler.
Thank you, Brian :)
 

Brian Element Thu, 04/04/2019 - 08:49

You need to test before you actually call this function.  You would have code that is populating the array and it is there that you should test if the array has at least one item in it.  Here is some example code, I used the Samples project and the Sample-Employees.IMD file as it doesn't have any date fields.  Before calling the sort I check to see if there is anything in the array using a boolean that I created to track if the array has any items.

Dim listbox1$()

Sub Main
	Call populateArray()
End Sub

Function populateArray()
	Dim i As Integer
	Dim bFirstPass As Boolean
	
	bFirstPass = True
	Set db = Client.OpenDatabase("Sample-Employees.IMD")
		Set table = db.TableDef
			For i = 1 To table.count
				Set field = table.GetFieldAt(i)
					If field.IsDate Then
						If bFirstPass Then
							bFirstPass = False
							ReDim listbox1$(0)
							listbox1$(0) = field.name
						Else
							ReDim preserve listbox1$(UBound(listbox1$) + 1)
							listbox1$(UBound(listbox1$)) = field.name
						End If
					End If
			Next i
		Set table = Nothing
		db.close
	Set db = Nothing
	If bFirstPass Then
		MsgBox "There are no date fields in this file"
	Else
		Call sortArray(listbox1$)
	End If
End Function

Private Function sortArray(MyArray() As String)
	Dim lLoop, lLoop2 As Integer
	Dim str1, str2 As String
	For lLoop = 0 To UBound(MyArray)
	
		For lLoop2 = lLoop To UBound(MyArray)
		
			If UCase(MyArray(lLoop2)) < UCase(MyArray(lLoop)) Then
			
				str1 = MyArray(lLoop)
				str2 = MyArray(lLoop2)
				MyArray(lLoop) = str2
				MyArray(lLoop2) = str1
			
			End If
		
		Next lLoop2
	
	Next lLoop

End Function