Using VBA's onboard sort function-CodePudding

I want to sort an array and since I am lazy I wanted to use the onboard sort function of EXCEL/VBA. According to the official MS Office support worksheetfunctions.sort should also be working with arrays, see here. Here is my first try.

Sub sorting()

Dim myArray1() As Double
Dim myArray2() As Double
ReDim myArray1(1 To 5)

myArray1(1) = 0.221157
myArray1(2) = -0.147981
myArray1(3) = -2.07119
myArray1(4) = 4.434685
myArray1(5) = -2.706056

myArray2 = WorksheetFunction.Sort(myArray1, 1, 1)

End Sub

This produces a type mismatch error, therefore I have adjusted my code to

Sub sorting()

Dim myArray1() As Variant
Dim myArray2() As Variant
ReDim myArray1(1 To 5)

myArray1(1) = 0.221157
myArray1(2) = -0.147981
myArray1(3) = -2.07119
myArray1(4) = 4.434685
myArray1(5) = -2.706056

myArray2 = WorksheetFunction.Sort(myArray1, 1, 1)

End Sub

The code runs without any error, but myArray2 is not sorted, it is just a copy of myArray1.

I am using:

Microsoft® Excel® for Microsoft 365 MSO (Version 2204 Build 16.0.15128.20128) 32-bit

CodePudding user response：

Your problem is connected to the fourth function parameter...

It is Optional, but its makes sorting by rows if not used (False). Since you try sorting a 1D array not having any row, it does not sort anything.

You should use:

   myArray2 = WorksheetFunction.Sort(myArray1, 1, 1, True)

Such an array has only columns...

Note: The array to be filtered must be declared As Variant, in the same way it is needed to be declared when it will be loaded from a range. The above function does not accept such an array declared As Double, As Long...