Hi all,
I am using Calc from LibreOffice 6.0.7.3 suitte.
I want to use the Calc function IsBlank(cell) with the uno service “com.sun.star.sheet.FunctionAccess” in LibreOffice Basic.
When I use the code below in LO Basic then I get the correct result for that function, so the code works.
dim oFunction as object : oFunction = createUnoService("com.sun.star.sheet.FunctionAccess")
dim sh_ as object : sh_ = ThisComponent.Sheets.getByIndex(2)
dim rng_ as object : rng_ = sh_.GetCellRangeByName("B18")
dim row_ as integer : row_= rng_.RangeAddress.startrow
dim column_ as integer : column_= rng_.RangeAddress.startcolumn
print "'" & oFunction.callFunction( "COUNTIFS", Array( sh_.getCellRangeByName("b1:b10"), ">=20" ) ) & "'"
However if I change the called function, “COUNTIFS”, in “IsBlank” and change the parameter accordingly by using the code below LO Basic stops and reports different errors.
dim oFunction as object : oFunction = createUnoService("com.sun.star.sheet.FunctionAccess")
dim sh_ as object : sh_ = ThisComponent.Sheets.getByIndex(2)
dim rng_ as object : rng_ = sh_.GetCellRangeByName("B18")
dim row_ as integer : row_= rng_.RangeAddress.startrow
dim column_ as integer : column_= rng_.RangeAddress.startcolumn
print "'" & oFunction.callFunction("IsBlank", Array(sh_.GetCellByPosition(column_, row_))) & "'"
'LO Basic produce the error: BASIC runtime error. An exception occurred, Type:
com.sun.star.lang.IllegalArgumentException, Message: .
print "'" & oFunction.callFunction("IsBlank", Array(sh_.getCellRangeByName("B18:B18"))) & "'"
'LO Basic produce the error: BASIC runtime error. An exception occurred, Type: com.sun.star.container.NoSuchElementException, Message: .
print "'" & oFunction.callFunction("IsBlank", Array(rng_.AbsoluteName)) & "'"
print "'" & oFunction.callFunction( "IsBlank", Array("""" & rng_.AbsoluteName & """")) & "'"
'LO Basic accepts the code, but gives the wrong result ‘0’ instead of ‘True’.
When I use the cellformula =IsBlank(B18) in a Calc sheet cell, I get the result True for a Blank cell and False when that cell has a value.
Can someone tell me what is going wrong here and how to solve to problem.
Thanks in advance.
By using the code below the problem is solved.
dim sh_ as object : sh_ = ThisComponent.Sheets.getByIndex(2)
dim rng_ as object : rng_ = sh_.GetCellRangeByName("B18")
print "'" & rng_.type & "'"