Calc: New macro won’t run

[aksarben]

I'm trying to create a macro to sort a column of numeric data in descending order.

I've recreated and saved the macro 10 times, and don't get any error messages. However, every time I choose Tools-Macro-Run Macro, select the macro and click the Run button, nothing happens. No error messages appear, but the column remains unsorted.

Here's the macro content (all auto-generated, I haven't done any manual editing):

Code: Select all

REM  *****  BASIC  *****

sub Sort_by_Google_Hits
rem ----------------------------------------------------------------------
rem define variables
dim document   as object
dim dispatcher as object
rem ----------------------------------------------------------------------
rem get access to the document
document   = ThisComponent.CurrentController.Frame
dispatcher = createUnoService("com.sun.star.frame.DispatchHelper")

rem ----------------------------------------------------------------------
dim args1(6) as new com.sun.star.beans.PropertyValue
args1(0).Name = "ByRows"
args1(0).Value = true
args1(1).Name = "HasHeader"
args1(1).Value = false
args1(2).Name = "CaseSensitive"
args1(2).Value = false
args1(3).Name = "IncludeAttribs"
args1(3).Value = true
args1(4).Name = "UserDefIndex"
args1(4).Value = 0
args1(5).Name = "Col1"
args1(5).Value = 3
args1(6).Name = "Ascending1"
args1(6).Value = false

dispatcher.executeDispatch(document, ".uno:DataSort", "", 0, args1())


end sub

Can anyone tell why this macro won't run?

[FJCC]

The macro works if the active cell is in the data range to be sorted. Click on a cell in your data set before running the macro. I assume your data are contiguous.

[aksarben]

Well, you're right. That did the trick.

But something's amiss. I just got a new computer with Windows 11. I had the same macro on a Open Office Calc spreadsheet, and it worked just fine when all I had to do was press the button. I never had to make any cell active, the button & macro took care of it all.

What's changed?

[aksarben]

I just solved the problem, but I don't understand why the solution works.

I initially recorded the macro by clicking at the top of the column (Column C, in this case), above the first row. Then I chose Data-Sort-Extend selection etc., and saved the macro. This is the version I submitted in at the beginning of this thread, which didn't work as expected.

Based on your reply, I tried recording the macro again, but this time instead of selecting the label “C” at the top of the column, I selected cell C1. Then I choose Data-Sort, and Calc automatically selected the whole column, extended the selection to include the entire sheet, and opened the Sort Criteria Options dialog. I then simply clicked OK, and the data was was sorted as desired. And the sort button now works without my having to pre-select any cell. It automatically sorts Column C.

So I guess that solves my immediate problem, but I can't figure out why such a bizarre and unintuitive recording sequence is required to make it work. And another question: If this behavior is by by design, where is it documented? I don't see any mention of such a procedure in any of the online documentation?

[FJCC]

If you select a single cell, OpenOffice will automatically extend the sort area to all the non-empty cells that are contiguous with the selected cell. If you select more than one cell, OpenOffice does not extend the area.
The code below selects A1 at the start of the macro. It should work for you, though you can change A1 to C1 if you want to.

Code: Select all

sub Sort_by_Google_Hits2
rem ----------------------------------------------------------------------
rem define variables
dim document   as object
dim dispatcher as object
rem ----------------------------------------------------------------------
rem get access to the document
document   = ThisComponent.CurrentController.Frame
dispatcher = createUnoService("com.sun.star.frame.DispatchHelper")

rem ----------------------------------------------------------------------
dim args1(0) as new com.sun.star.beans.PropertyValue
args1(0).Name = "ToPoint"
args1(0).Value = "$A$1"

dispatcher.executeDispatch(document, ".uno:GoToCell", "", 0, args1())

rem ----------------------------------------------------------------------
dim args2(6) as new com.sun.star.beans.PropertyValue
args2(0).Name = "ByRows"
args2(0).Value = true
args2(1).Name = "HasHeader"
args2(1).Value = false
args2(2).Name = "CaseSensitive"
args2(2).Value = false
args2(3).Name = "IncludeAttribs"
args2(3).Value = true
args2(4).Name = "UserDefIndex"
args2(4).Value = 0
args2(5).Name = "Col1"
args2(5).Value = 3
args2(6).Name = "Ascending1"
args2(6).Value = false

dispatcher.executeDispatch(document, ".uno:DataSort", "", 0, args2())


end sub