How to Handle Cells with LibreOffice Calc Basic

Table of Contents

This article explains how to handle cells with Basic in LibreOffice Calc, a free spreadsheet software. In Excel VBA, you can work with cells like Cell(1, 2).

Environment

The environment used for this tutorial is as follows:

LibreOffice 5.1.4.2
OS: Ubuntu 16.04.1 LTS

Basics

Let’s change the content of cell A1.

Sub Main
Dim document As Object
Dim sheet As Object
document = ThisComponent
sheet = document.Sheets(0)
Dim cell As Object
cell = sheet.getCellByPosition(0, 0)
cell.Formula = "12345"
End Sub

Sub Main

Dim document As Object

Dim sheet As Object

document = ThisComponent

sheet = document.Sheets(0)

Dim cell As Object

cell = sheet.getCellByPosition(0, 0)

cell.Formula = "12345"

End Sub

To access cells, you can use the getCellByPosition method of the sheet. It’s important to note that unlike Excel VBA, the row and column indices start from 0. The order of indices is also different from Excel VBA, with columns first and then rows.

Value Assignment

When assigning values to cells, you need to choose between Value, String, and Formula. In most cases, using Formula is sufficient.

Value

Used for assigning numeric values. If a non-numeric value is assigned, the cell will be set to 0 in the spreadsheet.

cell.Value = "123" ' => 123
cell.Value = "abc" ' => 0

1 2	cell.Value = "123" ' => 123 cell.Value = "abc" ' => 0

String

Used for assigning string values. Even if a numeric value is assigned, the spreadsheet will treat it as a left-aligned string.

cell.String = "abc" ' => abc (String)
cell.String = 2     ' => 2 (String)

1 2	cell.String = "abc" ' => abc (String) cell.String = 2 ' => 2 (String)

Formula

Used for assigning formulas. When assigning strings or numbers, it treats them as strings and numbers, respectively.

cell.Formula = "=A2+A3" ' => A2 + A3 (Formula)
cell.Formula = 123      ' => 123     (Number)
cell.Formula = "abc"    ' => abc     (String)

cell.Formula = "=A2+A3" ' => A2 + A3 (Formula)

cell.Formula = 123 ' => 123 (Number)

cell.Formula = "abc" ' => abc (String)

Value Reading

Just like with assignment, you can choose between Value, String, and Formula when reading values. However, in most cases, you already know in advance what type of value is in the cell (numeric or string), so it’s not difficult.

Value

Used for numeric values. If the cell contains a non-numeric (string-form) value, using Value will return 0.

String

Used for string values. Even if a non-string value (numeric) is in the cell, you can still get it as a string.

Formula

Used to retrieve formulas. When there are simple numeric or string values, you can handle them as numbers or strings, respectively. It’s usually best to treat them as strings.

cell.Value = 2
Dim s As String
s = cell.Formula
MsgBox s + 5 ' => 25
Dim n As Integer
n = cell.Formula
MsgBox n + 5 ' => 7
cell.Formula = "=A2+A3"
MsgBox cell.Formula ' => =A2+A3

cell.Value = 2

Dim s As String

s = cell.Formula

MsgBox s + 5 ' => 25

Dim n As Integer

n = cell.Formula

MsgBox n + 5 ' => 7

cell.Formula = "=A2+A3"

MsgBox cell.Formula ' => =A2+A3

Calculation Example

Imagine a table with scores for Japanese and Math as shown below. Here, we will calculate the average scores for Japanese and Math. In general, you would use a formula to calculate averages in such cases. Here, we use Basic code as an example.

Score Data

	A	B	C	D
1	Name	Math	Japanese	Average
2	Shinichi Iida	44	75
3	Kohei Yoneda	73	59
4	Hisashi Egawa	85	29
5	Ichiro Aoyama	12	30
6	Makoto Miura	55	78
7	Kazuo Kato	97	42

Sub Main
Dim document As Object
Dim sheet As Object
document = ThisComponent
sheet = document.Sheets(0)
Dim rowIndex As Integer
For rowIndex = 0 To 10
Dim arithmetic As Double, language As Double
arithmetic = sheet.getCellByPosition(1, rowIndex).Value
language = sheet.getCellByPosition(2, rowIndex).Value
sheet.getCellByPosition(3, rowIndex).Value = (arithmetic + language) / 2
Next rowIndex
End Sub