this macro worked with libreoffice 6.x and earlier, but with the new version it no longer works when I try to intercept merged cells.
(with LO 7.x it only works when it comes to intercepting single cells)
sub CellPrint
oSheet = ThisComponent.CurrentController.ActiveSheet
oActiveCell = ThisComponent.getCurrentSelection()
testo = oActiveCell.string
print testo
end sub
Can you help me?
A thousand thanks
You are asking about “BASIC runtime error. Property or method not found: string.”?
Try
testo = oActiveCell.getCellByPosition(0,0).getString()
Editing:_ Need to admit that my tests with old versions obviously weren’t done thoroughgly enough. The statement below is correct nonetheless, but it misses the point. Therefore the strikeout
To ask a selected range of more than one cell for its .String property is an erreor.
The property simply does not exist. To be sure that Basic reported an error in such a case also in older versions I just tested again with V 6.2.5.2 and also with V 4.2.8.
This works when the current selection happens to be a single cell. This has always been so in StarOffice, OpenOffice.org, Apache OpenOffice and all versions of LibreOffice.
This is not a “supposed”, this is a real different behavior. As noted, it worked in some versions, then stopped to work. And that is a regression, and is a bug (unless there’s a reason for the change, which in this case is an API change - something that would need good reasons to happen).
And this worked in 7.0, while doesn’t starting from 7.1 - namely, as a result of this commit. The discussion here, especially those posts trying to convince that this is OK, had diluted and distracted from the most important thing that a user can contribute to the project - the thing mentioned in my first comment: “Just file a bug report”. Workarounds are nice, and may allow you to have your job done - but without being responsible and filing bugs, there’s no way to improve things.
I write StarBasic macros since 20 years. The snippet never worked with any selection other than a single cell. I saved the code in a spreadsheet and tested it with AOO4.1, LO 6.0 and 7.2. A single cell has pseudo-property “String” which is method getString() actually. Any other spreadsheet selection has no string property. What should be the string of a cell range, let alone multiple cell ranges?
Just to make sure, I tested with LO 3.3.0.4, AOO 4.1.0, and with OOo 1.0.3.
The snippet cited in the question worked fine on merged cells in all those.
Here is OOo 3.2.0 for your enjoyment.
You are right. I hadn’t checked thoroughly enough, and will remove the insertion now again.
You may also have noted, the also the UI (NameBox) shows the range of the MergedArea for the selected cell now.
Assuming A1:B1 is merged, the namebox will show A1:B1 independent of whether you selected the area with the mouse, or entered either A1 or B1.
Alright. It is also indicated by highlighted row/column headers. LO 7 selects the merged area whereas old versions select only the first cell.
Thanks - done at tdf#147122.
Basic code for Calc coming with an object named oActiveCell or similar should be suspected to be part of an attempt to get copied and pasted code written in Excel VBA to work.
Actually Excel VBA has such a (function? predefined variable? something?) available for any “workbook”. What a Calc user will get from ThisComponent.CurrentSelection will return the cell the Excel programmer had in mind in many cases, but also often something else. Even the precautions suggeseted by @JohnSUN will not always get the cell currently having the keyboard focus what is what ActiveCell means in VBA.
LibreOffice Basic used for a Calc document has no simple way to access the mentioned cell.
The simplest way to get it I knoiw of goes back to times when a user “uros” posted elsewhere, and @Villeroy preserved his hints. Try:
currCtrl = ThisComponent.CurrentController
currVD = currCtrl.ViewData
currSheet = currCtrl.ActiveSheet
vDSplit = Split(currVD, ";")
currSNum = currSheet.RangeAddress.Sheet
sInfo = vDSplit(currSNum + 3)
REM For CellAddress.Row >= 8192 the "+" is used as the subdelimiter in ViewData. WHY?
If InStr(sInfo, "+")>0 Then
sInfoDelim = "+"
Else
sInfoDelim = "/"
End If
sInfoSplit = Split(sInfo, sInfoDelim)
currFocusCell = currSheet.GetCellByPosition(sInfoSplit(0), sInfoSplit(1))
To re-nestle into VBA, you may name the variable ActiveCell instead of using the more speaking currFocusCell I suggested above…
Recent LibO Basic under Option VBAsupport 1 has the ActiveCell thing, too, but the usage of that option may well also come with disadvantages. As always: Be careful! Careless programming is a way to hell.
@moe_ahpitsch The function ActiveCell() code usable in Basic is here.
With this function, your test code could look like this:
Sub CellPrint
oActiveCell = ActiveCell()
testo = oActiveCell.getString()
Print testo
End Sub
I tested your macro on old laptop in 5.4.7.2 and it is functional. The cell merging is improved but I don’t know from what version, maybe it is the cause of the regression bug.
You can add the .IsMerged condition to the macro:
sub CellPrint
oSheet = ThisComponent.CurrentController.ActiveSheet
oActiveCell = ThisComponent.getCurrentSelection()
if oActiveCell.IsMerged then 'test if the cells are merged
msgbox oActiveCell.DataArray(0)(0)
end if
end sub
This is the same as
Print ThisComponent.CurrentSelection.getCellByPosition(0,0).getString()
It has nothing to do with any cell being merged or not.