Bug 47479 - LibO Calc Macro .getCellRangeByName with named range
Summary: LibO Calc Macro .getCellRangeByName with named range
Status: RESOLVED WORKSFORME
Alias: None
Product: LibreOffice
Classification: Unclassified
Component: BASIC (show other bugs)
Version:
(earliest affected)
3.5.1 release
Hardware: x86-64 (AMD64) Windows (All)
: high major
Assignee: Not Assigned
URL:
Whiteboard:
Keywords:
Depends on:
Blocks: Macro-UNOAPI
  Show dependency treegraph
 
Reported: 2012-03-18 12:42 UTC by Kossem
Modified: 2024-01-24 19:59 UTC (History)
7 users (show)

See Also:
Crash report or crash signature:


Attachments

Note You need to log in before you can comment on or make changes to this bug.
Description Kossem 2012-03-18 12:42:23 UTC
I got an error message after updating Calc to LibO 3.5(.0 and still now with .1.2) when running a previously flawless macro.
The offending (?) statement is  .getCellRangeByName("namedcell")  where namedcell is a global named cell range (of one cell, in this instance).

Response from system:

    Basic runtime error.
    An exception occurred
    Type: com.sun.star.uno.RuntimeException
    Message:.

The (empty) returned message is quite cryptic, without any error number to start searching.

- Same error when tried with a multiple cells named range, and with different (plain and short) names for the range.
- It works well when the named cell range is replaced by its coordinates value (e.g. "K12").
- Works also ok with getCellByPosition (but I want it to work correctly, as it did before, with a named cell range).

Thanks in advance for a fast and complete solution, and/or explanation.
Cheers.
Comment 1 Jeremy 2013-04-08 11:31:34 UTC
I am getting the same error on 4.0.2.2 and previous release.  I only get the error if I have deleted a range.
The following code will recreate the problem.  Sheet1 has a single range defined as "MYRANGE" and there is some data in the following cells.

Sub Main
Dim oSheet as Object
DIM cBlock as New com.sun.star.table.CellRangeAddress
dim myRow as integer

oSheet = ThisComponent.sheets("Sheet1")
myRow=oSheet.getCellRangeByName("MYRANGE").RAngeAddress.EndRow


cBlock.Sheet=0
cBlock.StartColumn=0
cBlock.EndColumn=4
cBlock.StartRow=myRow+1
cBlock.EndRow=myRow+2
osheet.removeRange(cBlock, com.sun.star.sheet.CellDeleteMode.UP)

myRow=oSheet.getCellRangeByName("MYRANGE").RAngeAddress.EndRow ' << fails here

End Sub
Comment 2 Jeremy 2013-05-16 09:46:20 UTC
Still a problem in 4.0.3.3
Comment 3 QA Administrators 2015-04-19 03:20:48 UTC Comment hidden (obsolete)
Comment 4 Buovjaga 2015-06-15 12:45:09 UTC
Repro per comment 1.

Win 7 Pro 64-bit Version: 5.1.0.0.alpha1+
Build ID: 01a189abcd9a4ca472a74b3b2c000c9338fc2c91
TinderBox: Win-x86@39, Branch:master, Time: 2015-06-14_07:46:28
Locale: fi-FI (fi_FI)
Comment 5 Xisco Faulí 2017-06-12 10:54:09 UTC
Changing version back to the earliest version affected.
Comment 6 QA Administrators 2018-06-17 02:43:42 UTC Comment hidden (obsolete)
Comment 7 Buovjaga 2019-08-11 10:23:52 UTC
(In reply to Jeremy from comment #1)
> I am getting the same error on 4.0.2.2 and previous release.  I only get the
> error if I have deleted a range.
> The following code will recreate the problem.  Sheet1 has a single range
> defined as "MYRANGE" and there is some data in the following cells.
> 
> Sub Main
> Dim oSheet as Object
> DIM cBlock as New com.sun.star.table.CellRangeAddress
> dim myRow as integer
> 
> oSheet = ThisComponent.sheets("Sheet1")
> myRow=oSheet.getCellRangeByName("MYRANGE").RAngeAddress.EndRow
> 
> 
> cBlock.Sheet=0
> cBlock.StartColumn=0
> cBlock.EndColumn=4
> cBlock.StartRow=myRow+1
> cBlock.EndRow=myRow+2
> osheet.removeRange(cBlock, com.sun.star.sheet.CellDeleteMode.UP)
> 
> myRow=oSheet.getCellRangeByName("MYRANGE").RAngeAddress.EndRow ' << fails
> here
> 
> End Sub

I put numbers in A1-8 and defined A1-A4 as MYRANGE. I ran the macro and it did not fail. The macro removes two rows after the range.

Is there something I am missing?

Arch Linux 64-bit
Version: 6.4.0.0.alpha0+
Build ID: 37fc9f51a8de11d40632e8cda17ccf1fa4b1f503
CPU threads: 8; OS: Linux 5.2; UI render: default; VCL: gtk3; 
Locale: fi-FI (fi_FI.UTF-8); UI-Language: en-US
Calc: threaded
Built on 6 August 2019
Comment 8 QA Administrators 2021-09-18 03:26:54 UTC Comment hidden (obsolete)
Comment 9 Mike Kaganski 2021-12-19 12:30:25 UTC
Eike: wasn't there a change that introduced sheet-local ranges [1]? Could that change be related to this?

[1] https://wiki.documentfoundation.org/ReleaseNotes/3.4#Calc
Comment 10 QA Administrators 2023-12-20 03:12:55 UTC Comment hidden (obsolete)
Comment 11 Rafael Lima 2024-01-24 19:59:13 UTC
As of LO 7.6 the macro in Comment #1 works fine if the document has a named range "MYRANGE". The macro will fail if the named range does not exist.

So I'm closing this as WFM. Fell free to reopen if needed.

Tested with

Version: 7.6.4.1 (X86_64) / LibreOffice Community
Build ID: 60(Build:1)
CPU threads: 16; OS: Linux 6.5; UI render: default; VCL: kf5 (cairo+xcb)
Locale: pt-BR (pt_BR.UTF-8); UI: en-US
Ubuntu package version: 4:7.6.4-0ubuntu0.23.10.1
Calc: threaded