Ask Your Question
0

IsBlank using in Basic always '0'

asked 2019-06-06 23:11:52 +0200

rammy gravatar image

updated 2019-06-08 20:44:12 +0200

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 & "'"
edit retag flag offensive close merge delete

Comments

Edited for clarity.

Ratslinger gravatar imageRatslinger ( 2019-06-06 23:18:25 +0200 )edit

single Cells have already the Property .String respective the Method .getString()

print thisComponent.CurrentSelection.String = ""
'prints True or False '

So please ask about the real Task, not about you think how to solve it.

karolus gravatar imagekarolus ( 2019-06-07 07:44:33 +0200 )edit

2 Answers

Sort by » oldest newest most voted
0

answered 2019-06-08 02:14:54 +0200

Lupp gravatar image

ISBLANK() expects a reference to a cell when called by a formula in Calc.
Do you know what type of object such a refence is when passed to the body of the ISBLANK() function?
I don't. But I would expect it's not a cell object.

If you want to ask a cell object if the cell is blank, the FunctionAccess service is inapt, and even if you succeeded with making it work it would be a useless complication.

A blank cell myCell is earmarked by the propertyvalue myCell.Type = 0.

edit flag offensive delete link more
0

answered 2019-06-08 20:38:07 +0200

rammy gravatar image

updated 2019-06-08 20:55:02 +0200

Lupp gives the answer to work with. I also tryed the answer from Ratslinger.

Thank you both for replying to my question.

edit flag offensive delete link more

Comments

@rammy

Never gave an answer to this question, only edited your question.

Also, please don't post this as an answer. Instead use add a comment under answer responding to. Answers are to answer original questions.

Ratslinger gravatar imageRatslinger ( 2019-06-08 21:06:14 +0200 )edit
Login/Signup to Answer

Question Tools

1 follower

Stats

Asked: 2019-06-06 23:11:52 +0200

Seen: 104 times

Last updated: Jun 08 '19