Range Processing using Macro in LibreOffice Calc – Part 1

Advertisement

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:

A simple Range

A simple Range

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:

single cell fill using Range

single cell fill using Range

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:

Fill Rows Using Range

Fill Rows Using Range

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:

Fill Columns using Range

Fill Columns using Range

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:

Fill Rows and Columns of Same Size using Range

Fill Rows and Columns of Same Size using Range

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:

Fill Rows and Columns of Different  Size using Range

Fill Rows and Columns of Different Size using Range

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

You may also like...

  • Mons1990

    Hi. Thank you for the article. This is very interesting for me, and I’m looking for Part 2 but i can’t find it. Can you plis link it to me? Thank you in advance.

    • I am yet to post the part 2 of Range series. It would be available by End of this month.

Translate »