View source for 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
<code>Tools > Options > Security > Macro Security > Medium</code>



= GUI =
<blockquote>

== Dropdown Lists ==
<blockquote>
Dropdown lists are a nice way of enforcing consistency.
Select your cell, and click:
* <code>Data > Validity > List</code>
Enter the options that you would like

</blockquote><!--libreoffice calc dropdown list -->

== Buttons ==
<blockquote>
<code>View > Toolbars > Form Controls</code>

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


<syntaxhighlight lang="freebasic">
' Recalculate All Macros
'------------------------
Sub ReCalculate()
   ThisComponent.calculateAll()
End Sub
</syntaxhighlight>

Then Create a Button that executes that Macro when pushed.


</blockquote><!--libre office buttons-->
</blockquote><!--libre office gui-->

= Anatomy of LibreOfficeBasic =
<blockquote>
Open the Macro IDE {{{Tools > Macros > Organize Macros > LibreOffice Basic}}}


== Universal Macros ==
<blockquote>
You can create generic MacroModules under 
<code>My Macros & Dialogs > Standard > Module1</code>
</blockquote><!-- Universal Macros -->


== Embedded Macros ==
<blockquote>
You can also create Macro Modules embedded in your spreadsheet. They will be listed under 
<code>SpreadsheetTitle.ods > Standard > YourCustomModuleName </code>

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:
<code>Tools > Options > Security > Macro Security > Medium</code>

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:
<syntaxhighlight lang="freebasic">
Tools > Macros > Organize Macros > LibreOffice Basic
Click on SpreadsheetTitle.ods
Click on New
</syntaxhighlight>
</blockquote><!--Embedded Macros-->


== Hello World ==
<blockquote>
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:

<syntaxhighlight lang="freebasic">
Function helloWorld(someVar) as string
   helloWorld = "Hello World!"
EndFunction
</syntaxhighlight>

From this point forwards, in any cell you can call the function with:
<code>=helloWorld()</code>
Function names are not case-sensitive.
</blockquote><!-- hello world -->



== Alternative Hello World (print) ==
<blockquote>
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.
<syntaxhighlight lang="freebasic">
print "hello world!" 
</syntaxhighlight>

The result is a messagebox with your information
</blockquote><!-- alternative hello world-->

</blockquote><!-- first script -->


= Syntax =
<blockquote>
== Functions ==
----
<blockquote>
<syntaxhighlight lang="freebasic">
### 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)


</syntaxhighlight>
</blockquote><!--Functions-->


== Variables ==
<blockquote>
Variable Scope:
<syntaxhighlight lang="freebasic">
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
</syntaxhighlight>



Variable Types:
<syntaxhighlight lang="freebasic">
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 ?
</syntaxhighlight>
</blockquote><!--Variables-->


== Arrays ==
<blockquote>
<syntaxhighlight lang="freebasic">
' 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
</syntaxhighlight>
</blockquote><!--Arrays-->


== Conditionals ==
<blockquote>
<syntaxhighlight lang="freebasic">
If someVar = 1 then
   helloWorld = "Hello World"
ElseIf someVar = 0 then
   helloWorld = "Forget about it"
Else
   helloWorld = "Supersize!!!"
End If


</syntaxhighlight>
</blockquote><!-- conditionals -->


== Reading and Writing Cells ==
<blockquote>
=== 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.

<syntaxhighlight lang="freebasic">
value          --   a number
string         --   a string
formula        --   would typically begin with an '=' sign
</syntaxhighlight>

=== Query Selected Cell ===
<blockquote>
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>
</blockquote><!--query current cell -->


=== Writing To Cells ===
<blockquote>
You can edit as many cells as you like at a time from a Macro.
<syntaxhighlight lang="freebasic">
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
</syntaxhighlight>
</blockquote><!--writing to cells-->


=== Querying Namned Cells ===
<blockquote>
<syntaxhighlight lang="freebasic">
' 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

</syntaxhighlight>
</blockquote><!-- querying named cells -->
</blockquote><!-- reading and writing from cells -->
</blockquote><!-- syntax -->