Ask Your Question

rammy's profile - activity

2020-01-16 21:57:07 +0200 marked best answer Function with arguments returns #null after load

Hi all,

I use the LibreOffice Version: 6.0.7.3 on a Ubuntu system: Build ID: 1:6.0.7-0ubuntu0.18.04.10

I have the following problem with the LibreOffice.Calc:

I use the following functions in the LibreOffice.Basic:

Function Test20(year_) As Double
 Test20=year_
End Function

Function Test30() As Double
 Test30=30
End Function

When I call these functions in LibreOffice.Basic they produce the expected results. When I call these function in LibreOffice.Calc then I get also the results as expected. But when I now save the calc document to disc and load it again then the function, Test20(), with the parameter will produce the result #null. The other function, Test30(), gives the expected result. The only way to get it working again is to change the basic code and undo the changes. After compiling the code again then the functions works again.

Can anybody give me a clue of what is happing here and how to solve the problem. I already tryed it with the link:

https://stackoverflow.com/questions/39227757/libreoffice-macro-always-show-null-after-reopening-the-file

but that does not solve my problem.

Regards.

rammy

(Slightly edited for better readability by @Lupp.)

2020-01-08 00:42:29 +0200 edited answer Function with arguments returns #null after load

Hi all, I investigated the problem further and found that the couse of the problem is the use of the, Option VBASupport

2020-01-07 05:59:07 +0200 received badge  Notable Question (source)
2020-01-06 20:41:56 +0200 received badge  Popular Question (source)
2020-01-06 00:55:05 +0200 received badge  Famous Question (source)
2020-01-06 00:27:53 +0200 edited question Function with arguments returns #null after load

Function with arguments returns #null Hi all, I use the LibreOffice Version: 6.0.7.3 on a Ubuntu system: Build ID: 1:6.

2020-01-06 00:27:47 +0200 edited question Function with arguments returns #null after load

Function with arguments returns #null Hi all, I use the LibreOffice Version: 6.0.7.3 on a Ubuntu system: Build ID: 1:6.

2019-06-10 18:07:47 +0200 edited answer Macro: Listing multiple cell ranges

Hello Ratslinger, Thanks for your response. You responded with: Your line: rng_ = sh_.getCellRangeByName("c2:e

2019-06-10 13:26:29 +0200 edited answer Macro: Listing multiple cell ranges

Hello Ratslinger, Thanks for your response. You responded with: Your line: rng_ = sh_.getCellRangeByName("c2:e

2019-06-10 13:24:55 +0200 edited question Macro: Listing multiple cell ranges

Macro: Listing multiple cell ranges Hi all, I am using Calc, LibreOffice version 6.0.7.3. I have a problem with finding

2019-06-10 13:20:51 +0200 edited question Macro: Listing multiple cell ranges

Macro: Listing multiple cell ranges Hi all, I am using Calc, LibreOffice version 6.0.7.3. I have a problem with finding

2019-06-10 13:20:02 +0200 edited question Macro: Listing multiple cell ranges

Macro: Listing multiple cell ranges Hi all, I am using Calc, LibreOffice version 6.0.7.3. I have a problem with finding

2019-06-10 13:18:23 +0200 edited answer Macro: Listing multiple cell ranges

Hello Ratslinger, Thanks for your response. You responded with: Your line: rng_ = sh_.getCellRangeByName("c2:e

2019-06-10 13:17:39 +0200 edited question Macro: Listing multiple cell ranges

Macro: Listing multiple cell ranges Hi all, I am using Calc, LibreOffice version 6.0.7.3. I have a problem with finding

2019-06-10 13:12:05 +0200 answered a question Macro: Listing multiple cell ranges

Hello Ratslinger, Thanks for our response. You responded with: Your line: rng_ = sh_.getCellRangeByName("c2:e7

2019-06-08 20:55:02 +0200 edited answer IsBlank using in Basic always '0'

Lupp gives the answer to work with. I also tryed the answer from Ratslinger. Thank you both for replying to my question

2019-06-08 20:44:12 +0200 edited question IsBlank using in Basic always '0'

IsBlank using in Basic always '0' Hi all, I am using Calc from LibreOffice 6.0.7.3 suitte. I want to use the Calc func

2019-06-08 20:38:07 +0200 answered a question IsBlank using in Basic always '0'

Lupp gives the answer to work with. I also tryed the answer from Ratslinger but that gives problems with finding the cor

2019-06-08 20:33:18 +0200 marked best answer IsBlank using in Basic always '0'

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 & "'"
2019-06-08 20:33:18 +0200 received badge  Scholar (source)
2019-06-08 18:20:38 +0200 received badge  Notable Question (source)
2019-06-07 23:12:29 +0200 edited question IsBlank using in Basic always '0'

IsBlank using in Basic always '0' Hi all, I am using Calc from LibreOffice 6.0.7.3 suitte. I want to use the Calc func

2019-06-07 22:56:39 +0200 received badge  Famous Question (source)
2019-06-07 20:03:22 +0200 received badge  Popular Question (source)
2019-06-07 00:32:39 +0200 received badge  Enthusiast
2019-06-06 23:16:42 +0200 received badge  Organizer (source)
2019-06-06 23:15:37 +0200 edited question Macro: Listing multiple cell ranges

Macro: Listing multiple cell ranges Hi all, I am using Calc, LibreOffice version 6.0.7.3. I have a problem with finding

2019-06-06 23:11:52 +0200 asked a question IsBlank using in Basic always '0'

IsBlank using in Basic always '0' Hi all, I am using Calc from LibreOffice 6.0.7.3 suitte. When I use the code below i

2019-06-01 23:35:12 +0200 received badge  Notable Question (source)
2019-06-01 21:52:56 +0200 edited question Macro: Listing multiple cell ranges

Macro: Listing multiple cell ranges Hi all, I am using Calc, LibreOffice version 6.0.7.3. I have a problem with finding

2019-06-01 21:48:56 +0200 edited question Macro: Listing multiple cell ranges

Macro: Listing multiple cell ranges Hi all, I am using Calc, LibreOffice version 6.0.7.3. I have a problem with finding

2019-06-01 17:12:53 +0200 received badge  Popular Question (source)
2019-06-01 15:13:37 +0200 edited question Macro: Listing multiple cell ranges

Macro: Listing multiple cell ranges Hi all, I am using Calc, LibreOffice version 6.0.7.3. I have a problem with finding

2019-06-01 15:08:40 +0200 edited question Macro: Listing multiple cell ranges

Macro: Listing multiple cell ranges Hi all, I am using Calc, LibreOffice version 6.0.7.3. I have a problem with finding

2019-06-01 15:02:34 +0200 edited question Macro: Listing multiple cell ranges

Macro: Listing multiple cell ranges Hi all, I am using Calc, LibreOffice version 6.0.7.3. I have a problem with finding

2019-06-01 15:02:34 +0200 received badge  Editor (source)
2019-06-01 14:46:32 +0200 edited question Macro: Listing multiple cell ranges

Macro: Listing multiple cell ranges Hi all, I have a problem with finding the correct code to list the address of a mul

2019-05-31 01:33:28 +0200 asked a question Macro: Listing multiple cell ranges

Macro: Listing multiple cell ranges Hi all, I have a problem with finding the correct code to list the address of a mul