REM  *****  BASIC  *****
'  *****  BASIC Cell and sheet functions *****
' see also OOAndrewwmacro pp 130 and thereabouts
' these all rely on Cell and Sheet having been declared globally
Option Explicit
Sub PutStr_name(CR as string, mystring as string)
' write string to a cell with Col,Row name eg "A3"
Cell = Sheet.getCellRangeByName(CR) 
'xray cell
Cell.string = mystring
'Cell.iscellbackgroundtransparent = true
'Cell.charColor = RGB(255,21,56)
'cell.charshadowed = true
cell.orientation = 0
End Sub

Sub PutStr_pos(c as integer,r as integer, mystring as string)
' write string to a cell with c and r as integers eg 3,6
Cell = Sheet.getCellByPosition(c,r) 
Cell.string = mystring
End Sub

Sub PutNum_name(CR as string, mynum as double, optional numform as integer)
' write number to a cell with Col,Row name eg "A3" or a named range 
Cell = Sheet.getCellRangeByName(CR) 
Cell.value = mynum
if (NOT IsMissing(numform))  then Cell.NumberFormat  =numform   '23658.000
   'oCell.setString("oops")
   'oCell.setFormula("=FUNCTION()")
   'oCell.IsCellBackgroundTransparent = TRUE
'  Cell.Color = RGB(255,201,56)
End Sub

Sub PutNum_pos(c as integer,r as integer, mynum as double, optional numform as integer)
' write number to a cell with c and r as integers eg 3,6
Cell = Sheet.getCellByPosition(c,r) 
Cell.NumberFormat=numform
Cell.value = mynum
End Sub

Function GetStr_name(CR as string) as string
' get string in a cell with Col,Row name eg "A3"
Cell = Sheet.getCellRangeByName(CR)
GetStr_name = Cell.string
End Function

Function GetStr_pos(c as integer,r as integer) as string
' get string in a cell with c and r as integers eg 3,6
Cell = Sheet.getCellByPosition(c,r)
GetStr_pos = Cell.string
End Function

Function GetNum_name(CR as string) as double
' get number in a cell with Col,Row name eg "A3"
Cell = Sheet.getCellRangeByName(CR) 
GetNum_name = Cell.value
End Function

Function GetNum_pos(c as integer,r as integer) as double
' get number in a cell with c and r as integers eg 3,6
Cell = Sheet.getCellByPosition(c,r)
GetNum_pos = Cell.value
End Function

'******************************************************************

Sub PutCells_data (Myarray as object, norows as integer, nocols as integer, Optional Mystartcell)
'fills cells in sheet with data placed in range Myrange (or default starting at A1)
'based on cellrange.getDataArray and cellrange.SetDataArray
'useful general template for placing a large array data on the sheet
on Error goto Lerr
dim oCellRange, v(), r%, c%, Myrange$
'Sheet = thisComponent.sheets.getByName("t")
If IsMissing(MyStartcell) then 
	Myrange = "A1:" & colnumbertostring(nocols-1) & mid(str(norows),2) 'str adds a leading space if a +ve no
else 
' must adjust for a start cell beyond A1 by adding the offset of mystartcell to norows and nocols
' and recalculating end address of range
	Myrange = Mystartcell & ":" & colnumbertostring( strtocolnumber( getalpha( mystartcell) )+nocols-1 ) _
	 & mid( str( norows + val( getnum( mystartcell) ) ) ,2 )
endif
oCellRange = Sheet.GetCellRangeByName(Myrange)
v() = oCellRange.getDataArray() 'establishes size and placement of v()
'fill with data
for r = 0 to norows -1
	for c = 0 to nocols -1
		v(r)(c) =Myarray(r * nocols + c) ' limit)  'this array addessing is interesting-more like C
'		putstr_pos(c,r, Myarray(r * nocols + c) )  'slower by a factor of ~10
	next c
next r
oCellRange.setDataArray(v())                            'v() argument important
exit sub
Lerr:
MsgBox "PutCells Error " & Err & ": " & Error$ + chr(13) + "At line : " + Erl + chr(13) + Now , 16 ,"an error occurred"
end sub

Function GetCells_data ( norows as integer, nocols as integer, Optional Mystartcell) as object
'fills cells in sheet with data placed in range Myrange (or default starting at A1)
'based on cellrange.getDataArray and cellrange.SetDataArray
'useful general template for placing a large array data on the sheet
'on Error goto Lerr
dim oCellRange, v(), r%, c%, Myrange$, Sheet, MyArray(0 to norows*nocols)
Sheet = thisComponent.Sheets(0)
'Sheet = thisComponent.sheets.getByName("Sheet1")
If IsMissing(MyStartcell) then 
	Myrange = "A1:" & colnumbertostring(nocols-1) & mid(str(norows),2) 'str adds a leading space if a +ve no
else 
' must adjust for a start cell beyond A1 by adding the offset of mystartcell to norows and nocols
' and recalculating end address of range
	Myrange = Mystartcell & ":" & colnumbertostring( strtocolnumber( getalpha( mystartcell) )+nocols-1 ) _
	 & mid( str( norows + val( getnum( mystartcell) ) ) ,2 )
endif
oCellRange = Sheet.GetCellRangeByName(Myrange)

v() = oCellRange.getDataArray() 'establishes size and placement of v()
'get data in cells
for r = 0 to norows -1
	for c = 0 to nocols -1
	Myarray(r * nocols + c)  =  v(r)(c)
	next c
next r
getcells_data = Myarray
exit function
Lerr:
MsgBox "GetCells Error " & Err & ": " & Error$ + chr(13) + "At line : " + Erl + chr(13) + Now , 16 ,"an error occurred"
end function

'**********************************************************************************************
function testfornumber(Byval str as string) as object
'test string for a number and return as a number or a string
select case vartype(str)
case 2 to 5
	testfornumber = 1
end select
end function
'***************************************************************
Sub SelCol_pos(colIndex as integer)
'select a column in the first sheet ('ember index origin
  Dim oCol
  oCol = Sheet.getColumns.getByIndex(colindex)
  ThisComponent.getCurrentController.select(oCol)
End Sub

Sub SelCol_name(colName as string)
'select a column in the first sheet by name
  Dim oCol
  oCol = Sheet.getColumns.getByName(colName)
  ThisComponent.getCurrentController.select(oCol)
End Sub

Sub SelRow_pos(rowIndex as integer)
'select a row in the first sheet ('ember index origin
  Dim oRow
  oRow = Sheet.getRows.getByIndex(rowindex)
  ThisComponent.getCurrentController.select(oRow)
End Sub

Sub SelRow_name(rowNumber as string)
'select a row in the first sheet by its Number (IO = 1)
  Dim oRow, svc
  Dim index as integer
  index = rowNumber
  oRow = Sheet.getRows.getByIndex(index - 1) 'as getByName doesn't exist for getRows
  ThisComponent.getCurrentController.select(oRow)
End Sub

sub PrintNamedRanges
For i = 0 To thiscomponent.NamedRanges.Count - 1 
	print 	thiscomponent.NamedRanges.GetbyIndex(i).Name	
Next
end sub
'**********************************************************
Sub FormatNum_pos(c as integer,r as integer, optional numform as integer)
Cell = Sheet.getCellByPosition(c,r) 
Cell.NumberFormat=numform
end Sub

Sub FormatRange_name(CR as string, optional numform%, optional _weight%, optional _posture%, optional _underline%, _
optional _font$, optional _height$ ,  optional _textcolor&,  optional _backcolor&)
'range can be in error if contents or so were deleted
'get rid of any ' 
Cell = Sheet.getCellRangeByName(CR) 
Cell.NumberFormat = numform

' following in Andrew
Cell.CharWeight =_weight     ' or eg :   com.sun.star.awt.FontWeight.BOLD
  'DONTKNOW (0), THIN(50), ULTRALIGHT, LIGHT, SEMILIGHT,
  'NORMAL (100), SEMIBOLD, BOLD (150), ULTRABOLD, BLACK (200)


Cell.CharPosture = _posture						'com.sun.star.awt.FontSlant.OBLIQUE
 'NONE (0), OBLIQUE, ITALIC(1), DONTKNOW, REVERSE_OBLIQUE, REVERSE_ITALIC

 Cell.CharUnderline =  _underline% ' com.sun.star.awt.FontUnderline.SINGLE 
'NONE, SINGLE, DOUBLE, DOTTED, DONTKNOW, DASH, LONGDASH,
  'DASHDOT, DASHDOTDOT, SMALLWAVE, WAVE, DOUBLEWAVE, BOLD,
  'BOLDDOTTED, BOLDDASH, BOLDLONGDASH, BOLDDASHDOT,
  'BOLDDASHDOTDOT, BOLDWAVE       0 to 18

Cell.CharFontName= _font  '"Nimbus Sans L"   ' "Times New Roman" '   '"Courier New"  or whatever is available "URW Chancery L"
Cell.CharHeight=  _height  '"10"

Cell.charcolor = _textcolor  ' an RGB (  ,   ,   ) value
Cell.CellBackColor = _backcolor  ' ditto or -1 to cancel
end Sub
 
Function GetNumberFormat(oNumberFormats as Object, FormatString as String, oLocale as Object) as Long
Dim lLocDateFormat as Long
	lLocDateFormat = oNumberFormats.QueryKey(FormatString, oLocale, True)
	If lLocDateFormat = -1 Then
		lLocDateFormat = oNumberFormats.addNew(FormatString, oLocale)
	End If
	GetNumberFormat() = lLocDateFormat
End Function

Sub FormatSheet(celladdress as string, optional formatstr as string,  _
optional _weight%, optional _posture%, optional _underline%, optional _font$, optional _height$,  optional _textcolor&,  optional _backcolor& )
'formats a cell or range of cells according to the options eg celladdress = "a1:b5", formatstr="#,###.0000"
Dim oFormats as Object
Dim str1 as String
on Error goto Lerr
if ismissing(formatstr)  then formatstr = "0"
oFormats = thiscomponent.getNumberFormats()
FormatRange_name(celladdress, GetNumberFormat(oFormats,formatstr, thiscomponent.CharLocale), _
_weight, _posture, _underline, _font, _height, _textcolor,  _backcolor)
RemoveApostrophe(celladdress) 'this ensures that the apostrophe in front of true numbers is removed 
exit sub
Lerr:
MsgBox "FormatNumber_name  " & Err & ": " & Error$ + chr(13) + "At line : " + Erl + chr(13) + Now , 16 ,"an error occurred"
end sub 

sub RemoveApostrophe(optional MyRange as string)
'http://api.openoffice.org/docs/common/ref/com/sun/star/util/XReplaceable.html
' replace all leading apostrophes in a defined range or selected ranges on the current sheet
' find term is :      .*   ---finds any leading character and therefore all cells with content
' replace term is   & (or $0)  --- concatenates them with anything following or preceding the & in this case nothing
' apostrophe is not reinserted
dim xSel,XreplaceDescr,xFound
if ismissing(myrange) then
 xSel = ThisComponent.getcurrentselection
else
 xSel = Sheet.getCellRangeByName(myrange)
endif
 xReplaceDescr = xSel.createReplaceDescriptor()
 xReplaceDescr.SearchString = ".*" 'alternatively "^."
 xReplaceDescr.ReplaceString = "&" 'or  "$0"
 xReplaceDescr.SearchRegularExpression = True
 xFound = xSel.replaceAll( xReplaceDescr )
end sub

'*******************************************************************************************
Sub testEndColRow
  Dim nEndCol As Integer
  Dim nEndRow As Integer
             
  nEndCol = getLastUsedColumn()        'see functions below
  nEndRow = getLastUsedRow()
                                                                        
  ' Then do as you please, e.g.
  Cell = Sheet.GetCellByPosition( nEndCol + 1, nEndRow + 1 )      
     
  Cell.String = "test"
  ThisComponent.CurrentController.Select(Cell)
End Sub

Function getLastUsedColumn() as Integer
'from ooAndrewMacro example
  Dim oCursor As Object
  Dim aAddress As Variant
  Cell = Sheet.GetCellbyPosition( 0, 0 )
  oCursor = Sheet.createCursorByRange(Cell)
  oCursor.GotoEndOfUsedArea(True)
  aAddress = oCursor.RangeAddress
  getLastUsedColumn = aAddress.EndColumn
End Function

'Function GetLastUsedRow(oSheet as Object) as Integer
'Dim oCell As Object
'Dim oCursor As Object
'Dim aAddress As Variant
'	oCell = oSheet.GetCellbyPosition(0, 0)
'	oCursor = oSheet.createCursorByRange(oCell)
'	oCursor.GotoEndOfUsedArea(True)
'	aAddress = oCursor.RangeAddress
'	GetLastUsedRow = aAddress.EndRow
'End Function

Function GetActiveRange(Sheet as object) as String
'finds last active cell on specified sheet and return range as object
  Dim CellName as string
  Cellname = ConvertToColumn(getLastUsedColumn+1)
  Cellname = CellName & getLastUsedRow(Sheet)+1  'getlastusedrow in Tools module
  GetActiveRange = "a1:" & Cellname
end Function

Function GetSelection as string
'return the current selection runs slightly faster than GetActiveRange
dim oSelect, oSelectColumn,oSelectRow
dim CountColumn&, CountRow&
dim oSelectSC, oSelectEC, oSelectSR, oSelectER
  oSelect=ThisComponent.CurrentSelection.getRangeAddress
  oSelectColumn=ThisComponent.CurrentSelection.Columns
  oSelectRow=ThisComponent.CurrentSelection.Rows
  CountColumn=oSelectColumn.getCount 'counts columns
 ' CountRow=oSelectRow.getCount  'counts rows 
 'oSelectColumn.insertByIndex(0,1) 'inserts new column
  oSelectSC=oSelectColumn.getByIndex(0).getName
  oSelectEC=oSelectColumn.getByIndex(CountColumn-1).getName
  oSelectSR=oSelect.StartRow+1
  oSelectER=oSelect.EndRow+1
  GetSelection = oSelectSC + oSelectSR + ":" + oSelectEC + oSelectER
end Function
  
Sub ClearActiveRange
' clear the whole range found by running getLastUsedColumn & getLastUsedRow
on error goto Lerr
  Dim nEndCol As Integer
  Dim nEndRow As Integer
  Dim oCellRange As Object
  
	oCellRange =  Sheet.getCellRangeByName(GetActiveRange(Sheet))
    oCellRange.clearContents(_
    com.sun.star.sheet.CellFlags.VALUE OR _
    com.sun.star.sheet.CellFlags.DATETIME OR _
    com.sun.star.sheet.CellFlags.STRING OR _
    com.sun.star.sheet.CellFlags.ANNOTATION  OR _
    com.sun.star.sheet.CellFlags.FORMULA OR _
    com.sun.star.sheet.CellFlags.HARDATTR OR _
    com.sun.star.sheet.CellFlags.STYLES   OR _
    com.sun.star.sheet.CellFlags.OBJECTS OR _
    com.sun.star.sheet.CellFlags.EDITATTR)
    exit sub
    Lerr:
    msgbox("error in Clearactiverange")
end Sub

Sub PutCells_test 'works !!!!!!!!!!!!!!
'fills cells in specified range with increasing numbers 
'based on getDataArray and SetDataArray
'useful general template for placing a large array data on the sheet
'don't select whole sheet !!!!
on Error goto Lerr
'dim thisSelection
dim cellzone, v(), r&, c&, selcols, selrows, nocols&, norows&
dim sheet
'Sheet = thisComponent.sheets.getByName("Sheet1")
'thisSelection = ThisComponent.CurrentSelection.getRangeAddress

selcols = ThisComponent.CurrentSelection.Columns
selrows = ThisComponent.CurrentSelection.Rows
nocols = selcols.getCount
norows = selrows.getCount

Set cellzone = Sheet.getCellRangebyName(GetSelection)
v() = cellzone.getDataArray()
'fill with numbers
for r = 0 to norows -1
	for c = 0 to nocols -1
		v(r)(c) = r * nocols + c  'this array addessing is interesting-more like C
	next c
next r
cellzone.setDataArray(v()) 'v() argument important
exit sub
Lerr:
MsgBox "PutCells Error " & Err & ": " & Error$ + chr(13) + "At line : " + Erl + chr(13) + Now , 16 ,"an error occurred"
end sub



Sub FillCellsWithNumbers1()
'fills cells in specified range with increasing numbers
'using fillSeries 
'don't select whole sheet !!!!
dim oDoc,oSheet2,oCell2, oTarget,sString
oDoc = ThisComponent
oSheet2 = oDoc.getSheets().getByIndex( 0 )
oTarget = oSheet2.getCellRangeByName(GetSelection)
'oTarget = oSheet2.getCellRangeByName("A1:E42") ' 0, 0, 4, 41 )
'sString = "278"' "=IF(A1=0;" & Chr$( 34 ) & Chr$( 34 ) & ";MROUND(((B1-A1)/A1)*100;0.01))"
'oCell2 = oSheet2.getCellByPosition (0,0 )
'oCell2.setValue( sString )
'oTarget.fillAuto(0,3) ' based on the contents of the number of cells (in this case 3 down)
oTarget.fillSeries(1,1,0,3,8000) 'fill across with step 3
oTarget.fillSeries(0,1,0,5,8000) 'fill down with step 5
'direction 0= down, 1 ->; mode 0,none,1 = linear,2 = multipl;
'step or multiplier; upper limit of number
'oDoc.calculate()
oTarget.setDataArray( oTarget.getDataArray ) 
end sub

Sub FillCell
dim oCellRange
oCellRange = Sheet.getCellRangeByName(GetSelection)
DivideRange(oCellRange,1)
end sub

Sub DivideRange(oRange as object, dDivisor As Double)
  Dim oData()
  Dim oRow()
  Dim i As Integer
  Dim j As Integer

  oData() = oRange.getDataArray()
  For i = LBound(oData()) To UBound(oData())
    oRow() = oData(i)
    For j = LBound(oRow()) To UBound(oRow())
      oRow(j) = i * j 'oRow(j) / dDivisor
    Next
  Next
  oRange.setDataArray(oData())
End Sub

Sub Fillcellswithnumbers0 'very slow by comparison with setDataArray methods
'for testing purposes fill selection with numbers
dim oSelect, oSelectColumn,oSelectRow
dim c%,r%,nc%,nr%, n&
dim CountColumn&, CountRow&, oSelectSC$, oSelectSR&
    oSelect=ThisComponent.CurrentSelection.getRangeAddress
    oSelectColumn=ThisComponent.CurrentSelection.Columns
	oSelectSC=oSelectColumn.getByIndex(0).getName
	oSelectSR=ThisComponent.CurrentSelection.getRangeAddress.StartRow+1
 '	CountColumn=ThisComponent.CurrentSelection.Columns.getCount 'counts columns
'	CountRow=ThisComponent.CurrentSelection.Rows.getCount  'counts rows 
	nc =StrToColNumber(oSelectSC) 'this translates A to 0
	nr = oSelectSR-1
	for c = nc to nc + ThisComponent.CurrentSelection.Columns.getCount-1
		for r = nr to nr + ThisComponent.CurrentSelection.Rows.getCount-1
			PutNum_pos(c,r,n)
			n = n+1
		next r
	next c
end Sub

Sub SearchARange(RangeName as string, str1 as string)
  ' Author: Andrew Pitonyak; modified by norman bakker
  'searches current sheet in range RangeName for first occurrence of str1
  Dim oRange
  Dim oFoundCell
  dim s as string
  oRange = Sheet.getCellRangeByName(RangeName)
  oFoundCell = SimpleSheetSearch(str1, oRange, False)
  
  If IsEmpty(oFoundCell) OR IsNull(oFoundCell) Then
    s = s & "Text not found" & CHR$(10)
  Else
  s = "Found " & str1 & " at "
  s = s & ConvertToColumn(oFoundCell.CellAddress.Column +1) & _
            oFoundCell.CellAddress.Row +1  & CHR$(10)
  End If
  msgbox s
End Sub

Function SimpleSheetSearch(sString$, oRange, bWholeWord As Boolean) As Variant
' Find the first cell that contains sString$ in the range oRange on the current sheet
' If bWholeWord is True, then the cell must contain ONLY the text
' as indicated. If bWholeWord is False, then the cell must only contain
' the requested string.
  Dim oDescriptor
  Dim oFound
  ' Create a descriptor from a searchable document.
  oDescriptor =oRange.createSearchDescriptor()
  ' Set the text for which to search and other 
  ' http://api.openoffice.org/docs/common/ref/com/sun/star/util/SearchDescriptor.html 
  With oDescriptor
    .SearchString = sString$
    ' These all default to false
    ' SearchWords forces the entire cell to contain only the search string
    .SearchWords = bWholeWord
    .SearchCaseSensitive = False
  End With
  ' Find the first one
  oFound = oRange.findFirst(oDescriptor)
  SimpleSheetSearch = oFound
  
  ' Do you really want to find more instances
  ' You can continue the search using a cell if you want!
  'Do While Not IsNull(oFound)
  '  Print oFound.getString()
  '  oFound = Sheet.findNext( oFound, oDescriptor)
  'Loop
End Function

Private Function StrToColNumber(ByVal m As String) As Long
'converts column addresses in uppercase to decimal - max limit ZZZZ
'mine A=0 !!!
Dim val as long
dim as0%
m = Ucase(m) 'ensure is uppercase
as0% = 64 'Asc("A") - 1
Select case Len(m)
case 1: val = Asc(m) -as0
case 2: val = (Asc(Mid(m,1,1)))*26 + Asc(Mid(m,2,1))-27*as0
case 3: val = (Asc(Mid(m,1,1)))*676 + (Asc(Mid(m,2,1)))*26 + Asc(Mid(m,3,1))-703 * as0
case 4: val = (Asc(Mid(m,1,1)))*17576 + (Asc(Mid(m,2,1)))*676+(Asc(Mid(m,3,1)))*26 _
															+Asc(Mid(m,4,1))- 18279*as0  
case else val = 0
end select
StrToColNumber = val-1
End Function

' Columns are numbered starting at 0 where 0 corresponds to A
' They run as A-Z,AA-AZ,BA-BZ,...,IV
' This is esentially a question of how do you convert a Base 10 number to
' a base 26 number. 
' Note that the_column is passed by value!
Function ColNumberToString(ByVal the_column As Long) As String
  Dim s$
 ' print "no is ", the_column
  'Save this so I do NOT modify the parameter.
  'This was an icky bug that took me a while to find
  'A = 0
  Do while the_column >= 0
    s$ = Chr(65 +  the_column MOD 26) + s$
    the_column =  the_column \ 26 - 1
  Loop 
   ColNumberToString = s$
End Function

Function GetAlpha(str as string) as string
'filter out all non alphanumerics A-Z,a-z from string
dim s$, res$, r%
for r = 1 to Len(str)
	s = mid(str,r,1)
	select case asc(s)
	case 65 to 90,  97 to 122 'A-Z,a-z'
		res = res & s
	end select
next r
GetAlpha = res
end function

Function GetNum(str as string) as string
'filter out all alphanumerics A-Z,a-z from string
dim s$, res$, r%
for r = 1 to Len(str)
	s = mid(str,r,1)
	select case asc(s)
	case 48 to 57 '0-9'
		res = res & s
	end select
next r
GetNum = res
end function

sub ShowNumberFormatCodes(optional number)
dim r%,c%,m%
if ismissing(number) then number = 1234.567890
for r= 0 to 19 step 2 
	for c = 1 to 13
	putnum_pos(c,r+5,m)
	putnum_pos(c,r+6,number,m)
	m = m+1
	next c
next r
end sub

sub AutoColumnWidth(oRange as string)
'based on macro recording - set auto column width over defined range
dim dispatcher as object, document as object
document   = ThisComponent.CurrentController.Frame
dispatcher = createUnoService("com.sun.star.frame.DispatchHelper")

dim args1(0) as new com.sun.star.beans.PropertyValue
args1(0).Name = "ToPoint"
args1(0).Value = oRange
dispatcher.executeDispatch(document, ".uno:GoToCell", "", 0, args1())

dim args2(0) as new com.sun.star.beans.PropertyValue
args2(0).Name = "aExtraWidth"
args2(0).Value = 200
dispatcher.executeDispatch(document, ".uno:SetOptimalColumnWidth", "", 0, args2())
args1(0).Value = "$A$1"
dispatcher.executeDispatch(document, ".uno:GoToCell", "", 0, args1())
end sub

Sub ClearDefinedRange(myrange as string)
'Author: Andrew Pitonyak
'email:   andrew@pitonyak.org 
on error goto Lerr:
  Dim oDoc As Object, oSheet As Object, oSheets As Object
  Dim oCellRange As Object
  Dim nSheets As Long

'  oDoc = ThisComponent
'  oSheets = oDoc.Sheets
'  nSheets = oDoc.Sheets.Count
  ' Get the first sheet, as in 0, 1, 2
 'oSheet = oSheets.getByIndex(0)
  ' You can use a range such as "A1:B2"
  oCellRange = Sheet.getCellRangeByName(myrange)
  oCellRange.clearContents(_
    com.sun.star.sheet.CellFlags.VALUE OR _
    com.sun.star.sheet.CellFlags.DATETIME OR _
    com.sun.star.sheet.CellFlags.STRING OR _
    com.sun.star.sheet.CellFlags.ANNOTATION OR _
    com.sun.star.sheet.CellFlags.FORMULA OR _
    com.sun.star.sheet.CellFlags.HARDATTR OR _
    com.sun.star.sheet.CellFlags.STYLES OR _
    com.sun.star.sheet.CellFlags.OBJECTS OR _
    com.sun.star.sheet.CellFlags.EDITATTR)
    exit sub
    Lerr: 
    msgbox "error in cleardefinedrange"
End Sub

Sub RemoveSheet(index%) 
'removes sheet with index number index
dim oDoc
oDoc = ThisComponent
Sheet = oDoc.getSheets().getByIndex(index)
oDoc.getSheets().removeByName(  oDoc.getsheets().elementnames(index)
end sub

Sub AddSheetBefore(index&, optional name$)
'adds a sheet before the current one with optional name
dim oDoc, oSheet
oDoc = ThisComponent
oDoc.getSheets().insertNewByName( "Sheet1", 0 )
end Sub

'----------
'http://www.oooforum.org/forum/viewtopic.phtml?t=10315
'   Removes a control.
'   Parameters:
'      cControlName   -   The control's name.
'      cFormName      -   The form's name.  (Usually: "Standard")
'   Optional Parameters:
'      nPage         -   The page number of a document that has multiple
'                      draw pages, such as Draw, Impress or Calc.
'                     Passing -1 is the same as omitting this argument.
'      oDoc         -   The document model.  If not supplied, then ThisComponent is used.
'                     It is okay to pass one of the document's controllers or frame instead.
'
Sub FormRemoveControl( ByVal cControlName As String,_
            ByVal cFormName As String,_
            Optional nPage,_
            Optional oDoc )
   dim oControlShape, oDrawPage
   ' If no document specified, then use this document.
   If IsMissing( oDoc ) Then
      oDoc = ThisComponent
   EndIf
   ' If no page number specified, then pass -1.   
   If IsMissing( nPage ) Then
      nPage = -1
   EndIf
   
   oDrawPage = GetDrawPage( nPage, oDoc )

   ' Find the control shape for the draw page.
   oControlShape = FormFindControlShape( cControlName, cFormName, nPage, oDoc)
   ' Remove the control shape from the draw page.
  oDrawPage.dispose  'remove( oControlShape )
 ' xray oDrawPage
   ' Removing the control from the form is not necessary (and not possible)
   ' since the control already has been removed from the form by oDrawPage.remove
   
End Sub