Calc

Libre Office is extendable using Macros. The terminology is confusing because Macros seem to encompass both scripting in Calc, but also recording a sequence of events similar to Photoshop's actions. I don't care about the second, but the first is very useful.

Macro Modules can be written in LibreOffice freebasic, and Python among other languages.

NOTE If you are embedding macros in a document, you must enable them in Tools > Options > Security > Macro Security > Medium

GUI

Dropdown Lists

Dropdown lists are a nice way of enforcing consistency. Select your cell, and click:

• Data > Validity > List

Enter the options that you would like

Buttons

View > Toolbars > Form Controls

To create a button that refreshes all calculations (and macros), in the macro editor, define:

' Recalculate All Macros
'------------------------
Sub ReCalculate()
   ThisComponent.calculateAll()
End Sub

Then Create a Button that executes that Macro when pushed.

Anatomy of LibreOfficeBasic

Open the Macro IDE {{{Tools > Macros > Organize Macros > LibreOffice Basic}}}

Universal Macros

You can create generic MacroModules under My Macros & Dialogs > Standard > Module1

Embedded Macros

You can also create Macro Modules embedded in your spreadsheet. They will be listed under SpreadsheetTitle.ods > Standard > YourCustomModuleName

Note that you will have to enable Macros so that your embedded Macros work. If this hasn't been done before you create an Embedded Macro, you will have to restart LibreOffice Calc before the Functions can be used in cells.

To Safely Enable Macros, I recommend: Tools > Options > Security > Macro Security > Medium

If you modify a macro, you will have to retype the equasion for it to be reloaded. Simply entering edit mode, and hitting enter again doesn't seem to do it.

To create an embedded Module, From the Macro Editor:

Tools > Macros > Organize Macros > LibreOffice Basic
Click on SpreadsheetTitle.ods
Click on New

Hello World

Once you are in the Macro Editor with a module open, you can start declaring functions. Using a function from the Macro Editor is simple. Once it is declared you can call it in the spreadsheet with {{{=myFunctionName()}}}. To get you started, here is an example 'Hello World' function:

Function helloWorld(someVar) as string
   helloWorld = "Hello World!"
EndFunction

From this point forwards, in any cell you can call the function with: =helloWorld() Function names are not case-sensitive.

Alternative Hello World (print)

The function above writes the return value to your cell. Sometimes if you aren't sure of the datatype, or are trying to diagnose where your script is breaking you can use the print function.

print "hello world!"

The result is a messagebox with your information

Syntax

Functions

---

### function with return value, with forced return value type "string"
##----------------------------------------
Function functionName(inputA, inputB) as string
   REM   using the function name as a variable specifies the return value.
   functionName = "Hello World!"
End Function

###  CALL WITH: functionName(1)




### function with return value, conditional
##----------------------------------------
Function helloWorld(someVar) as string
	If someVar = 1 then
		helloWorld = "Hello World!"
	ElseIf someVar = 0 then
		helloWorld = "never mind then"
	Else
		helloWorld = "hell yes"
	End If 
End Function
### CALL WITH: helloWorld(1)




### function with array passed to it:
##----------------------------------------
Function testArrayA(arrayRange)
	total = 0
	i = 1
	For each i in arrayRange
		total = total + i
	next i
	testarray = total
End Function
### CALL WITH: =testArray(E1:E22)




### function with array passed to it and used in scope
##----------------------------------------
Function testArrayB( arrayRange )
	count = 0
	dim array(10) as integer
	for each i in arrayRange
		 array(count) = i
		 count = count + 1
	next i
	testMatrix = array(2)
End Function
### CALL WITH: =testArray(E1:E22)

Variables

Variable Scope:

REM  Variable Types
global varName as integer             REM valid everywhere until libreOffice closed
public varName as integer             REM valid across all modules

private varName as integer            REM local scope
dim varName as integer                REM local scope

Variable Types:

dim varName as integer               REM 2
dim varName as single                REM (float) 3.176
dim varName as double                REM (float) 
dim varName as string                REM 'blahblah'
dim varname as boolean               REM 'True' or 'False'

dim varName as long                  REM long integers. From -2147483648 to 2147483647
dim varName as date                  REM ?

Arrays

' Arrays in freebasic are like arrays in C, their size is not dynamically
' allocated, so you must do that yourself.

ubound(list)                        ' get array length


'expanding arrays'
'-----------------
' expand array	
dim list(10) as single
dim ibuffer as integer

ibuffer = ubound(list)                           ' get current size of array
ReDim preserve list( ibuffer +1 ) as single      ' use ibuffer to expand list. (cannot use 
                                                 ' ubound list while redeclaring it. bummer)

                                                 ' preserve means to keep previous values while 
                                                 ' redeclaring var


' Ex: grab cell range, convert to array
' --------------------------------------
Function rangeToArray() 
  
  'convert listRange to list()
  count = 0
  for each i in listRange
    redim preserve list(count)
    list(count) = i
    count = count + 1
  next i

End Function

Conditionals

If someVar = 1 then
   helloWorld = "Hello World"
ElseIf someVar = 0 then
   helloWorld = "Forget about it"
Else
   helloWorld = "Supersize!!!"
End If

Reading and Writing Cells

Cell Types

Cells can be of 3 different types. When reading or especially when writing it is important that you specify the type of data you wish to enter.

value          --   a number
string         --   a string
formula        --   would typically begin with an '=' sign

Query Selected Cell

Note that this is entirely based on the selected cell. If you refresh macros and your cursor is not overtop of your desired cell it will not behave as expected!!!! </syntaxhighlight lang="freebasic"> Function getCell() print ThisComponent.getCurrentSelection.CellAddress.Sheet print ThisComponent.getCurrentSelection.CellAddress.Column print ThisComponent.getcurrentSelection.CellAddress.Row End Function </syntaxhighlight>

Writing To Cells

You can edit as many cells as you like at a time from a Macro.

dim sheet as object, cell as object

Function writeToCell()
  sheet=thisComponent.sheets(1)       ##indicates sheet number you are writing to (begins at 0)
  cell=sheet.getCellByPosition(3,28) ##specify a cell. A=0, so A1=0,0. Easier to work with numbers than letters
  cell.string = "yes"
    -or-
  cell.value = 100
    -or-
  cell.formula = "sum(100/2)"
End Function

Querying Namned Cells

' Functions to pull values from named cell
' https://forum.openoffice.org/en/forum/viewtopic.php?f=20&t=17204
' =getValue("namedCell")
' Returns the value from a named single cell
Function getValue(namedValue As String)
  getValue = getRange(namedValue)(1, 1).value
End Function

' Returns a named range of cells
Function getRange(namedRange As String)
  getRange = StarDesktop.CurrentComponent.NamedRanges.getByName(namedRange).getReferredCells()
End Function