LibreOffice Macros: Manipulation of Ranges - Conditional Formatting and Queries 1.

Sub SetConditionalStyle
Dim CellRange
'Cell range to use
Dim CondFormat
'Conditional format object
Dim CondSet(2) As New com.sun.star.beans.PropertyValue
REM Sheets support returning a cell range based on UI type names.
CellRange = ThisComponent.Sheets(0).getCellRangeByName("B2:D6")
CondFormat = CellRange.ConditionalFormat
CondSet(0).Name = "Operator"
CondSet(0).Value = com.sun.star.sheet.ConditionOperator.LESS
CondSet(1).Name = "Formula1"
CondSet(1).Value = 0
CondSet(2).Name = "StyleName"
CondSet(2).Value = "MyStyle2"

CondFormat.addNew(CondSet())
CellRange.ConditionalFormat = CondFormat
End Sub


'/////////////////////////////////////


Sub EmptyCells
Dim ObjCell
Dim CellRanges
Dim AddrsArray()
Dim Addr
Dim CalcSheet
Dim i As Long

Dim nRow As Long
Dim nCol As Long
Dim s As String
Dim sep as String

CellRange = ThisComponent.Sheets(0).getCellRangeByName("B2:D6")
sep = " , "

CellRanges = CellRange.queryEmptyCells()

CellRanges.CellBackColor=RGB(0, 254, 0)


AddrsArray() = CellRanges.getRangeAddresses()
For i = 0 To UBound(AddrsArray())
Addr = AddrsArray(i)
For nRow = Addr.StartRow To Addr.EndRow
For nCol = Addr.StartColumn To Addr.EndColumn
ObjCell = CellRange.Spreadsheet.getCellByPosition(nCol, nRow)
s = s & ObjCell.AbsoluteName & sep
Next
Next
Next
ResultCells = s
msgbox ResultCells
End Sub


'////////////////////////////////////////////////////



Sub FilledCells

Dim ObjCell
Dim CellRanges
Dim AddrsArray()
Dim Addr
Dim CalcSheet

Dim i As Long
Dim nRow As Long
Dim nCol As Long
Dim s As String

Dim sep as String
Dim result as String

CellRange = ThisComponent.Sheets(0).getCellRangeByName("B2:D6")
sep = " , "

CellRanges = CellRange.queryContentCells(_
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.FORMULA)


AddrsArray() = CellRanges.getRangeAddresses()
For i = 0 To UBound(AddrsArray())
Addr = AddrsArray(i)
For nRow = Addr.StartRow To Addr.EndRow
For nCol = Addr.StartColumn To Addr.EndColumn
ObjCell = CellRange.Spreadsheet.getCellByPosition(nCol, nRow)
s = s & ObjCell.AbsoluteName & sep
Next
Next
Next

ResultCells = s
msgbox ResultCells
End Sub


'////////////////////////////////////////////

Sub UFNECIR
Dim CellRange
Dim sep as String
Dim result as String
CellRange = ThisComponent.Sheets(0).getCellRangeByName("A1:H12")
sep = " , "

'flags = (com.sun.star.sheet.CellFlags.VALUE OR com.sun.star.sheet.CellFlags.DATETIME)
flags = (16 OR 1)


'VALUE = 1 			'selects constant numeric values that are not formatted as dates or times.  
'DATETIME = 2	 	'selects constant numeric values that have a date or time number format.  
'STRING = 4 		'selects constant strings.  
'ANNOTATION = 8 	'selects cell annotations.  
'FORMULA = 16 		'selects formulas.  

result = ExtractedCellsInRange(CellRange,sep, flags)

msgbox result
End Sub

Function ExtractedCellsInRange(CellRange, sep$, flags) As String
Dim ObjCell

Dim AddrsArray()
Dim Addr
Dim CalcSheet
Dim i As Long

Dim nRow As Long
Dim nCol As Long
Dim s As String


CellRanges = CellRange.queryContentCells(flags)

AddrsArray() = CellRanges.getRangeAddresses()
For i = 0 To UBound(AddrsArray())
Addr = AddrsArray(i)
For nRow = Addr.StartRow To Addr.EndRow
For nCol = Addr.StartColumn To Addr.EndColumn
ObjCell = CellRange.Spreadsheet.getCellByPosition(nCol, nRow)
s = s & ObjCell.AbsoluteName & sep
Next
Next
Next

ExtractedCellsInRange = s
End Function



'/////////////////////////////////////////
Sub FormQuery
Dim CellRange
Dim sep as String
Dim result as String
CellRange = ThisComponent.Sheets(0).getCellRangeByName("A1:H12")
sep = " , "

flags = (4)
'VALUE = 1 	selects numeric results.  
'STRING = 2	selects non-numeric results.  
'ERROR =4 	selects errors.  

result =  QueryFormulaCellsInRange(CellRange,sep, flags)

msgbox result
End Sub



Function QueryFormulaCellsInRange(CellRange, sep$, flags) As String
Dim ObjCell

Dim AddrsArray()
Dim Addr
Dim CalcSheet
Dim i As Long

Dim nRow As Long
Dim nCol As Long
Dim s As String


CellRanges = CellRange.queryFormulaCells(flags)

AddrsArray() = CellRanges.getRangeAddresses()
For i = 0 To UBound(AddrsArray())
Addr = AddrsArray(i)
For nRow = Addr.StartRow To Addr.EndRow
For nCol = Addr.StartColumn To Addr.EndColumn
ObjCell = CellRange.Spreadsheet.getCellByPosition(nCol, nRow)
s = s & ObjCell.AbsoluteName & sep
Next
Next
Next

QueryFormulaCellsInRange = s
End Function