Description:
I’m not fluent in appropriate scripting terms and what not, so please forgive the poor usages.
I am in the process of creating a User-Defined Function (UDF) for users to automate LibreOffice using AutoIt (AutoIt is a  BASIC-like scripting language), while working on making functions to work with Calc, I have run into a problem while trying to use the Sort function. I have successfully worked with Calc using AutoIt, for other functions. 
I have followed Andrew Pitonyak’s book, and double checked all of my code to ensure I haven’t missed anything. It seems to be all correct.
The problem I am encountering is as follows: 
-I have a Range, say A1:A5, with the values 5, 4, 3, 1, 2. I run my AutoIt code that is supposed to sort the range (A1:A5), and have the sort copy the results to a separate cell, cell C3, so that I know the sort function is actually being called. 
-What occurs is the values are output to cell C3 and down, but remain in the order they were found, in this case, 5, 4, 3, 1, 2. 
-In case it matters, if I don’t copy the results, the sort still processes, but the values aren’t sorted that way either. 
-No COM Errors result from calling the script, in either case.

To rule out an incompatibility with AutoIt, I attempted to convert the code to work in VBA/VBS (the only other BASIC language I can figure out how to use to automate LibreOffice at the moment). The result is the same, the sort is processed, but the output is not sorted. As a side note, the VBA code I am running from a Microsoft Word Macro engine, the code, with a minor change runs fine from LibrOffice itself. (see post here be JeJe https://forum.openoffice.org/en/forum/viewtopic.php?p=545947&sid=d20cea321b08fdfe50cf39c80ad6e4d7#p545947) 

I have also converted the code to work in LibreOffice Basic Macro. That, of course, works fine. I am thinking that this may be a bug in LibreOffice itself, when an external language (or BASIC language at least) is used to call a sort function? 

As a side note, if I can explain this correctly, I think the main issue at hand is the SortField Struct or Sortfield Array is not being recognized in the array of sort descriptor settings. The reason for this conclusion is as follows: I can write a simple macro and store it in the LibreOffice Calc document, such as the following: 
REM Macro for Performing a Sort Function. 

			Sub AU3LibreOffice_Sort(oRange, avSortDesc, atField)
			
			For i = LBound(avSortDesc) To UBound(avSortDesc) 
			If (avSortDesc(i).Name() = ""SortFields"") Then avSortDesc(i).Value = atField
			
			Next 
			
			oRange.Sort(avSortDesc())
		End Sub

After creating the above macro in the Calc document, I can go to AutoIt, and using the SortField Struct array, the Sort Descriptor Array, and the range Object that I tried to call the sort command with directly from AutoIt (which failed, as noted above), I can instead pass those as parameters to the Macro in the document, and then have the macro insert the SortField array and call the sort, and the sort works just fine. If I pass only the Range and the SortDescriptor array with the SortField Array inserted already to the above Macro, the sort fails the same as when I tried to sort directly from AutoIt.

If you need me to clarify, or try to re-word anything, let me know, and I will try my best. As stated above I have attempted to reproduce the script in other BASIC language versions, in the hopes of demonstrating that it is not a AutoIt specific limitation. My sincere apologies if this is something that is known, and is not a LibreOffice bug.

I will include the scripts in the Other Information section.

Steps to Reproduce:
1. Run one of the below scripts external to LibreOffice
2.
3.

Actual Results:
Sort is processed but fails to sort the data.

Expected Results:
Sort should be processed, and data should be sorted.


Reproducible: Always


User Profile Reset: Yes

Additional Info:
Version: 7.3.4.2 (x64) / LibreOffice Community
Build ID: 728fec16bd5f605073805c3c9e7c4212a0120dc5
CPU threads: 2; OS: Windows 6.1 Service Pack 1 Build 7601; UI render: Skia/Raster; VCL: win
Locale: en-US (en_US); UI: en-US
Calc: CL



The scripts I have written are below. In all of them I have made  it automatically fill in the values 5, 4, 3, 1, 2 in cells A1:A5 to ease the demonstration.
Code for AutoIt, must be run using AutoIt scripting language editor etc, found here: https://www.autoitscript.com/site/autoit/downloads/
I could also compile it as an exe.

Global $oCOM_ErrorHandler = ObjEvent("AutoIt.Error", __COM_ERROR)

Global $oServiceManager = ObjCreate("com.sun.star.ServiceManager")
If Not IsObj($oServiceManager) Then Exit ConsoleWrite("! Error: " & @ScriptLineNumber & @CRLF)

Global $oDesktop = $oServiceManager.createInstance("com.sun.star.frame.Desktop")
If Not IsObj($oDesktop) Then Exit ConsoleWrite("! Error: " & @ScriptLineNumber & @CRLF)

Global Const $iURLFrameCreate = 8     ;frame will be created if not found
Global $aArgs[0]

; Create a new Calc document.
Global $oDoc = $oDesktop.loadComponentFromURL("private:factory/scalc", "_blank", $iURLFrameCreate, $aArgs)
If Not IsObj($oDoc) Then Exit ConsoleWrite("! Error: " & @ScriptLineNumber & @CRLF)

; Retrieve Active Sheet
Global $oSheet = $oDoc.CurrentController.getActiveSheet()
If Not IsObj($oSheet) Then Exit ConsoleWrite("! Error: " & @ScriptLineNumber & @CRLF)

; Retrieve Cell Range A1 to A5
Global $oRange = $oSheet.getCellRangeByName("A1:A5")
If Not IsObj($oRange) Then Exit ConsoleWrite("! Error: " & @ScriptLineNumber & @CRLF)

; Fill Arrays with numbers
Global $aaiArray[5]
Global $aiFill[1]

$aiFill[0] = 5
$aaiArray[0] = $aiFill
$aiFill[0] = 4
$aaiArray[1] = $aiFill
$aiFill[0] = 3
$aaiArray[2] = $aiFill
$aiFill[0] = 1
$aaiArray[3] = $aiFill
$aiFill[0] = 2
$aaiArray[4] = $aiFill

; Fill the Range with numbers.
$oRange.setData($aaiArray)

Global Const _
		$LOC_SORT_DATA_TYPE_AUTO = 0, _ ; Automatically determine Sort Data type.
		$LOC_SORT_DATA_TYPE_NUMERIC = 1, _ ; Sort Data type is Numerical.
		$LOC_SORT_DATA_TYPE_ALPHANUMERIC = 2 ; Sort Data type is Text.

; Create a Sort Descriptor,
Global $tSortField = $oServiceManager.Bridge_GetStruct("com.sun.star.table.TableSortField")
If Not IsObj($tSortField) Then Exit ConsoleWrite("! Error: " & @ScriptLineNumber & @CRLF)

With $tSortField
	.Field = 0 ; 0 = first column in the range A1-A5.
	.FieldType = $LOC_SORT_DATA_TYPE_NUMERIC ; Numerical values being sorted
	.IsAscending = False ; Descending order
	.IsCaseSensitive = False
EndWith

Global $atSortField[1] = [$tSortField]

$avSortDesc = $oRange.createSortDescriptor()

; Create a Cell Address to indicate where to copy output to. Cell C3
Global $tCellAddr = $oServiceManager.Bridge_GetStruct("com.sun.star.table.CellAddress")
If Not IsObj($tCellAddr) Then Exit ConsoleWrite("! Error: " & @ScriptLineNumber & @CRLF)

$tCellAddr.Sheet = 0    ; 0 = first sheet.
$tCellAddr.Column = 2    ; 2 = Column C
$tCellAddr.Row = 2    ; 2 = Row 3

; Apply Sort settings
For $i = 0 To UBound($avSortDesc) - 1

	Switch $avSortDesc[$i].Name()

		Case "IsSortColumns"
			$avSortDesc[$i].Value = False ; False = Sort rows top to bottom.

		Case "ContainsHeader"
			$avSortDesc[$i].Value = False ; False = Range has no headers to ignore.

		Case "SortFields"
			$avSortDesc[$i].Value = $atSortField

		Case "BindFormatsToContent"
			$avSortDesc[$i].Value = False ; False = Dont bind any formatting to the data when sorted.

		Case "CopyOutputData"
			$avSortDesc[$i].Value = True ; True = Copy the sort results instead of modifying the cell range itself.

		Case "OutputPosition"
			$avSortDesc[$i].Value = $tCellAddr

	EndSwitch

Next

; Perform the sort
$oRange.Sort($avSortDesc)


Func __COM_ERROR(ByRef $oComError)
	ConsoleWrite("!--COM Error-Begin--" & @CRLF & _
			"Number: 0x" & Hex($oComError.number, 8) & @CRLF & _
			"WinDescription: " & $oComError.windescription & @CRLF & _
			"Source: " & $oComError.source & @CRLF & _
			"Error Description: " & $oComError.description & @CRLF & _
			"HelpFile: " & $oComError.helpfile & @CRLF & _
			"HelpContext: " & $oComError.helpcontext & @CRLF & _
			"LastDLLError: " & $oComError.lastdllerror & @CRLF & _
			"At line: " & $oComError.scriptline & @CRLF & _
			"!--COM-Error-End--" & @CRLF)
EndFunc   ;==>__COM_ERROR


VBA script, produces the error when ran externally from LibreOffice, specifically from Microsoft Office Macro editor. This script works when run from LibreOffice itself, with the minor modification of changing the line 
Set tSortField = oServiceManager.Bridge_GetStruct("com.sun.star.table.TableSortField")
to
Set tSortField = new "com.sun.star.table.TableSortField"


Sub SortLibre()

 ' Create the Service Manager
  Set oServiceManager = CreateObject("com.sun.star.ServiceManager")

  ' Create the desktop.
  Set oDesktop = oServiceManager.createInstance("com.sun.star.frame.Desktop")

  ' Open a new empty Calc document.
  Dim args()
  Dim s As String
  s = "private:factory/scalc"
  Set oDoc = oDesktop.loadComponentFromURL(s, "_blank", 0, args())

  Dim oSheet As Object
' Retrieve Active Sheet
Set oSheet = oDoc.CurrentController.getActiveSheet()

' Retrieve Cell Range A1 to A5
Dim oRange As Object
Set oRange = oSheet.getCellRangeByName("A1:A5")

' Fill the Range with numbers.
oRange.setData (Array(Array(5), Array(4), Array(3), Array(1), Array(2)))

  ' Dim atSortFields(0) As New com.sun.star.Table.TableSortField
Dim atSortFields As Variant
Dim tSortField As Object

Set tSortField = oServiceManager.Bridge_GetStruct("com.sun.star.table.TableSortField")

With tSortField
.Field = 0
.FieldType = 1 ' Numeric
.IsAscending = False ' Descending Sort order
.IsCaseSensitive = False
End With

atSortFields = Array(tSortField)

Dim avSortDesc As Variant
avSortDesc = oRange.createSortDescriptor()

' Create a Cell Address to indicate where to copy output to. Cell C3
Dim tCellAddr As Object
Set tCellAddr = oSheet.getCellRangeByName("C3").CellAddress()

tCellAddr.Sheet = 0    ' 0 = first sheet.
tCellAddr.Column = 2    ' 2 = Column C
tCellAddr.Row = 2   ' 2 = Row 3

' Apply Sort settings
For i = LBound(avSortDesc) To UBound(avSortDesc)

    Select Case avSortDesc(i).Name()

        Case "IsSortColumns"
            avSortDesc(i).Value = False ' False = Sort rows top to bottom.

        Case "ContainsHeader"
            avSortDesc(i).Value = False ' False = Range has no headers to ignore.

        Case "SortFields"
            avSortDesc(i).Value = atSortFields

        Case "BindFormatsToContent"
            avSortDesc(i).Value = False ' False = Dont bind any formatting to the data when sorted.

        Case "CopyOutputData"
            avSortDesc(i).Value = True ' True = Copy the sort results instead of modifying the cell range itself.

        Case "OutputPosition"
            avSortDesc(i).Value = tCellAddr ' Copy to Cell C3

    End Select

Next

' Perform the sort
oRange.Sort (avSortDesc)

End Sub


VBS script. Produces the error when ran as a script on Windows computer.


 ' Create the Service Manager
  Set oServiceManager = WScript.CreateObject("com.sun.star.ServiceManager")

  ' Create the desktop.
  Set oDesktop = oServiceManager.createInstance("com.sun.star.frame.Desktop")

  ' Open a new empty Calc document.
  Dim args()
  Set oDoc = oDesktop.loadComponentFromURL("private:factory/scalc", "_blank", 0, args)

' Retrieve Active Sheet
Set oSheet = oDoc.CurrentController.getActiveSheet()

' Retrieve Cell Range A1 to A5
Set oRange = oSheet.getCellRangeByName("A1:A5")

' Fill the Range with numbers.
oRange.setData (Array(Array(5), Array(4), Array(3), Array(1), Array(2)))

Set tSortField = oServiceManager.Bridge_GetStruct("com.sun.star.table.TableSortField")

With tSortField
.Field = 0
.FieldType = 1 ' Numeric
.IsAscending = False ' Descending Sort order
.IsCaseSensitive = False
End With

atSortFields = Array(tSortField)

avSortDesc = oRange.createSortDescriptor()

' Create a Cell Address to indicate where to copy output to. Cell C3
Set tCellAddr = oSheet.getCellRangeByName("C3").CellAddress

tCellAddr.Sheet = 0    ' 0 = first sheet.
tCellAddr.Column = 2    ' 2 = Column C
tCellAddr.Row = 2   ' 2 = Row 3

' Apply Sort settings
For i = LBound(avSortDesc) To UBound(avSortDesc)

    Select Case avSortDesc(i).Name

        Case "IsSortColumns"
            avSortDesc(i).Value = False ' False = Sort rows top to bottom.

        Case "ContainsHeader"
            avSortDesc(i).Value = False ' False = Range has no headers to ignore.

        Case "SortFields"
            avSortDesc(i).Value = atSortFields

        Case "BindFormatsToContent"
            avSortDesc(i).Value = False ' False = Dont bind any formatting to the data when sorted.

        Case "CopyOutputData"
            avSortDesc(i).Value = True ' True = Copy the sort results instead of modifying the cell range itself.

        Case "OutputPosition"
            avSortDesc(i).Value = tCellAddr ' Copy to Cell C3

    End Select

Next

' Perform the sort
oRange.Sort (avSortDesc)


And the LibreOffice Macro I created as a test. This runs and produces correct results. 

Sub Main

' Retrieve Active Sheet
Dim oSheet 
oSheet = ThisComponent.CurrentController.getActiveSheet()

' Retrieve Cell Range A1 to A5
Dim oRange
oRange = oSheet.getCellRangeByName("A1:A5")

' Fill the Range with numbers.
oRange.setData(Array(Array(5), Array(4), Array(3), Array(1), Array(2)))

  Dim atSortFields(0) as new com.sun.star.table.TableSortField

  atSortFields(0).Field = 0 ' 0 = first column in the range.
 	 atSortFields(0).FieldType =com.sun.star.util.SortFieldType.NUMERIC '  = Numerical values being sorted
	 atSortFields(0).IsAscending = False ' Descending order
	 atSortFields(0).IsCaseSensitive = False

DIm  avSortDesc
avSortDesc = oRange.createSortDescriptor()

' Create a Cell Address to indicate where to copy output to. Cell C3
Dim tCellAddr  As New com.sun.star.table.CellAddress

tCellAddr.Sheet = 0    ' 0 = first sheet.
tCellAddr.Column = 2    ' 2 = Column C
tCellAddr.Row = 2   ' 2 = Row 3

' Apply Sort settings
For i = LBound(avSortDesc) To UBound(avSortDesc)

	Select Case avSortDesc(i).Name()

		Case "IsSortColumns"
			avSortDesc(i).Value = False ' False = Sort rows top to bottom.

		Case "ContainsHeader"
			avSortDesc(i).Value = False ' False = Range has no headers to ignore.

		Case "SortFields"
			avSortDesc(i).Value = atSortFields

		Case "BindFormatsToContent"
			avSortDesc(i).Value = False ' False = Dont bind any formatting to the data when sorted.

		Case "CopyOutputData"
			avSortDesc(i).Value = True ' True = Copy the sort results instead of modifying the cell range itself.

		Case "OutputPosition"
			avSortDesc(i).Value = tCellAddr

	End Select

Next

' Perform the sort
oRange.Sort(avSortDesc)
End Sub