basic SORT runtime error 423

Description:
I have a spreadsheet which is a database of group members ported into LibreOffice from Excel which contains Macros. When Libra executes the following command to Sort db entries by name or address in the macro it throws a "BASIC runtime error. ‘423’ Sort"

ActiveWorkbook.Worksheets("myRange").Sort.SortFields.Clear

Steps to Reproduce:

  1. Create a macro with this VBA command in it.

  2. Execute the macro.

Actual Results:
Basic Runtime error 423 Sort

Macro stops.

Expected Results:
Should clear the sort fields as in Excel

Reproducible: Always

User Profile Reset: No

Looking over past postings this seems to be a long existing bug! Is there any chance it might get fixed, if not is there a work around?

Thanks.
Clipped hereto is an image of the short macro. (Not easy to send actual macro as it is full of personal contact info!)

SortErr.jpg

This is already reported here #tdf121919 - and from the wording it is almost the same thing (possibly reported by yourself ?). So no joy, if you paste a bug report here, while OP of the bug report disregards the NEEDINFO status. This site is not about reporting bugs.

I just installed LibreOffice one day ago and have never before reported this as a bug.
In trying to find an answer before I bothered the Forum I have seen that there are prior instances of a similar nature, so this may well be a bug, but I don’t know. I am just trying to get my Excel based macros to work and looking for answers.
At this time I don’t know if this is a bug or a mistake on my part, so bear with me.

If you think it is a bug please advise how/where I take further action. Glad to do things right but since this is my 1st day here I haven’t a clue at the moment what the process is.

If this is possibly something I am doing wrong then please comment further rather than only being negative.
Thank you.

To rewrite your macro from a language that is alien to this office suite to StarBasiс is not difficult - it is much easier than writing complaints to Bugzilla and waiting for a solution to a problem that is not difficult to avoid.

Sub SortByUnit
Dim entries As Long
Dim oNamedRange As Variant, oSheet As Variant, oDatRange As Variant, oCurrentController As Variant
Dim aSortFields(0) As New com.sun.star.util.SortField
Dim aSortDesc(1) As New com.sun.star.beans.PropertyValue
	oNamedRange = ThisComponent.NamedRanges.getByName("EndRange")
Rem If  the named range "EndRange" is lost then operation is not possible.
	If IsNull(oNamedRange) Or IsEmpty(oNamedRange) Then Exit Sub
	entries = oNamedRange.getReferredCells().getCellByPosition(0, 0).getCellAddress().Row
	oSheet = ThisComponent.getSheets().getByName("byName")
Rem If  "byName" sheet renamed or deleted then operation is not possible.
	If IsNull(oSheet) Or IsEmpty(oSheet) Then Exit Sub
	oDatRange = oSheet.getCellRangeByName("A1:Q" & entries)
	oCurrentController = ThisComponent.getCurrentController()
	oCurrentController.Select(oDatRange)
	aSortFields(0).Field = 0	' First column - A
	aSortFields(0).SortAscending = TRUE
	aSortDesc(0).Name = "SortFields" 
	aSortDesc(0).Value = aSortFields()
	aSortDesc(1).Name = "ContainsHeader"
	aSortDesc(1).Value = True
	oDatRange.Sort(aSortDesc())
Rem Like as your "Sendkey {ESC}":
oCurrentController.Select(ThisComponent.createInstance("com.sun.star.sheet.SheetCellRanges"))
End Sub

(By the way, the message issued by Basic means something like this: my sheets do not have a property or method “Sort”)

@JohnSUN is always coming with great solutions.

However, this one has one bit that I would like to disagree with dear John:

it is much easier than writing complaints to Bugzilla and waiting for a solution to a problem that is not difficult to avoid

This reads as if you are discouraging OP from writing bug reports. And I strongly disagree with that discouragement (of course, not with the workaround/alternative way of achieving the goal that you suggest!). Filing bugs is a must, the required first step for something to change in LibreOffice. Yes, it’s wise to have correct expectations, specifically not waiting for a timely resolution of the report - but writing bugs is not something to be substituted by alternatives: they must come together, hand-in-hand.

Anyway - I want to stress that I always admire the quality of your advises, and only disagree with a single aspect.

@mikekaganski I could agree with you - a found and well-documented error is a help to the Community. But I do not welcome the publication (sorry, I can not find another word) of garbage under the guise of an error. Here it is, in the screenshot - whose fault is this? Is it really an office or Option VbaSupport?

VBAnotStarBasic

You mean that the text “BASIC runtime error. ‘423’” is something that could be a better message? I wholeheartedly agree - and that’s a reason to file another bug report.

Bug report about what? Is there little text in the popup? Many years, users have demanded compatibility with the MS! And MS is also not very verbose. Classic example:

Error400.png

Dear developer! Do you want to use the VBA you’re used to? You will receive error messages that you should be used to.

VBAErrCode.png

I might not quite understand what your comment was about - I mean “But I do not welcome the publication … of garbage under the guise of an error”. I thought you said that the error was not specific enough - but possibly you meant something different… sorry.

Also I would love to continue the discussion e.g. by mail, or in PMs on forumooo.ru if you prefer - or here, no problem at all, just suggesting :slight_smile: