Need guidance on Delete Shift Left/Up macros

[mstone42]

I have just recently switched from Micro$oft Office to LibreOffice, version 7.6.5.2, running in Ubuntu MATE 22.04. I’ve created a custom toolbar in Calc with the commands I use most often. There are two such features which I cannot seem to implement: “Delete Shift Up” and “Delete Shift Left”. The corresponding “Insert Shift Down” and “Insert Shift Right” commands DO exist, so I’m baffled as to why the Deletes have to rely an on extra step of the dialog box?!? I achieved this quite easily in Excel with two very simple macros which I assigned to toolbar buttons:

Sub DeleteShiftUp()
	Selection.Delete Shift:=xlUp
End Sub

Sub DeleteShiftLeft()
	Selection.Delete Shift:=xlToLeft
End Sub

Needless to say, these macros do not work in LO. I’ve tried recording a macro to capture the “Sheet > Delete Cells…” command and selecting the appropriate radio button. But this is what gets recorded…?!?

sub DeleteShiftLeft
rem ----------------------------------------------------------------------
rem define variables
dim document   as object
dim dispatcher as object
rem ----------------------------------------------------------------------
rem get access to the document
document   = ThisComponent.CurrentController.Frame
dispatcher = createUnoService("com.sun.star.frame.DispatchHelper")

rem ----------------------------------------------------------------------
rem dispatcher.executeDispatch(document, ".uno:DeleteCell", "", 0, Array())

rem ----------------------------------------------------------------------
dispatcher.executeDispatch(document, ".uno:InsertCell", "", 0, Array())


end sub

I found a couple of functions in the included SFDocuments macros (ShiftLeft and ShiftUp) but they’re way overkill for what I’m needing (I had forgotten how verbose Basic can be!) and I don’t yet know enough about how LO uses Basic to convert it to a Sub. Can anyone provide some help on writing these two macros? What am I missing?

[karolus]

@mstone42

Sub DeleteShiftUp()
	Selection.Delete Shift:=xlUp
End Sub

Sub DeleteShiftLeft()
	Selection.Delete Shift:=xlToLeft
End Sub

Try again with:

option VBASupport 1

Sub DeleteShiftUp()
	Selection.Delete Shift:=xlUp
End Sub

Sub DeleteShiftLeft()
	Selection.Delete Shift:=xlToLeft
End Sub

[mstone42]

Thanks, karolus, that was one of the first things I tried.
The error that pops up is:

BASIC runtime error.
'91'
Object variable not set.

Which suggests it doesn’t know what “Selection” is. I’ve tried replacing “Selection” with “ThisComponent.CurrentSelection”, but then I get the error:

BASIC runtime error.
'423'
Property or method not found: Delete.

I’ve stepped thru and looked at the CurSel object in the Watch, but it only shows properties, no methods. I’m still searching for an object library browser…

[sokol92]

When you select a rectangular range of cells, shift-up deletion can be done using the following macro.

Sub DeleteSelectionShiftUp()
  Dim oSel As Object
  oSel=ThisComponent.CurrentSelection 
  If HasUnoInterfaces(oSel, "com.sun.star.sheet.XSheetCellRange") Then
    oSel.Spreadsheet.removeRange oSel.RangeAddress, com.sun.star.sheet.CellDeleteMode.UP
  End If    
End Sub

Possible values for the shift are described in the enumeration CellDeleteMode.

[mstone42]

Thank you, sokol92! Perfect!