How to Handle Cells with LibreOffice Calc Basic


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.

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.
String
Used for assigning string values. Even if a numeric value is assigned, the spreadsheet will treat it as a left-aligned string.
Formula
Used for assigning formulas. When assigning strings or numbers, it treats them as strings and numbers, respectively.

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.

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