Bug 91744 - Macro failure when adding rows with named range scope restricted to the sheet
Summary: Macro failure when adding rows with named range scope restricted to the sheet
Status: RESOLVED DUPLICATE of bug 93127
Alias: None
Product: LibreOffice
Classification: Unclassified
Component: BASIC (show other bugs)
Version:
(earliest affected)
unspecified
Hardware: x86 (IA32) Linux (All)
: medium normal
Assignee: Not Assigned
URL:
Whiteboard:
Keywords:
Depends on:
Blocks:
 
Reported: 2015-05-30 10:03 UTC by David Lochrin
Modified: 2016-08-19 15:00 UTC (History)
1 user (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 David Lochrin 2015-05-30 10:03:22 UTC
This may be another manifestation of Bug ID 77459.

A BASIC macro is adding (n) rows to a spreadsheet beginning at the row indicated by a selected cell (RefRow).  The contents of the selected row are copied to the 'n' new rows then the values deleted, leaving formats & formulae.

As previously written for OpenOffice V2, the code added the rows so the originally selected row was moved down. The contents were then copied from the selected row (now in its new position further down the spreadsheet) into the added rows, and the values deleted leaving formats & formulae. That worked fine until recently because, it turns out, named ranges of data were always defined globally.

However a problem arises if rows are added within a named range of data AND the scope of the named range is confined to the sheet. Suppose we have a named range Fred = $A$2:$A$100 and we add ten rows somewhere in the middle of that range. Fred should be incremented to $A$2:$A$110 but instead it's decremented to $A$2:$A$90. If the scope is global everything is fine.

The problem lies in the code which copies the selected row to the added rows - around "oActSheet.copyRange (CellAddress, CellRangeAddress)". The selected row must be copied forward to higher-numbered rows rather than backwards to lower-numbered rows.  Code with & without the bug is shown below.

THIS DEMONSTRATES THE PROBLEM:

             CellRangeAddress.Sheet = SheetNbr
             CellRangeAddress.StartColumn = 0
             CellRangeAddress.EndColumn = EndColumn
             CellRangeAddress.StartRow = RefRow
             CellRangeAddress.EndRow = RefRow+n-1
             oActSheet.insertCells (CellRangeAddress, com.sun.star.sheet.CellInsertMode.ROWS)
    '
    '        Copy the the row originally selected from column-0 to 'EndColumn' to each of the inserted rows,
    '        then delete all values, date-time values, and strings.
    '
             CellRangeAddress.StartRow = RefRow+n
             CellRangeAddress.EndRow = RefRow+n
             CellAddress.Sheet = SheetNbr
             CellAddress.Column = 0
             For i = RefRow To RefRow+n-1
                CellAddress.Row = i
                oActSheet.copyRange (CellAddress, CellRangeAddress)
             Next i
             Flags = com.sun.star.sheet.CellFlags.VALUE + _
                     com.sun.star.sheet.CellFlags.DATETIME + _
                     com.sun.star.sheet.CellFlags.STRING + _
                     com.sun.star.sheet.CellFlags.ANNOTATION
             oActSheet.getCellRangeByPosition(0,RefRow,EndColumn,RefRow+n-1).clearContents(Flags)


BUT THIS WORKS FINE

    oCellRangeAddress.Sheet = SheetNbr
    oCellRangeAddress.StartColumn = 0
    oCellRangeAddress.EndColumn = EndColumn
    oCellRangeAddress.StartRow = RefRow+1
    oCellRangeAddress.EndRow = RefRow+n
    oActSheet.insertCells (oCellRangeAddress, com.sun.star.sheet.CellInsertMode.ROWS)
    '
    oCellRangeAddress.StartRow = RefRow
    oCellRangeAddress.EndRow = RefRow
    oCellAddress.Sheet = SheetNbr
    oCellAddress.Column = 0
    For i = RefRow+1 To RefRow+n
       oCellAddress.Row = i
       oActSheet.copyRange (oCellAddress, oCellRangeAddress)
       Next i
    '
    Flags = com.sun.star.sheet.CellFlags.VALUE + _
            com.sun.star.sheet.CellFlags.DATETIME + _
            com.sun.star.sheet.CellFlags.STRING + _
            com.sun.star.sheet.CellFlags.ANNOTATION
    oActSheet.getCellRangeByPosition (0,RefRow,EndColumn,RefRow+n-1).clearContents(Flags)


Thanks & Regards,
David Lochrin
dlochrin@d2.net.au
Comment 1 David Lochrin 2015-05-31 01:46:52 UTC
I posted a description of this problem in the OpenOffice Forum before lodging this bug report.  A comment was made there that named ranges in OpenOffice may still only have global scope, and the writer suggested this is better.

However I argued strongly that databases and spreadsheets are different things despite some functional overlap for small applications, and offering the choice of "global" or "sheet" scope was entirely appropriate.

See https://forum.openoffice.org/en/forum/viewtopic.php?f=20&t=77415#p353760

Regards,
David Lochrin
Comment 2 Alex Thurgood 2016-08-18 14:07:02 UTC
@David : in order to attempt to move this bug report from UNCONFIRMED to some other, hopefully more positive status, we would need a sample Calc sheet including the macro code (and any buttons) with instructions on how to use it. I kind of understand what you are getting at, but I'm not a macro programmer (at best a very basic one).

Setting to NEEDINFO

Please set back to UNCONFIRMED once you have provided the requested information.
Comment 3 Alex Thurgood 2016-08-18 14:10:04 UTC
Please also provide the version of LibreOffice with which you encountered the problem.
Comment 4 David Lochrin 2016-08-19 02:01:31 UTC
Alex, the status of this bug was changed to NEEDINFO on 5th August 2015 by Joel Madero, then to NEW on 20th August by raal <raal@post.cz>.

On 22nd March I received this notification:

-------
https://bugs.documentfoundation.org/show_bug.cgi?id=93127

--- Comment #10 from Commit Notification <libreoffice-commits@lists.freedesktop.org> ---
Eike Rathke committed a patch related to this issue.
It has been pushed to "libreoffice-5-1":

http://cgit.freedesktop.org/libreoffice/core/commit/?id=5e655ecfe007238080941077c6dc6187005e91fa&h=libreoffice-5-1

Resolves: tdf#93127 copying cells does not modify named expressions

It will be available in 5.1.3.

The patch should be included in the daily builds available at
http://dev-builds.libreoffice.org/daily/ in the next 24-48 hours. More
information about daily builds can be found at:
http://wiki.documentfoundation.org/Testing_Daily_Builds

Affected users are encouraged to test the fix and report feedback.
-------

I lodged sample a spreadsheet in response to Joel Madero's email, and the bug was solidly reproducible with LibreOffice 4.3.5.2.  I've since tested the fix on 5.1.4.2 and it seems OK.

I notice the bug number on the above notification (93127) is different to that now shown on Bugzilla (91744).  As mentioned above, I did describe it on the OpenOffice Forum before lodging the bug report here, so is it possible that's where the confusion lies?

Please email me if this doesn't resolve things.

Regards,
David Lochrin
Comment 5 Alex Thurgood 2016-08-19 15:00:52 UTC
@David : thanks for your reply. In view of your comments, I'm going to set this as a DUP of bug 93127

*** This bug has been marked as a duplicate of bug 93127 ***