Excel sort VB to LO sort help

[d0wnt0wn]

hey guys,

brand new to LO… I have a spreadsheet and all of my macros from excel work except my sort macros… any ideas why?

Sub sort8()
Range(“C541:O549”).Select

ActiveWorkbook.Worksheets("Scoresheet").Sort.SortFields.Clear
ActiveWorkbook.Worksheets("Scoresheet").Sort.SortFields.Add2 Key:=Range( _
    "O542:O549"), SortOn:=xlSortOnValues, Order:=xlDescending, DataOption:= _
    xlSortNormal
With ActiveWorkbook.Worksheets("Scoresheet").Sort
    .SetRange Range("C541:O549")
    .Header = xlYes
    .MatchCase = False
    .Orientation = xlTopToBottom
    .SortMethod = xlPinYin
    .Apply
End With

End Sub

[Lupp]

The question is about sorting in Calc (Workbook" / “Worksheets” are the related Excel terms).
You (the OQ) should therefore tag the question “calc” and remove the tag “common” (for all or many components).

[jimk]

That code is not written in LibreOffice Basic, so of course, it doesn’t work.

To get started with Basic, see for example www.pitonyak.org/oo.php. Also, Option Compatible is confusing for beginners. I would not recommend it unless you need certain specialized functions that Basic cannot normally do.

Python and Java are also good languages for LibreOffice macros.

EDIT:

some macros work perfectly

Some code may work on both platforms, but not most things, and some code that seems to work may fail in unexpected ways. Remove Option Compatible to help avoid unpleasant surprises.

I recorded a macro in libre to see the differences

Recording is generally not a good way to learn, at least not for beginners. The resulting dispatcher code is ugly and often not the best way to do things. It is good for special operations like copy and paste though.

I can barely read VB code as it is I was just wondering if there was a way to convert the VBA to libre.

Think of it as rewriting, not converting. These are programming languages, not just file formats.

With some effort, Basic macros can be learned. However, from your comment, it sounds like you are not up for the task, so you may need a different solution. For example, hire someone who can write LibreOffice macros.

[d0wnt0wn]

Thanks for your reply… The thing I don’t understand is some macros work perfectly but this one particular macro does not… I recorded a macro in libre to see the differences but it was so different I couldn’t understand it. I can barely read VB code as it is I was just wondering if there was a way to convert the VBA to libre

[jimk]

@d0wnt0wn: See edited answer.

[d0wnt0wn]

thanks Jim… although I like the program a lot I need to be able to share my excel sheet with a Libre user she might make changes and the next day I might… I think we will have to sort manually for now.

[ovari]

Can you please share an Excel spreadsheet which has the VBA code and data in the cells?

[ovari]

@d0wnt0wn

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

Perhaps each instance of strSheetName should be changed to intSheetIndex?

For example,
ActiveWorkbook.Worksheets(1).Sort.SortFields.Clear

Does that help?

Another option is to use version 7.4.0 or later of LibreOffice Calc as tdf#149579 may also help.

[Lupp]

I would like to encourage the questioner to get rid of VBA. It may be powerful, but its concept of being a programming language and a (the) ApplicationProgrammingInterface (API) at the same time is very doubtable - and supposed to be from the beginning mainly a means in the struggle for MS user lock-in. In addition VB is perpetuating outdated (bad?) special Basic syntax of the 1980es.

Check the code contained in the attached example for the absence of malign parts and try it then.

To be able to run it from the document, your macro security should be set to ‘Medium’. Don’t forget to restore your preferred setting if higher security is wanted! (Never select “Low”!).

disask53676sortHardCodedRangeBySub.ods (33.7 KB)

===EDITING a few minutes later===
Sorry! I wrongly left a cell in the demo sheet talking of “7 substantial lines”
Actually only 4 lines of the code are doing real work.

[Lupp]

BTW:
Coding in Basic for LibO I sometimes set the module option
Option VBAsupport 1
However, I don’t use wide-range VBA means then, but exclusively the feature that the range object for an actual parameter is passed when a function is called for the evaluation of a cell. Using original LibO Basic means it is complicated to pass a range. In fact you only can pass one or another kind of designation - and re-construct the object by the executable code then. Additional complications expected!

[jimk]

What is going on with this post? The question was written 2 years ago, then ovari posts two answers today, one of which isn’t really an answer, then @Lupp (who I respect a lot) posts a detailed answer, but why? It seems unlikely that we will hear anything more from user d0wnt0wn.

[Lupp]

Sorry. I simply hadn’t looked at the date of the original question when I answered. Probably I systematically biot out the dates to some degree because I extremely dislike the “formats”.used for date and time in this site.
Alas! There always are problems with these psychics like Lupp.
Regards.