Macros/ScriptForge/HowToDeriveNewCellsFromCalcRangeQuickWin
Appearance
< Macros | ScriptForge
TDF LibreOffice Document Liberation Project Community Blogs Weblate Nextcloud Redmine Ask LibreOffice Donate
How to derive new cell ranges from a Calc range
Authored by Jean-Pierre Ledure.
The loading of the ScriptForge Basic library is present for completeness. However it can be done elsewhere. In Basic, all used variables are declared explicitly. The concerned code is presented inside a Basic Sub
or a Python def
.
Run that piece of code and consider the result.

How to run it in BASIC
Create a new document.
- Open the Basic IDE
- Select the first available blank module
- Copy and paste the Basic code
- Verify line 14
- Run Main()
How to run it in Python
Create a new document.
- Run APSO
- Create a new module, call it 'Module1'
- Copy and paste the Python code below
- Verify line 15
- Save and run the Main() method
Code
REM How to derive new ranges from a Calc range ?
REM Minimal required version: LibreOffice 7.6
REM Used service(s) Calc
Sub computeranges()
Dim ui As Object
Dim calc As Object
Dim range As String
Dim rangeheight As Long, rangewidth As Long
Dim toprow As String, bottomrow As String, leftcol As String, rightcol As String
Dim shiftrange As String
Dim newline As String : newline = Chr(10)
GlobalScope.BasicLibraries.loadLibrary("ScriptForge")
Set ui = CreateScriptService("UI")
Set calc = ui.CreateDocument("Calc")
With calc
range = .OpenRangeSelector(title := "Select an initial range", _
singlecell := False, _
closeafterselect := True)
If Len(range) > 0 Then
.CurrentSelection = range ' Really select the chosen range
rangeheight = .Height(range)
rangewidth = .Width(range)
' https://help.libreoffice.org/latest/en-US/text/sbasic/shared/03/sf_calc.html?&DbPAR=BASIC#Offset
topleft = .FirstCell(range)
bottomright = .LastCell(range)
toprow = .Offset(range, height := 1)
bottomrow = .Offset(range, rows := rangeheight - 1, height := 1)
leftcol = .Offset(range, width := 1)
rightcol = .Offset(range, columns := rangewidth - 1, width := 1)
shiftrange = .Offset(range, columns := 1)
MsgBox "Initial range = " & range & newline & newline _
& "Dimensions = " & rangeheight & " rows x " & rangewidth & " columns" & newline _
& "Top-left cell = " & topleft & newline _
& "Bottom-right cell = " & bottomright & newline _
& "Top row = " & toprow & newline _
& "Bottom row = " & bottomrow & newline _
& "Left column = " & leftcol & newline _
& "Right column = " & rightcol & newline _
& "Range shifted by 1 column = " & shiftrange & newline _
, title := "Ranges ..."
End If
End With
' Housekeeping
Set calc = calc.Dispose()
End Sub
# coding: utf-8
from __future__ import unicode_literals
### How to derive new ranges from a Calc range ?
### Minimal required version: LibreOffice 7.6
### Used service(s) Calc
from scriptforge import CreateScriptService
def computeranges():
newline = '\n'
ui = CreateScriptService('UI')
basic = CreateScriptService('Basic')
calc = ui.CreateDocument('Calc')
range = calc.OpenRangeSelector(title = 'Select an initial range',
singlecell = False,
closeafterselect = True)
if len(range) > 0:
calc.CurrentSelection = range # Really select the chosen range
rangeheight = calc.Height(range)
rangewidth = calc.Width(range)
# https://help.libreoffice.org/latest/en-US/text/sbasic/shared/03/sf_calc.html?+DbPAR=BASIC#Offset
topleft = calc.FirstCell(range)
bottomright = calc.LastCell(range)
toprow = calc.Offset(range, height = 1)
bottomrow = calc.Offset(range, rows = rangeheight - 1, height = 1)
leftcol = calc.Offset(range, width = 1)
rightcol = calc.Offset(range, columns = rangewidth - 1, width = 1)
shiftrange = calc.Offset(range, columns = 1)
basic.MsgBox('Initial range = ' + range + newline + newline
+ 'Dimensions = ' + str(rangeheight) + ' rows x ' + str(rangewidth) + ' columns'+ newline
+ 'Top-left cell = ' + topleft + newline
+ 'Bottom-right cell = ' + bottomright + newline
+ 'Top row = ' + toprow + newline
+ 'Bottom row = ' + bottomrow + newline
+ 'Left column = ' + leftcol + newline
+ 'Right column = ' + rightcol + newline
+ 'Range shifted by 1 column = ' + shiftrange + newline,
title = 'Ranges ...')
# Housekeeping
calc = calc.Dispose()
g_exportedScripts = (, )
if __name__ == "__main__":
computeranges()