Table of contents

libreoffice calc

application

shortcut keys

Ctrl *   select current data block

adding a shortcut key for insert row

select a shortcut key then:
tools > customize > insert > insert row above > modify

conditional formatting based on cell values (A1 is first cell in selection)

Formula is: A1>$G$198

macros

creating a library of macros

<alt-F11> or
tools > macros > organize macros > basic > Organizer...

opening macro IDE

<alt-F11> or
tools > macros > organize macros > basic > My Macros > [library] > [module]

hello world macro

Sub HelloMacro
Print "Hello"
End Sub

assigning a macro to a shortcut key

select a shortcut key
tools > customize > category > LibreOffice Macros > ... > module > Function > Modify

macros howto

https://books.libreoffice.org/en/GS70/GS7013-GettingStartedWithMacros.html

libreoffice VB

language

comments

rem [comment]
' [comment]

strings

"this is a string"

appending string

"appending" & " three " & "strings"

print a message

Print "[message]"

variables

dim myvar as integer
dim myvar as string
dim myvar as date
dim myvar as object

if-else

If [condition] Then
	statements
{ElseIf|Else If} [expression] Then
	[statement]...
Else
	[statement]...
{EndIf|End If}

subroutine

sub [name]
end sub

dom

get selected cell (if only one cell selected)

cell = ThisComponent.getCurrentSelection()
print oCell.AbsoluteName
print cell.value

incrementing a cell value

cell = ThisComponent.getCurrentSelection()
cell.value = cell.value + 1

get the type of content of a cell (1=number, 2=text)

cct = ThisComponent.getCurrentSelection().CellContentType
If cct = 1 Then
	Print "the cell contains a number"
ElseIf cct = 2 Then
	Print "the cell contains text"
Else
	Print "the cell contains another data format: " & cct
End If

iterate all selected cells

Sub IncrementRange(range As Object, increment As Integer)
    For row = 0 To range.Rows.Count - 1
        For col = 0 To range.Columns.Count - 1
            cell = range.getCellByPosition(col, row)
            cell.value = cell.Value + increment
        Next col
    Next row
End Sub

Sub AddToSelection(increment As Integer)
	selection = ThisComponent.CurrentSelection
	If selection.SupportsService("com.sun.star.sheet.SheetCellRanges") Then
        For range = 0 To selection.Count - 1
            IncrementRange(selection.getByIndex(range), increment)
        Next range
    ElseIf selection.SupportsService("com.sun.star.sheet.SheetCellRange") Then
        IncrementRange(selection, increment)
    Else
        MsgBox "Please select a range of cells."
    End If
End Sub

Sub IncrementSelectedCells
	AddToSelection(1)
End Sub

Sub DecrementSelectedCells
	AddToSelection(-1)
End Sub