Looking for Calc macro to sort like Excel can with this one:

[danielson]

Looking for Calc macro to sort like Excel can with this one.
The VBA code:

Sub Sort()
Cells.Select
ActiveWorkbook.Worksheets("Reserve to start with").Sort.SortFields.Clear
ActiveWorkbook.Worksheets("Reserve to start with").Sort.SortFields.Add2 Key:= _
    Range("G2:G1000"), SortOn:=xlSortOnValues, Order:=xlAscending, DataOption _
    :=xlSortNormal
ActiveWorkbook.Worksheets("Reserve to start with").Sort.SortFields.Add2 Key:= _
    Range("C2:C1000"), SortOn:=xlSortOnValues, Order:=xlAscending, DataOption _
    :=xlSortNormal
With ActiveWorkbook.Worksheets("Reserve to start with").Sort
    .SetRange Range("A1:Z1000")
    .Header = xlYes
    .MatchCase = False
    .Orientation = xlTopToBottom
    .SortMethod = xlPinYin
    .Apply
End With
Application.WindowState = xlNormal
End Sub  

(Slightly edite for better readability by @Lupp.)

[Zizi64]

Please use the

101

010

icon for the code lines.

[anon73440385]

Looking for Calc macro to sort like Excel can with this one.

Don’t assume LibreOffice users have Excel installed and being able to evaluate what your VBA code really does and then build a similar Calc macro for you. Describe your data, how they are organised and what you want to achieve. If doing so, I’m pretty sure someone could help.

Btw: @Ziz64 is talking about to use icon  in the edit menu to format code using `preformatted text` to make it readable.

[danielson]

Hope it’s better now.

Have 7 columns: 1, 2 text. 3. date. 4,5,6 money-currencies. 7 date.
Often, no.7 date column items will fall sooner than previous ones and so, sorting according to effective done date with a macro does the trick to put all the data in place. Basic “sort” feature doesn’t do the trick. That’s why a macro is handy.

[danielson]

@Zizi64 - forgive me, but i’m a tech nerd, a simple kind of plug 'n play guy. Can you give me another hint as to what to do?

[Lupp]

-1- This is not an answer to the question. You should post a remark like this one as a comment.
‑2- You cannot port macros if you refuse to look at the details.
-3- Of course, Calc has means for sorting. The typical API call to sort a range named myRange looks like
myRange.sort(mySortDescriptor)
Simple enough? However, you need to know how to create the myRange object, and how to get an object accepted by the .sort method of a CellRange as its parameter.
This isn’t trivial - and you cannot do it by “translating VBA to CalcSpeak letter by letter”. You need to study it or to omit usage.
A Q&A site is generally not supposed to return a complete tutorial for a 10-word-question.

[erAck]

Please do not use the Answer field for comments that are not an answer to the original question, use add a comment instead. Thanks.

[danielson]

Got it (to you two)!

More homework…

[Lupp]

By accident there was a recent case where I needed too sort a range.
To show an example, I post the roughly adapted example here:

sheet = ThisComponent.Sheets(0)
rgN = "C2:F101"
targetRg = sheet.getCellRangeByName(rgN)
Dim sortDesc(1) As New com.sun.star.beans.PropertyValue 
Dim mySortFields(0) As New com.sun.star.util.SortField
sortDesc(0).Name = "IsSortColumns" REM Dispensable since 'False' is default.
sortDesc(0).Value = False
mySortFields(0).Field = 0 REM sort based on the first column (C in this case)
mySortFields(0).SortAscending = True
sortDesc(1).Name = "SortFields"
sortDesc(1).Value = mySortFields
targetRg.sort(sortDesc)

Of course there are additional property names you can set values for to get the descriptor as you want it.
And of course you can write another macro to help you with the preparation of sort descriptors…

Most non-professional users working with the LibreOffice API (in Basic here) rely on the famous texts by Andrew Pitonyak which you easily find in the web.
See also:
https://documentation.libreoffice.org/en/english-documentation/macro/
https://api.libreoffice.org/docs/idl/ref/namespacecom_1_1sun.html
https://api.libreoffice.org/docs/idl/ref/servicecom_1_1sun_1_1star_1_1util_1_1SortDescriptor.html
https://api.libreoffice.org/docs/idl/ref/servicecom_1_1sun_1_1star_1_1table_1_1TableSortDescriptor.html

[igorlius]

Since LO has some VBA support,

you could try enabling it and check if the macro would run without modification.

Refs:

• Option VBASupport Statement
• Support for VBA Macros

Hope that helps.

[newbie-02]

yeaahhhh,

theese samples show that it is! indeed possible to write something with 17 lines / statements in ex$el even longer and more confusing than in calc with 15 lines / statements …

but what if the ‘programmer’ / user has only one! clear thought and wants to express it in one! clear command instead of typing his fingers sore to confuse future generations and make debugging more difficult?

kiss! - ‘keep it simple and stupid’

would that one liner work?

Sub Macro1()
Range("A1:Z10000").Sort Key1:=Range("A1")
End Sub

ex$el: yes, i like that!,

calc: YES! (if you add Option VBASupport 1 beforehand)

surprised and pleased

!!! could we ‘please’! start to teach such simple samples instead of confusing readers? !!!

we can expand to more sophisticated options lateron, but first we need to get things to work, and users to understand them …

(at least i learned something from investigating )

P.S. ‘solved marks’ and ‘likes’ welcome,
OQ or ‘mods’: click the grey circled hook - ✓ - top left to the answer to turn it green if the problem is solved, you can toggle back if better solutions come up,
all: click the “^” above it if you ‘like’ the answer,
“v” if you don’t,
it error prone, you can toggle back,
OQ: do not! use ‘answer’ to add info to your question, either edit the question or add a comment,
‘answer’ only if you found a solution yourself …

[Lupp]

I completely disagree.
Those who want it the Excel way should use Excel.
The implementation of VBAsupport is a crutch which LibO may need to accept for “marketing” reasons. However, MS can never accept a free competitor to get completely compatible. As soon as LibO came near to it they would hire the needed number of engineers to enforce incompatibility again.

LibO Basic isn’t the API, but implements a rather short and wide bridge to the API. Other programming laguages/systems can have their own bridges. That’s a well proven concept.

BTW: The Key1:=Range("A1") is a good example how not to do things. You delegate the decision if there should be sorted ascending or descending, left to right or top down, assuming headers or not … to hidden defaults and automatisms. “Developing” by such means may be a relevant reason for generally bad software quality. Who “developed” Win 10?

[newbie-02]

hello @Lupp,
nice to hear from you, nearly expected that,
Win10: not the badest win acc. to some people from CCC, didn’t yet test myself, difficult to stop from phoning home,
delegating to hidden defaults: i agree, but respected that in my answer, ‘first get it up and running and users understand it, then procced to more options’,
LiBo API: probably too complicated for simple people like me,
marketing / producing incompatibility: have you seen !that! joke?
another question and
Micro$oft: LibrA Office Suite: BEST OFFICE SUITE FOR WINDOWS 10 …
i fell from my chair and ROFL

[Lupp]

The API may be too engineer-oriented under some aspects, it is too poor under others, and over-elaborate too now and then.
However, the main shortcoming I see is that the documentation gives easily accees to the complete namespace if you look for a service or just a (supposed?) method-name. It never tells you directly in what way you can get the service as a dedicated object itself, or which different objects support it by their specification.
It shouldn’t be impossible to get a kind of cross-index evaluating the idl-space. But the developers are all occupied with the enhancement of VBAsupport. Joked enough?
Concerning the link you posted: “This site uses cookies for analytics, personalized content and ads. By continuing to browse this site, you agree to this use.”