Open Office BASIC For Spreadsheets

This is a collection of useful BASIC functions/subs for Open Office Spreadsheets.
I have assembled the collection in small functions/subs with comments describing what the procedures do.
For more information see also: Open Office BASIC Programming Guide

Testing, accessing a spreadsheet document.

'------------------------------------------------------------------
' general utilities
'------------------------------------------------------------------
' check if document is a calc document
' returns: false, 	if no spreadsheet document
'	         true,	else
Function isSpreadhsheet( oDoc ) As Boolean
  On Local Error GoTo isSpreadhsheetException
  isSpreadhsheet = oDoc.SupportsService(_
    "com.sun.star.sheet.SpreadsheetDocument")

  isSpreadhsheetException:
  If Err <> 0 Then
     isSpreadhsheet = False
    Resume isSpreadhsheetExceptionResume
    isSpreadhsheetExceptionResume:
  End If
End Function

'------------------------------------------------------------------
' get the workbook
Function getWorkbook(  ) 
  getWorkbook = ThisComponent
End Function

Accessing a sheet.

'------------------------------------------------------------------
' get the sheet object by sheet name
'    oDoc, the workbook
'    name, the name of the sheet
Function getSheetByName( oDoc, name ) 
  if name <> "" then
    getSheetByName = oDoc.Sheets.getByName( name )
  end if  
End Function
'------------------------------------------------------------------
' gets a sheet object by index
'   oDoc, the workbook
'   index, the index of the sheet
Function getSheetByIndex( oDoc, index ) 
  getSheetByIndex = oDoc.Sheets.getByIndex( index )
End Function

Accessing a range of cells or a single cell.

'------------------------------------------------------------------
' get the cell range of sheet oSheet from column c1 to c2 and 
'   row r1 to r2
Function getRange( oSheet, c1,r1,c2,r2 ) 
  getRange = oSheet.getCellRangeByPosition( c1,r1,c2,r2)
End Function
'------------------------------------------------------------------
' get the cell range or a single cell of sheet oSheet by name 
'   e.g. A7 or A7:B11
Function getRangeByName( oSheet, name ) 
  getRangeByName = oSheet.getCellRangeByName( name )
End Function
'------------------------------------------------------------------
' get the cell range of workbook oDoc defined by name
Function getNamedRange( oDoc, name ) 
  getNamedRange = oDoc.NamedRanges.getByName(name).getReferredCells()
End Function
'------------------------------------------------------------------
' get the cell of oSheet from column c1 and row r1
Function getCell( oSheet, c1,r1 ) 
  getCell = oSheet.getCellByPosition( c1,r1 )
End Function

Get the address of a cell or range or named range

'------------------------------------------------------------------
' get the absolute address as string of the given simple range rng
Function getRangeName( rng ) as string
  getRangeName = rng.AbsoluteName
End Function
'------------------------------------------------------------------
' get the  range address of range rng as a string without the 
' sheet name
Function getRangeNameRel( rng ) as string
  dim str as string

  str = getRangeName( rng )
  pos = instr(str,".")+1
  str = mid(str, pos, len(str)-pos+1)
  getRangeNameRel = str
End Function

Get the current selection, select a range of cells or a single cell.

'-------------------------------------------------------------------
' returns the current selection of the workbook
sub getCurrentSelection()
  getCurrentSelection = ThisComponent.CurrentSelection
end sub
'-------------------------------------------------------------------
' selects the given range
sub selectRng( rng )
  ThisComponent.CurrentController.Select( rng )
end sub
'-------------------------------------------------------------------
' selects the range with rows and columns c1,r1,c2,r2 of sheet oSheet
sub selectRange( oSheet, c1,r1,c2,r2 )
  selectRng( oSheet.getCellRangeByPosition(c1,r1,c2,r2) )
end sub
'-------------------------------------------------------------------
' selects the cell with row r and column c of sheet oSheet
sub selectCell(oSheet, c, r)
  ThisComponent.CurrentController.Select(oSheet.getCellByposition(c,r))
end sub

Get a cell value, string, formula.

' get the value of cell
Function getVal( cell ) 
  getVal = cell.getValue
End Function
' get the string of cell
Function getString( cell ) 
  getString = cell.getString
End Function
' get the formula of cell
Function getFormula( cell ) 
  getFormula = cell.getFormula
End Function

Set a cell value, string, formula.

' set the value of cell
sub setVal( cell, val ) 
  cell.setValue( val )
End sub
' set the string of cell
sub setString( cell, val ) 
  cell.setString( val )
End sub
' set the formula of cell
sub setFormula( cell, val ) 
  cell.setFormula( val )
End sub
' set range to local current format
sub setLocalCurrency( rng ) 
  Dim aLocale As New com.sun.star.lang.Locale
  rng.NumberFormat = oFormats.getStandardFormat(_
		com.sun.star.util.NumberFormat.CURRENCY, aLocale)		
End sub

Getting areas of not empty cells.

'------------------------------------------------------------------
' returns the column for not empty cells starting from nCol, nRow
'  going left
Function getFilledCol( oSheet, nCol, nRow )
  dim cell as object

  x = nCol
  cell = oSheet.getCellByPosition(x,nRow)
  while  cell.getType() <> com.sun.star.table.CellContentType.EMPTY
    x = x+1
    cell = oSheet.getCellByPosition(x,nRow)
  wend
      
  getFilledCol = x-1
End Function

' returns the row for the area of not empty cells 
'   starting from nCol, nRow going down
Function getFilledRow( oSheet, nCol, nRow )
  dim cell as object

  y = nRow
  cell = oSheet.getCellByPosition(nCol,y)
  while  cell.getType() <> com.sun.star.table.CellContentType.EMPTY
    y = y+1
    cell = oSheet.getCellByPosition(nCol,y)
  wend
  getFilledRow = y-1 
End Function

Inserting, deleting rows (macro record).

'-----------------------------------------------------------------------
' insert row before the current selected position
sub insertRow
  dim document   as object
  dim dispatcher as object

  rem get access to the document
  document   = ThisComponent.CurrentController.Frame
  dispatcher = createUnoService("com.sun.star.frame.DispatchHelper")

  dispatcher.executeDispatch(document, ".uno:InsertRows", "", 0, Array())
end sub
'-----------------------------------------------------------------------
' insert row before the current selected position
sub insertRowIdx(sheet, index, elements)
  sheet.Rows.insertByIndex(index, elements)
end sub
'-----------------------------------------------------------------------
' insert row before the current selected position
sub insertColIdx(sheet, index, elements)
  sheet.Columns.insertByIndex(index, elements)
end sub

'-----------------------------------------------------------------------
' insert row before the current selected position
sub deleteRowIdx(sheet, index, elements)
  sheet.Columns.removeByIndex(index, elements)
end sub
'-----------------------------------------------------------------------
' insert row before the current selected position
sub deleteColIdx(sheet, index, elements)
  sheet.Rows.removeByIndex(index, elements)
end sub
'-----------------------------------------------------------------------
sub deleteColByName(name)
  rem ------------------------------------------------------------------
  rem define variables
  dim document   as object
  dim dispatcher as object
  rem ------------------------------------------------------------------
  rem get access to the document
  document   = ThisComponent.CurrentController.Frame
  dispatcher = createUnoService("com.sun.star.frame.DispatchHelper")

  rem ------------------------------------------------------------------
  dim args1(0) as new com.sun.star.beans.PropertyValue
  args1(0).Name = "Flags"
  args1(0).Value = name

  dispatcher.executeDispatch(document, ".uno:DeleteCell", "", 0, args1())
end sub

Copying, moving, deleting ranges of cells.

'-----------------------------------------------------------------------
' copy the range rngSrc to range starting at cell clDst
sub copyRng( rngSrc, clDst )
	Dim clDstAdr 
	Dim rngSrcAdr 

	rngSrcAdr = rngSrc.getRangeAddress()
  clDstAdr = clDst.getCellAddress()
  shDst.copyRange( clDstAdr, rngSrcAdr )
end sub

'-----------------------------------------------------------------------
' copy the range rngSrc to range starting at cell clDst
sub copyRng( rngSrc, clDst )
  Dim clDstAdr 
  Dim rngSrcAdr 

  rngSrcAdr = rngSrc.getRangeAddress()
  clDstAdr = clDst.getCellAddress()
  shDst.copyRange( clDstAdr, rngSrcAdr )
end sub

'-----------------------------------------------------------------------
' move the range rngSrc to range starting at cell clDst
' !!!TODO not tested yet
sub moveRng( sheet, rngSrc, clDst )
  Dim clDstAdr As New com.sun.star.table.CellAddress
  Dim rngSrcAdr As New com.sun.star.table.CellRangeAddress

  rngSrcAdr = rngSrc.getRangeAddress()
  clDstAdr = clDst.getCellAddress()
  sheet.moveRange( clDstAdr, rngSrcAdr )
end sub

'-----------------------------------------------------------------------
' delete the range rng of sheet and shift the underlaying cells up
' !!!TODO not tested yet
sub delRng( sheet, rng )
  Dim rngAdr As New com.sun.star.table.CellRangeAddress

  rngAdr = rng.getRangeAddress()
  sheet.removeRange(rngAdr, com.sun.star.sheet.CellDeleteMode.UP)
  ' NONE the current values remain in their current position. 
  ' UP   the cells at and below the delete position are moved upwards. 
  ' LEFT the cells at and to the right of the delete position are moved 
  '   to the left. 
  ' ROWS the rows after the delete position are moved upwards. 
  ' COLUMNS the columns after the delete position are moved to the left. 
end sub

'-----------------------------------------------------------------------
' insert the range rng of sheet and shift the underlaying cells up
' !!!TODO not tested yet
sub insertRng( sheet, rng )
  Dim rngAdr As New com.sun.star.table.CellRangeAddress

  rngAdr = rng.getRangeAddress()
  sheet.insertCells(rngAdr, com.sun.star.sheet.CellInsertMode.UP)
  ' NONE the current values remain in their current position. 
  ' UP   the cells at and below the insert position are moved upwards. 
  ' LEFT the cells at and to the right of the insert position are moved
  '   to the left. 
  ' ROWS the rows after the insert position are moved upwards. 
  ' COLUMNS the columns after the insert position are moved to the left. 
end sub

Compute functions of a range with

CellRange.computeFunction(com.sun.star.sheet.GeneralFunction.CMD)
'  for CMD  use one of
' SUM       sum of all numerical values 
' COUNT     total number of all values (including non-numerical values) 
' COUNTNUMS total number of all numerical values 
' AVERAGE   average of all numerical values 
' MAX       largest numerical value 
' MIN       smallest numerical value 
' PRODUCT   product of all numerical values 
' STDEV     standard deviation 
' VAR       variance 
' STDEVP    standard deviation based on the total population 
' VARP      variance based on the total population 

Dim ReplaceDescriptor As Object
ReplaceDescriptor = Sheet.createReplaceDescriptor()
ReplaceDescriptor.SearchString = "is"
ReplaceDescriptor.ReplaceString = "was"
Sheet.ReplaceAll(ReplaceDescriptor)

Dim Flags As Long
Flags = com.sun.star.sheet.CellFlags.FLAG + _
...
rng.clearContents(Flags)
' with FLAG one of
' VALUE   numerical values that are not formatted as date or time 
' DATETIME numerical values that are formatted as date or time 
' STRING  strings 
' ANNOTATION comments that are linked to cells 
' FORMULA  formulas 
' HARDATTR direct formatting of cells 
' STYLES   indirect formatting 
' OBJECTS  drawing objects that are connected to cells 
' EDITATTR character formatting that only applies to parts of the cells 

Starting a Shell script.

Shell( CommandString, ParameterString, 2, true )