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
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
…
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.
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
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.
Ah that sheds some light and makes sense. Thanks!
I submitted tdf#149579 for this.
And implemented for 7.4.0.0.beta2
1 Like
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
@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
If any VBA compatibility functions work differently compared to VBA, it is a bug.
1 Like
@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.
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.
@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 ..."
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.
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.
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.
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).
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.)
@Lupp, thanks for the interesting question!
As far as I know, the Worksheet.UsedRange range in the .xlsx archive structure is reflected in the <dimension tag ref attribute of the sheetN.xml file. If your querqueestions.ods file is saved as .xlsm format, we will see
<dimension ref="B4:J25"/>
Cell B4 is included in this range.
The dimension specification (for the format .xls) is here. It specifies which cells are included in the UsedRange (Excel).
My Opinion: the result of running macro
Option VbaSupport 1
Sub Test
Msgbox Worksheets(1).UsedRange.Address
End Sub
for your file should be $B$4:$J$25 (now $B$5:$J$25).