Microsoft Excel VBA Sort function which works with LibreOffice Calc without modification

English
, , , ,
#1

LibreOffice Calc opens the Statement Modified 2.xls Excel spreadsheet file (attachment in bug issue at tdf#149325). LibreOffice Calc runs the VBA macro without crashing when the “Generate Statement” button is clicked in the “Range” sheet.

The Sort function works in MS Excel, but not LO Calc. The MS Excel VBA sort code is shown below:

Private Sub SortData(intSheetIndex As Integer)

' Works with Microsoft Excel, not LibreOffice Calc
    With Worksheets(intSheetIndex)
        .Range("A1").Sort _
            Key1:=.Columns("A"), _
            Order1:=xlAscending, _
            Header:=xlYes, _
            MatchCase:=False, _
            Orientation:=xlTopToBottom
    End With
' Enums
' xlAscending   : 1
' xlYes         : 1
' False         : 0 (guess)
' xlTopToBottom : 1

End Sub

Eike Rathke kindly advised the location of ScVbaRange::Sort(), i.e.
https://opengrok.libreoffice.org/xref/core/sc/source/ui/vba/vbarange.cxx?r=d6331fc7#3411

How can the MS Excel VBA sort code be rewritten so that it works with both MS Excel and LO Calc?

Thank you

#4

You can use the following scheme in all cases:

Private Sub SortData2(intSheetIndex As Integer)
  
#If VBA6 = 0 Then
  ' Excel ignores all statements up to #End If
  ' Sort Calc Data
  ' ...
  Exit Sub
#End If

' Sort Excel Data
' ...
End Sub
3 Likes
Can I make a LO Calc ods macro work in Excel? Says file is corrupted
LibreOffice Calc `Application.ScreenUpdating` code
Detect if VBA is running under LibreOffice or Excel (or OpenOffice)
#22

FYI The link below shows some of the MS Compiler Constants that are available, including VBA6 and VBA7, both of which are boolean, so this would also work (and I guess should work in LO):

#If Not VBA6 Then ’ LO code
…
also
#If Not VBA7 Then’ LO code
…

#3

Hello
Please have a look on this

#6

I doubt the OP wanted a Python solution for code that should run in both Calc and Excel…

Apart from that, it is a very bad idea to let a user defined or Add-In spreadsheet function modify sheet content while it is interpreted. Bad advice. Simply don’t.

#5

The underlying problem seems to be that this

does exactly what is requested, it sorts the range A1, which of course does nothing.
I don’t know what Excel does there, maybe it automagically expands the range to the used data area, which also Calc does if Sort is triggered in the UI, but the API uses the exact range specified. Something like .Range("A1:D100").Sort should work.

Maybe that could be enhanced at least for the VBA compatibility layer to select a contiguous data area if only a cell was specified, if that is what Excel actually does. Question remains whether it also selects to the left and upwards (if it was given “B2” for example) or what happens if a smaller range within the data area was given.

So if Excel actually does such expansion of the range and the cause is not something different then best submit a request for enhancement (RFE) in the bug tracker.

1 Like
#7

Experiments show that the Range.Sort method expands the original range to Range.CurrentRegion if the Range consists of a single cell.
The Range.CurrentRegion property can expand the range in all directions.

#8

Ah that sheds some light and makes sense. Thanks!

#9

I submitted tdf#149579 for this.
And implemented for 7.4.0.0.beta2

1 Like
#10

I will not comment on the mentioned bug report, but tell my opinion here that automatic expansion of a single-cell-range to the cell.CurrentRegion comes with risks.
Having started a sort operation from the UI it may be different. The user -if not sleeping- Is informed of the effect by highlighting, and can cancel the sorting if that isn’t wjhat he wanted. The Sub simply will run.
Everybody should consider a probable need to stop the way to ever more automatisms -as long as sheets dont design themselves by AI, at least.
In Calc starting a sort from the UI the cell.CurrentRegion to which automaticfally is expanded even includes non-empty rows aboive (headers e.g.) and below (summarizing e.g.) I personally would not like to get it done this way in the background automatically without a prompt for my consent. Of course, I anyway don’t use VBA for sorting.

See also the related topic excel sort VB to LO sort help

#11

@Lupp, this is an Excel VBA, and there is nothing to discuss. The other thing is that it shouldn’t be done in LO Calc. By the way, the Cursor.gotoStartOfUsedArea() method returns really the first cell used, and in Excel Range.UsedArea* always starts with the first cell of sheet (A1). Not the same thing at all! But we go our own way.
Edit:
-----
* Means Range.UsedRange

#12

If any VBA compatibility functions work differently compared to VBA, it is a bug.

1 Like
#13

@mikekaganski, but I’m not at all sure we’re talking about the same thing here. Excel VBA: UsedRange; LO Calc: UsedArea. The getUsedRange() function is often suggested here, but I name it differently: getUsedArea(). The difference is the detection of the starting cell. If we are dealing with the same thing, it’s a bug.
Is it a bug? But I’m fine with it.
Besides, it’s a conceptual thing, and it’s hardly worth breaking.

#14

I am not an expert in VBA, but the above seems confusing. Indeed, the if in the “if any VBA compatibility functions work differently” implies that that needs checking.

#15

@eeigor: I think we are talking about LibreOffice: XSheetCellCursor Interface Reference.
Of course, I don’t know if Excel tzakes the region based on an exactly equivalent “specification” (whether explicit or implicit).
Its funny that a method named collapse... needs to be described starting with expands the cursor ..."

#16

And “areas” are essentially multiple ranges. To solve the problem of confusion, we would have to replace the term “UsedArea” with “UsedRange”, and always count it from the first cell of the sheet.
And declare UsedArea (methods that use the term as part of their names) deprecated.
This is a characteristic feature today. It is not particularly problematic.

#17

IMHO there is no VBA UsedArea, only UsedRange, see Worksheet.UsedRange property (Excel) | Microsoft Docs and there is no UsedArea in the Properties list on the left or Worksheet object (Excel) | Microsoft Docs nor in Range object (Excel) | Microsoft Docs.

In Calc VBA it’s also Worksheet.UsedRange.

#18

That’s a typo. Range.UsedRange. But it doesn’t change the point. The discussion was about how UsedArea works in LO Calc. It’s different from Excel UsedRange.

#19

Basic and VBA:

Sub test()
  Workbooks.Add
  Range("B2") = 1
  Msgbox ActiveSheet.UsedRange.Address
End Sub

Shows: $B$2 in Excel and in Calc (Option VBASupport 1).

#20

Just for @sokol92 : See attached demo.
querqueestions.ods (18.2 KB)

I wouldn’t trust my understanding of terms like “used area” and “current region” and some listed under LibreOffice: com::sun::star::sheet::CellFlags Constant Group Reference e.g.
I also doubt if the code actually is based on a consistent understanding of all that.
(https://bugs.documentfoundation.org/show_bug.cgi?id=137667 e.g.)
The clearContents() method has a comment “unneessary”, but nobody tells for what reasons.

If there can be a reliable reality behind a concept like “exactly as Excxel does” I doubt even more, and there seem to be experienced people seeing reasons in “unstable or missing specifications of Excel”. In fact it may be a miracle that a spreadsheet produces usable results now and then. Unnoticed errors must be the more common thing.

(Yes. I know I shouldn’t post uinder “solved” questions, but I’m a nasty boy.)

Impressum (Legal Info) | Datenschutzerklärung (Privacy Policy)   Content on this site is licensed under a Creative Commons Attribution Share Alike 3.0 license.