Range Processing using Macro in LibreOffice Calc – Part 1

In many automation tasks we need to access spreadsheet using range to reduce processing time. One can access each cells using “row, column” way which is expensive in terms of processing time and complexity. Thus Range is sometimes preferred on top of the individual cell processing.

In this two part tutorial series I will talk about how to process “Range” in LibreOffice (LO) Calc spreadsheet application.

Part 1 would consists of writing in spreadsheet ranges.

Define a Range

A range is typically defined using “start_cell_address:stop_cell_address”. In Calc all columns are identified using letters “A”, “B”, “C”, …. and all rows are numbers starting from 1 and so on.

So a typical range – “A1:D5” refers all cells starting from (1,1) to (4,5). See below:

Create a Macro

Create a new macro in LibreOffice Calc. Add a function say rang_processing_demo() with below lines which would provide a hold of the spreadsheet before we start processing range.
If you need guidance on how to create a basic macro – click here.

Sub range_processing_demo
      dim my_doc   as object
      Dim my_sheets as object
      Dim my_range as object
      my_doc = ThisComponent
      my_sheets = my_doc.Sheets 
End Sub

We will start with a single cell fill-up using range, then go on to more complex range processing.

Set a single cell using range

Ranges can be set using a function setDataArray(array). The argument of this function is a 2D array of same size of the range.

If you choose a range “A1” which is nothing but a single cell, then the correcponsing array should have only value. Thus define an array with one element and set its value.

Dim my_data(0,0) 			
my_data(0,0) = "Apple"

Now, define a range of 1 cell i.e. “A1” and put the defined array inside setDataArray() function.

my_range = ThisComponent.Sheets(0).getCellRangebyName("A1")
my_range.setDataArray(my_data)

If you run the above sets of code, the result would be:

Set a row of cells using range

Now, get on with more complex range processing. We want to fill a row of cells using range. We want to fill the cells from A3 to C3. For this, we need to define an array with (0,2) where 0=row and 2=column. Note that columns starts with 0 thus 0 to 2 is 3 columns that we want to fill.

' Set a row of cells using range
	ReDim my_data(0,2) 			
	my_data(0,0) = "Apple"
	my_data(0,1) = "1000"
	my_data(0,2) = "Red"
	my_range = ThisComponent.Sheets(0).getCellRangebyName("A3:C3")
	my_range.setDataArray(my_data)	

The result of above code:

Set a columns of cells using range

On the opposite side, we want to fill 1 single column with multiple rows. Define an array with (2,0) and have this code in place.

' Set a column of cells using range
	ReDim my_data(2,0) 			
	my_data(0,0) = "Apple"
	my_data(1,0) = "1000"
	my_data(2,0) = "Red"
	my_range = ThisComponent.Sheets(0).getCellRangebyName("C1:C3")
	my_range.setDataArray(my_data)	

The result:

Set a row and column of cells of same size using range

Lets fill both row and column ranges using same size. Similar to above examples, define an array with size (2,2).

' Set row and column of same size using range
	ReDim my_data(2,2) 			
        my_data(0,0) = "Apple"
	my_data(0,1) = "1000"
	my_data(0,2) = "Red"
	
        my_data(1,0) = "Apple-1"
	my_data(1,1) = "1000-1"
	my_data(1,2) = "Red-1"

        my_data(2,0) = "Apple-2"
	my_data(2,1) = "1000-2"
	my_data(2,2) = "Red-2"
		
	my_range = ThisComponent.Sheets(0).getCellRangebyName("B2:D4")
	my_range.setDataArray(my_data)	

Output:

Set a row and column of cells of different size using range

To fill the range of different size of column and row, define an array of different size.

' Set row and column of different size using range
	ReDim my_data(3,1) 			
        my_data(0,0) = "Apple"
	my_data(0,1) = "1000"
	
        my_data(1,0) = "Apple-1"
	my_data(1,1) = "1000-1"

        my_data(2,0) = "Apple-2"
	my_data(2,1) = "1000-2"
	
        my_data(3,0) = "Apple-3"
	my_data(3,1) = "1000-3"
		
	my_range = ThisComponent.Sheets(0).getCellRangebyName("B2:C5")
	my_range.setDataArray(my_data)

Output:

Conclusion:

Define your array size using (row, col) and use setDataArray () function to fill out the range using macro basic.

Looking for Something Else?

If you are looking for something else in LibreOffice macro tutorials, Or, wants to learn more about it, please follow below link for complete Macro Tutorials Index:

LibreOffice Macro Tutorial Index